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

Why This Job is Important

Job Configuration and Operation

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

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.