Is MySQL getting faster over time? I think so. It is difficult to keep things fast as features are added, but many operations have gotten faster between 4.0.26 and 5.0.54 and the performance of 5.0.54 is much improved over 5.0.37. I ran a few tests to measure the difference:
- dump and reload a table
- sql-bench
The formatting of the tables below looked great when I previewed them using the blog editor, but not so good when published.
Dump and ReloadI 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.
Performance can change significantly between releases, and usually in the right direction:
- 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.
This lists the number of seconds to run each test.
Version |
load data infile |
mysqldump |
insert to MyISAM |
insert to InnoDB |
|---|
4.0.26 |
8.9 |
19.7 |
33.7 |
72.7 |
4.1.22 |
8.5 |
22.7 |
42.7 |
79.9 |
5.0.37 |
9.3 |
9.4 |
69.6 |
117.7 |
5.0.54 |
9.2 |
9.6 |
50.8 |
52.5 |
sql-benchThis 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.
5.0.54 is the fastest. This lists the number of seconds to run each section of the test.
Release |
Total Time |
Alter |
ATIS |
big-tables |
connect |
create |
insert |
select |
transactions |
wisconsin |
|---|
4.0.26 |
390 |
92 |
44 |
35 |
41 |
59 |
31 |
39 |
32 |
27 |
4.1.22 |
412 |
85 |
42 |
50 |
52 |
62 |
33 |
32 |
36 |
31 |
5.0.37 |
422 |
42 |
45 |
66 |
67 |
61 |
34 |
38 |
48 |
36 |
5.0.54 |
378 |
40 |
45 |
58 |
54 |
56 |
33 |
36 |
39 |
30 |