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_performance_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 monitoring job titled “Temporary table check” is used to monitor the percentage of temporary tables that are being written to disk in a MySQL database.
- Why: This job is significant as excessive disk writing of temporary tables can degrade database performance, hence potentially affecting application speed and performance. Regular monitoring of these parameters can help in avoiding any unforeseen database performance issues.
- Manual checking: To manually check this, you can run SQL queries against tables generated by this job and stored in the tables including dbw_tmp_table_check_status, dbw_tmp_table_check_last, dbw_tmp_table_check_histr.
Description
The job is a part of the dbwatch package and is developed by the company dbwatch.com. The specific artifact of this job is mysql_tmp_table and the related object is dbw_tmp_table_check. The frequency of the monitoring is determined by the default schedule included in the XML content.
Important Statistics
This job checks the percentage of temporary tables that are being written to disk at specific intervals. Here, temporary tables are automatically created tables by MySQL while executing statements. If a temporary table outgrows its allowed size in memory, MySQL will automatically convert it to an on-disk MyISAM table. This job helps in monitoring these conversions and also the disk usage by these temporary tables.
Triggers
The job raises a warning or an alarm if certain thresholds are reached. These thresholds include ‘ratio warning threshold’, ‘ratio alarm threshold’, ‘warning threshold (time)’, and ‘alarm threshold (time)’.
Dependencies
Also, this job depends on multiple procedure and tables, which are included in the “dependencies” section of the XML content. These dependencies are required for the job to execute and generate the required statistics. The job has instructions to clean up in case they fail.
Report Template
Upon the conclusion of each monitoring cycle, a report is generated using a predefined template. This report includes the percentage of automatically generated temporary tables that have been written to disk.
Upgrade and Install Instructions
The XML file includes instructions to upgrade from a specific version (2.31) to the current version (2.32). Furthermore, there are express install and force install options, for quick and forced installations, respectively.
Compatibility
The monitoring job is compatible with MySQL databases where the engine is enabled and an accessibility to global variables is provided by the performance schema.
Metadata
The metadata indicates which category this job belongs to. In this case, it falls under the ‘Performance’ category.
Further Management
This job provides a set of SQL scripts and tools that allow you to manage the job more efficiently. For example, you can modify parameters, thresholds, and scripts as needed to tailor the monitoring to your environment.
Please remember that careful configuration and regular maintenance of your monitoring tasks can help to ensure a well-performing database.