I used most of the advice from a previous blog post to build & configure 5.6 for peak performance and that included disabling the performance schema at compile time. All binaries were compiled with the same version of gcc and used jemalloc. For 4.0, 5.0 and 5.6 the built-in version of InnoDB. For 5.1 the plug-in was used.
Two of the tests below report the time in seconds to reload a table and a lower value is better. The other test reports QPS for sysbench point queries and a higher value is better. To avoid (or add) confusion from looking at one graph where less is better and another where more is better I list a value for relative performance below and use that in the graphs. For relative performance a larger value is better and the result for 4.0.30 is the baseline. For the other binaries performance was always worse and the relative performance is less than 1.
I also ran the sql-bench tests from the 4.0.30 release with all MySQL versions. The total time to run all tests was 789, 921, 911 and 1093 seconds for 4.0.30, 5.0.85, 5.1.63 and 5.6.11. Much of the 5.6 regression was from the create tests but there were significant regression for many tests.
Bulk load
The first workload is bulk load. I used mysqldump to dump all rows for a sysbench table with 16M rows. For the important configuration variables: the InnoDB buffer pool was 64G, the doublewrite buffer was disabled, the binlog was disabled and I set innodb_flush_log_at_trx_commit=2. The test was repeated using two files created by mysqldump. The first used --opt and the second used --opt --skip-extended-insert.
Time to reload 16M rows
Input from mysqldump --opt
Relative-perf is Seconds-for-4.0.30 / Seconds
Seconds Relative-perf
4.0.30 143 1
5.0.85 178 0.80
5.1.63 176 0.81
5.6.11 203 0.70 -> no perf-schema
5.6.11 210 0.68 -> perf-schema
Time to reload 16M rows
Input from mysqldump --opt --skip-extended-insert
Relative-perf is Seconds-for-4.0.30 / Seconds
Seconds Relative-perf
4.0.30 966 1
5.0.85 1150 0.84
5.1.63 1201 0.80
5.6.11 1274 0.76
One source of overhead in 5.6 is the mysql client. For the first set of tests below that took 2 to 3 minutes the mysql client used 10 seconds of CPU time for 4.0.30 versus 26 seconds for 5.6. Why does it need 3X more CPU time? From CPU profile reports there is a lot more time in utf8 functions. I added results for 5.6.11 with perf-schema enabled & disabled at compile time. The table below has the CPU time (user + system) from the mysql client during the reload using the output from mysqldump --opt. There is a big regression from 4.0 to 5.1 mostly due to conversions to utf8. There is another regression from 5.1 to 5.6. I don't know why.
5.6 user 0m25.121s sys 0m0.629s -> 25.8 seconds
5.1 user 0m19.884s sys 0m0.747s -> 20.6 seconds
5.0 user 0m18.700s sys 0m0.853s -> 19.6 seconds
4.0 user 0m8.994s sys 0m0.923s -> 9.9 seconds
My server was using these character sets:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
Point lookup
The next workload is point queries from sysbench. These use auto-commit and fetch 1 row by primary key per query. The buffer pool was warmed prior to the test and cached all rows but the adaptive hash index was cold.
Queries per second from sysbench
Relative-perf is QPS / QPS-for-4.0.30
QPS Relative-perf
4.0.30 14898 1
5.0.85 12247 0.82
5.1.63 10538 0.71
5.6.11 10334 0.69
Posts
