InnoDB buffer pool Hit rate

InnoDB buffer pool Hit rate

(Publish Date: 2010-4-19 4:37pm, Total Visits: 255, Today: 1, This Week: 1, This Month: 3)

innodb buffer pool hit ratio =
1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

1. InnoDB buffer pool Hit rate:

innodb buffer pool hit ratio =
1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

using a SQL statement in mysql 5.1:

SELECT round ((1-P2.variable_value / P1.variable_value),4) , P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

2. MyISAM key buffer hit rate:

key buffer hit ratio = 
1 - ( key_reads / key_read_requests )

using a SQL statement in mysql 5.1:

SELECT 1- round ((P2.variable_value / P1.variable_value),4) , P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'key_read_requests'
AND P2. variable_name = 'key_reads';

3. perc_full_table_scans

perc_full_table_scans = 
   1 - ( handler_read_rnd_next + handler_read_rnd ) /
   ( handler_read_rnd_next + handler_read_rnd + handler_read_first +
   handler_read_next + handler_read_key + handler_read_prev )

http://ndba.egloos.com/2994119