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_performance_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
The monitoring job in question is the “MyISAM key cache check”; it’s an integral part of the ‘dbwatch’ Control Center designed specifically for MySQL databases.
Purpose:
- The primary goal of this job is to monitor and evaluate the efficiency of the MyISAM key cache in a MySQL database.
- MyISAM is built on the Indexed Sequential Access Method (ISAM) algorithm, primarily used for quick information retrieval from large data sets. It was the default storage engine for MySQL until 2009.
Importance:
- The task periodically checks the hit rate (i.e., the rate at which key reads are found already loaded in the cache instead of resorting to slower disk reads) of the MyISAM key cache and reports when the hit ratio drops below specified thresholds.
- Appropriate cache usage can greatly enhance the performance of a MySQL database, decreases disk I/O, therefore, monitoring the hit rate is vital for performance tuning.
Manual checking:
You can manually inspect the retrieved data from this job with SQL commands like:
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_name IN ('KEY_READ_REQUESTS', 'KEY_READS');Dependencies
The job relies on two tables and one procedure:
- The ‘dbw_myisam_key_cache_check_last’ table stores the values for the last checks.
- The ‘dbw_myisam_key_cache_check_histr’ table holds historical key read requests and hit ratios.
- The ‘dbw_myisam_key_cache_check’ procedure is used to implement the main logic of the job.
Report
The job generates a report entitled “MyISAM key cache check” that provides charts and tables demonstrating the MyISAM key cache use and hit ratio. It also provides summary information on the number of MyISAM tables in each schema and their total size, which can be useful for overall database management and performance tuning.