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

Question

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% = ?

Answer 1

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 cached into key_buffer

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

Answer 2

For those using MySQL Administrator, here is a nice script I found, which shows the main interesting values.

Worked perfectly for me (Win7 x64, MySQL 5.1.3 on Debian).

Thanks to the guy who wrote it !

Answer 3

I don't frequent here but the formula in the top answer for Hit Rate is not right. Please see Optimizing MySQL query_cache_size for the proper formula.

Answer 4

(Now it is 2019.)

I suggest that the question is moot since you should not be using MyISAM. Instead, you should be using InnoDB, which has different metrics.

And the Query Cache has been removed from MySQL 8.0. And it is not usable in many multi-master setup, including Galera and Group Replication.