Binlog cache check

Job details

Name:	Binlog cache check
Platform:	Mariadb
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_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘0′ & global_status_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 monitoring job is designed to assess the efficiency and capacity of the binary log cache system within MariaDB instances, focusing on transactions that exceed the pre-set binlog_cache_size and utilize a temporary file for transaction statement storage.
Why: This job is essential to ensure the performance stability and operational efficiency of database transactions in MariaDB. Monitoring helps identify and mitigate instances where large transactions are not efficiently handled by the binary log cache, preventing potential performance bottlenecks and excessive disk usage.
Manual Checking: To manually check the same statistics, you can execute the following SQL commands:

SELECT VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME IN ('BINLOG_CACHE_USE', 'BINLOG_CACHE_DISK_USE');

Implementation Details

This job involves several SQL structures and procedural operations to gather and analyze data:

Tables:
- ‘dbw_binlog_cache_check_histr’ stores historical data about cache usage and disk usage.
- ‘dbw_binlog_cache_check_last’ temporarily holds the most recent data for comparison and update purposes.

Procedures:
- ‘dbw_binlog_cache_check’: Main procedure that calculates the ratio of disk use to cache use, updates the historical tables, and determines whether the current state triggers a warning or alarm based on pre-defined thresholds.

Express Install and Force Install:
- Both are set to true, suggesting that the job is crucial and must be maintained consistently across applicable systems.

Monitoring Logic

The core functionality involves:

Determining the amount of binlog cache used versus disk usage.
Comparing current data with historical data to track trends and spikes.
Triggering warnings or alarms based on pre-set thresholds for disk usage ratios and cache usage values.
Storing computed data in a historical table for trend analysis and operational auditing.

Job Dependencies and Clean-up

Dependencies include:

Main procedures and tables that are crucial for the job execution, such as ‘dbw_binlog_cache_check’ and ‘dbw_binlog_cache_check_histr’.

Clean-up activities involve:

Dropping tables and procedures in case of failure to ensure that no corrupted or partial data disrupts the normal operation.

Reporting and Visualization

This monitoring job uses dbWatch report templates to provide visualizations and reports that can assist in evaluating the performance of the binary log cache over time. Key reports include:

Graphs showing the percentage of disk use versus cache use.
Trends in cache and disk usage over time to identify patterns or anomalies.

Metric	Visual Representation
Disk Use (%)	Category Chart – History Date vs. Disk Use (%)
Total Cache Usage vs. Disk Usage	# per sec., categorized by type over time

These reports are integral for DBAs to quickly assess and react to data trends, facilitating proactive management of database performance and capacity planning.