MySQL 5.6 is much faster on IO-bound, read-only workloads

MySQL 5.6 is much faster on IO-bound, read-only workloads: A lot of work was done to make InnoDB faster for MySQL 5.6. The results are especially obvious for IO-bound read-only workloads. I have many performance tests to run and started with the easy ones. Yesterday I published results for a cached read-only workload and found a few problems that can and should be fixed (bugs 66473 & 68413). Even on this workload, there is a significant performance overhead from enabling the PS.

The improvement on this workload from MySQL 5.6 is amazing. The benefit from the innodb_fast_free_list option in the FB patch is also nice but I don't know yet whether that change is needed in 5.6. I have a lot of work to do to publish changes from the FB patch to make the launchpad branch current.

The results below are QPS from sysbench where each query fetches 1 row by primary key. The database has 8 tables with 16M rows/table and the total database size is ~32GB. The innodb_buffer_pool_size is 4GB. The test server has flash storage devices. The test used 8 sysbench processes running on one host and 1 mysqld process on another host. Each sysbench process was limited to queries from one table and each test server has 24 cores (12 real X 2 with HT).

This is a graph of the results for all binaries except orig5610+ps. I tested 7 binaries. All used jemalloc.

• orig5610+bp8 - unmodified MySQL 5.6.10 with innodb_buffer_pool_instances=8, metadata_locks_hash_instances=256 and the PS disabled
• orig5610+bp1 - the same as orig5610+bp8 except innodb_buffer_pool_instances=1
• orig5610+ps - same as orig5610+bp1 except the PS was enabled
• fb5163+ffl+fastcs - MySQL 5.1.63 + the Facebook patch, innodb_fast_free_list=1, innodb_fast_checksums=1
• fb5163+ffl - MySQL 5.1.63 + the Facebook patch with innodb_fast_free_list=1
• fb5163 - the same as above except innodb_fast_free_list=0
• orig5163 - unmodified MySQL 5.1.63

    8      16      32       64     128     256  concurrent clients
23844   48049   81434   102006  103290  108152  orig5610+bp8
24446   47464   79788    96777   92565  100211  orig5610+bp1
23429   46270   74870    87650   84117   93382  orig5610+ps
26276   51899   83320    87192   77942   82560  fb5163+ffl+fastcs
22329   41153   72793    81506   75005   80943  fb5163+ffl
22726   42127   53933    48098   45310   43364  fb5163
21986   40370   43958    38453   36765   34036  orig5163

DIGITAL JUICE