MySQL Performance comparison test: MySQL 5.6 ga-vs-mysql 5.5

Date: November 07, 2013 ⁄ category: database technical documents ⁄ I'm going to spit on the slot. Comment

MySQL 5.6 GA released, without a doubt, this is the best version of MySQL.

If you're not sure about the new features and improvements in the MySQL 5.6 version, you can get a look at this here.

and the main purpose of my article is to test performance.

I use Sysbench workloads (read-only/read-write) to test. Here's my test environment:

Hardware configuration:

• Server: 32-core Bi-thread (HT) Intel 2300Mhz, 128GB RAM
• Operating system: Oracle Linux 6.2
• File system: XFS mounted with "noatime,nodiratime,nobarrier,logbufs=8"
• Mysql:5.6-ga, latest 5.5

MySQL Configuration:

#--------------------------------------------------max_connections = 4000 Key_buffer_size = 200M Low_priority_ Updates = 1 Sort_buffer_size = 2097152 Back_log = Query_cache_type = 0# files innodb_file_per_table innodb_log_file_s ize = 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 = innodb_log_buffer_size = 64M Join_buffer_siz E = 32K sort_buffer_size = 32k# Tune innodb_checksums = 0 Innodb_doublewrite = 0 Innodb_support_xa = 0 innodb_thread_concu rrency = 0 Innodb_flush_log_at_trx_commit = 2 Innodb_flush_method = O_direct innodb_max_dirty_pages_pct = Innodb_use_na Tive_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 = innodb_write_io_threads = 4 Innodb_io_capacity = 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 differences in configuration are AHI (innodb_adaptive_hash_index) and Spin Delay (Innodb_spin_wait_delay)--while others are basically good enough in this test process.
• About the impact of AHI I have written a lot of articles before. AHI main dilemma is "use or not", in many cases it can help because of the blockage caused by the lock and speed up access to the index, but in the case of high concurrency may cause the RW lock contention caused by its btr_search_latch bottleneck
• The Spin Delay setting in MySQL 5.6 requires special attention because it plays a very important role in managing internal mutexes and RW lock contention, and it may make it easier for you to maximize performance. (You can get the details here, but you should know that there is no silver bullet, and no fixed optimal value is suitable for a variety of environments, this is entirely dependent on your system load.) So its default value is as much as MySQL 5.5 is 6).
• So, in my tests, I was very curious to understand the configuration pairs for the best AHI and Spin Delay settings under different load conditions.
• The next thing to remember is the scalability limitations of MySQL 5.5 and 5.6. I re-tested at 8, 16, 32, and 64 cores (64 cores equivalent to 32 core machines with open and Hyper-threading, none of the others turned on Hyper-threading)

First I showed you the first interesting comparison, in order to keep "compatibility" with my previous tests, I first tested the TPS (Transactions/sec) and then the QPS (Query/sec), which I prefer.

The first test is the classic Sysbench Oltp_ro (Read only):

sysbench Oltp_ro:

Next is the same Oltp_ro test, but the open and close operation of the table is not performed in the transaction (see here for a complete explanation)

sysbench Oltp_ro-trx:

Next is "Point-selects", which is a way to read the data, in the previous version of MySQL 5.5 good performance, but in 5.6 is still a little different.

sysbench oltp_ro point-selects:

This is the most painful test, simple-ranges (you can read this article to learn why pain and frustration)

sysbench oltp_ro simple-ranges:

Here is the read and write test, which is not the heaviest read and write, but still can explain the problem:

sysbench OLTP_RW:

TPS began to drop from 256 concurrent users, expecting a higher level of this. But still twice times higher than version 5.5, trust me, the next version of MySQL will perform better. This phase can be more stable and more performant by adjusting the InnoDB thread concurrency settings.

I am happy to see that MySQL 5.6 GA is released because:

• This is the best version of MySQL ever.
• Much faster than MySQL 5.5 under high load
• A better design
• More Adjustable
• More Transparent performance analysis
• Better instrumentation
• These are better than you've ever seen.
• Contains many new features

So the only question I have is: When do you start testing MySQL 5.6 and developing a database migration plan?