Threads statistics
Job details
| Name: |
Threads statistics |
| Platform: |
Mariadb |
| 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_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_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 monitoring job is to oversee and analyze the MariaDB threads statistics within a database instance, focusing on aspects such as threads created per minute, threads cached, and threads connected.
- Why: This job is crucial as it helps in assessing the performance and health of the database by tracking how threads are being managed. Proper monitoring can prevent performance degradation by ensuring threads are being utilized and managed efficiently. If the system consistently reaches high thresholds of thread creation, it may indicate underlying performance issues leading to a more proactive management strategy.
- Manual checking: Manual verification can be done through the following SQL commands:
select threads_created, threads_created_diff, threads_cached, threads_connected, histr_date from dbw_threads_statistics_histr order by histr_date asc;
Implementation Details
- The query /.[hasengine=‘YES’ & global_status_information_schema = ‘1′] ensures that the job only operates on MariaDB instances that support the required features.
- Procedures and tables such as dbw_threads_statistics, dbw_threads_statistics_histr, and dbw_threads_statistics_last are used to maintain statistics and history.
- The default schedule runs at intervals such as every 5 minutes within an hour which ensures frequent monitoring and up-to-date statistics.
Dependency Structure
- This job relies on several dependencies to ensure smooth execution and robust error handling:
- dbw_threads_statistics (Main procedure)
- dbw_threads_statistics_histr and dbw_threads_statistics_last (Dependency tables)
- On failure, cleanup actions such as dropping these tables and procedures are enacted to ensure consistency and free up resources.
Parameters Management
- Parameters like warning threshold, alarm threshold, and time thresholds are configurable allowing fine-tuning based on specific performance considerations of the database.
- The monitoring system can either operate in a passive mode, where it only collects and logs data, or in an active mode, where it can issue warnings and alarms based on predefined thresholds.
Reporting Mechanism
- The results of the monitoring tasks contribute to reports that provide insights on thread activity such as creation and caching.
- A key report component is the presentation of the thread_cache_size and how frequently new threads are created versus being fetched from the cache, influencing performance optimization decisions.
Conclusion
This job forms an integral part of the database performance monitoring suite in dbWatch Control Center by providing administrators with detailed and actionable insights into thread usage and management in a MariaDB environment. This proactive monitoring facilitates better resource management and performance tuning in dynamic database environments.