Temporary table check
Job details
| Name: | Temporary table check |
| Platform: | Mysql |
| 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_mysql_branch=‘1′]/.[hasengine=‘YES’ & use_global_variables_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
The XML script describes a task for dbWatch Control Center, which is a database monitoring tool. The task is meant for monitoring MySQL databases, with a specific focus on temporary tables that are written to disk.
Purpose
- Purpose: Monitor the percentage of MySQL temporary tables written to disk.
- Why: Temporary tables are a natural part of database management and most are stored in memory for speed. However, when temporary tables are written to disk instead, it can indicate potential performance issues. If there’s a high percentage of tables being written to disk, it can be a sign that the memory space for temporary tables (‘tmp_table_size’) might need to be increased. Regular monitoring helps identify potential bottlenecks before they become issues.
- Manual checking: Use the SQL procedures created in the “implementation” part of the job content to manually check the values. These procedures gather the required data and calculate the ratio of temporary tables written to disk.
Description
The task checks MySQL instances that have a working Information Schema and are running on engines that support it. The performance category is monitored, notably tracking the creation of temporary tables and how many of them are written to disk, as opposed to being stored in memory.
This job uses several stored procedures and temporary tables to perform this check, such as ‘dbw_tmp_table_check’, ‘dbw_tmp_table_check_last’, and ‘dbw_tmp_table_check_histr’. It also defines several parameters to customize the checking process according to user needs.
Dependencies of the job include several objects, among which are procedures and tables that it requires for correct execution. If any part of the job fails, it cleans up by dropping these dependencies.
Reporting
The job generates detailed reports on temporary table usage, including the proportion of disk-written temporary tables. From these, users can derive insights into their MySQL database performance and, if necessary, adjust MySQL parameters accordingly.
Updates
An upgrade process is also defined in this job to handle changes for a new version. Custom SQL is applied to change table structure, update records, and perform cleanup tasks to make sure everything runs properly.
Conclusion
In conclusion, this monitoring job by dbWatch Control Center provides a valuable means to manage the performance of MySQL databases, monitoring the use of temporary tables and alerting administrators if a high proportion is being written to disk. It saves time and improves the efficiency of the database management process by automatically handling this monitoring task.