SQL|如何查看bgwriter和checkpoint的执行效率

（一）背景

当生产环境出现如：应用大量写入操作SQL性能异常、pglog后台频繁记录checkpoint、bgwriter进程资源使用率较高、磁盘I/O读写活动频繁、换页缓慢等等时，我们常常会关注后台写bgwriter和检查点checkpoint进程的效率，从而判断是否是bgwriter进程无法有效地进行刷脏块(已修改但未写入磁盘的数据块)、或者大量写导致bgwriter清理操作无法跟上脏块生成速度、或者频繁checkpoint导致资源浪费、磁盘IO性能差、又或者是否可以通过调整参数提高效率等等，从而更好的进行PG数据库的运维。

（二）参考

推荐SQL：

SELECT round(checkpoints_req*100/tot_cp,1) "Forced Checkpoint %" ,
round(min_since_reset/tot_cp,2) "Avg mins between CP",
round(checkpoint_write_time::numeric/(tot_cp*1000),4) "Avg CP write time (s)",
round(checkpoint_sync_time::numeric/(tot_cp*1000),4)  "Avg CP sync time (s)",
round(total_buffers::numeric*8192/(1024*1024),2) "Tot MB Written",
round((buffers_checkpoint::numeric/tot_cp)*8192/(1024*1024),4) "MB per CP",
round(buffers_checkpoint::numeric*8192/(min_since_reset*60*1024*1024),4) "Checkpoint MBps",
round(buffers_clean::numeric*8192/(min_since_reset*60*1024*1024),4) "Bgwriter MBps",
round(buffers_backend::numeric*8192/(min_since_reset*60*1024*1024),4) "Backend MBps",
round(total_buffers::numeric*8192/(min_since_reset*60*1024*1024),4) "Total MBps",
round(buffers_alloc::numeric/total_buffers,3)  "New buffers ratio",
round(100.0*buffers_checkpoint/total_buffers,1)  "Clean by checkpoints (%)",
round(100.0*buffers_clean/total_buffers,1)   "Clean by bgwriter (%)",
round(100.0*buffers_backend/total_buffers,1)  "Clean by backends (%)",
round(100.0*maxwritten_clean/(min_since_reset*60000 / delay.setting::numeric),2)   "Bgwriter halts (%) per runs (**1)",
coalesce(round(100.0*maxwritten_clean/(nullif(buffers_clean,0)/ lru.setting::numeric),2),0)  "Bgwriter halt (%) due to LRU hit (**2)",
round(min_since_reset/(60*24),1) "Reset days"
FROM pg_stat_bgwriter
CROSS JOIN 
(SELECT 
    NULLIF(round(extract('epoch' from now() - stats_reset)/60)::numeric,0) min_since_reset,
    GREATEST(buffers_checkpoint + buffers_clean + buffers_backend,1) total_buffers,
    NULLIF(checkpoints_timed+checkpoints_req,0) tot_cp 
    FROM pg_stat_bgwriter) AS bg
LEFT JOIN pg_settings delay ON delay.name = 'bgwriter_delay'
LEFT JOIN pg_settings lru ON lru.name = 'bgwriter_lru_maxpages';

执行结果样例如下：

SQL说明：

Forced Checkpoint %: 强制执行checkpoint的百分比，即checkpoints_req占(checkpoints_timed+checkpoints_req)的百分比。

checkpoints_req数值的增加：达到checkpoint_timeout参数设置的时间后触发的检查点。

checkpoints_timed数值的增加：达到max_wal_size参数设置大小或者手动触发checkpoint。合理情况：checkpoints_timed要比checkpoints_req大。

建议参考： 我们希望这个百分比无限接近于0，减少强制执行checkpoint的频率。如果该值过大(如超过10%)，说明系统强制执行checkpoint操作较多，可以根据具体原因，适当考虑增加：checkpoint_timeout、checkpoint_completion_target、max_wal_size参数的值。

Avg mins between CP: 两次检查点中间的平均时间间隔(分钟)，以默认的checkpoint_timeout为5分钟为例，如果低于这个时间间隔可能表示数据库写入负载较高，在超时参数之前就已经需要完成一次checkpoint。注意，checkpoint_timeout设置过大，会增加数据库crash恢复时间。

建议参考： 配置合理的checkpoint参数。

Avg CP write time (s): 平均checkpoint写入时间(秒)，主要是从数据库缓存shared_buffer中写入到操作系统page cache花费的时间。如果写入时间较长，可能是由于硬件性能不足(如磁盘I/O性能)或者检查点频率过高(如checkpoint_timeout设置比较大)导致的。

Avg CP sync time (s): 平均checkpoint检查点执行sync同步时间（秒）。主要指调用fsync（可调整）将脏块同步到磁盘花费的时间。如果同步时间较长，可能是由于磁盘IO性能不佳或者同步操作被阻塞/获取不到资源导致的。

建议参考： Avg CP write和sync time较长，可以考虑更换更优的存储/磁盘设备，或者调整数据库checkpoint_timeout|checkpoint_completion_target等参数配置减少时间。

Tot MB Written: 总写入的数据量（MB）。这个值表示从上次重置以来所有检查点写入的数据量。可以通过监视获取数据库写入活动的总体趋势。

MB per CP: 每个checkpoint检查点写入脏块的平均数据量（MB）。用于观察对比并评估每个检查点写入的数据量是否合理，如观察正常和异常情况下值的对比。

Checkpoint MBps: checkpoint检查点每秒写入脏块的速率（MBps）

Bgwriter MBps: bgwriter后台写每秒的写入块的速率（MBps）

Backend MBps: 后端进程每秒写入速率（MBps）

Total MBps: 每秒总写入速率（MBps）。它表示数据库每秒写入的总数据量。

New buffers ratio: 新分配的缓冲区比例。它表示在上次重置后新分配的缓冲区与总缓冲区的比例。即buffers_alloc占(buffers_checkpoint + buffers_clean + buffers_backend)的百分比。

建议参考： 如果该值很高，说明在统计周期内，系统新增了很多的缓冲区，可能是shared_buffers参数过小导致需要频繁地新增缓冲区、也可以是缓冲区不能即使被释放/重用或者写入量过大等等。

Clean by checkpoints (%): 由检查点清理的缓冲区百分比。

建议参考： 如果比例对比chean by bgwriter过高，可以通过加大checkpoint_timeout、checkpoint_completion_target参数来降低检查点，同时加大bgwriter后台写的效率，如增加 bgwriter_delay、bgwriter_lru_maxpages 等

Clean by bgwriter (%): 由后台写入器清理的缓冲区百分比。较高正常情况下是一个好的表现。其中，bgwriter常常关注如下三个参数：

bgwriter_delay – 超过已处理缓冲区数时的睡眠延迟时间。
bgwriter_lru_maxpages – bgwriter 延迟后已处理的缓冲区数。
bgwriter_lru_multiplier – bgwriter 用来计算下一轮需要清理多少缓冲区。

Clean by backends (%): 由后端进程清理的缓冲区百分比。

建议参考: buffers_backend值远低于 buffers_checkpoint和 buffers_clean 的总和。否则建议加大bgwriter_lru_multiplier和减少bgwriter_delay，当然也说明了shared_buffer共享缓冲区不足导致热数据被迫在内存和磁盘之间传输。

Bgwriter halts (%) per runs (1): bgwriter后台暂停的百分比。通常情况下后台写暂停，可能是由于(**2)的LRU命中，还可能是正在执行checkpoint检查点、IO瓶颈如磁盘操作被堵塞/IO繁忙无响应资源，当然也可能是其他系统资源如内存和CPU等无法获取导致的，等等。

Bgwriter halt (%) due to LRU hit (2): bgwriter后台由于 LRU 命中导致暂停的百分比。LRU即最近最少使用，改良版的是时钟扫描算法，bgwriter会根据策略来确定哪些脏页需要写入磁盘，如果大多数都是脏页且这些页最近都被使用过，不满足最近最少使用策略，不需要被写入磁盘，那后台写进程就会暂停，等待新的脏页。

建议参考： 计算bgwriter halts的maxwritten_clean值很高，说明bgwriter由于超过bgwriter_lru_maxpages导致不得不停止的次数很多，可以适当加大bgwriter_lru_maxpages参数降低这种情况。

Reset days: 距离上一次视图重置的时间，也就是上述数据是在重置时间到现在这段时间内发生的，假设最近相关异常较多，你只想关注未来一周的情况，你可以通过如下重置视图的方式重置视图，一周后查看即可。

SELECT pg_stat_reset_shared('bgwriter');

后话：

如果有任何需要指正/指教/交流的，可以关注微信公众号《PostgreSQL手记》后台评论，大家一起学习~