What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql?

Now, I have retrieved some dates from table of 'GLOBAL_STATUS' and 'GLOBAL_VARIABLES' by MySQL DB named information_schema .

I can retrieve

-
key_buffer_size

-
key_cache_block_size

-
query_cache_limit

-
Query_cache_size... and so on...

But, I don't know how to calculate the rate of Key hitrate, Key buffer used,and query cache hitrate

so, someone can get the formula about those, such as

? / ? * 100% = ?

For your reference i am trying to explain some concepts also.

Key hit rate

There are basically two forms of key hit rate

1.Key Read Efficiency


Key Reads: The number of physical reads of a key block from disk.


Key Read Request: The number of requests to read a key block from the
cache.

Key Read Efficiency = [1 - (Key_reads/Key_read_requests)]*100

Key Read Efficiency: The ratio of the number of physical reads of a
key block from the cache to the number of requests to read a key block
from the cache in percentage. The MySQL performance is good if the
value of Key Read Efficiency is 90 percent and above. Increasing the
size of the cache improves the value of Key Read Efficiency and hence
an improved the performance.

2.Key Write Efficiency


Key Writes: The number of physical writes of a key block to disk.


Key Write Request: The number of requests to write a key block to the
cache.

Key Write Efficiency=(Key_write/Key_write_requests)*100

Key Write Efficiency: The ratio of the number of physical writes of a
key block to the cache to the number of requests to write a key block
to the cache in percentage. For a good performance of the MySQL
server, the value of Key Write Efficiency must be 90 percent and
above. If it is found less, then you can increase the size of the
cache to improve the performance.

Key Buffer Used

Find value of key_buffer_size as show variables like 'key_buffer_size';

convert it into MB.

Find the All MyISAM index Size :


SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index Size' FROM
information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT
IN('mysql','information_schema');

Find

(Index Size)/key_buffer_size(in MB) * 100

if result is (100) then your all indexes are not cached into
key_buffer you may gain performance boost by increasing
key_buffer_size.

query cache hitrate


Hit rate = Qcache_hits / (Qcache_hits + Com_select) * 100


Insert rate = Qcache_inserts / (Qcache_hits + Com_select) * 100


Prune rate = (Qcache_lowmem_prunes / Qcache_inserts) * 100

StackExchange Database Administrators Q#20083, answer score: 11