1. MyIsam
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';
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 )
2. InnoDB
buffer hit ratio : Oracle 과 비슷
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';