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

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:

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.