Movatterモバイル変換

• 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.

• Texts and plans of some SPI queries are not normalized.

• pgpro_stats is incompatible withpg_stat_statements, as well as other extensions that use parser, planner, or executor hooks to modify parse and plan trees and execution of the queries. Note also that in order to dump the final versions of the queries and plans,pgpro_stats should be the last on the list ofshared_preload_libraries, but some existing extensions, such aspg_pathman, will not work at all unless they are the last on this list.

• pgpro_stats may not work correctly with third-party extensions that produceCustomScan andForeignScan nodes.

• At present, planning statistics are not collected and the corresponding view columns contain zeroes.

1. Addpgpro_stats to theshared_preload_libraries parameter in thepostgresql.conf file:

   shared_preload_libraries = 'pgpro_stats'

2. Restart thePostgres Pro Standard 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_statsconfiguration parameters.

3. To access the collected statistics, you have to createpgpro_stats extension:

   CREATE EXTENSION pgpro_stats;

• Collecting Statistics on Query Statements and Plans

• Monitoring Custom Metrics

Once installed, thepgpro_stats extension starts collecting statistics on the executed statements. The collected data is similar to the one provided bypg_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 thepgpro_stats_statements view.

By default,pgpro_stats collects statistics on all the executed statements that satisfy thepgpro_stats.track andpgpro_stats.track_utility settings. If performance is a concern, you can set a sample rate for queries using thepgpro_stats.query_sample_rate parameter, andpgpro_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 thepgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, thepgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if thepgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting thepgpro_stats.enable_profile parameter tofalse.

pgpro_stats_statements.plans andpgpro_stats_statements.calls aren't always expected to match because planning and execution statistics are updated at their respective end phase, and only for successful operations. For example, if a statement is successfully planned but fails during the execution phase, only its planning statistics will be updated. If planning is skipped because a cached plan is used, only its execution statistics will be updated.

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 thepgpro_stats_statements view:

SELECT queryid, query, planid, plan, wait_stats FROM pgpro_stats_statements WHERE query LIKE 'select * from test where%';-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------queryid    | 1109491335754870054query      | select * from test where x >= $1 and x <= $2planid     | 8287793242828473388plan       | 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    | 1109491335754870054query      | select * from test where x >= $1 and x <= $2planid     | -9045072158333552619plan       | 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    | 1109491335754870054query      | select * from test where x >= $1 and x <= $2planid     | -1062789671372193287plan       | Seq Scan on public.test           |   Output: i, x           |   Filter: ((test.x >= $3) AND (test.x <= $4))           |wait_stats | NULL-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------queryid    | 1109491335754870054query      | select * from test where x >= $1 and x <= $2planid     | -1748292253893834280plan       | Index Scan using test_x_idx on public.test           |   Output: i, x           |   Index Cond: ((test.x >= $3) AND (test.x <= $4))           |wait_stats | NULL

Withpgpro_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:

The statistics gathered by the module are made available via a view namedpgpro_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 inTable F.91.

Take into account that likepg_stat_statements,pgpro_stats normalizes into one record those DML queries (containingSELECT,INSERT,UPDATE,DELETE andMERGE commands) that have equivalent structures according to some internal hash value. Being compared this way, two queries are normally considered equal if they are semantically equivalent up to constants included in the queries. All the other commands are, however, compared strictly as query texts. When the value of a constant in a query is ignored for comparison with other queries, this constant is replaced in thepgpro_stats output with a symbol of a parameter, such as,$k, wherek is a positive integer. If a query already contains parameters, the initial value ofk equals the number following the last number of a$n parameter in the original query text. If there are no parameters, the initial value ofk equals 1. Note that sometimes hidden parameter symbols affect this numbering. For example, PL/pgSQL uses such hidden symbols to insert values of function local variables into queries, so a PL/pgSQL statementSELECT i + 1 INTO j will be represented asSELECT i + $2 in the normalized query text.

pgpro_stats uses a similar technique to normalize plan texts. When doing so, an attempt is made to associate numbers of constants in the plan text with the corresponding numbers of constants in the query text. If such an attempt appears unsuccessful for a certain constant in the plan text, it is assigned the number following the maximum number of a constant replaced in the query text. For example, consider the query:

SELECT 1::int, 'abc'::VARCHAR(3), 2::int;

pgpro_stats will replace numbers of constants in the query text and in the text of the corresponding plan as follows:

postgres=# SELECT query, plan FROM pgpro_stats_statements;                     query                      |                                plan------------------------------------------------+--------------------------------------------------SELECT $1::int, $2::VARCHAR(3), $3::int         | Result                                           +                                                |   Output: $1, $4, $3                             +

In this plan text, it appeared possible to associate constants numbered 1 and 3 from the query text, but not the constant numbered 2, and the latter was replaced with the number following the maximum number in the query text, that is, number 4.

Replacement of numbers in plan texts has an exception for version numbers of XML documents. If in the original query such a number is represented with a constant, e.g., '1.0', it is retained as is in the plan text rather than replaced with$k. If the version number of an XML document is represented with an expression, replacement of constants follows usual rules.

The aggregate statistics gathered by the module are made available via a view namedpgpro_stats_totals. This view contains one row for each distinct object (up to the maximum number of distinct objects that the module can track). The columns of the view are shown inTable F.92.

The statistics of thepgpro_stats module itself are tracked and made available via a view namedpgpro_stats_info. This view contains only a single row. The columns of the view are shown inTable F.93.

The metrics gathered bypgpro_stats are displayed in thepgpro_stats_metrics view. The table below describes the columns of the view.

Thepgpro_stats_archiver view will contain one row showing data about the archiver process of the cluster.

Thepgpro_stats_vacuum_database view will contain one row for each database in the current cluster, showing statistics about vacuuming that database. These statistics are collected by the core system as explained inSection 26.2. The table below describes the columns of the view.

Thepgpro_stats_vacuum_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about vacuuming that specific table. These statistics are collected by the core system as explained inSection 26.2. The table below describes the columns of the view.

Columnstotal_*,wal_* andblk_* include data on vacuuming indexes on this table, while columnssystem_time anduser_time only include data on vacuuming the heap.

Thepgpro_stats_vacuum_indexes view will contain one row for each index in the current database (including TOAST table indexes), showing statistics about vacuuming that specific index. These statistics are collected by the core system as explained inSection 26.2. The table below describes the columns of the view.

pgpro_stats_rusage is a record type that contains resource usage statistics of statement planning/execution. The fields of this type are shown inTable F.99.

pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint, minmax_only boolean) returns timestamp with time zone

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. If all statistics in thepgpro_stats_statements view are discarded, it will also reset the statistics in thepgpro_stats_info view. Whenminmax_only istrue only the values of minimum and maximum planning and execution time will be reset (i.e.min_plan_time,max_plan_time,min_exec_time andmax_exec_time fields). The default value forminmax_only parameter isfalse. Time of last min/max reset performed is shown inminmax_stats_since field of thepgpro_stats_statements view. This function returns the time of a reset. This time is saved tostats_reset field ofpgpro_stats_info view or tominmax_stats_since field of thepgpro_stats_statements view if the corresponding reset was actually performed. By default, this function can only be executed by superusers. Access may be granted to others usingGRANT.

Note

As statistics in thepgpro_stats_vacuum_database,pgpro_stats_vacuum_tables, andpgpro_stats_vacuum_indexes views are collected by the core system, to reset them, call thepg_stat_reset() function (seeSection 26.2.3 for details).

pgpro_stats_statements(showtext boolean) returns setof record

Thepgpro_stats_statements view is defined in terms of a function also namedpgpro_stats_statements. Users can also call thepgpro_stats_statements function directly, and by specifyingshowtext := false make 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_info() returns record

pgpro_stats_info view is defined in terms of a function also namedpgpro_stats_info. Users can also call thepgpro_stats_info function directly.

pgpro_stats_totals_reset(type text, id bigint) returns timestamp with timezone

pgpro_stats_totals_reset discards statistics gathered so far bypgpro_stats corresponding to the specified objecttype andid. If no parameter is specified or thetype parameter is set to0, all statistics will be discarded. Iftype is set to a valid object type, then ifid is specified, then statistics will be discarded only for the specified object, else, statistics will be discarded for all objects of the specified type. Otherwise, no statistics will be discarded. This function returns the time of a reset. By default, this function can only be executed by superusers. Access may be granted to others usingGRANT.

pgpro_stats_totals() returns setof record

Thepgpro_stats_totals view is defined in terms of a function also namedpgpro_stats_totals. Users can also call thepgpro_stats_totals function directly.

pgpro_stats_metrics() returns setof record

Defines thepgpro_stats_metrics view, which is described in detail inTable F.94.

pgpro_stats_get_archiver() returns setof record

Defines thepgpro_stats_archiver view, which is described in detail inTable F.95.

pgpro_stats_wal_sender_crc_errors() returns bigint

InPostgres Pro Enterprise, returns the number of errors detected by the WAL sender process when thewal_sender_check_crc parameter ison. InPostgres Pro Standard, returns zero. Note that this counter is reset to zero when the cluster restarts.

pgpro_stats_vacuum_database(dboid oid) returns setof record

Defines the row of thepgpro_stats_vacuum_database view, which is described in detail inTable F.96, for the database specified bydboid.

pgpro_stats_vacuum_tables(dboid oid, relid oid) returns setof record

Defines the row of thepgpro_stats_vacuum_tables view, which is described in detail inTable F.97, for the database specified bydboid and table specified byreloid. Ifreloid = 0, the statistics for each table in the specified database are returned.

pgpro_stats_vacuum_indexes(dboid oid, relid oid) returns setof record

Defines the row of thepgpro_stats_vacuum_indexes view, which is described in detail inTable F.98, for the database specified bydboid and index specified byreloid. Ifreloid = 0, the statistics for each index in the specified database are returned.

Inpgpro_stats, tracing of application sessions is implemented. It is based on filters, which trigger logging the execution of queries that match filtering conditions. Queries and their plans are logged in so calledtrace files specified by the user or in the system log file (if the trace file is not specified). Filters are stored in a table located in the shared memory. The rows of this table are filters, and the columns contain filtering conditions. You should fill this table with filters to start tracing queries.

Once a database administrator adds a filter in any session, all subsequent executions of queries that match the filter conditions will be traced by all sessions of the instance without a need in the server restart. In other words, filters can be added, deleted or updated“on the fly”, and tracing with these filters immediately starts for existing and future sessions.

Each filter includes:

Specialized functions enable creation, update and deletion of query filters:

SELECT pgpro_stats_trace_insert('alias', 'first', 'pid', pg_backend_pid(), 'explain_analyze', true);

SELECT pgpro_stats_trace_insert('alias', 'second', 'database_name', current_database(), 'explain_costs', false, 'tracefile', 'second_tf');

\x autoSELECT * from pgpro_stats_trace_show();

 -[ RECORD 1 ]-------+---------- filter_id           | 1 active              | t alias               | first tracefile           |  pid                 | 243183 database_name       |  client_addr         |  application_name    |  username            |  queryid             |  planid              |  duration            |  plan_time           |  exec_time           |  user_time           |  system_time         |  rows                |  shared_blks_hit     |  shared_blks_read    |  shared_blks_fetched |  shared_blks_dirtied |  shared_blks_written |  local_blks_hit      |  local_blks_read     |  local_blks_fetched  |  local_blks_dirtied  |  local_blks_written  |  temp_blks_read      |  temp_blks_written   |  wal_bytes           |  total_wait_time     |  total_inval_msgs    |  explain_analyze     | t explain_verbose     | f explain_costs       | t explain_settings    | f explain_buffers     | f explain_wal         | f explain_timing      | t explain_format      | text -[ RECORD 2 ]-------+---------- filter_id           | 2 active              | t alias               | second tracefile           | second_tf pid                 |  database_name       | postgres client_addr         |  application_name    |  username            |  queryid             |  planid              |  duration            |  plan_time           |  exec_time           |  user_time           |  system_time         |  rows                |  shared_blks_hit     |  shared_blks_read    |  shared_blks_fetched |  shared_blks_dirtied |  shared_blks_written |  local_blks_hit      |  local_blks_read     |  local_blks_fetched  |  local_blks_dirtied  |  local_blks_written  |  temp_blks_read      |  temp_blks_written   |  wal_bytes           |  total_wait_time     |  total_inval_msgs    |  explain_analyze     | f explain_verbose     | f explain_costs       | f explain_settings    | f explain_buffers     | f explain_wal         | f explain_timing      | f explain_format      | text

SELECT 1 as result;

2023-04-18 04:52:53.242 MSK [63112] LOG:  Filter 1 triggered explain of the plan:Query Text: SELECT 1 as result;Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

Query Text: SELECT 1 as result;Result

SELECT pgpro_stats_trace_delete(1);

SELECT pgpro_stats_trace_update(2, 'pid', 2);

SELECT 2 as result;

SELECT pgpro_stats_trace_reset();

pgpro_stats can create views similar to those available inpg_stat_statements andpg_stat_kcache extensions. Specifically,pg_stat_statements,pg_stat_statements_info,pg_stat_kcache andpg_stat_kcache_detail views can be created. Each view is only created in aPostgres Pro version if it is available inpg_stat_statements/pg_stat_kcache extension for the same version ofPostgres Pro/PostgreSQL. For example, thepg_stat_statements_info view is only created inPostgres Pro versions starting with 14. The following functions enable creating these views:

By default, these functions can only be executed by superusers. Access may be granted to others usingGRANT.

To createpg_stat_statements* views, drop thepg_stat_statements extension if it was previously installed and call the function:

select pgpro_stats_create_pg_stat_statements_compatible_views();

To createpg_stat_kcache* views, drop thepg_stat_kcache extension if it was previously installed and call the function:

select pgpro_stats_create_pg_stat_kcache_compatible_views();

Once the views are created, you can work with them as if thepg_stat_statements/pg_stat_kcache extension is installed.

If you need to remove the views created earlier, do it in a regular way:

drop view pg_stat_statements;drop view pg_stat_statements_info;drop view pg_stat_kcache;drop view pg_stat_kcache_detail;

The following parameters can be used to define a custom metric to collect. TheN 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 thepostgresql.conf configuration file.

Thepgpro_stats_inval_status view shows one row with the current status of the cache invalidation global queue. The columns of the view are shown inTable F.101.

In a working system,num_inval_messages usually approximately equals 4000, which means that the queue is pretty full. The speed of thenum_inval_queue_cleanups growth is determined by how fast invalidation messages are generated. Growth ofnum_inval_queue_resets is normally zero, and non-zero growth indicates either too fast generation of messages or delays in processing messages by backends. Monitoringnum_inval_queue_cleanups andnum_inval_queue_resets may in some cases allow you to detect problematic backend/backeds as described below.

If for a certain time interval,num_inval_queue_cleanups considerably increased, whilenum_inval_queue_resets did not, this indicates that invalidation messages are generated faster and/or backends process them more slowly, but backends still manage to process messages before the queue overflows.

If for a time interval,num_inval_queue_cleanups did not considerably increase, whilenum_inval_queue_resets did, this definitely indicates a delay in processing messages by backend(s), and thecache_resets column of thepgpro_stats_totals view allows you to figure out which backend(s) to blame.

If for a time interval, both counters considerably increased, this also indicates that invalidation messages are generated faster and/or backends process them more slowly, but this time backends fail to process messages before the queue overflows. Thecache_resets column of thepgpro_stats_totals view allows you detect which backend(s) delay message processing. In this case, it is not possible to definitely conclude whether too fast generation of messages or a delay in message processing accounts for the growth ofnum_inval_queue_resets. However, thetotals counter of thepgpro_stats_inval_msgs view may help here. If the change of this counter for that interval is pretty the same as for a previous interval of the same length, you can definitely conclude that the growth is caused by backend delays.

Thepgpro_stats_inval_status view can be defined in terms of a function:

Thepgpro_stats_statements andpgpro_stats_totals views for each corresponding object, show a record of thepgpro_stats_inval_msgs record type containing counters for cache invalidation messages. The fields of the type are shown inTable F.102.