The following article was originally written on the MySQL Performance Blog by Vadim Tkachenko
MySQL 5.6.7 RC is there, so I decided to test how it performs in
tpcc-mysql workload from both performance and stability standpoints.
I can’t say that my experience was totally flawless, I bumped into two bugs:
But at the end, is not this why RC for? And Oracle asked for a feedback, so I do my part.
- Benchmark date: Oct-2012
- Benchmark goal: Test how MySQL 5.6.7 performs
- Hardware specification
- Server: Dell PowerEdge R710
- CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
- Memory: 192GB
- Storage: Very Fast PCIe Flash Card
- Filesystem: ext4
- OS: CentOS 6.3
- MySQL Version: 5.6.7-RC
- Benchmark specification
- Benchmark name: tpcc-mysql
- Scale factor: 2500W (~250GB of data)
- Benchmark length: 4000 sec but the result is taken only for last 2000 sec to remove warm-up phase. Measurements are taken every second.
- Parameters to vary: we vary innodb_buffer_pool_size:13, 25, 50, 75, 100, 125GB to have different memory/data ration. We vary innodb_buffer_pool_instances: 1 and 8, and innodb_log_file_size: 2x4GB and 2x8GB.
We can see that innodb_buffer_pool_instances=8 makes a big difference on small buffer_pool sizes, while on bigger buffer_pool, innodb_buffer_pool_instances=1 is more preferable.
The the results on big buffer_pool is quite unstable, and the reason is that InnoDB falls into asynchronous flushing mode, the problem which was supposed to be fixed in new InnoDB flushing mechanism. However Dimitry told me that we may need a bigger innodb logfiles to get more stable results.
Obviously the result is quite better with bigger logs, so size does matter.
innodb_buffer_pool_instances parameter may change the result significantly, especially in intensive IO workloads.
In MySQL 5.6 it is finally possible to achieve stable throughput without dips, but an adaptive flushing still requires big log files.
[mysqld] gdb innodb_file_per_table = true innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method = O_DIRECT innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size = 125G innodb_buffer_pool_instances=8 innodb_log_file_size = 4G innodb_log_files_in_group = 2 #####plugin options innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 20000 innodb_io_capacity_max = 40000 #not innodb options (fixed) port = 3306 back_log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover user=root skip-grant-tables