一款不错的巡检工具

前言

前阵子在夜里加班(摸鱼)的时候,闲来无事翻到一篇不错的博文,遂翻译了一下。在翻译的过程中,发现文中提到了pg_gather,下来捣鼓体验了一下,不失为一款好用的PostgreSQL巡检分析工具。

介绍

pg_gather,如其名,介绍很简单,用来采集PostgreSQL数据库性能的纯SQL脚本,支持多平台。

This is a SQL-only script for gathering performance and configuration data from PostgreSQL databases

支持 10、11、12、13,最新的14还没有看到,不过自己改吧改吧也没问题。

链接在:https://github.com/jobinau/pg_gather

数据采集

采集很简单,大概会花费20s左右

psql <connection_parameters_if_any> -X -f gather.sql > out.txt

但是这里面有个小坑,就是假如某个表持有了AccessExclusiveLock的话,这个采集进程会被阻塞,直到获取到锁。

然后将数据导入,最好使用一个临时实例进行操作,避免污染生产环境。此步骤主要是删除一些字符,比如Tuples、PREPARE

sed -e '/^Pager/d; /^Tuples/d; /^Output/d; /^SELECT pg_sleep/d; /^PREPARE/d; /^\s*$/d' out.txt | psql -f gather_schema.sql -f -

生成HTML报告

psql -X -f gather_report.sql > GatherReport.html

至此,性能分析报告就出来了。

分析

随便用个浏览器打开即可。主要采集的项包括

1.Index Info,索引信息2.Parameter settings,参数配置3.Extensions,插件4.Session Summary,会话总览5.Database Time,数据库时间消耗6.Session Details,会话7.Blocking Sessions,阻塞信息8.Top 10 Statements,TOP 10 SQL9.Background Writer and Checkpointer,检查点状况10.Important Findings,重要发现

首先是总览报告了,一些鸡零狗碎的信息,可以重点关注一下数据库级别的信息,基本就是采集pg_stat_database视图,比如年龄age、命中率hit_ratio,和临时文件temp_files和temp_bytes,这两者过多说明溢出到磁盘太多次数了,性能会受到影响。

一款不错的巡检工具
一款不错的巡检工具

值得一提的是,还有一个类似于PGTune的窗口,协助分析,PGTune是一款傻瓜式的优化工具了,根据你键入的服务器配置,生成一套通用的优化参数,大多数场景还是能应付应付了。https://pgtune.leopard.in.ua/#/

一款不错的巡检工具
一款不错的巡检工具

Tables info

下面是表的summary,关注膨胀率、年龄和last analyze即可,是否及时更新统计信息,是否膨胀比较厉害等

一款不错的巡检工具
一款不错的巡检工具

当然也可以按照感兴趣的列进行排序,比如我按照Dead/Live进行排序,可以看到pgbench_brancher膨胀的最厉害

一款不错的巡检工具
一款不错的巡检工具

Index info

索引关注扫描次数即可,对于扫描次数过低的索引,可以考虑删除,因为还会影响写入性能

一款不错的巡检工具
一款不错的巡检工具

接下来是参数,可以看到,该报告提示你max_wal_size才500MB,要动一动,同理还有random_page_cost,不过赶PGTune还是差一点

一款不错的巡检工具
一款不错的巡检工具

Database time

另外一个需要关注的是Database time,这个反馈的是数据库整体的等待时间,可以大致上分析出当前数据库的性能瓶颈。

一款不错的巡检工具
一款不错的巡检工具

通过这么一个报告,我们可以得出如下几个结论,参照官网 https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-ACTIVITY-TABLE

1.ClientRead花费了最多,Waiting to read data from the client.,这意味着服务端正在等待客户端发送下一个请求,其实就是跑完了处于idle的状态,应用服务器和数据库服务器之间的网络速度较慢的话影响会更大2.transactionid,Waiting for a transaction to finish.,说明在等待事务结束,通常发生了大量的行锁竞争,update,需要等待其他事务结束3.WALWrite、WALSync Waiting for WAL buffers to be written to disk和 Waiting for a WAL file to reach durable storage.,因为服务器没有调优,默认是同步提交,所以要保证事务commit时,WAL是刷盘的,那么刷盘就会竞争这个锁,可以考虑调大WAL buffer,或者异步提交4.CPU,这个就是纯CPU资源不够了,硬条件,只能sacle up了,我的小破云主机才2C5.DataFileRead, Waiting for a read from a relation data file,等待读取数据文件,可能shared buffer太小了,发生了大量的evict,也可能磁盘太挫了,随机寻址的速度跟不上(我的小破云主机又躺枪了)6.VacuumDelay,这个就是达到了vacuum的成本,要睡眠了,可以调整一下vacuum_cost_limit,不要那么频繁休眠,生产队的驴都不敢这么歇7.SLRUSync,Waiting for SLRU data to reach durable storage following a page write.,比如CLOG、子事务、多事务采用的SLRU的算法进行刷盘,把相关共享内存数据写到磁盘,并调用pg_fsync 方法把相关内容文件同步到磁盘上对应文件。注意Buffer(数据文件)不是采用的SLRU。内存中的clog buffer的大小为 Min(128, Max(4, NBuffers / 512)),默认 256KB,可以直接使用pg_stat_slru查询

一款不错的巡检工具
一款不错的巡检工具
一款不错的巡检工具
一款不错的巡检工具

8.XidGen,分配事务ID,最顶层的锁9.DataFileExtend,莫有办法,扩数据块,9.6以后做了优化,动态按需扩展,但仍然是一个块一个块扩展。当然也可以选择调大blocksize,不过只能弱化这个影响,好在在此例只有1条记录

Session details

会话的信息,最后一列的waits挺不错,可以分析该会话都耗费在了哪些事件中,如果说database time是全局的,那么session details则是细节了

一款不错的巡检工具
一款不错的巡检工具

Block sessions

锁的信息

一款不错的巡检工具

当然可以直接用如下SQL,还会打印阻塞的时间

postgres=# SELECT                                      
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    now() - blocked_activity.query_start AS blocked_duration,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    now() - blocking_activity.query_start AS blocking_duration,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM                                                       
    pg_catalog.pg_locks AS blocked_locks
    JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks AS blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted;
-[ RECORD 1 ]------+---------------------------------------
blocked_pid        | 7047
blocked_user       | postgres
blocked_duration   | 00:00:03.061062
blocking_pid       | 6017
blocking_user      | postgres
blocking_duration  | 00:03:21.216624
blocked_statement  | alter table test add column info text;
blocking_statement | update test set id = 99 where id = 1;

Background Writer and Checkpointer Information

关于检查点和bgwriter的信息,这个也是一个不错的性能报告

一款不错的巡检工具
一款不错的巡检工具

我们可以得出如下结论:

1.两次检查点的平均时间是4.98分钟,说明是达到了checkpoint_timeout触发的,而不是max_wal_size2.Clean by backends占到了81.1%,说明绝大多数刷脏的事情都被backend process做了,backend申请shared buffer的时候找到了脏块,首先把脏块写回,再清理掉脏块,存入新的数据。backend process居然还要干这种脏活!bgwriter应该是脏块回写的主力,需要调整一下bgwriter_delay、bgwriter_lru_maxpages和bgwriter_lru_multiplier3.最后两列,1 What percentage of bgwriter runs results in a halt, 2 What percentage of bgwriter halts are due to hitting on bgwriter_lru_maxpages limit,可以看到15%的bgwriter时间由于达到了bgwriter_lru_maxpages参数的限制,就休息了,休息bgwriter_delay这么多毫秒继续工作4.其他的就是检查点期间写入了多少量(Total MB Written)、检查点每次刷到page cache和最后做fsync的时间(write time和sync time),可以根据情况调整checkpoint_compeletion_target

Important Findings

下面就是需要关注的事件,可以看到有两个空闲事务和阻塞进程,DB killer啊,需要及时处理

一款不错的巡检工具
一款不错的巡检工具

小结

总体上来说,pg_gather是一款不错的性能分析脚本,关于等待事件和检查点的报告也是十分详细的,并且做了一个简单的分析。各位可以依葫芦画瓢进行分析,而且该脚本是纯SQL的,你完全可以按需添加,改吧改吧,形成自己的瑞士军刀。

发表评论

登录后才能评论
服务中心
服务中心
联系客服
联系客服
返回顶部