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.