Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
MySQL 5.6 versus 4.0 for a read-only workload
+2 Vote Up -0 Vote Down
I compared the performance of MySQL 5.6.11 versus 4.0.30 using a read-only workload with sysbench. Performance was much better for 5.6.11 in most cases. At low-concurrency MySQL 4.0 was a bit faster. MySQL 5.6.11 was faster at high-concurrency and when doing many page reads per second. The product has improved a lot since I started using MySQL.

I followed most of my advice on building and configuring MySQL 5.6 for peak performance. I used two test servers - one for the sysbench clients and the other for mysqld. Each server has 16 real cores and 32 with HT enabled. Each test server has 144 GB of memory and fast storage that can do ~150,000 16 KB page reads/second.

The tests selected 1 row by primary key per query and all queries used auto-commit mode. Tests were run in IO-bound and cached modes. For cached tests all of the tables were in the InnoDB buffer pool at test start, but the adaptive hash index might not be warm. For the IO-bound tests only a subset of the tables fit in the InnoDB buffer pool. Direct IO was used to avoid caching by the OS. The buffer pool was 64 GB for the cached tests and 2 GB for the IO-bound tests. The full my.cnf files are at the end of this post.

One group of tests used 1 table and was run for 1, 2, 4, 8, 16 and 32 concurrent clients. The other group of tests used 8 tables and was run for 8, 16, 32, 64, 128 and 256 concurrent clients. The database size was 4 GB for the 1 table tests and 32 GB for the 8 table tests. Thus for the IO-bound tests about 50% of the table is in cache for the 1-table test and about 7% is in cache for the 8-tables test.

IO bound

MySQL 5.6.11 was much faster than 4.0.30 except at low concurrency. Some of the changes that make this possible include multiple buffer pool instances and a more efficient LRU page replacement algorithm.

QPS, 1-table

    1      2      4      8     16     32  clients
 3224   6231  12705  26193  55477  92301  5.6.11
 3579   7160  13418  12646  10769  10184  4.0.30

QPS, 8-tables
    8     16     32     64    128    256  clients
22676  44121  71019  79787  79082  78269  5.6.11
17168  16520  14990  14852  14767  14696  4.0.30




Cached

Had I run the 1-table test for more than 32 clients I expect that the difference between 5.6.11 and 4.0.30 would have been significant as it was in the 8-table test. At low-concurrency 4.0.30 is faster.


QPS, 1-table
    1      2      4      8     16     32  clients
 5005  10065  20178  39595  83888 154852  5.6.11
 6130  11868  24391  45856 106598 133349  4.0.30

QPS, 8-tables
    8     16     32     64    128    256  clients
39895  76498 153421 227335 245511 244313  5.6.11
49603 101307  97728  77737  75790  75500  4.0.30



Configuration

This is the my.cnf file for 4.0.30:

[mysqld]
innodb_log_file_size=1900M
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=32
innodb_buffer_pool_size= --> 64G or 2G
max_connections=2000
table_cache=2000
key_buffer_size=200M
query_cache_size=0
query_cache_type=0

This is the my.cnf file for 5.6.11

innodb_log_file_size=1900M
innodb_doublewrite=1
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=80
innodb_file_format=barracuda
innodb_file_per_table
innodb_io_capacity=8000
innodb_lru_scan_depth=8000
innodb_checksum_algorithm=CRC32
metadata_locks_hash_instances=256
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=32
innodb_buffer_pool_size=64G
innodb_buffer_pool_instances=8
table-definition-cache=1000
table-open-cache=2000
table-open-cache-instances=1
max_connections=2000
key_buffer_size=200M
skip_log_bin
query_cache_size=0
query_cache_type=0



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.