Server Administration - MySQL - page 780

To calculate a hit rate, pass the variable hash and the names of the reads and requests
variables to this routine:
def cache_hit_rate ( vars , reads_name , requests_name )
reads = vars [ reads_name ]. to_f
requests = vars [ requests_name ]. to_f
hit_rate = requests == 0 ? 0 : 1 - ( reads / requests )
printf " Key reads: %12d (%s) \n " , reads , reads_name
printf "Key read requests: %12d (%s) \n " , requests , requests_name
printf " Hit rate: %12.4f \n " , hit_rate
end
Now we're all set. Call the routines that fetch status information and calculate the hit
rates like this:
statvars = get_status_variables ( dbh )
cache_hit_rate ( statvars ,
"INNODB_BUFFER_POOL_READS" ,
"INNODB_BUFFER_POOL_READ_REQUESTS" )
cache_hit_rate ( statvars ,
"KEY_READS" ,
"KEY_READ_REQUESTS" )
Run the script to see your server's hit rates:
% hitrate.rb
Key reads: 6280 (INNODB_BUFFER_POOL_READS)
Key read requests: 70138276 (INNODB_BUFFER_POOL_READ_REQUESTS)
Hit rate: 0.9999
Key reads: 23269 (KEY_READS)
Key read requests: 8902674 (KEY_READ_REQUESTS)
Hit rate: 0.9974
For tasks involving system variables, code similar to get_status_variables() suffices.
This implementation uses the GLOBAL_VARIABLES table:
def get_system_variables ( dbh )
vars = {}
query = "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM
INFORMATION_SCHEMA.GLOBAL_VARIABLES"
dbh . select_all ( query ) . each { | name , value | vars [ name . upcase ] = value }
return vars
end
To use SHOW instead, replace the query with this one:
query = "SHOW GLOBAL VARIABLES"
22.7. Creating and Using Backups
Problem
You want to protect yourself against data loss.