作者
digoal
日期
2020-09-20
标签
PostgreSQL , 监控 , 统计信息
背景
postgrespro的一款插件, 比pg_stat_statements支持更好的统计信息展示(包括 等待事件采样、执行计划统计, metric监控可配置等)
https://postgrespro.com/docs/enterprise/12/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.
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.
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
```
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'
SELECT * FROM pgpro_stats_metrics;
```
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 the pgpro_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. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable statement statistics collection. The default value is top. 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 than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.
pgpro_stats.save (boolean)
pgpro_stats.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are neither saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.
pgpro_stats.plan_format (text)
pgpro_stats.plan_format selects the EXPLAIN format for the query plan. Possible values are text, xml, json, and yaml. The default value is text. 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 is true. 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 between 0.0 (no queries) and 1.0 (all queries). The default value is 1.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 is 10. 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 is 16kB. 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 to 0 disables metrics collection. The default value is 2. 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_name (text)
The name of metric N. This name will be displayed in the metric_name column of the pgpro_stats_metrics view.
pgpro_stats.metric_N_query (text)
The query statement that defines the metric to collect.
pgpro_stats.metric_N_period (integer)
The time interval at which to collect metric N, in milliseconds. Default: 60000 ms
pgpro_stats.metric_N_db (text)
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_user (text)
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.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
