MySQL variables for 5.6

Posted by admin on Jul 23, 2013 in Database |

After upgrading MySQL Cluster from 7.2 to 7.3 (5.6.11) performance was poor. My workload is not typical OLTP or OLAP but a mixture of both and some real-time updating. Also I have 2 MySQL instances running on different ports and the disk subsystem is quiet slow.

The following parameters got the databases back to normal (or better)

innodb_thread_concurrency=4
innodb_old_blocks_time=10
innodb_old_blocks_pct=37
innodb_flush_method=O_DIRECT
innodb_io_capacity=100
innodb_buffer_pool_instances=1

innodb_thread_concurrency is limited to 4 on each instance. This matches the CPU count of 8. The default is 0 – unlimited which seemed to generate excessive load averages.

innodb_old_blocks_time has changed from a default of 0 to a default of 1000 (milliseconds). The purpose of this parameter is to not flood the buffer pool when a large table scan occurs. The default of 1000 caused the system load averages to increase rapidly probably due to the slow disks causing io wait. Setting to 10 (milliseconds) seems a good compromise.

innodb_old_blocks_pct=37 is the default but for some reason mine was set to 95.

innodb_flush_method is O_DIRECT to improve insert/update performance

innodb_io_capacity has a default of 200 and a maximum of 2000. I have it set lower on one instance than the other to prioritize io.

innodb_buffer_pool_instances=1. The default is 8 buffer pool instances when a large buffer pool is allocated. This is not satisfactory for me and I prefer a single buffer pool. Thread concurrency is not a concern.

Leave a Reply

XHTML: You can use these tags:' <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Copyright © 2008-2017 Brinsmead Data Services All rights reserved.