SQL statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and manage performance statistics for SQL statements on MariaDB platforms specifically for versions less than 10.5, which do not support certain performance_schema features natively.
• Why: This job is important because it enables detailed performance tracking of SQL queries, which can help in optimizing database operations, identifying resource-intensive queries, and thereby improving overall performance and stability of the database system.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT * FROM performance_schema.events_statements_summary_by_digest;

Job Details

• This monitoring job targets MariaDB instances specifically branches that are identified using performance schema where the major version is less than 10.5.
• The tasks involve collecting data from the ‘performance_schema.events_statements_summary_by_digest’ table and managing historical data of SQL statements.
• Uses dbWatch functionality to install necessary database objects and perform clean-up on failure.

Implementation Necessary Actions

• This job installs various database objects including tables and indexes tailored for tracking and storing SQL statement performance metrics.
• Key procedures used include:
  • ‘dbw_sql_statistics’ for collecting SQL statement statistics.
  • ‘dbw_sql_internal_clean’ for cleaning up statistics data based on configurable intervals or conditions.

Key Configurations and Parameters

• Maintains configurations and parameters essential for monitoring and controlling the range and depth of data collection, such as:

max_digest_length = 4096
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096
* This configuration is crucial to ensure accurate and meaningful statistics collection especially for longer SQL queries.

Dependencies and Data Management

• The job has multiple dependencies on internal statistical tables like ‘dbw_sql_stat_current’, ‘dbw_sql_stat_histr’, etc., to function effectively.
• It ensures rigorous data integrity and cleanliness during operational failures by enforcing clean-up protocols.
• Data from the system and user-created tables are used to carry out functional reporting on SQL performance statistics making it crucial for maintaining system stability and efficient performance debugging.

Reporting and Monitoring

• Employs a comprehensive reporting template incorporated through dbWatch, providing insights on SQL statistics and internal table size history.
  • Reports include total SQL statements tracked, breakdown of active vs. inactive SQL statements, and detailed execution timing metrics.

Scheduled Jobs and Frequency of Execution

• The default execution schedule is set to run every 5 minutes, this periodic check helps in keeping the statistics up-to-date and allows for proactive performance management.

Cleanup Operations

• The implementation includes procedures for clean-up actions that help in maintaining optimal database performance by removing outdated records and managing disk space efficiently.

Through effective monitoring, reporting, and cleanup mechanisms, this dbWatch job aids in maintaining high performance and swift identification of potential issues within MariaDB installations, emphasizing databases that are still running on older, less autonomous versions.