F.41. pgpro_stats
The pgpro_stats
extension provides a means for tracking execution statistics of all SQL statements executed by a server. It is based on the pg_stat_statements module and provides the following additional functionality:
Storing query plans in addition to query statements.
Configuring sample rate for statistics collection to reduce overhead.
Calculating wait event statistics for executed queries.
F.41.1. Limitations
pgpro_stats
can sometimes fail to match identical parameters in the query statement and the corresponding query plan.Some SPI queries are not included into statistics.
F.41.2. Installation and Setup
The pgpro_stats
extension is included into Postgres Pro Enterprise, but has to be installed separately. Once you have pgpro_stats
installed, complete the following steps to enable pgpro_stats
:
Add
pgpro_stats
to the shared_preload_libraries parameter in thepostgresql.conf
file:shared_preload_libraries = 'pgpro_stats'
Restart the Postgres Pro Enterprise instance for the changes to take effect.
Once the server is reloaded,
pgpro_stats
starts tracking statistics across all databases of the cluster. If required, you can change the scope of statistics collection or disable it altogether usingpgpro_stats
configuration parameters.To access the collected statistics, you have to create
pgpro_stats
extension:CREATE EXTENSION pgpro_stats;
F.41.3. Usage
F.41.3.1. Collecting Statistics on Query Statements and Plans
Once installed, the pgpro_stats
extension starts collecting statistics on the executed statements. The collected data is similar to the one provided by pg_stat_statements, but also includes information on query plans and wait events for each query type. The statistics is saved into an in-memory ring buffer and is accessible through the pgpro_stats_statements view.
By default, pgpro_stats
collects statistics on all the executed statements that satisfy the pgpro_stats.track and pgpro_stats.track_utility settings. If performance is a concern, you can set a sample rate for queries using the pgpro_stats.query_sample_rate parameter, and pgpro_stats
will randomly select queries for statistics calculation at the specified rate.
To collect statistics on wait events, pgpro_stats
uses time-based sampling. Wait events are sampled at the time interval specified by the pgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, the pgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if the pgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting the pgpro_stats.enable_profile parameter to false
.
As an example, let's create a table with some random data and build an index on this table:
CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i); CREATE INDEX test_x_idx ON test (x);
Now run the following query several times using different values for :x_min
and :x_max
:
SELECT * FROM test WHERE x >= :x_min AND x <= :x_max;
The collected statistics should appear in the pgpro_stats_statements
view:
SELECT queryid, query, planid, plan, wait_stats FROM pgpro_stats_statements WHERE query LIKE 'select * from test where%'; -[ RECORD 1 ]---------------------------------------------------------------------------------------------------------- queryid | 1109491335754870054 query | select * from test where x >= $1 and x <= $2 planid | 8287793242828473388 plan | Gather | Output: i, x | Workers Planned: 2 | -> Parallel Seq Scan on public.test | Output: i, x | Filter: ((test.x >= $3) AND (test.x <= $4)) | wait_stats | {"IO": {"DataFileRead": 10}, "IPC": {"BgWorkerShutdown": 10}, "Total": {"IO": 10, "IPC": 10, "Total": 20}} -[ RECORD 2 ]---------------------------------------------------------------------------------------------------------- queryid | 1109491335754870054 query | select * from test where x >= $1 and x <= $2 planid | -9045072158333552619 plan | Bitmap Heap Scan on public.test | Output: i, x | Recheck Cond: ((test.x >= $3) AND (test.x <= $4)) | -> Bitmap Index Scan on test_x_idx | Index Cond: ((test.x >= $5) AND (test.x <= $6)) | wait_stats | {"IO": {"DataFileRead": 40}, "Total": {"IO": 40, "Total": 40}} -[ RECORD 3 ]---------------------------------------------------------------------------------------------------------- queryid | 1109491335754870054 query | select * from test where x >= $1 and x <= $2 planid | -1062789671372193287 plan | Seq Scan on public.test | Output: i, x | Filter: ((test.x >= $3) AND (test.x <= $4)) | wait_stats | NULL -[ RECORD 4 ]---------------------------------------------------------------------------------------------------------- queryid | 1109491335754870054 query | select * from test where x >= $1 and x <= $2 planid | -1748292253893834280 plan | Index Scan using test_x_idx on public.test | Output: i, x | Index Cond: ((test.x >= $3) AND (test.x <= $4)) | wait_stats | NULL
F.41.3.2. Monitoring Custom Metrics
With pgpro_stats
, you can define custom metrics to be monitored. The collected data will be saved into an in-memory ring buffer and then sent to a monitoring system. Unlike direct polling of a database by a monitoring system that can lose some data if the connection is interrupted, this approach allows to get all the collected data regardless of connection issues, as long as this data is still available in the ring buffer.
To set up a custom metric to collect, do the following:
For each metric, define all configuration parameters listed in Section F.41.6.2. You must specify a unique numeric identifier of each metric in the parameter names.
For example, to monitor index bloating each 60 seconds, you can define a new metric by setting metrics-related parameters as follows:
pgpro_stats.metric_1_name = index_bloat pgpro_stats.metric_1_query = 'select iname, ibloat, ipages from bloat' pgpro_stats.metric_1_db = 'postgres' pgpro_stats.metric_1_user = postgres pgpro_stats.metric_1_period = '60s'
Restart the server.
pgpro_stats
starts collecting statistics on executed statements and saves it into the ring buffer, and the collected data appears in thepgpro_stats_metrics
view:SELECT * FROM pgpro_stats_metrics;
Once the new metric is added, its parameters can be changed without a server restart by simply reloading the
postgresql.conf
configuration file.If required, set up data export to a monitoring system of your choice.
F.41.4. Views
F.41.4.1. The pgpro_stats_statements
View
The statistics gathered by the module are made available via a view named pgpro_stats_statements
. This view contains one row for each distinct database ID, user ID and query ID (up to the maximum number of distinct statements that the module can track). The columns of the view are shown in Table F.30.
Table F.30. pgpro_stats_statements
Columns
Name | Type | References | Description |
---|---|---|---|
userid | oid |
| OID of user who executed the statement |
dbid | oid |
| OID of database in which the statement was executed |
queryid | bigint | Internal hash code, computed from the statement's parse tree | |
planid | bigint | Internal hash code, computed from the statement's plan tree | |
plan | text | The text of the query plan, in the format defined by the pgpro_stats.plan_format configuration parameter | |
wait_stats | jsonb | A jsonb object containing statistics on wait events, for each execution of the query that uses the corresponding plan | |
query | text | Text of a representative statement | |
calls | bigint | Number of times executed | |
total_time | double precision | Total time spent in the statement, in milliseconds | |
min_time | double precision | Minimum time spent in the statement, in milliseconds | |
max_time | double precision | Maximum time spent in the statement, in milliseconds | |
mean_time | double precision | Mean time spent in the statement, in milliseconds | |
stddev_time | double precision | Population standard deviation of time spent in the statement, in milliseconds | |
rows | bigint | Total number of rows retrieved or affected by the statement | |
shared_blks_hit | bigint | Total number of shared block cache hits by the statement | |
shared_blks_read | bigint | Total number of shared blocks read by the statement | |
shared_blks_dirtied | bigint | Total number of shared blocks dirtied by the statement | |
shared_blks_written | bigint | Total number of shared blocks written by the statement | |
local_blks_hit | bigint | Total number of local block cache hits by the statement | |
local_blks_read | bigint | Total number of local blocks read by the statement | |
local_blks_dirtied | bigint | Total number of local blocks dirtied by the statement | |
local_blks_written | bigint | Total number of local blocks written by the statement | |
temp_blks_read | bigint | Total number of temp blocks read by the statement | |
temp_blks_written | bigint | Total number of temp blocks written by the statement | |
blk_read_time | double precision | Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
blk_write_time | double precision | Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
F.41.4.2. The pgpro_stats_metrics
View
The metrics gathered by pgpro_stats
are displayed in the pgpro_stats_metrics
view. The table below describes the columns of the view.
Table F.31. pgpro_stats_metrics
Columns
Name | Type | Description |
---|---|---|
metric_number | int4 | A unique ID of the collected metric assigned by user. This ID is included into parameter names that define the metric. |
metric_name | text | The name of the metric defined by the pgpro_stats.metric_ parameter. |
db_name | text | The name of the database for which a particular metric was collected. |
ts | timestamptz | The time when the metric value got calculated. |
value | jsonb | The result of the query used for metric measurement. It is serialized in jsonb as an array of objects received via to_jsonb( . If an error occurs, a single object is returned that contains code , message , detail , and hint fields. |
F.41.5. Functions
-
pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint) returns void
pgpro_stats_statements_reset
discards statistics gathered so far bypgpro_stats
corresponding to the specifieduserid
,dbid
,queryid
, andplanid
. If any of the parameters are not specified, the default value0
(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are0
(invalid), it will discard all statistics. By default, this function can only be executed by superusers. Access may be granted to others usingGRANT
.-
pgpro_stats_statements(showtext boolean) returns setof record
The
pgpro_stats_statements
view is defined in terms of a function also namedpgpro_stats_statements
. It is possible for clients to call thepgpro_stats_statements
function directly, and by specifyingshowtext := false
have query text be omitted (that is, theOUT
argument that corresponds to the view'squery
column will return nulls). This feature is intended to support external tools that might wish to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Such tools can instead cache the first query text observed for each entry themselves, since that is allpgpro_stats
itself does, and then retrieve query texts only as needed. Since the server stores query texts in a file, this approach may reduce physical I/O for repeated examination of thepgpro_stats_statements
data.-
pgpro_stats_metrics(showtext boolean) returns setof record
Defines the
pgpro_stats_metrics
view, which is described in detail in Table F.31.
F.41.6. Configuration Parameters
F.41.6.1. General Settings
-
pgpro_stats.max
(integer
) pgpro_stats.max
is the maximum number of statements tracked by the module (i.e., the maximum number of rows in thepgpro_stats_statements
view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 5000. This parameter can only be set at server start.-
pgpro_stats.track
(enum
) pgpro_stats.track
controls which statements are counted by the module. Specifytop
to track top-level statements (those issued directly by clients),all
to also track nested statements (such as statements invoked within functions), ornone
to disable statement statistics collection. The default value istop
. Only superusers can change this setting.-
pgpro_stats.track_utility
(boolean
) pgpro_stats.track_utility
controls whether utility commands are tracked by the module. Utility commands are all those other thanSELECT
,INSERT
,UPDATE
andDELETE
. The default value ison
. Only superusers can change this setting.-
pgpro_stats.save
(boolean
) pgpro_stats.save
specifies whether to save statement statistics across server shutdowns. If it isoff
then statistics are neither saved at shutdown nor reloaded at server start. The default value ison
. This parameter can only be set in thepostgresql.conf
file or on the server command line.-
pgpro_stats.plan_format
(text
) pgpro_stats.plan_format
selects theEXPLAIN
format for the query plan. Possible values aretext
,xml
,json
, andyaml
. The default value istext
. Changing this parameter requires a server restart.-
pgpro_stats.enable_profile
(boolean
) pgpro_stats.enable_profile
enables sampling of wait events for separate statements. The default value istrue
. Changing this parameter requires a server restart.-
pgpro_stats.query_sample_rate
(float
) pgpro_stats.query_sample_rate
specifies the fraction of queries that are randomly selected for statistics calculation. Possible values lie between0.0
(no queries) and1.0
(all queries). The default value is1.0
. Changing this parameter requires a server restart.-
pgpro_stats.profile_period
(integer
) pgpro_stats.profile_period
specifies the period, in milliseconds, during which to sample wait events. The default value is10
. Changing this parameter requires superuser rights.-
pgpro_stats.metrics_buffer_size
(integer
) pgpro_stats.metrics_buffer_size
specifies the size of the ring buffer used for collecting statistical metrics. The default value is16kB
. Changing this parameter requires a server restart.-
pgpro_stats.metrics_workers
(integer
) pgpro_stats.metrics_workers
specifies the number of workers used to collect statistical metrics. If this parameter is set to 2 or higher, one of the workers serves as the master worker distributing queries to other workers. If only one worker is available, it gets reloaded to connect to different databases. Setting this parameter to0
disables metrics collection. The default value is2
. Changing this parameter requires a server restart.
F.41.6.2. Metrics Settings
The following parameters can be used to define a custom metric to collect. The N
placeholder in the parameter name serves as a unique identifier of the metric to which this setting should apply; it must be set to a non-negative integer for each metric.
When you add these parameters for a new metric, you have to restart the server for the changes to take effect. Once the new metric is added, its parameters can be changed without a server restart by simply reloading the postgresql.conf
configuration file.
-
pgpro_stats.metric_
(N
_nametext
) The name of metric
N
. This name will be displayed in themetric_name
column of thepgpro_stats_metrics
view.-
pgpro_stats.metric_
(N
_querytext
) The query statement that defines the metric to collect.
-
pgpro_stats.metric_
(N
_periodinteger
) The time interval at which to collect metric
N
, in milliseconds. Default: 60000 ms-
pgpro_stats.metric_
(N
_dbtext
) The list of databases for which to collect metric
N
. Database names must be separated by commas. You can specify the*
character to select all databases in the cluster except the template databases. If you need to analyze the template databases as well, you have to specify them explicitly.-
pgpro_stats.metric_
(N
_usertext
) The name of the user on behalf of which to collect metric
N
. This user must have access to the database for which the metric is collected.
F.41.7. Authors
Postgres Professional, Moscow, Russia