MyISAM key cache check

Job details

Name:	MyISAM key cache check
Platform:	Mariadb
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_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘1′ ]

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.

Purpose: The purpose of this job is to monitor and check the hit rate of the MyISAM key cache in MariaDB instances that utilize the MyISAM storage engine.
Why: This job is critical for optimizing the MyISAM key cache performance and ensuring efficient data retrieval processes. It identifies when the cache hit ratio falls below certain thresholds which could lead to increased disk reads and degraded database performance. An optimized cache ensures faster data access and enhances overall application performance.
Manual checking: To manually check the cache hit rate in the database, issue the following SQL commands:

SELECT key_reads, key_read_requests FROM performance_schema.global_status;

This monitoring job assesses the effectiveness of the MyISAM key cache by calculating the hit rate.
MyISAM, an older storage engine primarily used before MySQL 5.5, is designed for high-speed retrieval but is not ACID-compliant and lacks support for transactions and foreign keys.
Regularly checking the key cache hit rate is crucial, especially in environments where MyISAM tables are still in use for data warehousing or specific application needs due to their simplicity and quick read capabilities.

The job specifically tracks:
- Key read requests: Number of requests to read a key block from the cache.
- Key reads: Number of actual key reads from the disk.
- Hit ratio: Percentage of times the needed key was found in the cache versus having to be read from the disk.

The monitoring job can be configured to trigger warnings or alarms based on the hit ratio thresholds, ensuring proactive performance management:
- Warning Threshold: If the hit ratio falls below 95%.
- Alarm Threshold: If the hit ratio dips under 80%.
Additionally, the system checks the average number of KEY_READ_REQUESTS to ensure there is sufficient data volume to make the statistics meaningful.

Tables and Procedures Involved:
- A table (dbw_myisam_key_cache_check_last) to store the last checked values.
- A historical table (dbw_myisam_key_cache_check_histr) to track changes and trends over time.
- A procedure (dbw_myisam_key_cache_check) to implement the monitoring logic and update the check values.

Cleanup on Failure:
- Should there be a problem with the monitoring job or its subcomponents, it is configured to clean up by dropping the tables and procedure associated with this job.

The output of this monitoring job feeds into several types of reports:
- A time-based chart tracking the number of key read requests per second and disk utilizations.
- A graph displaying the hit ratio of the MyISAM key cache over time.
- Statistics on the amount and size of MyISAM tables across schemas.

The provided data helps database administrators understand cache performance trends, potential bottlenecks, and areas where tuning could enhance system efficiency.