4

We are trying to tune our MySQL database (Myisam, 5.1.73) which is getting busy.

We investigated Key_reads:

$ mysqladmin ext -ri10 | grep Key_reads

During a normal load we have the following values (average):

Key_reads          40      (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

But sometimes, during a few dozens of minutes, we see values like this:

Key_reads          1000    (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

We have increased Key_buffer_size to 512 (which is bigger than the total of our indexes). We still have 16Gb of free RAM.

Should Key_reads be low or null?

Are these peacks normal or avoidable?

4

DEFINITIONS

First, you need to know what these status variables mean

  • Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
  • Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

OBSERVATIONS

  • 40 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (40.0 / 350000.0)) = 99.98857 %.
  • 1000 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (1000.0 / 350000.0)) = 99.7143 %.
  • I have discussed this aspect of Performance tuning before : How to improve MySQL Server Performance..??

CONJECTURE

When your Key_reads started increasing, it simply indicated you read new data and the index pages accessed have been freshly loaded into the MyISAM Key Cache. Such Key_reads are unavoidable. Once that spike stopped, then either your read requests have drastically reduced or subsequent queries are now using the key blocks you last read into the MyISAM Key Cache. High Key reads indicate that your queries are frequently reading index blocks from disk, loading them into the Key Cache, bumping out index blocks from the MyISAM Key Cache and subsequent queries are not accessing them.

CONCLUSION

Since your cache hit ratio (> 99.00 %) is excellent, I would say you are fine because your Key_reads are significantly low and infrequent.

1
  • You are right the ratio is good. But it also means that we have 100 random I/O on disk per second only for the key reads. It looks a little high. (We are not on SSD now). Why is key_reads not zero?
    – Toto
    Feb 4 '14 at 17:15
1

100 reads per second is probably all your spinning disk can handle. That means that you are I/O-bound 'sometimes'.

You say key_buffer_size is 512?? Do you really mean 0.5K?? Or did you mean 512K or 512M? For 16GB of RAM, you should probably set it to 3G. This should decrease key_reads.

You should consider switching from MyISAM to InnoDB and upgrade from 5.1 (if you have not done so in the 4 years since this Question was asked).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.