Skip to content

MySql tuning: Increasing the buffer size for ISAM type storage

May 13, 2015

Even after increasing the provisioned IOPS to 300/sec for EBS the IO was choking at times. So now I tried with increasing the key buffer size of MySql (which is advised, and seemingly the only option for MySAM storage engines. It is simple change, just the following line in /etc/my.cnf

#default value is 8M
key_buffer_size=128M

The MySql documentation[1] advises to check the ratio of values key_reads & key_read_requests status variables. ‘show status;'[2] query can be run to see the values. And it should be .01 (or less). Meaning bulk of the queries should result in keys being fetched from the cache. In our case it was .11. Meaning cache hit was only around 89% or so.

With these numbers in millions, it can be be a problem, as it translates to IO calls to the EBS device. 15 million of these requests translate to around 170 IO request/second if they all miss the cache.

So now have increased it. And need to observe it for a day. As IO choking happens at least 6/8 times in a day. Shall update this post tomorrow on the results.

Update on 14-May-2015:
On two of the servers the ratio was .002. But on one which has a higher load it was 8116868 / 669793730 ~ .012 . So may be it should be brought down a bit. As seen on some other blogs that when cache hit dropped to 90%, the servers ran into huge load issues. So let me try to make it less that .01 (meaning > 99% cache hit).

[1] – https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size
[2] – Better to use : show status like ‘key%’;
[3] – Has good info (overall) on the problem: http://stackoverflow.com/questions/14839560/mysql-on-ec2-ebs-too-slow
[4] – This tells clearly how to set the value : http://www.ewhathow.com/2013/09/what-is-the-recommended-value-of-key_buffer_size-in-mysql/

Advertisements

From → Uncategorized

One Comment

Trackbacks & Pingbacks

  1. Shining Leaf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: