SQL statistics

Job details

Name:	SQL statistics
Platform:	Postgres
Category:	Performance
Premium package:	SQL Performance package
Description:	Collects performance statistics for the SQL statements.
Long description:	Collects performance statistics for SQL statements from pg_stat_statements view. During installation it checks if the pg_stat_statements extension is created, and create it if it is not. For tracking which queries get executed in your database you need to add the following entries to your postgresql.conf file:
Version:	1.3
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & maj_version > 12 ]

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 size (in MB) of dbwatch database.
collect execution plan	YES	Collects execution plan into history table if set to “YES“.
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 logical reads	100	Minimum logical reads 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

Collects performance statistics for SQL statements executed within a PostgreSQL database environment.
Tracks queries to help analyze database load and optimize query performance.

Why This Job Is Important

Ensures critical insight into query efficiency and resource usage.
Helps in identifying slow or resource-heavy queries which can degrade database performance.
Facilitates proactive database management and tuning based on comprehensive and detailed statistics.

Manual Checking

To manually check if the ‘pg_stat_statements’ extension is properly set up and to verify configuration parameters, use:

SELECT * FROM pg_stat_statements;

To manually check the extension creation:

SELECT installed_version from pg_available_extensions WHERE name = 'pg_stat_statements';

For the current configuration settings related to ‘pg_stat_statements’:

SELECT name, setting FROM pg_catalog.pg_settings WHERE name IN ('track_activity_query_size', 'shared_preload_libraries', 'pg_stat_statements.track', 'pg_stat_statements.max');

Detailed Description

Collects detailed metrics such as number of calls, total execution time, rows affected, blocks hit, and more for each SQL command.
Monitors and archives these statistics over time, saving them in ‘dbw_sql_stat_current’, ‘dbw_sql_stat_histr’, and several other related tables.
Includes several maintenance and cleanup mechanisms to ensure data is current and storage is efficiently used.

Essential SQL Tables and Commands

Table	Description
dbw_sql_stat_current	Holds current SQL execution statistics
dbw_sql_stat_histr	Records historical data about SQL execution
dbw_sql_text_histr	Logs the text of SQL queries
dbw_internal_sql_stat_histr	Statistical summary about the internal operations and sizes
dbw_internal_sql_size_histr	Tracks size history of internal tables
dbw_match_similarity_tab	Helps in matching similar SQL texts

Key Functions Implemented

dbw_sql_statistics: Core function for managing SQL stats tasks.
dbw_sql_internal_clean: Cleans historical statistical data to ensure accurate reporting.

Configuration and Installation

The job automatically checks and sets up the necessary PostgreSQL configuration parameters and extensions if they do not exist.
Scheduled to run every five minutes by default, providing near real-time statistics update.

Usage in Database Health Checks and Reporting

Allows for comprehensive health checks of SQL queries contributing to load.
Generates reports and graphs to visually analyze trends, pinpointing issues and potential performance bottlenecks.
Facilitates database optimization strategies based on empirical data.