Binlog cache check
Job details
| Name: | Binlog cache check |
| Platform: | Mysql |
| Category: | Performance |
| Description: | Checks the number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction. Statistics collected from information_schema.global_status. |
| Long description: | Checks the number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction. |
| Version: | 2.32 |
| Default schedule: | 9,19,29,39,49,59 * * * |
| 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 |
|---|---|---|
| warning threshold | 10 | Will give a warning if the disk use exceeds this value. (in %) |
| alarm threshold | 20 | Will give an alarm if the disk use exceeds this value. (in %) |
| threshold (time) | 30 | A period of time (in minutes) which must be passed (combined with average values of BINLOG_CACHE_USE) before an alarm or a warning is returned by the procedure. |
| min BINLOG_CACHE_USE | 100 | The minimum average value of BINLOG_CACHE_USE per seconds (over a period of time defined by the [time threshold] parameter) before a warning or an alarm is triggered. |
| enable warnings and alarms | NO | If set to “NO” (default), the alert will only collect statistics without returning status warning or alarm. Value “YES“ will activate the alert. |
| history threshold | 7 | The maximum number of day to kept statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor the usage of the temporary binary log cache in MySQL. It keeps a check on the number of transactions that exceeded the designated binlog_cache_size, which may lead to usage of a temporary file to store statements from the transaction.
- Why: This job is critical in order to maintain consistent performance, as excessive usage of the temporary file can potentially be a performance issue. By monitoring this, necessary actions can be taken to optimize the binlog_cache_size setting in MySQL.
- Manual checking: Manual check for this can be conducted within the database by viewing the ‘BINLOG_CACHE_USE‘ and ‘BINLOG_CACHE_DISK_USE‘ global status variables.
Job Contents
The job checks the number of transactions which used the temporary binary log cache but exceeded the value of binlog_cache_size. It logs this data according to a predetermined default schedule, then examines the cached data for any alarms or warnings. It stores this information in historical tables, tracking the average usage of the binary log cache and the estimated percentage of transactions that exceed the cache size.
Reporting
The job also includes two presentations for a dbwatch report. In the first presentation, it displays a chart showing the ratio of disk usage, with an in-depth explanation of the significance of this data. The second presentation provides a graph showing the total number of times the binlog cache has been used and how many times it defaulted to using the disk.
Dependencies
The main procedure `dbw_binlog_cache_check` and the two tables `dbw_binlog_cache_check_histr` and `dbw_binlog_cache_check_last` are dependencies. If the job fails, these objects are cleared and the whole procedure is repeated.
Upgrade
The job also contains an upgrade script, which updates the current job from version 2.31 to 2.32. The upgrade makes alterations to the parameters and adjusts the main procedure.
Compatibility
The job is compatible with MySQL instances that have engine support and use the global_variables_information_schema.
On overall, ‘Binlog cache check’ job is crucial for optimizing the performance of MySQL transactions and maintaining database health.