作者
digoal
日期
2020-11-27
标签
PostgreSQL , pg_stat_statements , 跟踪 , max , 覆盖
背景
pg_stat_statements 是PG的一款SQL运行统计插件, 统计SQL的执行效率(调用次数、plan时间、执行时间、返回rows、buffer和逻辑读写、产生wal日志等).
postgres=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
queryid | bigint | | |
query | text | | |
plans | bigint | | |
total_plan_time | double precision | | |
min_plan_time | double precision | | |
max_plan_time | double precision | | |
mean_plan_time | double precision | | |
stddev_plan_time | double precision | | |
calls | bigint | | |
total_exec_time | double precision | | |
min_exec_time | double precision | | |
max_exec_time | double precision | | |
mean_exec_time | double precision | | |
stddev_exec_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
但是PG pg_stat_statements能跟踪多少条sql, 取决于pg_stat_statements.max参数配置.
sql的变量会被替换成符号, 如果只是输入条件不同会认为是同一类sql, 在pg_stat_statements中只占用一条, 当SQL数超过pg_stat_statements.max会挤出最老(最早更新)的sql, 也就是deallocate操作.
PostgreSQL 14 新增功能 : pg_stat_statements: Track number of times pgss entries were deallocated. 跟踪deallocate次数.
说明是否需要加大pg_stat_statements.max参数配置
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fbc3f318d039c3e1e8614c38e40843cf8fcffde
```
pg_stat_statements: Track number of times pgss entries were deallocated.
author Fujii Masao fujii@postgresql.org
Thu, 26 Nov 2020 20:18:05 +0800 (21:18 +0900)
committer Fujii Masao fujii@postgresql.org
Thu, 26 Nov 2020 20:18:05 +0800 (21:18 +0900)
commit 9fbc3f318d039c3e1e8614c38e40843cf8fcffde
tree d23518e6b4de9c0ddf86cfc40541ecc26d0e82e8 tree | snapshot
parent 4a36eab79a193700b7b65baf6c09c795c90c02c6 commit | diff
pg_stat_statements: Track number of times pgss entries were deallocated.
If more distinct statements than pg_stat_statements.max are observed,
pg_stat_statements entries about the least-executed statements are
deallocated. This commit enables us to track the total number of times
those entries were deallocated. That number can be viewed in the
pg_stat_statements_info view that this commit adds. It's useful when
tuning pg_stat_statements.max parameter. If it's high, i.e., the entries
are deallocated very frequently, which might cause the performance
regression and we can increase pg_stat_statements.max to avoid those
frequent deallocations.
The pg_stat_statements_info view is intended to display the statistics
of pg_stat_statements module itself. Currently it has only one column
"dealloc" indicating the number of times entries were deallocated.
But an upcoming patch will add other columns (for example, the time
at which pg_stat_statements statistics were last reset) into the view.
Author: Katsuragi Yuta, Yuki Seino
Reviewed-by: Fujii Masao
Discussion: https://postgr.es/m/0d9f1107772cf5c3f954e985464c7298@oss.nttdata.com
```
查询pg_stat_statements_info()即可得到deallocate次数。
1 /* contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql */
2
3 -- complain if script is sourced in psql, rather than via ALTER EXTENSION
4 \echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.9'" to load this file. \quit
5
6 --- Define pg_stat_statements_info
7 CREATE FUNCTION pg_stat_statements_info(
8 OUT dealloc bigint
9 )
10 RETURNS bigint
11 AS 'MODULE_PATHNAME'
12 LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
13
14 CREATE VIEW pg_stat_statements_info AS
15 SELECT * FROM pg_stat_statements_info();
16
17 GRANT SELECT ON pg_stat_statements_info TO PUBLIC;
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
