As you already know, MySQL 5.6 GA is announced today and, there
is no doubt, it the best ever MySQL release for today!
If you've missed something about a long list of new features
coming with MySQL 5.6, I'm inviting you to read excellent
summaries written by Rob and Peter, as well many other detailed articles
coming this week..
While my story will be, of course, about performance ;-)
I'll present here the results I've obtained on Sysbench workloads
(Read-Only and Read-Write) and will update this article with more
details along the day (as some other tests are still
running)..
For my tests I'm using:
HW Config :
- Server : 32cores bi-thread (HT) Intel 2300Mhz, 128GB RAM
- OS : Oracle Linux 6.2
- FS : XFS mounted with "noatime,nodiratime,nobarrier,logbufs=8"
- MySQL : 5.6-GA, latest 5.5
MySQL Config :
#-------------------------------------------------- max_connections = 4000 key_buffer_size = 200M low_priority_updates = 1 sort_buffer_size = 2097152 back_log = 1500 query_cache_type = 0 # files innodb_file_per_table innodb_log_file_size = 1024M innodb_log_files_in_group = 3 innodb_open_files = 4000 table_open_cache = 8000 table_open_cache_instances = 16 # buffers innodb_buffer_pool_size = 32000M innodb_buffer_pool_instances = 32 innodb_log_buffer_size = 64M join_buffer_size = 32K sort_buffer_size = 32K # tune innodb_checksums = 0 innodb_doublewrite = 0 innodb_support_xa = 0 innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_use_native_aio =1 innodb_stats_persistent = 1 innodb_spin_wait_delay = 6 / 96 # perf special innodb_adaptive_flushing = 1 innodb_flush_neighbors = 0 innodb_read_io_threads = 16 innodb_write_io_threads = 4 innodb_io_capacity = 2000 innodb_purge_threads =1 innodb_adaptive_hash_index = 1 / 0 # Monitoring innodb_monitor_enable = '%' performance_schema = ON performance_schema_instrument = '%=on' #--------------------------------------------------
MySQL Tuning :
- the main key configuration options here which are making the difference are AHI (innodb_adaptive_hash_index) and Spin Delay (innodb_spin_wait_delay) -- while others are just good enough most of the time for the tested workloads..
- about the impact of the AHI I've wrote a lot in the past (and you may find all articles here), and the main dilemma with AHI is "to use or not to use".. - in many cases it helps a lot to avoid block locking and speed-up index access, but on a high concurrency may become a show stopper due high contention on its "btr_search_latch" rw-lock..
- while Spin Delay setting is requiring a very particular attention within MySQL 5.6, because it plays a critical role in managing of hot internal mutex and rw-locks contentions and may make you a difference in performance by 100% very easily ;-) (and you may find here all details about, while I may just add that there is no "silver bullet" e.g. no "universal" value which will be good enough for most cases -- all depends on your workload! - and that's why the default value (6) is the same as in MySQL 5.5, while it'll be up to you to find the most optimal for your workload; and as the setting is dynamic, you may try it live )..
- so, during my testing I was curious to see the best pair of AHI and Spin Delay settings for each workload
- then, keeping in mind scalability limits in MySQL 5.5 and 5.6, I'm replaying all the tests with MySQL server limited to run on 8, 16, 32 and 64 cores (64 means 32cores with HT-enabled, while on all other tests HT is disabled (only one thread per core))
So far, let me show you first the most fun comparison - the
Best-to-Best - where the highest results from one engine
(obtained on a given test and on any of tested configurations)
compared to another one. To remain "compatible" with historical
MySQL results I'll present results first in TPS
(transactions/sec), and then switch to QPS (query/sec) which I'm
preferring ;-)
The first test is the classic Sysbench OLTP_RO (Read-Only):
Sysbench OLTP_RO:
Then the "fun" case with the same OLTP_RO test, but using the
"feature" that table open/close is not involved for tables within
a transaction (the full explanation about is here )
Sysbench OLTP_RO-trx :
Then, "point-selects" - this kind of reads was historically
pretty good on MySQL, well improved on MySQL 5.5, but it's still
a pleasure to see that even here MySQL 5.6 is showing a
difference!
Sysbench OLTP_RO Point-Selects :
The most "painful" test - Simple-Ranges (and you may read from
here why it was so painful and frustrating
during MySQL 5.6 development, and understand why the gap between
MySQL 5.6 and 5.5 is so big ;-))
Sysbench OLTP_RO Simple-Ranges :
And then the Read-Write test, which is not the most heavy RW
workload, but still very representative to fire internal MySQL
and InnoDB contentions and other limits:
Sysbench OLTP_RW :
The decreasing TPS level since 256 concurrent users is still
wishing to get a higher level.. - but it's already x2 times
better comparing to MySQL 5.5, and, after all, work is
continuing, and then next MySQL version will be yet more better,
you may just trust me ;-)) (for the time being you may get a more
stable and higher performance level on bigger number of
concurrent users by tuning innodb thread concurrency setting (see
my previous article with results about))..
While today I'm simply enjoying to see MySQL 5.6 GA
release! ;-)
- it's really the best ever MySQL version today!
- it's way faster than MySQL 5.5 on many workloads!
- it's way better designed!
- it's way more "tunable"!
- it's way more "transparent" for performance analyzing!
- it's way better instrumented!
- it's just way better than everything you saw until now! ;-)
- and it comes with many new features in MySQL code you even did not hear around before 5.6 ;-)
So, my the only question : What are you waiting to test
your workload on MySQL 5.6 and prepare your database
migration?..
to be continued...
Rgds,
-Dimitri