Tuning key_reads in MySQL

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?

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.

StackExchange Database Administrators Q#58182, answer score: 4