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.
Results
The first result is 2x4GB innodb logfiles.
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.
So there it is with 2x4GB vs 2x8GB innodb logfiles.
Obviously the result is quite better with bigger logs, so size does matter.
Conclusion
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.
MySQL configuration:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
[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 |
