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.2 |
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’ & newer_than_ninefour = 1 & maj_version < 13] |
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: The SQL Statistics job collects and analyzes performance statistics for SQL statements executed on a PostgreSQL database.
- Why: This job is important because it allows database administrators to monitor and optimize the performance of SQL queries by providing insights into query execution and resource usage.
- Manual checking: To manually check SQL statistics, you can query the PostgreSQL’s `pg_stat_statements` view using the following command:
SELECT * FROM pg_stat_statements;
Details
- The job collects data such as total execution time, number of executions, and rows returned for each SQL statement.
- It utilizes the `pg_stat_statements` module, which must be enabled in the PostgreSQL configuration.
- Additional settings such as `track_activity_query_size` and `shared_preload_libraries` need to be configured to fully utilize the capabilities of the monitoring job.
Job Configuration
Parameter Name |
Description |
history threshold |
Defines the number of days to retain SQL statistics in historical tables. |
dbwatch database max size |
Sets the maximum size in MB for the dbWatch database to prevent excessive space usage. |
collect execution plan |
Determines whether to collect the execution plan for SQL queries and store them in the history table. |
collect internal statistics |
Indicates how often to check the size of internal objects within the dbWatch database. |
SQL repository max size |
Establishes the maximum space usage for the SQL repository as a percentage of the dbWatch database’s maximum size. |
min logical reads |
Sets the minimum logical reads required for a SQL statement to be recorded. |
return status |
Specifies the return status such as ALARM, WARNING, or OK based on certain conditions being met regarding space usage. |
Installation Requirements and Setup
- The pg_stat_statements extension must be installed and properly configured, which includes modifying settings in the `postgresql.conf` file.
- Provides automated setup scripts to ensure the extension is created if it doesn’t exist.
Handling Dependencies
This job includes multiple dependencies essential for proper operation and data consistency:
- dbw_sql_stat_current: Tracks the current SQL execution statistics.
- dbw_sql_stat_histr: Handles the historical execution statistics storage.
- dbw_sql_text_histr: Maintains historical records for query texts.
- dbw_internal_sql_stat_histr: Used for storing internal SQL statistics records.
- dbw_match_similarity_tab: Tracks similarities between SQL queries for optimizing performance handles.
Implementation
The job provides a comprehensive implementation strategy that includes:
- Monitors SQL statements and their impact on the database.
- Proposes creation of various helper tables and functions to manage statistics.
- Determines the new and historical SQL registrations.
- Implements monitoring of not just SQL statements but also internal database sizes and resource statistics.
Reporting Templates and Cleanup Mechanisms
- Includes detailed templates for reporting the SQL performance statistics.
- Offers optional clean-up functions to maintain database hygiene by removing outdated data sets.
Updates and Maintenance
A monitoring job like this requires regular updates to adjust to the changes in database versions and underlying system architecture, ensuring compatibility and performance efficiency.