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.
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 = ‘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 binary log cache in MariaDB and check if transactions are using temporary file storage due to exceeding the configured binlog_cache_size, potentially causing performance issues.
Why: This job is crucial for maintaining database performance and efficiency. Monitoring how often the binlog cache size is exceeded can help in tuning the cache settings to minimize disk I/O, which is generally slower than memory operations. Noticing trends where the cache size is consistently exceeded can indicate that an increase in binlog_cache_size might be necessary.
Manual checking: To manually check the binary log cache usage in MariaDB, you can execute the following SQL command:

SELECT VARIABLE_VALUE as "Binlog Cache Use" FROM performance_schema.global_status WHERE variable_name = 'BINLOG_CACHE_USE';

Detailed Analysis

This task targets MariaDB instances specifically by checking for the is_mariadb_branch flag.
It only operates on instances where both the engine is available and the performance schema is enabled (checked by hasengine and global_status_performance_schema flags).
The primary function of this task is outlined in the dbw_binlog_cache_check procedure, which calculates the number of times binlog cache was used and how often it exceeded the cache size.
It automatically establishes various statistics tables such as dbw_binlog_cache_check_histr and dbw_binlog_cache_check_last, which store historical data and the most recent cache usage data respectively.
The procedure also determines if a warning or alarm should be triggered based on the predefined thresholds for disk use percentage and cache use frequency.
Cleanup routines are in place to remove any residual data structures in case of installation failure, ensuring no remnants are left that might affect database performance or integrity.

Implementation Code

The SQL code associated with task implementation involves creating temporary storage tables and a procedure that calculates and logs the necessary statistics. It also contains error handling to log and handle exceptions gracefully.
Post-execution, the job cleans up old historical data beyond the configured history threshold days to manage space effectively.

Reporting and Notification

A dbWatch report template is set up to visualize the performance data collected by this task.
Reports can plot the cache usage and disk usage over time to help identify patterns and potential areas of concern.
Alerts can be configured to notify the DBAs when thresholds are exceeded, allowing for proactive management and tuning of database settings.
The presentation within the dbWatch Control Center includes graphs to visually represent the binlog cache usage and the proportion that required disk usage, providing an intuitive dashboard for DBAs to monitor these metrics effectively.

By monitoring these aspects, the job supports maintaining the operational health and performance of MariaDB instances under management, thereby ensuring database reliability and efficiency.