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 = ‘0′ & global_status_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 job is to monitor and analyze the hit rate of the MyISAM key cache in MariaDB instances where MyISAM is still in use. This includes assessing the performance of key reads versus the total key read requests to provide insights into cache efficiency.
Why: Monitoring the MyISAM key cache is crucial as a low hit rate could lead to increased disk I/O, affecting the overall performance of the database. Effective cache usage ensures faster data retrieval and optimal resource utilization, critical in performance tuning and capacity planning.
Manual checking: To manually check the MyISAM key cache performance, you can use the following SQL commands:
bc. SELECT key_reads, key_read_requests, ((key_reads / key_read_requests) * 100) AS hit_ratio FROM information_schema.global_status WHERE variable_name IN (‘KEY_READS‘, ‘KEY_READ_REQUESTS‘);

Job Details

Category: Performance
Job Name: MyISAM key cache check
Description: Checks the hit rate of the MyISAM key cache to determine how effectively the cache is being used to retrieve the keys without reaching out to disk storage.

Metrics Collected

Metric	Description
Key Reads	Number of requests to read a key block from cache.
Key Read Requests	Total number of requests for reading a key, whether served from cache or disk.
Hit Ratio	Percentage of key read requests served successfully from the cache.

Thresholds and Warnings

This job calculates the hit ratio and compares it against pre-defined thresholds:
- If the hit ratio falls below 95%, a warning is triggered.

Tables and Dependencies

Tables Created:
- dbw_myisam_key_cache_check_histr

Reporting

Report Title: MyISAM key cache check
The report includes:
- Graph showing the number of total and disk-based key read requests per second over time.

This monitoring job is designed for proactive performance tuning, offering insights into typical scenarios where tuning the MyISAM key cache could lead to significant improvements in database responsiveness and efficiency.