Postgresql检查点checkpoint参数配置
Postgresql检查点checkpoint参数配置
mingjie
发布于 2022-05-12 11:03:53
发布于 2022-05-12 11:03:53
文章被收录于专栏：Postgresql源码分析
相关参数 & 经验值
- timeout配的时间太长没用，shared_buffers写满了就强制刷了，所以希望间隔长要有足够大的buffer+足够慢的写入速度
- 希望平缓需要首先有较长时间的timeout（参考上一条），然后target给0.5 - 0.8 左右，配完了切记观察checkpoint log，不同的业务模型效果差距巨大。
shared_buffers = 128GB # 1/4 内存
checkpoint_timeout = 30min # range 30s-1d
max_wal_size = 256GB # 2*shared_buffers
min_wal_size = 64GB # shared_buffers * 1/2
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_timeout
Maximum time between automatic WAL checkpoints. If this value is specified without units, it is taken as seconds. The valid range is between 30 seconds and one day. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line. . 自动检查点间隔
max_wal_size
Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances, such as heavy load, a failing archive_command, or a high wal_keep_size setting. If this value is specified without units, it is taken as megabytes. The default is 1 GB. Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line. . 自动chk的时候，最大的WAL文件大小，超了就开始删了。这是软限制，经常会超。
min_wal_size = 64GB
As long as WAL disk usage stays below this setting, old WAL files are always recycled for future use at a checkpoint, rather than removed. This can be used to ensure that enough WAL space is reserved to handle spikes in WAL usage, for example when running large batch jobs. If this value is specified without units, it is taken as megabytes. The default is 80 MB. This parameter can only be set in the postgresql.conf file or on the server command line. . WAL的使用空间低于64GB的时候，chk的时候，用过的WAL不会删除，而是直接复用。这样的效果就是可以看到至少会有这么多WAL文件。
参数计算
shared_buffers = 128GB # 1/4 内存
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min上述的情况下：
- 假设shared buffer所有页都是脏页，128 GB脏页
- 时间窗口：30min x 0.9 的窗口内刷完全部脏页
- 空间窗口：256GB * 0.9 的窗口内刷完全部脏页
- 调度进程会判断上述限制能否达到，否则全力刷脏不做sleep
随机IO（数据）：128GB / (30 x 0.9 x 60) s = 80.9MB/s 顺序IO（日志）：256GB / (30 x 0.9 x 60) s = 161.8MB/s
bgwrite刷脏速率需要另作假设，和参数、写脏块速度有关，这里不在估算了，可见上一篇。
建议参数
shared_buffers = 128GB # 1/4 内存
checkpoint_timeout = 15min
max_wal_size = 256GB
min_wal_size = 64GB
checkpoint_completion_target = 0.2
checkpoint_flush_after = 256kB # measured in pages, 0 disables
checkpoint_warning = 30s # checkpoint_warning 两次填充WAL导致的检查点，比这个时间短，产生告警。
wal_buffers # 一个WAL段默认16MB，wal_buffers给16MB
# --wal-segsize可配为1GB，对应的wal_buffers给1GBcase
shared_buffers = 128GB # 1/4 内存
checkpoint_timeout = 15min
max_wal_size = 256GB
min_wal_size = 64GB
checkpoint_completion_target = 0.1
checkpoint_flush_after = 256kB # measured in pages, 0 disables
checkpoint_warning = 30s # checkpoint_warning 两次填充WAL导致的检查点，比这个时间短，产生告警。
log_checkpoints = on # 一定要开，日志量很少，否则没什么可分析的【QPS=516353.40】这是一个刷的过于用力的例子：
- chk给的时间是15min x 60 x 0.1 = 90秒内刷完，实际每次刷200s+，一定是全速刷了，RT应该会有明显抖动。
- 这个写业务的写压力很大，一个周期内基本把68% - 90%的sharebuffer写脏了
2021-09-10 10:54:13.421 CST [7861] LOG: 00000: checkpoint complete: wrote 11498192 buffers (68.5%); 0 WAL file(s) added, 38 removed, 85 recycled; write=196.472 s, sync=0.063 s, total=204.008 s; sync files=496, longest=0.003 s, average=0.001 s; distance=129470705 kB, estimate=129470705 kB
2021-09-10 11:09:24.162 CST [7861] LOG: 00000: checkpoint complete: wrote 13320365 buffers (79.4%); 0 WAL file(s) added, 39 removed, 97 recycled; write=205.924 s, sync=0.039 s, total=214.550 s; sync files=493, longest=0.003 s, average=0.001 s; distance=142717676 kB, estimate=142717676 kB
2021-09-10 11:24:27.192 CST [7861] LOG: 00000: checkpoint complete: wrote 14254670 buffers (85.0%); 0 WAL file(s) added, 43 removed, 100 recycled; write=209.332 s, sync=0.021 s, total=217.852 s; sync files=493, longest=0.002 s, average=0.001 s; distance=149645227 kB, estimate=149645227 kB
2021-09-10 11:39:00.483 CST [7861] LOG: 00000: checkpoint complete: wrote 10468864 buffers (62.4%); 0 WAL file(s) added, 41 removed, 99 recycled; write=183.227 s, sync=0.043 s, total=191.054 s; sync files=501, longest=0.002 s, average=0.001 s; distance=147064174 kB, estimate=149387121 kB
2021-09-10 11:55:07.532 CST [7861] LOG: 00000: checkpoint complete: wrote 15597825 buffers (93.0%); 0 WAL file(s) added, 42 removed, 134 recycled; write=249.431 s, sync=0.040 s, total=257.621 s; sync files=519, longest=0.003 s, average=0.001 s; distance=184289674 kB, estimate=184289674 kB【QPS=535149.54】target=0.1 -> 0.5优化效果 如果target=0.5的话，15min x 60 x 0.5 = 450s内刷完，按上面数据全速需要200s+刷完，看看现在的情况：
略有改善
2021-09-10 13:19:20.285 CST [56025] LOG: 00000: checkpoint complete: wrote 11765386 buffers (70.1%); 0 WAL file(s) added, 0 removed, 129 recycled; write=247.805 s, sync=0.021 s, total=247.992 s; sync files=499, longest=0.003 s, average=0.001 s; distance=135578996 kB, estimate=135578996 kB
2021-09-10 13:34:57.387 CST [56025] LOG: 00000: checkpoint complete: wrote 13427754 buffers (80.0%); 0 WAL file(s) added, 0 removed, 139 recycled; write=284.723 s, sync=0.045 s, total=284.922 s; sync files=497, longest=0.003 s, average=0.001 s; distance=145919085 kB, estimate=145919085 kB
2021-09-10 13:50:12.772 CST [56025] LOG: 00000: checkpoint complete: wrote 14648150 buffers (87.3%); 0 WAL file(s) added, 0 removed, 148 recycled; write=300.070 s, sync=0.013 s, total=300.224 s; sync files=497, longest=0.002 s, average=0.001 s; distance=154783746 kB, estimate=154783746 kB
2021-09-10 14:03:04.080 CST [56025] LOG: 00000: checkpoint complete: wrote 12415625 buffers (74.0%); 0 WAL file(s) added, 0 removed, 147 recycled; write=170.627 s, sync=0.160 s, total=170.936 s; sync files=495, longest=0.012 s, average=0.001 s; distance=154896944 kB, estimate=154896944 kB
2021-09-10 14:17:42.386 CST [56025] LOG: 00000: checkpoint complete: wrote 15659728 buffers (93.3%); 0 WAL file(s) added, 0 removed, 170 recycled; write=260.426 s, sync=0.008 s, total=260.593 s; sync files=526, longest=0.001 s, average=0.001 s; distance=177451720 kB, estimate=177451720 kB
2021-09-10 14:31:34.856 CST [56025] LOG: 00000: checkpoint complete: wrote 14672273 buffers (87.5%); 0 WAL file(s) added, 0 removed, 155 recycled; write=192.843 s, sync=0.205 s, total=193.222 s; sync files=519, longest=0.017 s, average=0.001 s; distance=162528667 kB, estimate=175959415 kB
2021-09-10 14:45:53.966 CST [56025] LOG: 00000: checkpoint complete: wrote 14532199 buffers (86.6%); 0 WAL file(s) added, 10 removed, 160 recycled; write=192.913 s, sync=0.013 s, total=194.934 s; sync files=533, longest=0.001 s, average=0.001 s; distance=178291484 kB, estimate=178291484 kB
2021-09-10 15:00:19.335 CST [56025] LOG: 00000: checkpoint complete: wrote 14993707 buffers (89.4%); 0 WAL file(s) added, 0 removed, 170 recycled; write=206.732 s, sync=0.174 s, total=207.086 s; sync files=565, longest=0.020 s, average=0.001 s; distance=178236389 kB, estimate=178285974 kB
2021-09-10 15:06:45.707 CST [56025] LOG: 00000: checkpoint complete: wrote 10991604 buffers (65.5%); 0 WAL file(s) added, 0 removed, 92 recycled; write=92.176 s, sync=0.001 s, total=92.187 s; sync files=530, longest=0.001 s, average=0.001 s; distance=96433284 kB, estimate=170100705 kB【QPS=532185.14】target=0.1 -> 0.9优化效果
既然写这么多，可以慢慢触发chk，让脏页多写几次在刷下去 15min x 60 x 0.9 = 810s
效果很明显，大部分都在400-600内刷完，平滑了很多，但是QPS降低了一点点，说明全局上看打散的效果略微低于集中的效果。
2021-09-11 18:49:47.909 CST [59218] LOG: 00000: checkpoint complete: wrote 11958480 buffers (71.3%); 0 WAL file(s) added, 0 removed, 131 recycled; write=670.578 s, sync=0.057 s, total=670.796 s; sync files=507, longest=0.004 s, average=0.001 s; distance=138206330 kB, estimate=138206330 kB
2021-09-11 19:02:33.831 CST [59218] LOG: 00000: checkpoint complete: wrote 12982080 buffers (77.4%); 0 WAL file(s) added, 0 removed, 134 recycled; write=606.659 s, sync=0.037 s, total=606.848 s; sync files=494, longest=0.003 s, average=0.001 s; distance=139668159 kB, estimate=139668159 kB
2021-09-11 19:14:57.614 CST [59218] LOG: 00000: checkpoint complete: wrote 14061094 buffers (83.8%); 0 WAL file(s) added, 0 removed, 134 recycled; write=619.574 s, sync=0.121 s, total=619.811 s; sync files=502, longest=0.013 s, average=0.001 s; distance=140508377 kB, estimate=140508377 kB
2021-09-11 19:25:56.213 CST [59218] LOG: 00000: checkpoint complete: wrote 12201314 buffers (72.7%); 0 WAL file(s) added, 0 removed, 134 recycled; write=496.072 s, sync=0.099 s, total=496.317 s; sync files=493, longest=0.006 s, average=0.001 s; distance=140510077 kB, estimate=140510077 kB
2021-09-11 19:32:08.515 CST [59218] LOG: 00000: checkpoint complete: wrote 13770466 buffers (82.1%); 0 WAL file(s) added, 0 removed, 134 recycled; write=282.906 s, sync=0.113 s, total=283.139 s; sync files=525, longest=0.013 s, average=0.001 s; distance=140514597 kB, estimate=140514597 kB
2021-09-11 19:43:01.185 CST [59218] LOG: 00000: checkpoint complete: wrote 14146487 buffers (84.3%); 0 WAL file(s) added, 0 removed, 134 recycled; write=545.018 s, sync=0.176 s, total=545.330 s; sync files=519, longest=0.015 s, average=0.001 s; distance=140505972 kB, estimate=140513735 kB
2021-09-11 19:53:20.928 CST [59218] LOG: 00000: checkpoint complete: wrote 12595691 buffers (75.1%); 0 WAL file(s) added, 0 removed, 134 recycled; write=467.938 s, sync=0.191 s, total=468.278 s; sync files=513, longest=0.029 s, average=0.001 s; distance=140513963 kB, estimate=140513963 kB
2021-09-11 20:03:10.033 CST [59218] LOG: 00000: checkpoint complete: wrote 14424781 buffers (86.0%); 0 WAL file(s) added, 0 removed, 134 recycled; write=473.644 s, sync=0.262 s, total=474.037 s; sync files=519, longest=0.019 s, average=0.001 s; distance=140501831 kB, estimate=140512750 kB
2021-09-11 20:09:08.988 CST [59218] LOG: 00000: checkpoint complete: wrote 13001493 buffers (77.5%); 0 WAL file(s) added, 0 removed, 134 recycled; write=266.628 s, sync=0.113 s, total=266.877 s; sync files=531, longest=0.008 s, average=0.001 s; distance=140522900 kB, estimate=140522900 kB
2021-09-11 20:17:02.578 CST [59218] LOG: 00000: checkpoint complete: wrote 13506457 buffers (80.5%); 0 WAL file(s) added, 0 removed, 134 recycled; write=383.178 s, sync=0.140 s, total=383.459 s; sync files=529, longest=0.015 s, average=0.001 s; distance=140499376 kB, estimate=140520548 kB
2021-09-11 20:32:54.097 CST [59218] LOG: 00000: checkpoint complete: wrote 14260672 buffers (85.0%); 0 WAL file(s) added, 0 removed, 134 recycled; write=809.563 s, sync=0.001 s, total=809.595 s; sync files=554, longest=0.001 s, average=0.001 s; distance=140506380 kB, estimate=140519131 kB上面是15min的结果，60min长间隔的checkpoint的效果呢？
【QPS=533518.83】60min / 0.1 预期：60 x 60 x 0.1 s = 360s（写压力过大，注意实际两次启动的间隔并不是1h） 实际：242 - 259s
2021-09-11 22:54:47.526 CST [31906] LOG: 00000: checkpoint complete: wrote 14915397 buffers (88.9%); 0 WAL file(s) added, 60 removed, 172 recycled; write=244.050 s, sync=0.028 s, total=256.327 s; sync files=533, longest=0.002 s, average=0.001 s; distance=243286048 kB, estimate=243286048 kB
2021-09-11 23:25:09.933 CST [31906] LOG: 00000: checkpoint complete: wrote 15518769 buffers (92.5%); 0 WAL file(s) added, 63 removed, 169 recycled; write=230.803 s, sync=0.020 s, total=242.452 s; sync files=538, longest=0.002 s, average=0.001 s; distance=243268128 kB, estimate=243284256 kB
2021-09-11 23:56:36.222 CST [31906] LOG: 00000: checkpoint complete: wrote 14257998 buffers (85.0%); 0 WAL file(s) added, 71 removed, 161 recycled; write=233.410 s, sync=0.009 s, total=247.076 s; sync files=557, longest=0.002 s, average=0.001 s; distance=243285477 kB, estimate=243285477 kB
2021-09-12 00:58:29.046 CST [31906] LOG: 00000: checkpoint complete: wrote 15468360 buffers (92.2%); 0 WAL file(s) added, 0 removed, 194 recycled; write=359.382 s, sync=0.001 s, total=359.425 s; sync files=566, longest=0.001 s, average=0.001 s; distance=203897004 kB, estimate=239346630 kB【QPS=537889.09】60min / 0.5 参数预期：60 x 60 x 0.5 s = 1800s（写压力过大，注意实际两次启动的间隔并不是1h） 实际预期：大概10-20min就会起来一次，(600 到 1200) x 0.5 = 300 到 600秒 实际：242 - 259s
2021-09-12 21:06:22.039 CST [15507] LOG: 00000: checkpoint complete: wrote 13073393 buffers (77.9%); 0 WAL file(s) added, 0 removed, 170 recycled; write=365.454 s, sync=0.005 s, total=365.656 s; sync files=520, longest=0.001 s, average=0.001 s; distance=178274703 kB, estimate=178274703 kB
2021-09-12 21:27:12.666 CST [15507] LOG: 00000: checkpoint complete: wrote 14855273 buffers (88.5%); 0 WAL file(s) added, 0 removed, 170 recycled; write=294.554 s, sync=0.168 s, total=294.899 s; sync files=514, longest=0.013 s, average=0.001 s; distance=178248930 kB, estimate=178272126 kB
2021-09-12 21:43:18.321 CST [15507] LOG: 00000: checkpoint complete: wrote 14478242 buffers (86.3%); 0 WAL file(s) added, 13 removed, 157 recycled; write=203.919 s, sync=0.018 s, total=206.471 s; sync files=511, longest=0.002 s, average=0.001 s; distance=178278733 kB, estimate=178278733 kB
2021-09-12 21:58:30.371 CST [15507] LOG: 00000: checkpoint complete: wrote 14681478 buffers (87.5%); 0 WAL file(s) added, 0 removed, 170 recycled; write=238.374 s, sync=0.182 s, total=238.722 s; sync files=524, longest=0.015 s, average=0.001 s; distance=178247496 kB, estimate=178275609 kB
2021-09-12 22:14:13.631 CST [15507] LOG: 00000: checkpoint complete: wrote 15264205 buffers (91.0%); 0 WAL file(s) added, 3 removed, 167 recycled; write=203.508 s, sync=0.022 s, total=204.287 s; sync files=529, longest=0.002 s, average=0.001 s; distance=178253857 kB, estimate=178273434 kB
2021-09-12 22:27:10.882 CST [15507] LOG: 00000: checkpoint complete: wrote 14710942 buffers (87.7%); 0 WAL file(s) added, 1 removed, 169 recycled; write=193.947 s, sync=0.040 s, total=194.321 s; sync files=543, longest=0.003 s, average=0.001 s; distance=178247404 kB, estimate=178270831 kB
2021-09-12 23:53:57.047 CST [15507] LOG: 00000: checkpoint complete: wrote 15553349 buffers (92.7%); 0 WAL file(s) added, 0 removed, 152 recycled; write=1799.949 s, sync=0.001 s, total=1799.976 s; sync files=557, longest=0.001 s, average=0.000 s; distance=160375441 kB, estimate=176481292 kB【QPS=537607.10】60min / 0.9 参数预期：60 x 60 x 0.9 s = 3240（写压力过大，注意实际两次启动的间隔并不是1h） 实际预期：大概10-20min就会起来一次，(600 到 1200) x 0.5 = 300 到 600秒
配60min完全没有效果，因为checkpoint会提早触发。
配参数的时候要考虑这一点。
2021-09-13 10:40:03.926 CST [45345] LOG: 00000: checkpoint complete: wrote 12001261 buffers (71.5%); 0 WAL file(s) added, 0 removed, 134 recycled; write=634.073 s, sync=0.143 s, total=634.376 s; sync files=507, longest=0.013 s, average=0.001 s; distance=140524105 kB, estimate=140524105 kB
2021-09-13 10:52:45.491 CST [45345] LOG: 00000: checkpoint complete: wrote 13057682 buffers (77.8%); 0 WAL file(s) added, 0 removed, 134 recycled; write=592.468 s, sync=0.082 s, total=592.687 s; sync files=494, longest=0.017 s, average=0.001 s; distance=140495935 kB, estimate=140521288 kB
2021-09-13 11:05:28.931 CST [45345] LOG: 00000: checkpoint complete: wrote 14160823 buffers (84.4%); 0 WAL file(s) added, 0 removed, 134 recycled; write=637.676 s, sync=0.168 s, total=637.992 s; sync files=503, longest=0.015 s, average=0.001 s; distance=140508621 kB, estimate=140520022 kB
2021-09-13 11:16:01.279 CST [45345] LOG: 00000: checkpoint complete: wrote 11613341 buffers (69.2%); 0 WAL file(s) added, 0 removed, 134 recycled; write=478.779 s, sync=0.142 s, total=479.045 s; sync files=494, longest=0.020 s, average=0.001 s; distance=140509977 kB, estimate=140519017 kB
2021-09-13 11:22:35.627 CST [45345] LOG: 00000: checkpoint complete: wrote 14112562 buffers (84.1%); 0 WAL file(s) added, 0 removed, 134 recycled; write=298.245 s, sync=0.122 s, total=298.497 s; sync files=508, longest=0.012 s, average=0.001 s; distance=140510117 kB, estimate=140518127 kB
2021-09-13 11:32:50.507 CST [45345] LOG: 00000: checkpoint complete: wrote 14223580 buffers (84.8%); 0 WAL file(s) added, 0 removed, 134 recycled; write=523.096 s, sync=0.166 s, total=523.390 s; sync files=509, longest=0.013 s, average=0.001 s; distance=140511042 kB, estimate=140517418 kB
2021-09-13 11:43:20.482 CST [45345] LOG: 00000: checkpoint complete: wrote 12823037 buffers (76.4%); 0 WAL file(s) added, 0 removed, 134 recycled; write=482.893 s, sync=0.158 s, total=483.197 s; sync files=505, longest=0.020 s, average=0.001 s; distance=140510602 kB, estimate=140516737 kB
2021-09-13 11:52:35.551 CST [45345] LOG: 00000: checkpoint complete: wrote 14344034 buffers (85.5%); 0 WAL file(s) added, 0 removed, 134 recycled; write=443.264 s, sync=0.124 s, total=443.517 s; sync files=520, longest=0.015 s, average=0.001 s; distance=140505375 kB, estimate=140515601 kB
2021-09-13 11:58:59.722 CST [45345] LOG: 00000: checkpoint complete: wrote 13197446 buffers (78.7%); 0 WAL file(s) added, 0 removed, 134 recycled; write=288.740 s, sync=0.100 s, total=288.964 s; sync files=528, longest=0.012 s, average=0.001 s; distance=140524172 kB, estimate=140524172 kB
2021-09-13 12:07:26.197 CST [45345] LOG: 00000: checkpoint complete: wrote 13393191 buffers (79.8%); 0 WAL file(s) added, 0 removed, 134 recycled; write=418.311 s, sync=0.096 s, total=418.556 s; sync files=533, longest=0.012 s, average=0.001 s; distance=140507649 kB, estimate=140522520 kB本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2021-09-11，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
