Threads statistics
Job details
| Name: | Threads statistics |
| Platform: | Mysql |
| Category: | Performance |
| Description: | Checks the number of threads created per minute. |
| Long description: | ‘thread’, mean either a client connection or an OS thread and there is 1-1 correspondence between threads and connections. |
| Version: | 1.1 |
| Default schedule: | 2,7,12,17,22,27,32,37,42,47,52,57 * * * |
| 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 | 30 | The maximum value of created threads per minute (over a period of time defined by the [time threshold] parameter) before a warning is triggered. |
| alarm threshold | 600 | The maximum value of created threads per minute (over a period of time defined by the [time threshold] parameter) before an alarm is triggered. |
| threshold (time) | 20 | A period of time (in minutes) which must be passed (combined with the number of aborted clients) before an alarm or a warning is returned by the procedure. |
| 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 threads created per minute in MySQL databases. Threads in this context refer to either a client connection or an operating system (OS) thread, and there’s a one-to-one correspondence between threads and connections.
- Why: This job is crucial in ensuring optimal database performance and stability by tracking and reporting on thread creation, caching and client connections. By setting defined thresholds, it helps trigger warnings and alarms for potential issues before they escalate, thus preventing server crashes and performance bottlenecks.
- Manual checking: This monitoring can manually be achieved by querying MySQL’s performance_schema.global_status and performance_schema.global_variables for the current ‘Threads_created’, ‘Threads_cached’, ‘Threads_connected’ and ‘thread_cache_size’ values respectively.
What does it monitor
- Threads_created: This refers to the number of OS threads created so far.
- Threads_cached: This refers to the number of OS threads cached. These cached threads are reused when new connections are established.
- Threads_connected: This is the number of client connections.
How does it work
The job uses SQL queries to pull the relevant data from the system’s performance schemas. The job runs at certain intervals, with the default schedule running every 5 minutes.
The job calculates the frequency of thread creation and compares this to set warning and alarm thresholds. If thread creation frequency surpasses these thresholds and alarms are enabled, the job will trigger either a warning or alarm status.
The job also records statistical data in history tables for further analysis and keeps these records for a period defined by the ‘history threshold’ parameter.
Report Output
The job produces a report titled ‘Aborted clients’ which gives the value of ‘thread_cache_size’ and displays graphic thread cache statistics over time. The report provides a visual representation of thread creation, caching and connection data, making it easier to track and monitor changes over time.
Metadata
The job is categorized under the ‘Performance’ category.