import mysql db dump from v. 4 to v 5.5

DDL of create table is changed, instead of TYPE ENGINE is used
one option is to change TYPE=MyISAM to ENGINE=MyISAM

wireshark - network protocol analyzer

http://www.wireshark.org/

50 things to know before migrating Oracle to MySQL

http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/

notes on mysql while reading ... (3)

High Performance MySQL

Indexes

...
We’re not just being picky: these two kinds of index accesses perform differently. The
range condition (i.e. BETWEEN, >, <)makes MySQL ignore any further columns in the index, but the multiple equality condition (i.e. IN) doesn’t have that limitation.


repairing

CHECK TABLE
REPAIR TABLE
myisamchk

Updating Index Statistics
API calls: records_in_range( ) and info( )
MySQL’s optimizer is cost-based
ANALYZE TABLE
Each storage engine implements index statistics differently
MyISAM stores statistics on disk, and ANALYZE TABLE performs a full index scan to compute cardinality. The entire table is locked during this process.
InnoDB does not store statistics on disk, but rather estimates them with random index dives the first time a table is opened. -> less accurate statistics, no blocking

You can examine the cardinality of your indexes with the SHOW INDEX FROM command -> Cardinality; or by using INFORMATION_SCHEMA.STATISTICS

Fragmentation - Row fragmentation & Intra-row fragmentation
MyISAM tables may suffer from both types of fragmentation, but InnoDB never fragments
short rows. To defragment data, you can either run OPTIMIZE TABLE or dump and reload the data.

hm they recommend using of summary table instead of counting of the records of the real table

MySQL’s ALTER TABLE performance can become a problem with very large tables. ALTER TABLE lets you modify columns with ALTER COLUMN, MODIFY COLUMN, and CHANGE COLUMN. All three do different things. MODIFY COLUMN always cause table rebuilds.

Building MyISAM Indexes Quickly - ALTER TABLE test.load_data DISABLE KEYS; -- load the data ALTER TABLE test.load_data ENABLE KEYS;
! Unfortunately, it doesn’t work for unique indexes, because DISABLE KEYS applies only to nonunique indexes

The InnoDB Storage Engine
Clustering by primary key: All InnoDB tables are clustered by the primary key, which you can use to your advantage in schema design.
No cached COUNT(*) value: Unlike MyISAM or Memory tables, InnoDB tables don’t store the number of rows in the table, which means COUNT(*) queries without a WHERE clause can’t be
optimized away and require full table or index scans.

telnet for windows vista / windows 7

http://www.leateds.com/2009/telnet-for-windows-vista-windows-7/

to enable telnet client on windows vista / windows 7
Control Panel -> Programs -> turn windows features on or off -> Telnet client

notes on mysql while reading ... (2)

High Performance MySQL

Finding Bottlenecks: Benchmarking and Profiling
A benchmark measures your system’s performance. In contrast, profiling helps you find where your application spends the most time or consumes the most resources.

*) http_load - windows port: http://www.orenosv.com/misc/
*) MySQL’s BENCHMARK( ) - SET @input := 'hello world'; SELECT BENCHMARK(1000000, MD5(@input)); (an exec var to avoid cache hits)
*) SysBench -

notes on mysql while reading ...

High Performance MySQL

(hm there is a risk already to copy and paste the whole book)

Storage engine - the 3rd level of MySQL architecture is responsible for... among others:
- storage engines can implement there policies and lock granularities (although alter table issues a table lock regardless of storage engine)
- row locks are implemented by storage engines e.g. by InnoDB, Falcon

(
REPEATABLE READ is MySQL’s default transaction isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
InnoDB supports all four ANSI standard isolation levels

)

- InnoDB and Falcon solve phantom reads with multi-version concurrency control (MVCC)
- the underlying storage engines implement transactions: MySQL AB provides three transactional storage engines: InnoDB, NDB Cluster, and
Falcon. Several third-party engines are also available; the best-known engines right
now are solidDB and PBXT.

(
MySQL operates in AUTOCOMMIT mode by default -> SHOW VARIABLES LIKE 'AUTOCOMMIT'; SET AUTOCOMMIT = 1;
Changing the value of AUTOCOMMIT
has no effect on nontransactional tables, such as MyISAM or Memory tables
commit is forced by DDL, LOCK
MySQL will not usually warn you or raise errors if you do transactional operations on a nontransactional table
InnoDB uses a two-phase locking protocol; SELECT ... LOCK IN SHARE MODE ; SELECT ... FOR UPDATE; MySQL server! (not storage engine) also supports the LOCK TABLES and UNLOCK TABLES commands;
actually, InnoDB and others use row-locking mechanism with MVCC, InnoDB implements MVCC by storing with each row two additional, hidden values that record when the row was created and when it was expired (or deleted); the row stores the system version number at the time each event occurred. This is a number that increments each time a transaction begins. Each transaction keeps its own record of the current system version, as of the time it began
)

MySql storage engines:
SHOW TABLE STATUS LIKE 'user' \G
MyISAM tables created in MySQL 5.0 with variable-length rows are configured by default to handle 256 TB of data, using 6-byte pointers to the data records:
CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
ALTER TABLE mytable ENGINE = Falcon;

mysql

yeah, time to learn it

resources:

book:
http://www.highperfmysql.com/

web sites:
http://www.mysqlperformanceblog.com/