What is MySQL Key Efficiency?

Question

MySQL Workbench reports a value called Key Efficiency in connection with server security. What does this mean and what are its consequences?

From MySQL.com "Key Efficiency":

... an indication of the number of key_read_requests , which led to the actual key_reads .

Good, so what does that mean. What does this tell me about how I should configure the server?

Answer 1

Key performance is an indication of how much value you get from index caches stored in MySQL memory. If the efficiency of the key is high, then most often MySQL performs key searches from the memory area, which is much faster than the need to extract the corresponding index blocks from disk.

A way to increase key efficiency is to devote more of your system memory to MySQL indexing caches. How you do this depends on the storage engine you use. For MyISAM, increase the size of the buffer key. For InnoDB, increase the pool size of innodb-buffer.

However, as Michael Eakins points out, the operating system also stores disk caches that he recently accessed. The more memory available to your operating system, the more disk blocks it can cache. In addition, disks in the cases themselves (and in some disk controllers) also have caches, which can also speed up data retrieval from the disk. The hierarchy is a bit like this:

• fastest - Get index data from the MySQL index cache. Cost is a few memory operations.
• obtaining index data stored in the cache of the OS file system. Cost - a system call (for reading) and some memory operations.
• obtaining index data stored in the cache of the disk system (controller and disks). Cost - a system call (for reading), communication with a disk device, and some memory operations.
• slower - getting index data from the disk surface. Cost is a system call, communication with the device, the physical movement of the disk (moving the arm + rotation).

In practice, the difference between 1 and 2 is almost invisible if your system is not very busy. Also, it is unlikely (if your system has less backup memory than your disk controller), scenario 3 will run.

I used servers with MyISAM tables with relatively small index caches (512 MB), but massive system memory (64 GB), and it was difficult for me to demonstrate the value of increasing the size of the index cache. I think it depends on what else is going on on your server. If all you use is a MySQL database, it is likely that the OS cache will be quite efficient. However, if you run other jobs on the same server, and they use a large number of memory / disk accesses, then they can supplant valuable cached index blocks, resulting in frequent removal of the MySQL disk.

An interesting exercise (if you have the time) is to mess with your system so that it runs slower. When performing the standard workload on large tables, reduce the MySQL buffers until the effect is noticeable. Flush the file system cache by pumping huge amounts (more than RAM) of irrelevant data through your file system (cat large-file> / dev / null). Watch iostat as your requests run.

Key performance is NOT an indicator of how good your keys are. Well-designed keys will have a much greater impact on performance than the high Key Efficiency. MySQL, unfortunately, cannot help you.

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. Thus, these 2 variables can increase independently. ( http://bugs.mysql.com/bug.php?id=28384 )

It is still as clear as dirt.

On the next bit of explanation:

Partially Valid Use of Key_reads

There is a partially justified reason to study Key_reads, believing that we care about the amount of physical that happens, because we know that drives are very slow relative to other parts of the computer. And that’s where I get back to what I called “mostly factual” above, because Actually, key_reads are not physical disk reads at all. If the requested data block is not in the system cache, then Key_read - this disk is read - but if it is cached, then this is just a system call. However, let them make our first ill-conceived Assumption:

Difficult proof No. 1: A Key_read may correspond to a possibly readable physical disk. If we accept this assumption as true, then what other reason can we have for caring for Key_reads? This assumption leads to a “cache miss” much slower than getting into the cache, which makes sense. If it were to make Key_read as Key_read_request as fast, what use the key buffer anyway? Let the creators of MyISAM trust this alone, because they developed a cache hit to be faster than a miss. ( http://planet.mysql.com/entry/?id=23679 )