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. |
| 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_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 and check the number of transactions that used the temporary binary log cache but exceeded the value of binlog_cache_size in MySQL databases. It checks how often the cache is too small to hold the statements, and disk IO is required. It is designed to provide alarms and warnings if certain thresholds are exceeded.
- Why: This job is important because it ensures that data integrity and performance are maintained in a MySQL environment. It evaluates the effectiveness of the binary log cache and helps identify potential problems where cache overflows result to disk usage, that can impact performance. This job also keeps a record of statistics over a period which can help in identifying patterns and making informed decisions regarding database management.
- Manual Checking: You can manually check this by issuing these SQL commands:
SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_name = 'BINLOG_CACHE_USE'SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_name = 'BINLOG_CACHE_DISK_USE'Job Description
This monitoring job known as ‘Binlog cache check’, developed by dbwatch.com, runs by default every 10 minutes, tracking the number of times MySQL transactions used the temporary binary log cache and exceeded the specified value, binlog_cache_size. The severity of the issue is measured in terms of alarms and warnings which are dependent on the thresholds set within the defined parameters of the job.
Furthermore, transactions that cause binary log cache to overflow and consequently use a temporary file to store statements are also monitored. The effectiveness of the cache is determined based on whether cache overflows and prompts disk IO. Ideally, the binary log cache disk use ratio should remain at 0. If this is not the case, it suggests that the binlog_cache_size may need to be increased.
This job also details specific tasks for cleanup if the main procedure fails, including dropping the main procedure and two dependency tables involved in the process. It also specifies the creation tables, and registers parameters in the database.
Job Reports
The job produces reports that show disk usage and cache use. The reports show the average binary log ratio over a certain period of time, and also the total times the binary log cache has been used and how many times it had to use the disk. These reports can be used to analyze how often the cache is too small to hold statements.
Upgrades
The job also includes implementation details for upgrading from a previous version, indicates specified improvements made, and real-time adjustments based on new set parameters.
Compatibility
This job works exclusively on instances running MySQL branch ‘1′ with a global variable performance schema. It is tailored for databases that operate on the MySQL engine, making it useful for MySQL database administrators in improving database performance.
Overall, this monitoring job not only measures the efficiency of binary log cache in MySQL databases but also assists in identifying potential pitfalls to watch out for, in an effort to optimize the performance of databases.