MySQL Work bench reviews something known as "Key Efficiency" in colaboration with server health. Exactly what does this suggest and what exactly are its implications?
From MySQL.com, "Key Efficiency" is:
...a sign of the amount of
key_read_demandsthat led to actualkey_reads.
Ok, what exactly does which means that. Exactly what does it let me know about how exactly I am designed to tune the server?
"Key Efficiency" is definitely an indication of methods much value you are receiving in the index caches held within MySQL's memory. In case your key efficiency is high, then most frequently MySQL is carrying out key searches from inside storage, that is considerably faster than needing to retrieve the appropriate index blocks from disk.
The best way to improve key efficiency would be to dedicate much more of the body memory to MySQL's index caches. The way you do that is dependent around the storage engine you utilize. For MyISAM, increase the need for key-buffer-size. For InnoDB, increase the need for innodb-buffer-pool-size.
However, as Michael Eakins highlights, the operating-system also holds caches of disk blocks so it has utilized lately. The greater memory that the operating-system has available, the greater disk blocks it may cache. Further, the disk drives themselves (and disk remotes in some instances), also provide caches - which again can accelerate locating data from disk. The hierarchy is a little such as this:
- quickest - locating index data from inside MySQL's index cache. The price is really a couple of memory procedures.
- locating index data that's locked in the OS file system cache. The price is really a system call (for that read), plus some memory procedures.
- locating index data that's locked in the disk system cache (controller and drives). The price is really a system call (for that read), communication using the disk device, plus some memory procedures.
- slowest - locating index data in the disk surface. The price is really a system call, communication using the device, physical movement from the disk (arm movement + rotation).
Used, the main difference between 1 and a pair of is nearly undetectable unless of course the body is extremely busy. Also, it's unlikely (unless of course the body has less spare RAM than your disk controller) that scenario 3 will come up.
I have tried personally servers with MyISAM tables with relatively small index caches (512MB), but massive system memory (64GB) and also have thought it was hard to demonstrate the need for growing how big the index cache; I guess it is dependent on which else is going on in your server. If whatever you are running is really a MySQL database, it is extremely likely the OS cache is going to be very efficient. However, should you run other jobs on a single server which use plenty of memory / disk accesses, then these might evict valuable cached index blocks resulting in MySQL striking disk more frequently.
A fascinating exercise (for those who have time) would be to mess together with your system to really make it run reduced. Managing a standard workload on large tables, lessen the MySQL buffers before the impact becomes noticeable. Flush your file system cache by moving large sums (more than RAM) of irrelevant data using your file system ( cat large-file > /dev/null ). Watch iostat as the queries run.
"Key Efficiency" isn't a way of measuring how good your secrets are. Smartly designed secrets will have a bigger effect on performance than high "Key Efficiency". MySQL doesn't have much that will help you there, regrettably.