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_information_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 thread statistics in MySQL instances. It checks the number of threads created per minute, number of OS threads cached, and the number of client connections.
- Why: It’s important as it helps in tracking the performance and behavior of the database, ensuring efficient resource usage and seamless functioning. It allows understanding whether threads and connections are being efficiently reused, or too many threads are being newly created causing a strain on Azure resources. If the threshold of created threads per minute is surpassed, warnings or alarms can be triggered.
- Manual checking: The data can be manually checked from tables ‘dbw_threads_statistics_histr’ and ‘dbw_threads_statistics_last’, as well as the global status and variables information schemas.
Job Configuration
This job functions through an implemented procedure ‘dbw_threads_statistics’:
- It fetches current thread statistics.
- It compares the number of threads created since its last run vs. current number.
- It maintains a historical record of threads created, threads cached, and connections.
- It calculates the frequency of thread creation in the last predefined minutes and averages over the last 24 hours.
- Depending on the results and thresholds set, it triggers or disables warnings and alarms.
- It also cleans up historical records older than a set threshold.
Then it populates tables in the control center with the monitored data for a graphical visualization of thread cache statistics over time.
Job Report
This job creates a report ‘Aborted clients’ which presents the value of ‘thread_cache_size’ variable which when increased can potentially improve performance and a graph that visually represents threads created, cached, and connections over time.