Over a million developers have joined DZone.

Dealing With a Couple Bugs in MySQL 5.6.7-RC

DZone's Guide to

Dealing With a Couple Bugs in MySQL 5.6.7-RC

· Performance Zone ·
Free Resource

Sensu is an open source monitoring event pipeline. Try it today.

The following article was originally written on the MySQL Performance Blog by

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
  • Software
    • 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.
  • 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.

    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:



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_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_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

Sensu: workflow automation for monitoring. Learn more—download the whitepaper.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}