postgresql \watch实用的使用方法

1.介绍

\watch Postgres 9.3 版带来的一个有用的命令，与linux watch指令类似，可以帮我们在指定间隔时间内持续观察db活动，如单位时间内的事务数，commit、rollback量、刷脏量、读写量、xlog写入量、长会话、wait等

2.语法

\watch [ seconds ]

\watch 不需要在每次执行查询时获取新连接，从而节省了一些执行开销。 此外，如果查询失败，\watch会自动停止。
注意， \watch 只能在要运行的查询末尾使用。

postgres=# postgres=# \watch 2 "SELECT 1"
ERROR:  syntax error at or near "postgres"
LINE 1: postgres=# 
        ^
\watch: extra argument ""SELECT 1"" ignored

正确的使用方法

postgres=# select 1; \watch 1;                                                                                                                                                                                       ?column? 
----------
        1       

如果未指定查询语句，它将使用缓冲区中最后的查询语句。

postgres=\watch 1;
Fri 18 Aug 2023 11:34:18 AM WIB (every 1s)

 ?column? 
----------
        1

3.实用的使用方法

3.1 慢sql监控

postgres=select query,wait_event_type,wait_event from pg_stat_activity 
where wait_event is not null and now()-query_start>interval '5 second';
ostgres=#\watch 1

3.2 长wait事件

postgres=select query,wait_event_type,wait_event from pg_stat_activity where state='active' and wait_event is not null and now()-state_change>interval '5 second';
ostgres=#\watch

3.3 日志输出量

postgres=with wal_cte as (select pg_current_wal_lsn() last_wal_lsn),                                                                                                                                                  sleep_cte as (select pg_sleep(1))                                                                                                                                                                                     SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),last_wal_lsn)) from wal_cte,sleep_cte;
ostgres=#\watch 0.000001

3.3结合pg_stat_database使用

可以结合pg_stat_database 了解block命中情况、insert、update、update情况，以及temp写入情况等等
先看一下，pg_stat_database有哪些字段

commondb=# \d pg_stat_database;
                     View "pg_catalog.pg_stat_database"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 datid          | oid                      |           |          | 
 datname        | name                     |           |          | 
 numbackends    | integer                  |           |          | 
 xact_commit    | bigint                   |           |          | 
 xact_rollback  | bigint                   |           |          | 
 blks_read      | bigint                   |           |          | 
 blks_hit       | bigint                   |           |          | 
 tup_returned   | bigint                   |           |          | 
 tup_fetched    | bigint                   |           |          | 
 tup_inserted   | bigint                   |           |          | 
 tup_updated    | bigint                   |           |          | 
 tup_deleted    | bigint                   |           |          | 
 conflicts      | bigint                   |           |          | 
 temp_files     | bigint                   |           |          | 
 temp_bytes     | bigint                   |           |          | 
 deadlocks      | bigint                   |           |          | 
 blk_read_time  | double precision         |           |          | 
 blk_write_time | double precision         |           |          | 
 stats_reset    | timestamp with time zone |           |          | 

postgres=\c commondb
commondb=select pg_stat_reset();
 pg_stat_reset 
---------------
 (1 row)
commondb=select datid,datname,pg_size_pretty(blks_read*8192) blks_read,pg_size_pretty(blks_hit*8192) blks_hit,round(blks_hit/(blks_read+blks_hit),2) hit_ratio from pg_stat_database where datname='commondb';
 datid  | datname  | blks_read | blks_hit | hit_ratio 
--------+----------+-----------+----------+-----------
 525340 | commondb | 0 bytes   | 1259 MB  |      1.00

3.4 结合pg_stat_bgwriter使用

可以结合pg_stat_database分别了解checkpoint、bgwriter、backend三个进程完成的刷脏量

postgres=\c commondb
commondb=select pg_stat_reset();
 pg_stat_reset 
---------------
 (1 row)
commondb=select checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
round(buffers_checkpoint/(buffers_checkpoint+buffers_clean+buffers_backend )::decimal,2) ratio_checkpoint,
buffers_clean,
round(buffers_clean/(buffers_checkpoint+buffers_clean+buffers_backend )::decimal,2) ratio_bgwriter, 
buffers_backend,
round(buffers_backend/(buffers_checkpoint+buffers_clean+buffers_backend )::decimal,2) ratio_checkpoint 
from pg_stat_bgwriter;   
checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | ratio_checkpoint | buffers_clean | ratio_bgwriter | buffers_backend | ratio_checkpoint 
-------------------+-----------------+-----------------------+----------------------+--------------------+------------------+---------------+----------------+-----------------+------------------
              4812 |              35 |             367777342 |              3289494 |           10630385 |             0.32 |      10245413 |           0.30 |        12763415 |             0.38
commondb=# \watch 1
                                                                            Fri 18 Aug 2023 02:36:19 PM WIB (every 1s)

 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | ratio_checkpoint | buffers_clean | ratio_bgwriter | buffers_backend | ratio_checkpoint 
-------------------+-----------------+-----------------------+----------------------+--------------------+------------------+---------------+----------------+-----------------+------------------
              4812 |              35 |             367777342 |              3289494 |           10630423 |             0.32 |      10245413 |           0.30 |        12763418 |             0.38
(1 row)

                                                                            Fri 18 Aug 2023 02:36:20 PM WIB (every 1s)

 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | ratio_checkpoint | buffers_clean | ratio_bgwriter | buffers_backend | ratio_checkpoint 
-------------------+-----------------+-----------------------+----------------------+--------------------+------------------+---------------+----------------+-----------------+------------------
              4812 |              35 |             367777342 |              3289494 |           10630426 |             0.32 |      10245413 |           0.30 |        12763418 |             0.38
(1 row)

3.5 其他

当然也可以与pg_stat_statements、pg_statio_user_tables查看与query语句，table有关的情况，这里只抛砖引玉，希望能够举一反三