What is MySQL "Key Efficiency"

Question

MySQL Workbench reports a value called "Key Efficiency" in association with server health. What does this mean and what are its implications?

From MySQL.com, "Key Efficiency" is:

...an indication of the number of key_read_requests that resulted in actual key_reads.

Ok, so what does that mean. What does it tell me about how I'm supposed to tune the server?

Answer 1

"Key Efficiency" is an indication of how much value you are getting from the index caches held within MySQL's memory. If your key efficiency is high, then most often MySQL is performing key lookups from within memory space, which is much faster than having to retrieve the relevant index blocks from disk.

The way to improve key efficiency is to dedicate more of your system memory to MySQL's index caches. How you do this depends on the storage engine you use. For MyISAM, increase the value of key-buffer-size. For InnoDB, increase the value of innodb-buffer-pool-size.

However, as Michael Eakins points out, the operating system also holds caches of disk blocks which it has accessed recently. The more memory that your operating system has available, the more disk blocks it can cache. Further, the disk drives themselves (and disk controllers in some cases), also have caches - which again can speed up retrieving data from disk. The hierarchy is a bit like this:

1. fastest - retrieving index data from within MySQL's index cache. The cost is a few memory operations.
2. retrieving index data that is held in the OS file system cache. The cost is a system call (for the read), and some memory operations.
3. retrieving index data that is held in the disk system cache (controller and drives). The cost is a system call (for the read), communication with the disk device, and some memory operations.
4. slowest - retrieving index data from the disk surface. The cost is a system call, communication with the device, physical movement of the disk (arm movement + rotation).

In practice, the difference between 1 and 2 is almost unnoticeable unless your system is very busy. Also, it is unlikely (unless your system has less spare RAM than your disk controller) that scenario 3 will come into play.

I have used servers with MyISAM tables with relatively small index caches (512MB), but massive system memory (64GB) and have found it difficult to demonstrate the value of increasing the size of the index cache. I guess it depends on what else is happening on your server. If all you are running is a MySQL data base, it is quite likely that the OS cache will be quite effective. However, if you run other jobs on the same server and these use lots of memory / disk accesses, then these might evict valuable cached index blocks leading to MySQL hitting disk more often.

An interesting exercise (if you have time) is to tinker with your system to make it run slower. Running a standard workload on large tables, reduce the MySQL buffers until the impact becomes noticeable. Flush your file system cache by pumping huge amounts (greater than RAM) of irrelevant data through your file system ( cat large-file > /dev/null ). Watch iostat as your queries run.

"Key Efficiency" is NOT a measure of how good your keys are. Well designed keys will have a much larger impact on performance than high "Key Efficiency". MySQL does not have much to help you there, unfortunately.

Answer 2

Key_read_requests is the number of requests to read a key block from the cache. While key_reads is the number of physical reads of a key block from disk. So these 2 variables can increase independently. (http://bugs.mysql.com/bug.php?id=28384)

Which is still as clear as mud.

On to the next bit of explaination:

A partially valid use of Key_reads

There is a partially valid reason to examine Key_reads, assuming that we care about the number of physical reads that occur, because we know that disks are very slow relative to other parts of the computer. And here's where I return to what I called "mostly factual" above, because Key_reads actually aren't physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read -- but if it is cached, then it's just a system call. However, let's make our first hard-to-prove assumption:

Hard-to-prove assumption #1: A Key_read might correspond to a physical disk read, maybe. If we take that assumption as true, then what other reason might we have for caring about Key_reads? This assumption leads to "a cache miss is significantly slower than a cache hit," which makes sense. If it were just as fast to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let's trust MyISAM's creators on this one, because they designed a cache hit to be faster than a miss. (http://planet.mysql.com/entry/?id=23679)