I’m puzzled by the behavior of my key cache. The cache read ratio is currently 6.55% which is said to be high, and there are 2.7 million reads out of 46 million read requests. This is over about 6.4 million MySQL requests (last 5 days).
But at the same time there are 32K unused blocks against only 22K used blocks.
The comment against the 2.7 million reads (in red) is “If Key_reads is big, then your key_buffer_size value is probably too small”.
With more free space than used, how would increasing the key buffer size any further help? And with so much free space, why is the performance of the key cache so poor?
First, I am guessing that you’re getting this advice from tuning_primer.sh. Every tuning script has its problems. If the advice doesn’t make sense, it is not surprising.
You should not focus on the ratio. The problem with ratios is that they obscure magnitude, and magnitude is what you care about. Specifically, the magnitude of misses. But even still, these misses are just numbers – they don’t have any timing information associated with them, so it’s doubly meaningless. Who cares how many misses there are, unless a) you know how much time it’s taking or b) you know there are more misses than your server can healthily handle?
So here’s my advice:
Completely ignore the ratio, and ignore all the advice you see on the web about the ratio. The ratio is a surrogate measure[1] that is useless and distracting. Yes, I know 99% of people are going to disagree with me. They are wrong.
If your key buffer has free space after the server is fully warmed up, it’s big enough. And because it’s not allocated to its full size, any “free space” is actually not wasted at all – the memory isn’t reserved, it’s still available for anything else to use if needed.