Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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

share|improve this answer
    
Very nice. You makes me question. Thank you very much, this is my first time to ask a question in English! –  Xia Yu Jun 28 '12 at 10:35
    
oh great !!!.Hope that answer will help you. –  Abdul Manaf Jun 28 '12 at 10:38
    
Why is the key read efficiency "1-" but the key write efficiency is not? –  user11575 Sep 12 '12 at 16:48
add comment

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 !

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.