Configuration¶
Use the following command to view available configuration parameters in the pg_stat_monitor_settings view:
SELECT *
FROM pg_settings
WHERE name like 'pg_stat_monitor.%';
To amend the pg_stat_monitor configuration, use the General Configuration Unit (GCU) system.
GUC variable types
There are two types of GUC variables:
The first type can only be set in the postgresql.conf configuration file and comes into an effect on the start of the postgres server. You can set the same variable using the ALTER SYSTEM command. In this case the value of this variable is written into the postgresql.auto.conf which has preference over postgresql.conf. You also must restart the postgres server to apply the values from the postgresql.auto.conf file. The user can change the variable using the SET command without any error, but that does not affect the variable settings.
The second type of GUC variables can be set by the user from the client (psql) using the SET command. These variables are session-based, and their values can only be visible on that sessions. These variables can also be set with the or ALTER SYSTEM command and in the configuration file, but in that case, the effect of these variables is on all new sessions.
Parameters¶
The following table shows setup options for each configuration parameter and whether the server restart is required to apply the parameter’s value:
Parameters description¶
pg_stat_monitor.pgsm_max¶
Values:
- Min: 10
- Max: 10240
- Default: 256
Server restart - YES
This parameter defines the limit of shared memory (in MB) for pg_stat_monitor. This memory is used by buckets in a circular manner. The memory is divided between the buckets equally, at the start of the PostgreSQL.
pg_stat_monitor.pgsm_query_max_len¶
Values:
- Min: 1024
- Max: 2147483647
- Default: 2048
Server restart - YES
Sets the maximum size of the query. This parameter can only be set at the start of PostgreSQL. For long queries, the query is truncated to that particular length. This is to avoid unnecessary usage of shared memory.
pg_stat_monitor.pgsm_track_utility¶
Type: boolean (YES / NO). Default: YES
Server restart - NO
This parameter controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE, and DELETE. Starting with version 1.1.0, the values are changed from 1 / 0 to YES / NO.
pg_stat_monitor.pgsm_normalized_query¶
Type: boolean. Default: NO
Server restart - NO
Starting with version 1.1.0, the data type changed to boolean and the query shows the actual parameter instead of the placeholder by default. It is quite useful when users want to use that query and try to run that query to check the abnormalities. Users should be aware, however, that running queries with disabled normalization can expose some sensitive data.
But in most cases users like the queries with a placeholder. This parameter is used to toggle between the two said options.
In version 1.0.0 and earlier, the parameter type is “integer” and the default value is 1. This means that the query shows placeholders instead of the actual data
pg_stat_monitor.pgsm_max_buckets¶
Values:
- Min: 1
- Max: 20000
- Default: 10
Server restart - YES
pg_stat_monitor accumulates the information in the form of buckets. All the aggregated information is bucket based. This parameter is used to set the number of buckets the system can have. For example, if this parameter is set to 2, then the system will create two buckets. First, the system will add all the information into the first bucket. After its lifetime (defined in the pg_stat_monitor.pgsm_bucket_time parameter) expires, it will switch to the second bucket, reset all the counters and repeat the process.
pg_stat_monitor.pgsm_bucket_time¶
Values:
- Min: 1
- Max: 2147483647
- Default: 60
Server restart - YES
This parameter is used to set the lifetime of the bucket. System switches between buckets on the basis of pg_stat_monitor.pgsm_bucket_time.
pg_stat_monitor.pgsm_histogram_min¶
Values:
- Min: 0
- Max: 50000000
- Default: 1
Server restart - YES
The minimum execution time for a query to appear in histogram output (in ms). Starting with version 2.0.0, this parameter can be set as a decimal value, allowing to output queries with the execution time less than 1 ms.
pg_stat_monitor.pgsm_histogram_max¶
Values:
- Min: 10
- Max: 50000000
- Default: 10000
Server restart - YES
This parameter sets the upper bound of the query execution time for the histogram output (in ms). Starting with version 2.0.0, you can set a decimal value for this parameter thus fine-tuning the output to more precision.
pg_stat_monitor.pgsm_histogram_buckets¶
Values:
- Min: 2
- Max: 50
- Default: 20
Server restart - YES
This parameter sets the maximum number of histogram buckets. Starting with version 1.1.0, the maximum value is changed to 50.
Starting with version 2.0.0, on server startup pg_stat_monitor calculates the maximum number of buckets that can be created for the time range specified in pgsm_histogram_min/pgsm_histogram_max and compares it with the number of buckets specified by the user. If the calculated number falls below the user configuration, the warning is written to the server log.
pg_stat_monitor.pgsm_query_shared_buffer¶
Values:
- Min: 1
- Max: 10000
- Default: 20
Server restart - YES
This parameter defines the shared memory limit (in MB) allocated for a query tracked by pg_stat_monitor.
pg_stat_monitor.pgsm_enable_overflow¶
Type: boolean. Default: YES
Server restart - NO
Controls whether pg_stat_monitor can grow beyond shared memory into swap space.
pg_stat_monitor.pgsm_overflow_target¶
Type: boolean (YES / NO). Default: NO
Server restart - NO
Starting with version 2.0.0, this option is deprecated. Use the pg_stat_monitor.pgsm_enable_overflow instead.
Sets the overflow target for the pg_stat_monitor. Starting with version 1.1.1, the default value is NO. In version 1.0.0 and earlier, the default value was 1 (YES).
pg_stat_monitor.pgsm_enable_pgsm_query_id¶
Type: boolean. Default: YES
Server restart - NO
Controls the generation of a unique hash code that identifies the query. This hash code is independent of PostgreSQL server version, constants within the query, database, user or schema. Its usage allows getting insights into how the query is being planned and executed across PostgreSQL versions, database, users or schemas. Enabling this parameter results in additional load on the database.
pg_stat_monitor.pgsm_enable_query_plan¶
Type: boolean. Default: NO
Server restart - NO
Enables or disables query plan monitoring. When the pgsm_enable_query_plan is disabled (no), the query plan will not be captured by pg_stat_monitor. Enabling it may adversely affect the database performance.
pg_stat_monitor.pgsm_track¶
Default: top
Server restart - NO
This parameter controls which statements are tracked by pg_stat_monitor.
Values:
top: Default, track only top level queries (those issued directly by clients) and excludes listing nested statements (those called within a function).all: Track top along with sub/nested queries. As a result, some SELECT statement may be shown as duplicates.none: Disable query monitoring. The module is still loaded and is using shared memory, etc. It only silently ignores the capturing of data.
pg_stat_monitor.pgsm_extract_comments¶
Type: boolean (YES/NO). Default: NO
Server restart - NO
This parameter controls whether to enable or disable extracting comments from queries.
pg_stat_monitor.pgsm_track_planning¶
Type: boolean. Default: NO
Server restart - YES
Available for PostgreSQL 14 and later versions.
This parameter instructs pg_stat_monitor to monitor query planning statistics.
pg_stat_monitor.pgsm_track_application_names¶
Type: boolean. Default: YES
Server restart - NO
Controls if to record or not the name of the application that executes the query. When enabled, the application name is a part of the entry key. However, the application name tracking is an expensive operation and you can face perfomance degradation correlated to the number of connections.
Disabling this parameter results in statistics cumulation for the same query issued by different applications in the same entry. For the sake of query execution performance, it’s recommeded to disable this feature.
Get expert help¶
If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.