- dump and reload a table
Dump and Reload
I measured the time to dump and reload a table. The table has 10 million rows, 3 int columns and no indexes. MyISAM and InnoDB storage engines were used. The InnoDB tables were on a tmpfs filesystem to avoid disk IO. The test was performed in 4 steps:
- Load the data into a MyISAM table using load data infile. This measures the performance of inserting without the overhead of parsing SQL statements.
- Dump the table using mysqldump --extended-insert. This measures the performance of the MySQL client library and the select code path in the storage engine and the server.
- Reload the output of mysqldump into a MyISAM table. This measures the performance of the MySQL client library, the mysql command-line tool, the parser and the storage engine.
- Reload the output of mysqldump into an InnoDB table.
- mysqldump is much faster in 5.0. The client library was changed to do fewer read() calls when fetching a query result. This is great.
- the parser was very fast in 4.0 and 5.0.54 has returned some of that performance. Inserting a large number of rows is frequently bound by the performance of the parser and this was much slower in early 5.0 releases.
- because of a bug in the 5.0.54 mysql command line client, I used the version from 5.0.51 for the 5.0.54 tests.
|Version||load data infile||mysqldump||insert to MyISAM||insert to InnoDB|
This is a simple benchmark distributed with MySQL. I ran the version provided with 4.0.26.
- InnoDB was used for all tables.
- Constants that determine the amount of work done for each test were changed.
- Tests were run on a server with 4 CPU cores.
- 32-bit binaries were used.