MySQL5.6.7-RC tpcc-mysql benchmark test result _ MySQL

MySQL5.6.7-RC tpcc-mysql benchmark test result bitsCN.com

MySQL 5.6.7 RC was released a few days ago, so I decided to use tpcc-mysql to test its performance, including performance and stability.

I can't say that my testing process is flawless because I found two bugs:

• MySQL 5.6.7 is locked during CREATE INDEX
• MySQL 5.6.7-rc crashes when using tpcc-mysql workload test

I don't know if it is because of the RC version. I submitted some feedback to Oracle later. The following is a detailed test environment:

• Test Date: Oct-2012
• Objective: to test MySQL 5.6.7 performance
• Hardware change
  • Server: Dell PowerEdge R710
  • CPU: 2x Intel (R) Xeon (R) CPU E5-2660 0 @ 2.20 GHz
  • Memory: 192 GB (this memory is too strong)
  • Storage: Very Fast PCIe Flash Card
  • File System: ext4
• Software
  • Operating system: CentOS 6.3
  • MySQL version: 5.6.7-RC
• Test Specification
  • Test Tool: tpcc-mysql
  • Test data: 2500 million (~ 250 GB of data)
  • Test Time: 4000 seconds in total, but only the last 2000 seconds are used to avoid inaccurate test results due to cold start.
• Different test parameters: use different groups of innodb_buffer_pool_size: 13, 25, 50, 75,100,125 GB, innodb_buffer_pool_instances: 1 and 8, and innodb_log_file_size: 2x4 GB and 2x8 GB.

Test results:

The first result uses 2x4 GB InnoDB log file:

We can see that when innodb_buffer_pool_instances = 8 is very different in the small buffer_pool size, and when large buffer_pool is used, innodb_buffer_pool_instances = 1 is the best performance.

The test results are very stable in large buffer_pool because InnoDB adopts the asynchronous flush mode and the previous problems have been fixed under the new InnoDB flush mechanism. However, Dimitry tells me that a larger InnoDB log file is needed to obtain more stable results.

The following is a comparison of the log file sizes of 2x4 GB vs 2x8 GB innodb:

Obviously, the test results are more stable with larger log files!

Conclusion:

The innodb_buffer_pool_instances parameter significantly affects the test results, especially for very high I/O loads.

In MySQL 5.6, stable throughput is achieved, but the adaptive flush mechanism still requires large log files.

The MySQL configuration is as follows:
01 [mysqld] 02gdb03 04innodb_file_per_table = true05innodb_data_file_path = ibdata1: 100 M: plugin = 256M08 plugin = 812 13innodb_log_file_size = plugin = 215 #### plugin = 1618innodb_io_capacity = plugin = 4000020 21 22 # not innodb options (Ed fixacity) 23 port = 330624back_log = 5025max_connections = bytes = 1028table_open_cache = bytes = 438thread_stack = bytes = 64M40 41server-id = 1042 # * ** MyISAM Specific options43key_buffer_size = 8M44read_buffer_size = primary = 4M46bulk_insert_buffer_size = primary = 150myisam_recover51user = root52skip-grant-tables

Original English, OSCHINA original translation

BitsCN.com