Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
MySQL 5.6 is much faster on IO-bound, read-only workloads
+8 Vote Up -0 Vote Down
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 9 binaries. All used jemalloc.
  • orig5610+bp8+fastcs - unmodified MySQL 5.6.10 with innodb_buffer_pool_instances=8, metadata_locks_hash_instances=256, innodb_checksum_algorithm=crc32 and the PS disabled
  • orig5610+bp8 - same as orig5610+bp8+fastcs except innodb_checksum_algorithm=innodb
  • orig5610+bp1+fastcs - same as orig5610+bp8+fastcs except innodb_buffer_pool_instances=1
  • orig5610+bp1 - 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
26747   52738   86175   109836  108605  114046  orig5610+bp8+fastcs23844   48049   81434   102006  103290  108152  orig5610+bp8
26650   52007   85561   100739   92439  100941  orig5610+bp1+fastcs
24446   47464   79788    96777   92565  100211  orig5610+bp123429   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



Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.