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

Purpose: The purpose of this monitoring job is to check the hit rate of the MyISAM key cache in a MySQL database. The MyISAM key cache helps improve the speed of databases by storing frequently-accessed information from large datasets. This is especially beneficial for data warehousing and web applications where speed and efficiency are desired. The job compares the values of the key read requests (total amount of requests to read a key block from the MyISAM key cache) and keys read from the disk (the total number of requests to read a key from the disk cache). A hit ratio is calculated, which can then be used to determine the performance of the key cache.
Why: The health and efficiency of the key cache can significantly impact the performance of a MySQL deployment. Checking its status and behaviour over time is useful in foreseeing potential issues and optimizing the overall functionality of the database.

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.