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

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

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.