PostgreSQL 监控1统计进程和统计信息的解读|学习笔记（二）

开发者学堂课程【PostgreSQL快速入门：PostgreSQL监控1统计进程和统计信息的解读】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/16/detail/79

PostgreSQL  监控1统计进程和统计信息的解读

对于9.2以及.2以上的版本，toatl time 的单位是毫秒，老版本单位是秒，如果是秒，就需要乘以1000，如果不是秒就不需要乘以1000。此处使用的脚本是9.2版本以下，所以不需要乘以1000，total time 需要除以1000。

输出内容排在第一位的是 Insert into Test，调用了七十七万多次，总共耗时八千多毫秒，平均毫秒是0.01毫秒。排在第二位的是创建表，调用了一次，耗费了5.6毫秒，平均毫秒是5.6毫秒，将结果以邮件形式输出，发送给需要了解的用户。

接下来是第四个例子，查看 Background writer 的统计信息，当中包含的字段如下：Checkpoint time，指多少个计划的 checkpoint，此处计划的 checkpoint Segment 是指产生三个 segment 就进wl日志，自动触 发checkpoint。如果五分钟都没有触发 checkpoint，也会自动触发一次 checkpoint，例如五分钟数据库没有任何活动，不会切换 xlog ，5分钟没有切换到3个 xlog。也会做 checkpoint。

以下内容会计划到 checkpoint 当中：

Checkpoint请求三次，计划是两次。Stats Reset 是指上次重置的 Research。

Check point Write time 是指所有的 checkpoint 所消费的时间，也就是文件所写到磁盘当中所耗费的时间。Sync Time 是指文件同步到磁盘的时间，以上分别是 write to disk 和 sync to disk，单位都是毫秒。一个是写到磁盘，但是没有进行同步，之后是进行同步的时间。同步需要 flash 到 disk，需要写入到 disk 当中。写入到disk是指还在缓存当中。Sync to disk 才是真正持久化到存储当中。

如图所示：

Sync time 是299毫秒，Write time 是57702毫秒。

buffer Writer checkpoint，是指有多少个 buffer 写入，该参数的单位是 pages。

查看视图：

pg_stat_byWriter;

结果如下:

SELECT

pg_stat_get_hgwriter_tined_checkpoints<>As checkpoints_timed,

pg_stat_get_bgwriter_requested_checkpoints<>As checkpoints_req,

pg_stat_get_checkpoint_write_tine<>As checkpoint_write_tine ,

pg_stat get checkpoint s ync_tine<> As

checkpoint_s ync_time ,

pg_stat_set_bgwriter_buf_written_checkpoints<>As buffers_checkpoint,

pg_stat_get_ bgwriter_buf _written_clean<> As

buffers_clean,

pg_stat_get_bgwriter_naxwritten_clean<>As

naxwritten_clean ,

pg_stat_ get_buf __written_backend<> As

buf fers_backend,

pg_stat_get_ buf f s ync_ backend<> As

buffers_ backend f s ync ,

pg_stat _et buf alloc<> As

buf fers_ alloc ,

pg_stat_get_bgwriter_stat _reset_tine<>As

stats _reset ;

调用的函数如下:

pg_stat_set_bgwriter_buf_written_checkpoints<>As buffers_checkpoint,

该函数定义位置如下：

src/ backend/utils/adt/pgstatfuncs.c

返回的是 global 当中的 buffer writer checkpoint。

头文件位置如下：

Src /inc ude/pgstat .h

返回内容如下：

pgstat fetch global<void>;

当中存在 buf_written_checkpoint，buf_write time 等结构：

Pgstat_Counter timed_checkpoints ;

PgStat_Counter requested_ checkpoints ;

PgStat_Counter checkpoint_write_ tine ;

PgStat_Counter checkpoint sync_tine ;

PgStat_Counter buf _written_checkpoints;

PgStat _Counter buf ._ _written_c lean ;

PgStat__Counter maxwritten_lean ;

PgStat__Counter buf _uritten backend;

PgStat __Counter buf  f s ync backend;

PgStat_Counter buf _ alloc ;

TinestampTz stat reset_timestamp ;

结构当中的单位未指明，单位是数据块。

前方式 bg_write 的统计信息，后面是 backend 的统计信息。如果 backend 的 buffer 写过于频繁，说明需要的buffer 不够，或是 bg_write_slip 太长，需要调整。只有脏数据才需要写到磁盘，如果不是脏数据，就不需要回写。

backend 的 buffer 写过于频繁，因为正常情况下有专门的进程，也就是 bgwrite 进程，负责 shared buffer 的脏数据写到磁盘当中。例如在执行 sql 语句时，需要将部分内容写入 sharedbuffer 当中，但如果 sharedbuffer 空间不够，例如 shared buffer 当中的脏数据太多，剩余的非脏数据较少，如果需要申请sharedbuffer使用，只能将脏数据fresh，才能将 sharedbuffer 空闲，之后进行申请使用。如果 backend 写脏数据过于频繁，说明 sharedbuffer 不够用，或者 bg write 睡眠时间太长。

例如产生的脏数据是每秒1,000个，bg write 每次写完1,000个之后，睡眠0.1秒或0.2秒，此时就产生较多脏数据在sharedbuffer 中，所以 bg write 单独进行写入，显然不能满足。因为睡眠时间太长，或者 sharedbuffer 不够用，此时 backgroundwrite 需要申请 sharedbuffer 时，就会主动将在脏 buffer 写掉。Bufferbackend 值过大，从侧面反映出 sharedbuffer 不够用，或睡眠时间太长，或 bgwrite 每次写 page 数太少。

参数控制：

Shared buffer 控制大小，backgroundwrite delay 表示每次写完循环之后，休眠10毫秒再写下一个循环，每次最多从lr预算法当中100个页面。

Multiples=2.0：上次写了一个脏页面，下一次预计写两个脏页面。2.0是指上一次的倍数。如果超过100个，就只写100个。例如这次写60个，下次预计写120个，但是如果超过100，就只会写100，因为120超过100了。

以上是当中涉及的内容调整。如果 backend 的 buffer 写过于频繁，就可以进行调整，例如将时间调小或空间调大即可。

第五个例子是查看数据库级别的统计信息，例如数据库的事物的提交次数，回滚次数，未命中数据块的读，命中的读，行的统计信息，行的统计信息当中只能有扫描行数，输出行数，插入行数，更新行数，删除行数，还包括临时文件，死锁，iotime 等统计信息。之前的课程讲述 iotime 需要打开 CHECK iotime，否则就是0。

数据行的扫描和输出的区别：

扫描行不一定输出，例如输入以下代码：

Select count(*) from iso_test;

总共扫描了1230行，但是只输出了1行。所以 tup returned 有一千多，但是 fetch 只有1。查看结果，代码如下：

digoal =#fcreate table tblid int);

CREATE TABLE

digoal=#insert into tbl select generate_series( 1,10000);

INSERT 0 10000

Digoal=# analyze tbl;

ANALYZE

重置统计信息,便于查看

Digoal=# select pg_stat_reset()；

pg_stat_reset

(1 row)

Digoal=# select tup_returned,tup_fetched

from pg_stat_datatase

where datname =digoal';tup_returned | tup_fetched

----------------*-----------------

0|0

(1 row)

首先创建测试表，然后插入10000行，分析表，将整个统计信息重置，重置之后查看 debase，查看结果如下：

tup returned=0；

tup  fetch=0；

当前如果没有索引，需要输出4行，但是加了 where 条件，此时是全表扫描，tup returned 有全表扫描的行，对于fetch，只 fetch 5行。

查看 database 当中包含的信息：

1. Datid
   
2. datname
   
3. numbackends
   
4. xact_commit
   
5. xact_rollback
   
6. blks_read
   
7. blks_hit
   
8. tup_returned
   
9. tup_fetched
   
10. tup_inserted
    
11. tup_updated
    
12. tup_deleted
    
13. conflicts
    
14. temp_files
    
15. temp_bytes
    
16. deadlocks
    
17. blkread_time
    
18. blk_rite_time
    
19. stats_reset
    

numbackends 指当前数据库上连接的客户端数量；

xact_commit 指自从上一次数据统计信息重置以来提交的事物数量，rollback 指回滚的事务数量；

blk read指读的数据块数量；

brox hint指的是命中读的数据块数量，命中读指在 postgre shared buffe r当中的读，只要没有在postgre shared buffer 当中命中读，都叫 read。可能在 OS catch 当中命中，也叫 read。

tup_returned 指扫描行数；

tup_fetched 获取行数；

tup_inserted 插入行数；

tup_updated 更新行数；

tup_deleted 删除行数；

冲突次数指数据库的查询和 standerbyrecurry 发生冲突，会将其记录。例如冲突了10次，表示发生了10次冲突；

temp_files，指创建的临时文件数量；

temp_bytes 是临时文件被创建的所有的大小；

deadlocks 指发生了多少次死锁；

blkread_time 和blk_rite_time，指打开了track blk_rite_time时，统计了数据库所消耗的 lO 的时间；

对于表级别的统计，例如stats_all_tables，当中包含内容如下：

1. relid
   
2. schemaname
   
3. relname
   
4. seq_scan
   
5. seg_tup_read
   
6. id_scan
   
7. id_tup_fetch
   
8. n_tup_ins
   
9. n_tup_upd
   
10. n_tup_del
    
11. n_tup_hot_upd
    
12. n_live_tup
    
13. n_dead_tup
    
14. last_vacuum
    
15. last_autovacuum
    
16. last_analyze
    
17. vacuum_count
    
18. autovacuum_count
    
19. analyze_count
    
20. autoanalyze_count
    

包括通过全表扫描，扫描了多少数据块；使用全表扫描时，取了多少数据，返回了多少行，例如全表扫描，查询了5条记录，但是表有一万行，此处也只有5。

索引扫描，例如表有5个索引，统计的是 5个索引所有的统计信息；通过索引返回的行数；插入行数；更新行数，当前行数；删除行数； hlt 行数；表当前的 live 行；deadtup数量；最后一次 vacuum 时间；最后一次 analyze 时间；最后一次自动 vacuum 时间；最后一次自动analyze时间；表总共发生 vacuum 次数；发生自动 acuum 次数；发生analyze 次数；发生自动 analyze 次数。

第七个例子包含如下内容：

1. relid
   
2. indexrelid
   
3. schemaname
   
4. relname
   
5. indexrelname
   
6. idx_scan
   
7. idx_tup_read
   
8. idx_tup_fetch
   

指单个索引，当中包括通过一个索引的扫描次数；从索引当中的扫描行数；search 指返回行数，指单个索引。表级别的是表上所有的索引合起来的形式。

第八个例子指的是 IO，例如对于一个表 IO 统计，当中包含如下内容：

1. relid
   
2. schemaname
   
3. relname
   
4. heap_blks_read
   
5. heap_blks_hit
   
6. idx_blks_read
   
7. idx_blks_hit
   
8. toast_blks_read
   
9. toast_blks_hit
   
10. tidx_blks_read
    
11. tidx_blks_hit
    

表可能有表自身的 heap 部分， toast 部分，例如变长字段，是8K的数据块，压缩之后，如果字段数据还超过2k，该字段的数据就会存到 toast 的表当中，所以也有 toast 表的统计。还包括索引的数据块的统计，如果索引是 toast 字段，那么也区分 toast 字段的索引统计。

当中定的 read 和 hint 和之前含义相同，read 是指没有在 shared buffer 当中命中的读， hint 指在postgre shared buffer当中命中的读。

第九个例子是指索引：

1、relid

2、indexrelid

3、schemaname

4、relname

5、indexrelname

6、idx_blks_read

7、idx_blks_hit

之前的表当中也有索引，是整个表所有的索引合起来的统计，此处是单个索引。例如通过该索引命中了多少个的数据块数量，该索引的数据块在非 shared buffer 当中读的数量。

第十个例子是序列，在数据库当中，序列存在对应的对象文件，与表相同，结构较为简单，与表结构不同。包含内容如下：

1、sequence_name | seq2

2、last_value l 1

3、start_value| 1

4、increment_by l 1

5、max_value| 9223372036854775807

6、min_value l 1

7、cache_value

8、log_cnt|0

9、is_cycled | f

10、is_called |f(1 row)

当中也包含是否在 shared buffer 当中命中的信息。

第十一个例子是用户的函数的统计：

1、funcid

2、schemaname

3、funcname

4、calls

5、total_time

6、self_time

例如函数被调用次数，函数被调用的时间，self_time,假设一个函数当中调用了其他函数，会把其他函数的调用排除，指的只是自己所调用的部分所耗费的时间。例如a函数当中调用了b函数，那么a函数总共的执行时间就包含b函数执行时间，total_time 就是10。如果b函数执行时间是6秒， a函数要排除6秒，self_time 就是4秒。

第十二个例子是流复制的统计，流复制包含如下内容：

1、pid

2、usesysid

3、usename

4、application_name

5、client_addr

6、client_hostname

7、client_port

8、backend_start

9、state

10、sent_location

11、write_location

12、flush_location

13、replay_locations

14、sync_priority

15、sync_state

Pg_stat_replications 是在主节点查询，当中被查到的信息是被节点连过来的信息。例如被节点是通过 replication 的用户名连接，username 就是 application，Application 如果配置了其他姓名，此时也会被连接显示。

客户端的ID，地址，主机名，端口，指的是流复制连接的，例如IP地址、主机名、端口；backend_start 指的是对应的 postgre 主节点的 backend 的启动时间；state 是当前的状态，sync_stat 是指当前状态是同步还是异步，例如当前是同步节点，就是sync，如果是异步节点，就是 unsync。

Sent_location、write_location、flush_location 指 wal数据发送到对应的节点的发送的位置，对方写的位置，flush 到磁盘的位置；replay 的位置，是对方已经做 recovery 的位置，以上能够看出延迟，在之后的课程中会讲述监控，也就是如何通过该部分监控standby的延迟；sync_priority 是同步节点的优先级；

例如当中查出的信息如下：

postgresl select * from pg_stat_replication ;

-[ RECORD 1 ]----+--

pid|24085

usesysid|16391

usename l replica

application_name | walreceiver

client_addr| 192.168.xxx.xXX

client_hostname l

client_port |52568

backend_start|2014-02-1116:54:57.788442+08

state| streaming

sent_location |85FfF9F48ECO

write_location |85FF9F48ECO

flush_location|85FfF9F48ECO

replay_location | 85FF9F48ECO

sync _priority |  0

sync_state --async--------

sent、write、flush、replay 均已达到一致，表示数据库性能较好，较为空闲。

第十三个例子是数据库的冲突。冲突在 stand by 节点查看，是 stand by 冲突试图。standby在apply xlog 时可能与正在 standby 执行的 SQL 发生冲突。例如查询的表在 wal 信息中有 truncate 的信息，如果将其 truncate，那么就无法查询，就发生冲突。

冲突可以通过几个配置参数来决定是否需要 cancel 冲突的该 sql：

1. max_standby_archive_delay
   
2. max_standby_streaming_delay (catching up (sent
   

loc=write loc)后的允许时长)，Catching up 指 sent 和 write 达到一致。

以下状态就是 catch up 的状态：

sent_location |85FfF9F48ECO

write_location |85FF9F48ECO

flush_location|85FfF9F48ECO

replay_location | 85FF9F48ECO

例如从该时间开始进行运行 sql 语句，此时刚好发生冲突，允许的冲突时长，如果是流复制就通过该参数决定，假设设定为十秒，系统会等待十秒，十秒之后如果仍然不能解决冲突，SQL 语句还未执行完，此时数据库会将其取消，继续进行 apply，

replay_location会产生变化。以上就能监控到数据库的冲突。