pg插件使用学习-上

pg_waldump
PostgreSQL 10.x 常用工具之一，把PG数据库集群的 wal 日志翻译成人为可阅读的信息。
该工具要求访问数据库 data 目录权限,主要用于展示和debug的目的。

[postgres@pgpool-node01 bin]$ pg_waldump --help
pg_waldump decodes and displays PostgreSQL write-ahead logs for debugging.

Usage:
pg_waldump [OPTION]… [STARTSEG [ENDSEG]]

STARTSEG 从指定的日志段文件开始读取。这也隐含地决定了要搜索文件的路径以及 要使用的时间线。
ENDSEG 在读取指定的日志段文件后停止。
Options:
-b, --bkp-details 输出有关备份块的细节。
-e, --end=RECPTR 在指定的日志位置停止读取，而不是一直读取到日志流的末尾。
-f, --follow 在到达可用 WAL 的末尾之后，保持每秒轮询一次是否有新的 WAL 出现。
-n, --limit=N 显示指定数量的记录，然后停止。
-p, --path=PATH 要在哪个目录中寻找日志段文件。默认是在当前目录的pg_xlog 子目录中搜索。
-r, --rmgr=RMGR 只显示由指定资源管理器生成的记录。如果把list作为资源管理器名称 传递给这个选项，则打印出可用资源管理器名称的列表然后退出。
-s, --start=RECPTR 要从哪个日志位置开始读取。默认是从找到的最早的文件的第一个可用日志记录开始。
-t, --timeline=TLI 要从哪个时间线读取日志记录。默认是使用startseg（如果指定） 中的值，否则默认为 1
-V, --version 打印pg_xlogdump版本并且退出。
-x, --xid=XID 只显示用给定事务 ID 标记的记录。
-z, --stats[=record] 显示概括统计信息（记录的数量和尺寸以及全页镜像）而不是显示 每个记录。可以选择针对每个记录生成统计信息，而不是针对每个 资源管理器生成。
-?, --help show this help, then exit

简单用法如下：
[postgres@pgpool-node01 archive]$ ll
total 49156
-rw------- 1 postgres postgres 16777216 Feb 12 01:35 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Feb 12 01:35 000000010000000000000007
-rw------- 1 postgres postgres 337 Feb 12 01:35 000000010000000000000007.00000028.backup
-rw------- 1 postgres postgres 16777216 Feb 12 16:09 000000010000000000000008
[postgres@pgpool-node01 archive]$ pg_waldump 000000010000000000000008
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/08000028, prev 0/070000F8, desc: RUNNING_XACTS nextXid 586 latestCompletedXid 585 oldestRunningXid 586
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/08000060, prev 0/08000028, desc: RUNNING_XACTS nextXid 586 latestCompletedXid 585 oldestRunningXid 586
rmgr: XLOG len (rec/tot): 106/ 106, tx: 0, lsn: 0/08000098, prev 0/08000060, desc: CHECKPOINT_ONLINE redo 0/8000060; tli 1; prev tli 1; fpw true; xid 0:586; oid 16401; multi 1; offset 0; oldest xid 561 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 586; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/08000108, prev 0/08000098, desc: RUNNING_XACTS nextXid 586 latestCompletedXid 585 oldestRunningXid 586
rmgr: XLOG len (rec/tot): 30/ 30, tx: 586, lsn: 0/08000140, prev 0/08000108, desc: NEXTOID 24593
rmgr: Heap len (rec/tot): 54/ 990, tx: 586, lsn: 0/08000160, prev 0/08000140, desc: INSERT off 10, blkref #0: rel 1663/13237/2615 blk 0 FPW
rmgr: Btree len (rec/tot): 53/ 289, tx: 586, lsn: 0/08000540, prev 0/08000160, desc: INSERT_LEAF off 4, blkref #0: rel 1663/13237/2684 blk 1 FPW
rmgr: Btree len (rec/tot): 53/ 233, tx: 586, lsn: 0/08000668, prev 0/08000540, desc: INSERT_LEAF off 7, blkref #0: rel 1663/13237/2685 blk 1 FPW
rmgr: Heap len (rec/tot): 131/ 131, tx: 586, lsn: 0/08000758, prev 0/08000668, desc: INSERT off 11, blkref #0: rel 1663/13237/2615 blk 0
rmgr: Btree len (rec/tot): 72/ 72, tx: 586, lsn: 0/080007E0, prev 0/08000758, desc: INSERT_LEAF off 7, blkref #0: rel 1663/13237/2684 blk 1
rmgr: Btree len (rec/tot): 64/ 64, tx: 586, lsn: 0/08000828, prev 0/080007E0, desc: INSERT_LEAF off 8, blkref #0: rel 1663/13237/2685 blk 1
rmgr: Heap len (rec/tot): 54/ 4398, tx: 586, lsn: 0/08000868, prev 0/08000828, desc: INSERT off 24, blkref #0: rel 1663/13237/1247 blk 8 FPW
rmgr: Btree len (rec/tot): 53/ 7753, tx: 586, lsn: 0/08001998, prev 0/08000868, desc: INSERT_LEAF off 383, blkref #0: rel 1663/13237/2703 blk 1 FPW
rmgr: Btree len (rec/tot): 53/ 2177, tx: 586, lsn: 0/08003800, prev 0/08001998, desc: INSERT_LEAF off 57, blkref #0: rel 1663/13237/2704 blk 2 FPW
rmgr: Heap len (rec/tot): 54/ 7790, tx: 586, lsn: 0/080040A0, prev 0/08003800, desc: INSERT off 97, blkref #0: rel 1663/13237/2608 blk 44 FPW
rmgr: Btree len (rec/tot): 53/ 5497, tx: 586, lsn: 0/08005F10, prev 0/080040A0, desc: INSERT_LEAF off 150, blkref #0: rel 1663/13237/2673 blk 31 FPW
rmgr: Btree len (rec/tot): 53/ 6841, tx: 586, lsn: 0/080074A8, prev 0/08005F10, desc: INSERT_LEAF off 135, blkref #0: rel 1663/13237/2674 blk 45 FPW
rmgr: Heap len (rec/tot): 203/ 203, tx: 586, lsn: 0/08008F80, prev 0/080074A8, desc: INSERT off 25, blkref #0: rel 1663/13237/1247 blk 8

rmgr : 资源名称
lsn: 0/0162D3F0 日志编号
desc ： 对日志详细信息的描述
xid 事务id

postgres=# select * from t;
id

1
2
3
4
(4 rows)

postgres=# insert into t values(5);
INSERT 0 1
postgres=# delete from t where id=1;
DELETE 1
postgres=# select pg_switch_wal();
pg_switch_wal

0/90003C8
(1 row)

[postgres@pgpool-node01 archive]$ pg_waldump 000000010000000000000009
rmgr: XLOG len (rec/tot): 106/ 106, tx: 0, lsn: 0/09000028, prev 0/08070BB0, desc: CHECKPOINT_SHUTDOWN redo 0/9000028; tli 1; prev tli 1; fpw true; xid 0:606; oid 16429; multi 1; offset 0; oldest xid 561 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/09000098, prev 0/09000028, desc: RUNNING_XACTS nextXid 606 latestCompletedXid 605 oldestRunningXid 606
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/090000D0, prev 0/09000098, desc: RUNNING_XACTS nextXid 606 latestCompletedXid 605 oldestRunningXid 606
rmgr: XLOG len (rec/tot): 106/ 106, tx: 0, lsn: 0/09000108, prev 0/090000D0, desc: CHECKPOINT_ONLINE redo 0/90000D0; tli 1; prev tli 1; fpw true; xid 0:606; oid 16429; multi 1; offset 0; oldest xid 561 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 606; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/09000178, prev 0/09000108, desc: RUNNING_XACTS nextXid 606 latestCompletedXid 605 oldestRunningXid 606
rmgr: Heap len (rec/tot): 54/ 258, tx: 606, lsn: 0/090001B0, prev 0/09000178, desc: INSERT off 5, blkref #0: rel 1663/13237/16385 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 606, lsn: 0/090002B8, prev 0/090001B0, desc: COMMIT 2020-02-17 12:30:57.121152 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/090002E0, prev 0/090002B8, desc: RUNNING_XACTS nextXid 607 latestCompletedXid 606 oldestRunningXid 607
rmgr: Heap len (rec/tot): 54/ 54, tx: 607, lsn: 0/09000318, prev 0/090002E0, desc: DELETE off 1 KEYS_UPDATED , blkref #0: rel 1663/13237/16385 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 607, lsn: 0/09000350, prev 0/09000318, desc: COMMIT 2020-02-17 12:31:36.279131 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/09000378, prev 0/09000350, desc: RUNNING_XACTS nextXid 608 latestCompletedXid 607 oldestRunningXid 608
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/090003B0, prev 0/09000378, desc: SWITCH

扩展模块：
select * from pg_available_extensions;
create extension xxxx;
\dx

pg_stat_statements
用于收集数据库中sql运行信息，如调用次数，执行时间，常用于监控数据库sql性能

修改postgresql.conf
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max=10000 记录最大sql数
pg_stat_statements.track=all
pg_stat_statements.track_utility=on
pg_stat_statements.save=on

常用的统计sql参考
最耗IO SQL，单次调用最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

总最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

最耗时 SQL，单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

总最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

响应时间抖动最严重 SQL
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

最耗共享内存 SQL
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

最耗临时空间 SQL
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

监控执行最频繁sql
select userid,dbid,queryid,query,calls,total_time,min_time,max_time,mean_time,rows from pg_stat_statements order by calls limit 2;

监控慢sql
select userid,dbid,queryid,query,calls,total_time,min_time,max_time,mean_time,rows from pg_stat_statements order by mean_time desc limit 1;

清理监控表
select pg_stat_statements_reset();

auto_explain
如果想查看sql历史执行计划需要此插件
修改postgresql.conf
shared_preload_libraries = ‘pg_stat_statements,auto_explain’
auto_explain.log_min_duration=0 记录所有sql执行计划
auto_explain.log_analyze=on explain命令开启analyze模式
auto_explain.log_buffers=off 执行计划输出包含块信息

之后启动日志里会记录sql真实的执行计划和sql内容，开这个模块会增加些库的负载，但是有利于性能波动时排查sql

pg_prewarm
手动加载经常访问的表到操作系统的cache或PG的shared buffer，从而减少检查系统重启对应用的影响，非常重要的组件

函数语法：
pg_prewarm(regclass, mode text default ‘buffer’, fork text default ‘main’,
first_block int8 default null,
last_block int8 default null) RETURNS int8
参数含义如下
regclass：需要缓存的数据库对象，可以是表和索引
mode：prewarm的模式。prefetch表示异步预取到os cache；read表示同步预取；buffer表示同步读入PG的shared buffer
fork：relation fork的类型。一般用main，一般不设置
first_block & last_block：开始和结束块号。表的first_block=0，last_block可通过pg_class的relpages字段获得

测试：
create table test_pre (id int4,name character varying(64),creat_time timestamp(6) without time zone);
insert into test_pre select generate_series(1,100000),generate_series(1,100000)|| ‘_pre’,clock_timestamp();
加载数据到数据库缓存
select pg_prewarm(‘test_pre’,‘buffer’);
pg_prewarm

    637
复制

表示637个数据块加载到了内存中
select relname,relpages from pg_class where relname=‘test_pre’;
relname | relpages
----------±---------
test_pre | 637

先造一个大表
postgres=# select pg_size_pretty(pg_relation_size(‘test_pre’));
pg_size_pretty

924 MB
(1 row)

不进行pg_prewarm
postgres=# explain analyze select count(*) from test_pre;
QUERY PLAN

Finalize Aggregate (cost=140046.04…140046.05 rows=1 width=8) (actual time=2207.796…2207.797 rows=1 loops=1)
-> Gather (cost=140045.83…140046.04 rows=2 width=8) (actual time=2206.801…2207.817 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=139045.83…139045.84 rows=1 width=8) (actual time=2198.701…2198.701 rows=1 loops=3)
-> Parallel Seq Scan on test_pre (cost=0.00…134880.66 rows=1666066 width=0) (actual time=0.131…1598.515 rows=1333333 loops=3)
Planning Time: 2.717 ms
Execution Time: 2208.301 ms

read模式prewarm（test表的数据被同步读入os cache）
postgres=# select pg_prewarm(‘test_pre’,‘read’,‘main’);

postgres=# explain analyze select count(*) from test_pre;
QUERY PLAN

Finalize Aggregate (cost=140046.04…140046.05 rows=1 width=8) (actual time=1395.613…1395.613 rows=1 loops=1)
-> Gather (cost=140045.83…140046.04 rows=2 width=8) (actual time=1395.084…1395.910 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=139045.83…139045.84 rows=1 width=8) (actual time=1388.994…1388.994 rows=1 loops=3)
-> Parallel Seq Scan on test_pre (cost=0.00…134880.66 rows=1666066 width=0) (actual time=0.024…774.149 rows=1333333 loops=3)
Planning Time: 0.034 ms
Execution Time: 1395.980 ms

buffer模式prewarm（同步读入PG的shared buffer）
postgres=# select pg_prewarm(‘test_pre’,‘buffer’,‘main’);

postgres=# explain analyze select count(*) from test_pre;
QUERY PLAN

Finalize Aggregate (cost=140046.04…140046.05 rows=1 width=8) (actual time=1315.206…1315.207 rows=1 loops=1)
-> Gather (cost=140045.83…140046.04 rows=2 width=8) (actual time=1313.979…1317.607 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=139045.83…139045.84 rows=1 width=8) (actual time=1302.936…1302.936 rows=1 loops=3)
-> Parallel Seq Scan on test_pre (cost=0.00…134880.66 rows=1666066 width=0) (actual time=0.009…744.724 rows=1333333 loops=3)
Planning Time: 4.871 ms
Execution Time: 1319.384 ms
(8 rows)

prefetch模式
postgres=# select pg_prewarm(‘test_pre’,‘prefetch’,‘main’);

postgres=# explain analyze select count(*) from test_pre;
QUERY PLAN

Finalize Aggregate (cost=140046.04…140046.05 rows=1 width=8) (actual time=1329.473…1329.473 rows=1 loops=1)
-> Gather (cost=140045.83…140046.04 rows=2 width=8) (actual time=1327.271…1333.849 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=139045.83…139045.84 rows=1 width=8) (actual time=1322.148…1322.149 rows=1 loops=3)
-> Parallel Seq Scan on test_pre (cost=0.00…134880.66 rows=1666066 width=0) (actual time=0.008…751.178 rows=1333333 loops=3)
Planning Time: 0.043 ms
Execution Time: 1333.928 ms
(8 rows)

测试结果差不多。。。。。

参考链接：http://mysql.taobao.org/monthly/2015/02/04/

当生产数据库重启建议将频繁访问的小表缓存到操作系统或数据库缓存中。