Temporary table check

Job details

Name:	Temporary table check
Platform:	Mariadb
Category:	Performance
Description:	Shows percentage of temporary tables written to disk
Long description:	Task shows the percentage of temporary tables which have been written to disk
Version:	2.32
Default schedule:	3,13,23,33,43,53 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & is_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_information_schema = ‘1′ ]

Parameters

Name	Default value	Description
ratio warning threshold	50	If this threshold is reached over a period of time (controlled by parameters “warning threshold (time)”), a warning is returned. At the same time, the average value of CREATED_TMP_TABLES per second must be higher than the “min CREATED_TMP_TABLES“ parameter value for the entire time period.
ratio alarm threshold	80	If this threshold is reached over a period of time (controlled by parameters “alarm threshold (time)”), an alarm is returned. At the same time, the average value of CREATED_TMP_TABLES per second must be higher than the “min CREATED_TMP_TABLES“ parameter value for the entire time period.
keep data for	7	The number of days to keep the data for.
alarm threshold (time)	30	A period of time (in minutes) which must be passed (combined with average values of CREATED_TMP_TABLES) before an alarm is returned by the procedure.
warning threshold (time)	30	A period of time (in minutes) which must be passed (combined with average values of CREATED_TMP_TABLES) before a warning is returned by the procedure.
min CREATED_TMP_TABLES	100	The minimum average value of CREATED_TMP_TABLES 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.

Job Summary

Purpose: The purpose of this job is to monitor and report on the performance of temporary tables in MariaDB, specifically focusing on the percentage of temporary tables that are written to disk.
Why: Monitoring the use of disk for temporary tables is crucial for database performance optimization. High usage of disk-based temporary tables can significantly slow down operations compared to memory-based tables. This job helps in identifying and mitigating performance bottlenecks by alerting on thresholds and providing statistical data for analysis.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT variable_name, variable_value FROM dbw_tmp_table_check_status;

Job Description

The monitoring job is defined for MariaDB instances that support the `INFORMATION_SCHEMA` for global status data.
It tracks and reports on execution details and the status of temporary tables, recording metrics about their creation and the extent to which they are written to disk versus stored in memory.
The job uses thresholds to trigger warnings and alarms if the ratio of disk-based temporary tables to total temporary tables exceeds specified limits.

Implementation Details

The job consists of several SQL script parts that:
- Create necessary tables for historical data storage.
- Define a procedure `dbw_tmp_table_check` for checking and logging the state of temporary tables.
- Handle possible errors and update the monitoring status accordingly.
Cleanup procedures are defined to remove historical data older than a specified number of days and to drop tables and procedures in case of deployment failure.

Dependency and Cleanup

Dependency Object	Object Type	Description	Cleanup on Failure
dbw_tmp_table_check	Procedure	Dependency procedure	true
dbw_tmp_table_check_histr	Table	Stores historical data of checks	true
dbw_tmp_table_check_last	Table	Stores the last recorded values	true
dbw_tmp_table_check_status	Table	Stores current status and variables	true

Reporting

The job generates reports that provide insights into:
- The maximum allowed size for temporary tables in-memory and changes to temporary table size settings.
- Historical data and current status of temporary use, distinguishing between memory-based and disk-based usage.
The reports include:
- A table showing current configuration of key parameters affecting temporary table behavior.
- A line graph illustrating the trend of disk usage ratio over time, helping in visual analysis of performance.
- Another graph showing the evolution of the number of temporary tables created versus those written to disk.

This structured reporting aids in proactive database management, helping to adjust system settings to optimize performance and avoid potential slowdowns due to excessive disk-based temporary table usage.