SQL statistics


Job details

Name: SQL statistics
Platform: Mysql
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.4
Default schedule: 0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & is_mysql_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘1′ & maj_version > ‘5.6′]

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

SELECT * FROM performance_schema.events_statements_summary_by_digest;

Implementation Details

The job consists of several steps to gather statistics, manage historical data, and ensure proper handling of the database space:

Key Components

Cleanup on Fail

DROP TABLE statements are utilized to clear artifacts such as:

drop table dbw_sql_statistics_histr;
drop table dbw_sql_statistics_last;
drop table dbw_sql_stat_current;
...

Dependencies Check

A series of dependency checks ensure all required objects are in place for the job to function correctly. Failure in any dependencies triggers a cleanup:

Parameters Overview

The following parameters are crucial for job configuration but are excluded from deep analysis as per request:

Reporting Template

The execution results populate internal tables that can be visualized through predefined reports, proving insights like:

This monitoring job is vital for maintaining optimal performance and operational health of MySQL database environments, especially those managing extensive data transactions and complex query operations.