MyISAM key cache check


Job details

Name: MyISAM key cache check
Platform: Mysql
Category: Performance
Description: Checks the hit rate of the MyISAM key cache.
Long description: This job checks the hit rate of the MyISAM key cache. MyISAM stands for Indexed Sequential Access Method. It was the default storage engine for MySQL until December 2009. With the release of MySQL 5.5, MyISAM was replaced with InnoDB.
Version: 1.1
Default schedule: 3,8,13,18,23,28,33,38,43,48,53,58 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & is_mysql_branch=‘1′]/.[hasengine=‘YES’ & use_global_variables_information_schema = ‘1′ ]

Parameters

Name Default value Description
hit ratio warning threshold 95 If this threshold is reached over a period of time (controlled by parameters “warning/alarm threshold (time)”), a warning is returned.
hit ratio alarm threshold 80 If this threshold is reached over a period of time (controlled by parameters “warning/alarm threshold (time)”), an alarm is returned.
history threshold 7 The maximum number of day to kept statistics for in the history table.
alarm threshold (time) 30 A period of time (in minutes) which must be passed (combined with average values of KEY_READ_REQUESTS) before an alarm is returned by the procedure.
min KEY_READ_REQUESTS 10000 The minimum average value of KEY_READ_REQUESTS (over a period of time defined by the “alarm/warning threshold (time)” parameters) before an alarm or a warning is triggered. KEY?READ?REQUESTS is the number of requests to read a key block from the MyISAM key cache.
warning threshold (time) 30 A period of time (in minutes) which must be passed (combined with average values of KEY_READ_REQUESTS) before a warning is returned by the procedure.
enable warnings and alarms NO If set to “NO” (default), the alert will only collect performance statistics without returning status warning or alarm.

Job Summary

Manual Checking

To perform this check manually, administrators can directly query the database’s information schema. Using the SQL commands provided by dbWatch, they can retrieve information about the key read requests and keys read from the disk. The hit ratio can be calculated by subtracting the keys read from the key read requests and dividing the difference by key read requests and multiplying it with 100.

SELECT VARIABLE_VALUE as key_read_requests
FROM information_schema.global_status
WHERE variable_name = 'KEY_READ_REQUESTS'
SELECT VARIABLE_VALUE as keys_read
FROM information_schema.global_status
WHERE variable_name = 'KEY_READS'
SELECT ROUND((1 - (keys_read/key_read_requests)) * 100,2) as hit_ratio

Note: It’s important to keep track of these values over a particular interval of time to identify trends and patterns.

Job Monitoring

Built into this job are alarm and warning thresholds, which trigger notifications if hit ratios fall below a certain point, potentially indicating an issue with the key cache’s performance. There’s also a history threshold which sets the number of days to keep statistics, this allows for monitoring performance over time.

Report

This job includes a template for a report which shows hit ratios and key cache use over time, displayed as graphs, and a table showing the number of MyISAM tables in each schema and their total size.