Wednesday, February 13, 2013

MySQL Key Blocks

We've been getting alerts in our monitoring for MySQL that look like this:

There are 3205072 unused key cache blocks on ServerX, even though the hit rate is over 99%.
You may wish to reduce the size of key_buffer_size to free up memory for other caches.

They've been kind've hanging around because we've had bigger things to deal with, it was a warning error (not critical), and quite frankly none of us knew what it was or what to do with it. In an effort to clear up the boards I decided to dive into it, and in the end the solution was simple: disable monitoring for this metric.

A little background:

MySQL can use indexing to make searches of tables faster. You essentially identify columns that will be searched on frequently, and turn on indexing for that column which makes MySQL create a hidden table with that columns info, indexed and ordered. When you then need to search a table for info, instead of having to read each row sequentially MySQL uses some algorithm to make use of the index table to retrieve that information more quickly.

That key information is stored in blocks in a buffer, whose size is set by the key_buffer_size variable in my.cnf. There are some basic rules about how to come up with this number, but the general idea is to make it as large as possible, with 1/4 the total of system memory being a good figure. At least, that's what I read in one place. One of the most interesting things about MySQL configuration is that there are countless sites and articles with information on how to performance-tune MySQL, and they all differ in their information, even down to which aspects are important to fiddle with. I can see why so many people leave it to its defaults.

With that in mind, you then have two other values that are important in terms of evaluating whether your MySQL server is performing optimally. As with most things, in-memory access is faster than disk access, and disk I/O can be intensive and cause system performance to decrease. That's why MySQL loads as much of this key data into the buffer as possible. If it cannot find the data it needs in the buffer it has no choice but to go to disk, which slows things down some. The values of key_reads and key_request_reads reflect this activity. Key_read_requests is how many times MySQL was looking for an index. Key_reads is how many times in those searches it had to go to disk to get the index. You want your key_reads to be as low as possible, of course. It will never be zero, as when MySQL starts up it has to load all of that data into the buffer to begin with, but the ratio (key_reads/key_read_requests) should be less than 0.01 according to most sources.

I had to learn all of this in order to really understand the alert and determine what (if anything) to do with it. In our case the facts were as such:

| Key_blocks_unused      | 3370430   |
| Key_blocks_used        | 107358    |
| Key_read_requests      | 624444185 |
| Key_reads              | 153583    |
| key_buffer_size          | 4294967296 |

4GB set aside for the buffer, not using a ton of that space at all, and a cache miss ratio of 0.0002. That's good. This confirms the original alert, that the key buffer size may be a little overzealous for what we're doing with the system. Do I need to reset the key buffer though? Well, according to one resource on the #mysql channel, MySQL buffers affect VIRT and not RES, so not really.

And so, I disabled this particular monitoring metric. In fact, after I read this article, I took this monitoring and I threw it on the ground.

No comments:

Post a Comment