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.