Planet MySQL :: Planet MySQL - Archives - MySQL Performance: MySQL 5.6 GA -vs- MySQL 5.5 @32...

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

Search

MySQL Links