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]
- MySQL 5.6.10 vs Percona 5.6.6 TPCCC performance test
- MariaDB has become a turning point in the fate of MySQL?
- Rollback in case of a DELETE misoperation in MySQL
- One-step migration from MySQL to Redis
- Two ways to solve the problem of master-slave synchronization in mysql