SQL statistics
Job details
| Name: | SQL statistics |
| Platform: | Mariadb |
| Category: | Performance |
| Premium package: | SQL Performance package |
| Description: | Collects performance statistics for SQL statements. |
| Long description: | Collects performance statistics for SQL statements from performance_schema.events_statements_summary_by_digest table. |
| Version: | 1.2 |
| Default schedule: | 0,5,10,15,20,25,30,35,40,45,50,55 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & is_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘1′ & maj_version > ‘10.4′] |
Parameters
| Name | Default value | Description |
|---|---|---|
| history threshold | 7 | The maximum number of day to kept statistics for in the historic tables. |
| dbwatch database max size | 5000 | Maximum dbwatch database size (in MB). |
| collect internal statistics | 30 | How often (in minutes) size of internal objects should be checked. “SQL statistics” job collects a large amount of data in the dbWatch schema tables, so it is important to keep track of space usage in the dbWatch database. |
| SQL repository max size | 80 | Maximum space consumption (in percentage) of maximum size of the dbWatch database for internal/repository objects that contain statistics for SQL statements. |
| min TIMER_WAIT | 1000 | Minimum TIMER_WAIT value (per execution) for a SQL statement to be registered into history tables (repository tables). |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when reached maximum space consumption (in percentage) of maximum size of the dbWatch database (parameter “SQL repository max size”) |
Job Summary
- Purpose: The job is tasked with collecting and managing performance statistics for SQL statements specifically for MariaDB instances. It targets monitoring performance metrics and environmental settings that assure effective database performance tracking and resolution of efficiency-related issues.
Why This Job is Important
- Analysis Insight: It primarily collects extensive statistics from the MariaDB performance_schema’s events_statements_summary_by_digest table and maintains these statistics in various custom tables for current, historical, and text analysis of SQL queries.
- Impact on Database Health: By actively gathering and refreshing these statistics, the job aids in identifying performance bottlenecks, inefficient SQL queries, and aids database administrators in tuning the queries.
- Automatic vs. Manual: The automated collection and cleanup tasks significantly reduce the manual effort required in periodically checking and cleaning the database schema which would potentially be error-prone and inefficient if done manually.
Job Configuration and Operation
- Scheduling: Configured to run every 5 minutes as per the default schedule setting in XML.
- Data Management: Employs multiple internal checks to prevent data overflow and removes out-of-threshold historical entries to manage the database size effectively.
- Error Management: Integrates robust error handling during its SQL operations to ensure stability and continuous monitoring in the occurrence of internal execution failures.
Dependencies and Cleanup
The job has several dependencies including various tables like dbw_sql_stat_histr, dbw_sql_stat_current, and procedures like dbw_sql_statistics which are critical for its operation. On failure, certain cleanup scripts are executed as shown below:
| Code | Description |
|---|---|
| “drop table dbw_sql_statistics_histr” | Drops the historical statistics table |
| “drop table dbw_sql_statistics_last” | Drops the last-seen statistics table |
| “drop table dbw_sql_stat_current” | Clears current statistics |
| “drop procedure dbw_sql_statistics” | Removes the main statistical procedure |
What is Monitored?
| Statistic Type | Description |
|---|---|
| Total SQL statements | Monitors total SQL executions |
| New SQL statements | Tracks newly executed SQL statements |
| Active/Non-active SQL statements | Differentiates between actively and rarely used statements |
| Historical records | Keeps track of historical execution times and other metrics |
Considerations & Recommendations
- Ensure appropriate storage allocation as the job can potentially generate large amounts of data.
- Regular monitoring of the job’s performance and the impact on the server’s overall performance is recommended.
- Database settings and thresholds should be reviewed and possibly adjusted based on the specific operational and business requirements.
This job setup provides a comprehensive solution for SQL performance monitoring and maintenance activities within a MariaDB environment, critical for maintaining efficient and optimal database operation.