MySQL 5.6 GA and 5.5 Performance Comparison Test

Source: Internet
Author: User

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:

 
 
  1. #--------------------------------------------------  
  2.  max_connections = 4000  
  3.  
  4.  key_buffer_size = 200M  
  5.  low_priority_updates = 1  
  6.  sort_buffer_size = 2097152  
  7.  back_log = 1500  
  8.  query_cache_type = 0  
  9.  
  10. # files  
  11.  innodb_file_per_table  
  12.  innodb_log_file_size = 1024M  
  13.  innodb_log_files_in_group = 3  
  14.  innodb_open_files = 4000  
  15.  table_open_cache = 8000  
  16.  table_open_cache_instances = 16  
  17.  
  18. # buffers  
  19.  innodb_buffer_pool_size = 32000M  
  20.  innodb_buffer_pool_instances = 32  
  21.  innodb_log_buffer_size = 64M  
  22.  join_buffer_size = 32K  
  23.  sort_buffer_size = 32K  
  24.  
  25. # tune  
  26.  innodb_checksums = 0  
  27.  innodb_doublewrite = 0  
  28.  innodb_support_xa = 0  
  29.  innodb_thread_concurrency = 0  
  30.  innodb_flush_log_at_trx_commit = 2  
  31.  innodb_flush_method = O_DIRECT  
  32.  innodb_max_dirty_pages_pct = 50  
  33.  innodb_use_native_aio =1  
  34.  innodb_stats_persistent = 1  
  35.  innodb_spin_wait_delay = 6 / 96  
  36.  
  37. # perf special  
  38.  innodb_adaptive_flushing = 1  
  39.  innodb_flush_neighbors = 0  
  40.  innodb_read_io_threads = 16  
  41.  innodb_write_io_threads = 4  
  42.  innodb_io_capacity = 2000  
  43.  innodb_purge_threads =1  
  44.  innodb_adaptive_hash_index =  1 / 0  
  45.  
  46. # Monitoring  
  47.  innodb_monitor_enable = '%' 
  48.  performance_schema = ON 
  49.  performance_schema_instrument = '%=on' 
  50. #-------------------------------------------------- 

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.