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 performance_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_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 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 this job is to monitor the hit rate of the InnoDB buffer pool, a memory buffer that MySQL’s InnoDB storage engine uses to cache data and indexes of its tables. Having a high hit rate means less disk I/O is necessary, which leads to better database performance.
- Why: This monitoring job is crucial for maintaining the efficiency of a MySQL database that employs the InnoDB storage engine. If the hit rate falls below certain thresholds, a warning or alert will be triggered, prompting necessary action for database optimization.
- Manual checking: You can check this manually by using these SQL commands:
SELECT cast(VARIABLE_VALUE as signed) FROM performance_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS'; SELECT cast(VARIABLE_VALUE as signed) FROM performance_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_READS';Job Details
- Name: InnoDB buffer pool check
- Group: com.dbwatch.job
- Artifact ID: mysql_innodb_buffer_pool
- Version: 2.22
- Company: dbwatch.com
- Scheduled: by default executes at minutes 2,7,12,17,22,27,32,37,42,47,52,57 of every hour
This job includes multiple SQL procedures, chart presentations and a report template for visual analysis.
Upon completion or failure, the job cleans up specific tables and stored procedures related to the task at hand including ‘dbw_innodb_buffer_pool_check_last’, ‘dbw_innodb_buffer_pool_check_histr’, and ‘dbw_innodb_buffer_pool_check’ procedure. The cleanup ensures that the database environment remains clean and uncluttered with past task remnants.
The report generated by this task consists of InnoDB buffer pool use, hit ratio, and InnoDB tables description. All these factors collectively provide a comprehensive view of the database’s performance.
This job is highly suitable for large databases that rely heavily on relational data with a focus on high reliability and performance, typical for MySQL databases using InnoDB storage engine.
The job also supports express installation and force installation which means it can be quickly installed and can be installed even when some conditions are not met, respectively.
It filters for MySQL Instances that have the InnoDB engine and use global variables performance schema – ensuring that prerequisite conditions are in place for the monitoring task to function correctly.