京公网安备11010502063487号“LOG: checkpoints are occurring too frequently” 是 PostgreSQL 日志里最常见的警告之一,HINT 只说调大 max_wal_size。这篇在本机自己起一个 PostgreSQL 16.15,用 pgbench 复现警告,用 log_checkpoints 读检查点日志,验证 max_wal_size、checkpoint_completion_target 的作用和手动 CHECKPOINT 的耗时。
max_wal_size = 64MB:10 秒内 9621 tps;检查点次数:timed 0、requested(因为 WAL 满了)22
LOG: checkpoints are occurring too frequently (0 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
LOG: checkpoints are occurring too frequently (1 second apart)max_wal_size 太小,WAL 很快逼近上限,检查点被连续触发,日志出现 too frequently 和 HINT。
日志里出现 21 次 checkpoint starting,21 次 checkpoint complete;前两组:
LOG: checkpoint starting: wal
LOG: checkpoint complete: wrote 4307 buffers (13.1%); 0 WAL file(s) added, 1 removed, 1 recycled; write=0.231 s, sync=0.052 s, total=0.295 s; sync files=18, longest=0.042 s, average=0.003 s; distance=32553 kB, estimate=43285 kB;
LOG: checkpoint starting: wal
LOG: checkpoint complete: wrote 3229 buffers (9.9%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.221 s, sync=0.050 s, total=0.282 s; sync files=8, longest=0.038 s, average=0.007 s; distance=33139 kB, estimate=42271 kB; ls
所有 starting 的原因:wallog_checkpoints = on 记录触发原因、写了多少缓冲区、write / sync / total 各用时。
max_wal_size tps timed requested 日志里 too frequently
32MB 10179 0 34 34
64MB 8956 0 17 17
1GB 11231 0 0 0
4GB 10919 0 0 0调到 1GB 之后 WAL 触发的检查点和警告消失;吞吐在这台机器上波动不大。
checkpoint_completion_target = 0.9:65 秒内 11766 tps,1 次检查点完成,write 阶段平均 26.1 s(最长 26.1 s) checkpoint_completion_target = 0.1:65 秒内 11652 tps,2 次检查点完成,write 阶段平均 2.6 s(最长 3.0 s)
0.9 把写脏页摊在整个间隔里,0.1 集中写完。
更新行数 脏缓冲区数 CHECKPOINT 耗时 ms
1000 80 3.6
50000 2455 15.8
300000 12769 70.3
1000000 32089 179.7耗时大致随脏页数线性增长。