MySQL 5.6 GA and 5.5 Performance Comparison Test

If you do not know the new features and improvements of MySQL 5.6, you can learn from here.

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

I use Sysbench workloads (Read-Only/Read-Write) for testing. The following is my test environment:

Hardware configuration:

Server: 32-core bi-thread (HT) Intel 2300 Mhz, 128 GB 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 = 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 adjustment:

The main difference in configuration is AHI (innodb_adaptive_hash_index) and Spin Delay (innodb_spin_wait_delay). Other parts are basically enough in this test.
I have written many articles about the impact of AHI. The main dilemma of AHI is whether to use or not. In many cases, AHI can help block the lock and speed up index access, however, in the case of high concurrency, rw lock contention may be caused by its btr_search_latch.

Note the Spin Delay settings in MySQL 5.6 because it plays a very important role in managing internal mutex and rw lock contention, using it may easily double your performance. You can learn more here, but you should know that there is no silver bullet, and there is no fixed optimal value that is suitable for different environments. This is totally dependent on your system load. Therefore, the default value is 6 in the same way as MySQL 5.5 ).
Therefore, in my tests, I was very curious about the configuration pairs of the best AHI and Spin Delay settings under different loads.

Next, remember the limitations of MySQL 5.5 and 5.6 in terms of scalability. I re-tested 64-core machines with 8, 16, 32, and 64 cores, which is equivalent to 32-core machines with super threads enabled, and others did not have hyper-threading enabled)

This article from the Linux community website (www.linuxidc.com) original link: http://www.linuxidc.com/Linux/2013-04/82070.htm

Edit recommendations]

1. MySQL 5.6.10 vs Percona 5.6.6 TPCCC performance test
2. MariaDB has become a turning point in the fate of MySQL?
3. Rollback in case of a DELETE misoperation in MySQL
4. One-step migration from MySQL to Redis
5. Two ways to solve the problem of master-slave synchronization in mysql