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

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.