SQL statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect comprehensive performance statistics for SQL statements executed on the MySQL database, utilizing the performance_schema’s event statements summary by digest.
• Why: Monitoring SQL statement performance is crucial for identifying bottlenecks, inefficient queries, and opportunities for optimization. The detailed statistics help in understanding system behavior and ensure database performance stability.
• Manual checking: Manual checking can be done by directly querying the MySQL performance schema with the appropriate SQL commands to retrieve statistics:

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:

• Creating and managing tables and indices for current and historical SQL statistics.
• Custom procedures to insert and update statistics records into these tables.
• Conditional execution based on parameters set for history threshold, maximum DB size, and interval for collecting internal statistics.
• Error handling and clean-up mechanisms to maintain data integrity and system stability.

Key Components

• “dbw_sql_stat_current” – A table maintained for real-time SQL statistics.
• “dbw_sql_stat_histr” – A table to retain historical summary data about SQL statements.
• “dbw_sql_text_histr” – A historical table for the text of the SQL statements.
• “dbw_internal_sql_stat_histr” and “dbw_internal_sql_size_histr” – Internal tables to monitor SQL and index sizes over time.
• “dbw_sql_statistics”, “dbw_sql_internal_clean” – Procedures to populate, clean and manage the statistical data.

Cleanup on Fail

• In the event of a job failure, specific cleanup operations are initiated to remove temporary or corrupt data ensuring system cleanliness.

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:

• Main procedures and several auxiliary SQL and history tables are checked before the job runs.

Parameters Overview

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

• history threshold
• dbwatch database max size
• collect internal statistics
• SQL repository max size
• min TIMER_WAIT
• return status

Reporting Template

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

• SQL statistics overview
• Internal table and index size history

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.