Introduction
I think identifying what to tune matters more than the SQL tuning itself.
You need to know clearly the limits of the information each DBMS provides, and you need to combine information scattered in many places to reach a sound conclusion. If an inaccurate judgment leads you to pick the wrong SQL, the result can be worse than doing nothing, so this step could not be more important.
In PostgreSQL, an extension called PG_STAT_STATEMENTS is widely used to select tuning targets. It is very general-purpose, but the information it provides falls short in some areas.
In this post, we look at Percona's PG_STAT_MONITOR extension, which extends PG_STAT_STATEMENTS, and at how to use it to select tuning targets and monitor SQL performance.
📢 This post is based on PostgreSQL 15. Some features may not be available in earlier versions, and some columns may be missing from the PG_STAT_MONITOR view. Parameter defaults may also differ.
What is PG_STAT_MONITOR?
Percona's PG_STAT_MONITOR extension is an SQL performance monitoring tool built on PG_STAT_STATEMENTS to provide richer performance information. The collected SQL performance information is available through a single view of the same name, where you can see the original SQL, the execution plan, performance, and other metadata.
Unlike PG_STAT_STATEMENTS, PG_STAT_MONITOR does not stop at cumulative per-SQL information; it shows SQL performance over time through interval information called time buckets. This lets users analyze SQL much more closely. The main features and constraints of PG_STAT_MONITOR are as follows.
PG_STAT_MONITOR features
- Time Buckets: PG_STAT_MONITOR stores statistics for configured time intervals.
- Table and index access statistics per SQL: You can easily identify every SQL that accesses a specific table.
- Client information: You can see detailed client connection information such as the user name (
username), application name (application_name), and client IP (client_ip) that ran the SQL.
- SQL execution plan: You can see the execution plan of the SQL. (
query_plan)
- Monitoring by SQL type: You can monitor SQL separately for SELECT, INSERT, UPDATE, and DELETE. (
cmd_type,cmd_type_test)
- Top SQL tracking: You can track SQL run inside functions or procedures.
- SQL error analysis: You can see problems with SQL that ended with ERROR, WARNING, or LOG status. (
elevel,sqlcode,message)
- Histogram: It provides the distribution of execution counts per execution-time range as a histogram for closer analysis.
PG_STAT_MONITOR constraints
- PG_STAT_MONITOR is available from PostgreSQL 11.
- It is not included by default when you install the PostgreSQL engine and must be installed separately, so it cannot be used on managed databases such as RDS.
Extension Setup
The test environment is as follows. The PG_STAT_MONITOR extension is installed with the package manager (yum) in an environment with internet access, at the same version as the database engine (15).
- CentOS 7.9
- PostgreSQL 15
📢 You can use several yum repositories, such as the Percona repository, the PostgreSQL PGDG repository, PGXN, and github. Check the name of the PG_STAT_MONITOR package in each repository and install it.
1. Download and install PG_STAT_MONITOR
2. Change the configuration (postgresql.conf) and restart
3. Create the PG_STAT_MONITOR extension
📢 In the test environment, the PG_STAT_MONITOR extension was created in the postgres database. If you have multiple databases, you must create the extension in every database to collect statistics for each one.
Using PG_STAT_MONITOR
1. Time Grouping
Summarizing SQL by a preset collection period (time) to review performance is a very useful capability, but unfortunately few DBMSs provide it. PG_STAT_MONITOR lets you set the number of time buckets, which are used in a circular structure, and the retention period (time) per time bucket, so you can review SQL performance by period, much like Oracle AWR.
The number of time buckets is set with the
PG_STAT_MONITOR.PGSM_MAX_BUCKETS parameter, and the default is 10 (up to 20,000). The period each time bucket covers is set with the PG_STAT_MONITOR.PGSM_BUCKET_TIME parameter, and the default is 60 Sec (up to 2,147,483,647).With this, you can view SQL performance data for any period you want in PG_STAT_STATEMENTS form, and because it is calculated as the sum of point-in-time data rather than cumulative data, it is more accurate than PG_STAT_STATEMENTS. (See Script 1)
Using the
bucket_start_time column, you can also see how SQL performance changes over the interval you want (30 minutes, 1 hour, etc.). (See Script 2)Script 1. Top SQL for a specific period
Script 2. Execution info over time for a specific SQL
2. Histogram
The execution time information PG_STAT_STATEMENTS provides (total, max, min, mean, stddev) is relatively rich compared with other DBMSs. However, PG_STAT_MONITOR additionally provides execution counts per execution-time range as a histogram through an array-type column called
resp_calls, allowing closer analysis.PG_STAT_MONITOR.PGSM_HISTOGRAM_MIN = 1
PG_STAT_MONITOR.PGSM_HISTOGRAM_MAX = 100000
PG_STAT_MONITOR.PGSM_HISTOGRAM_BUCKETS = 20
The default histogram settings are as shown above, which means the range 1-100000ms (10sec) is divided into 20 buckets. The ranges of histogram buckets are not evenly divided, and you can check the range of each histogram bucket with the range() or histogram() function.
Run the test SQL
Check SQL statistics
Check histogram bucket ranges
📢 The word bucket is used in two senses, which can be confusing. The bucket in
PG_STAT_MONITOR.PGSM_MAX_BUCKETS mentioned earlier is a PG_STAT_MONITOR snapshot interval, while the bucket in this section means a range that makes up the histogram of resp_call.3. Top Level Tracing
PG_STAT_MONITOR can track SQL run inside procedures or functions, controlled by the
PG_STAT_MONITOR.PGSM_TRACK parameter.The default value of this parameter is
TOP, which collects only top-level SQL, but changing it to ALL lets you track recursive SQL. In this case, the toplevel column shows whether an SQL is top-level, and for recursive SQL you can trace the calling SQL through top_queryid and top_query.With this feature, it is easy to ① pinpoint slow SQL inside a function, ② trace back to the SP (stored procedure) that called the slow SQL.
Create a test function
Change the PG_STAT_MONITOR.PGSM_TRACK parameter
Run the test function
Check the top-level query in PG_STAT_MONITOR
📢 Buckets are kept according to the
PG_STAT_MONITOR.PGSM_BUCKET_TIME parameter (default : 1min), so if you run SQL several times, it may fall into different buckets and appear as multiple rows.📢 Functions and procedures are stored under the bucket number at the time they finish. (e.g. if Bucket = 3 when the function starts and Bucket = 5 when it finishes, the function's SQL is recorded in Bucket = 5)
📢 When run as an anonymous block rather than an SP (stored procedure), the top query cannot be tracked.
4. Object Access
One of PG_STAT_MONITOR's useful columns is
relations, which holds the relations used by the SQL as an array.With this information, you can extract SQL that accessed a relation you specify without a like search on the SQL text, which makes it very useful.
Create a test table
Run the test SQL
Check object access in PG_STAT_MONITOR
Combining this column with the
cmd_type_text and calls columns opens up many uses, such as ① DML volume or ② CRUD patterns per table.① Check DML volume
② Check CRUD patterns per table
5. SQL Nomalize
The last feature of PG_STAT_MONITOR is that it normalizes the constants used in SQL before storing them.
SQL normalization turns potentially sensitive constants into variables, which is good for security, and it lets you view similar SQL together.
However, normalized SQL cannot be matched with the query in PG_STAT_ACTIVITY (session level) (before version 14), and when performance varies widely depending on the values used, there is no way at all to track it.
With PG_STAT_MONITOR, users can choose whether to normalize SQL, controlled by the
PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY parameter. (Default is NO)Change the PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY parameter
Query text sample with PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘NO’
Query text sample with PG_STAT_MONITOR.PGSM_NORMALIZED_QUERY = ‘YES’
📢 However, if the query is already parameterized in the application, it is shown in $n form regardless of the setting.
Reference
PG_STAT_MONITOR Prameter
After installing the extension, you can set the following parameters in postgresql.conf.
Parameters that do not require a restart can be changed with
ALTER SYSTEM and applied by calling pg_reload_conf().Parameter Name | Default | Desc | Restart |
pg_stat_monitor.pgsm_max | 256 | Maximum size (MB) of shared memory used by PG_STAT_MONITOR | yes |
pg_stat_monitor.pgsm_query_max_len | 2048 | Sets the maximum SQL length | yes |
pg_stat_monitor.pgsm_track_utility | yes | Whether to track statements other than SELECT and DML | no |
pg_stat_monitor.pgsm_normalized_query | no | Selects whether save query in normalized format. | no |
pg_stat_monitor.pgsm_max_buckets | 10 | Sets the maximum number of buckets | yes |
pg_stat_monitor.pgsm_bucket_time | 60 | Sets the retention time (lifetime) of a bucket | yes |
pg_stat_monitor.pgsm_histogram_min | 1 | Minimum SQL execution time (ms) shown in the histogram output | yes |
pg_stat_monitor.pgsm_histogram_max | 100000 | Maximum SQL execution time (ms) shown in the histogram output | yes |
pg_stat_monitor.pgsm_histogram_buckets | 20 | Maximum number of histogram buckets | yes |
pg_stat_monitor.pgsm_query_shared_buffer | 20 | Maximum size (MB) of shared memory used for SQL tracking | yes |
pg_stat_monitor.pgsm_enable_overflow | on | Controls whether to extend beyond shared memory into swap | yes |
pg_stat_monitor.pgsm_overflow_target | 1 | Sets the overflow target. Not used since version 2.0.0 (current 2.0.1) | yes |
pg_stat_monitor.pgsm_enable_pgsm_query_id | on | Whether to use pgsm_query_id | yes |
pg_stat_monitor.pgsm_enable_query_plan | no | Whether to monitor SQL execution plans (may affect database performance when enabled.) | no |
pg_stat_monitor.pgsm_track | top | Specifies which statements to track (top, all, none) | no |
pg_stat_monitor.pgsm_extract_comments | no | Whether to enable extracting comments from SQL | no |
pg_stat_monitor.pgsm_track_planning | no | Whether to track planning statistics | yes |
PG_STAT_MONITOR View
PG_STAT_MONITORColumn | PG_STAT_STATEMENTSExist | Desc |
bucket | Bucket number the queried record belongs to | |
bucket_start_time | Bucket start time | |
userid | O | ID of the user who ran the SQL |
username | Name of the user who ran the SQL | |
dbid | O | ID of the database where the SQL ran |
datname | Name of the database where the SQL ran | |
client_ip | Client IP that ran the SQL | |
pgsm_query_id | PG_STAT_MONITOR query ID. A hash calculated from the normalized SQL. Controlled by the pg_stat_monitor.pgsm_enable_pgsm_query_id parameter | |
queryid | O | SQL ID |
toplevel | O | Whether the SQL is top-level (TRUE means it ran at the top level) |
top_queryid | Shows the query ID of the parent SQL. By default, only top-level SQL is collected. Tracking recursive SQL requires a parameter change. (pg_stat_monitor.pgsm_track = ALL) | |
query | O | The executed SQL statement |
comments | Description of the SQL | |
planid | SQL execution plan ID | |
query_plan | SQL execution plan. Shows the plain execution plan only. Not collected by default; requires a parameter change. (pg_stat_monitor.pgsm_enable_query_plan = yes) | |
top_query | Shows the parent SQL statement | |
application_name | Name of the application that ran the SQL | |
relations | List of tables referenced by the SQL (schema.table_name) | |
cmd_type | ID of the executed SQL type (1:SELECT, 2:UPDATE, 3:INSERT, 4:DELETE) | |
cmd_type_text | Executed SQL type | |
elevel | Records the SQL error level (WARNING, ERROR, LOG) | |
sqlcode | Records the SQL error code (see https://www.postgresql.org/docs/current/errcodes-appendix.html for what each error code means) | |
message | Records the SQL error message | |
calls | O | Number of times the SQL ran |
total_exec_time | O | Total time spent executing the SQL (ms) |
min_exec_time | O | Minimum time spent executing the SQL (ms) |
max_exec_time | O | Maximum time spent executing the SQL (ms) |
mean_exec_time | O | Mean time spent executing the SQL (ms) |
stddev_exec_time | O | Standard deviation of the time spent executing the SQL (ms) |
rows | O | Total rows retrieved by SQL execution |
shared_blks_hit | O | Total number of shared memory blocks returned from cache |
shared_blks_read | O | Total number of shared memory blocks not returned from cache |
shared_blks_dirtied | O | Number of shared memory blocks “dirtied” by SQL execution |
shared_blks_written | O | Number of shared memory blocks written to the drive concurrently during SQL execution |
local_blks_hit | O | The number of blocks which are considered as local by the backend and thus are used for temporary tables |
local_blks_read | O | Total number of local blocks read during SQL execution |
local_blks_dirtied | O | Number of local blocks “dirtied” by SQL execution |
local_blks_written | O | Number of local blocks written to the drive concurrently during SQL execution |
temp_blks_read | O | Total number of temp file blocks read from the drive |
temp_blks_written | O | Total number of temp file blocks written to the drive |
blk_read_time | O | Total wait time for block reads (ms) |
blk_write_time | O | Total wait time for block writes (ms) |
temp_blk_read_time | O | Total number of temp blocks read by the statement |
temp_blk_write_time | O | Total number of temp blocks written by the statement |
resp_calls | Histogram calls | |
cpu_user_time | CPU time spent running the SQL (ms) | |
cpu_sys_time | CPU time spent running kernel code (ms) | |
wal_records | O | Total number of WAL records generated by the SQL |
wal_fpi | O | Total number of WAL FPIs (Full Page Images) generated by the SQL |
wal_bytes | O | Size of WAL used by the SQL (Byte) |
bucket_done | Bucket state- false : active. Statistics can be added to the bucket.- true : finished. No statistics are added to the bucket. | |
plans | O | Number of times the statement was planned |
total_plan_time | O | Total time spent planning the statement (ms) |
min_plan_time | O | Minimum time spent planning the statement (ms) |
max_plan_time | O | Maximum time spent planning the statement (ms) |
mean_plan_time | O | Mean time spent planning the statement (ms) |
stddev_plan_time | O | Standard deviation of the time spent planning the statement (ms) |
jit_functions | O | Total number of functions JIT-compiled by the statement |
jit_generation_time | O | Total time the statement spent generating JIT code (ms) |
jit_inlining_count | O | Number of times functions were inlined |
jit_inlining_time | O | Total time spent inlining functions in the statement (ms) |
jit_optimization_count | O | Number of times the statement was optimized |
jit_optimization_time | O | Total time spent optimizing the statement (ms) |
jit_emission_count | O | Number of times code was emitted |
jit_emission_time | O | Total time the statement spent emitting code (ms) |
Related articles
