InnoDB buffer pool check
Job details
| Name: | InnoDB buffer pool check |
| Platform: | Mysql |
| Category: | Performance |
| Description: | Checks the hit rate of the innodb buffer pool using information_schema.global_status. |
| Long description: | This job checks the hit rate of the innodb buffer pool. InnoDB has been the default storage engine for MySQL since the release of MySQL 5.5. It is best suited for large databases that hold relational data with focuses on high reliability and performance. |
| Version: | 2.22 |
| Default schedule: | 2,7,12,17,22,27,32,37,42,47,52,57 * * * |
| 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 INNODB_BUFFER_POOL_READ_REQUESTS) before an alarm is returned by the procedure. |
| min BUFFER_POOL_READ_REQUESTS | 10000 | The minimum average value of INNODB_BUFFER_POOL_READ_REQUESTS (over a period of time defined by the “alarm/warning threshold (time)” parameters) before an alarm or a warning is triggered. |
| warning threshold (time) | 30 | A period of time (in minutes) which must be passed (combined with average values of INNODB_BUFFER_POOL_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 the “InnoDB buffer pool check” job is to monitor the performance of MySQL databases, focusing on the InnoDB buffer pool. It checks the hit rate of the buffer pool to ensure high performance and reliability, particularly in large databases holding relational data.
- Why: This monitoring job is crucial because InnoDB, set as the default storage engine for MySQL since version 5.5, is designed for high reliability and performance handling in large relational databases. By checking the buffer pool’s hit rate, you can be alerted when buffer pool performance decreases, potentially indicating necessary adjustments.
- Manual Checking: You can manually check the hit rate of the InnoDB buffer pool by issuing the following SQL command:
SELECT cast(VARIABLE_VALUE as signed) INTO buffer_read_requests_val FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS';
SELECT cast(VARIABLE_VALUE as signed) INTO buffer_reads_val FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_READS';Dependency
The main dependencies of this job are:
- Main Procedure: dbw_innodb_buffer_pool_check
- Dependency Tables: dbw_innodb_buffer_pool_check_histr and dbw_innodb_buffer_pool_check_last
Report Template
This job comes with a built-in report template reported as “Innodb buffer pool check” providing visual and tabular data including ‘InnoDB buffer pool use’, ‘Innodb buffer pool hit ratio’, and ‘InnoDB tables’. This report helps in identifying the status of buffer pool use and timely performance evaluation.
Implementation codes
For monitoring the MySQL engines, codes are implemented to create and manipulate tables such as ‘dbw_innodb_buffer_pool_check_histr’ and ‘dbw_innodb_buffer_pool_check_last’. Furthermore, a procedure named dbw_innodb_buffer_pool_check is defined to analyze the hit rate and yield status alerts if predetermined thresholds are reached.
Upgrade Considerations
If the system is upgraded from version 2.21, the implementation codes are modified to account for a precise duration calculation and checking the hit ratio. It also assists in capturing the average value of INNODB_BUFFER_POOL_READ_REQUESTS in a particular timeframe. Parameters like ‘alarm threshold (time)’, ‘warning threshold (time)’, ‘min BUFFER_POOL_READ_REQUESTS‘, and ‘history threshold’ are added or revised to enable an improved performance monitoring system.