PostgreSQL checkpoint 相关参数优化设置与解释
背景
数据库的检查点相关参数如何配置，能让数据库运行更加顺滑？
内存、磁盘能力如何搭配，能发挥更好的性能？
OS的参数如何设置，能尽可能的降低大内存机器带来的IO风暴问题？
数据库检查点设置
假设服务器内存是512GB。磁盘顺序、随机写吞吐是2 GB/s。
1、经验值
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
这里还没有考虑磁盘的读写能力。
因此可能经验值配置的参数会导致一些性能问题。
我们根据上面的设置，来计算一下数据库发生检查点时到底会产生多少IO。
1、以极端为例进行讲解。假设shared buffer所有页都是脏页。
那么有128 GB脏页。
另外两个参数控制平滑刷脏页的调度：
2、30分钟*0.9 的时间窗口内，要刷完所有脏页。也就是说超过时间后，CKPT进程会全速刷脏页，checkpoint进程不进行任何sleep。
3、在新产生 256GB * 0.9 的WAL一个周期内，要刷完所有脏页。也就是检查点开始后，产生的WAL如果超过了，CKPT进程会全速刷脏页，checkpoint进程不进行任何sleep。
开销计算
1、刷脏页开销，属于离散IO。
平均值: 128GB / (30600.9) = 80.9 MB/s
（实际是sleep调度的，峰值可能还会更高一些） 。 物理写。
2、业务产生的写WAL开销，属于顺序IO。
最大平均值: 256GB / (30*60) = 145.6 MB/s
（实际是sleep调度的，峰值可能还会更高一些） 。 物理写。
3、业务上写操作除了产生WAL，还有bgwriter（异步WRITER） 。 异步写。
最大平均值: 256GB / (30*60) = 145.6 MB/s (因为一个块可能被多次修改，但是writer此时可能更少) 。
4、操作系统merge IO（写bgwriter的DIRTY PAGE），离散大块IO。 物理写。
最大平均值: 256GB / (30*60) = 145.6 MB/s
因此最糟糕的情况下，以上述配置为例，检查点期间，数据库写操作吞吐均值可能是 80.9 MB/s + 145.6 MB/s + 145.6 MB/s = 372.1 MB/s。
这个值，结合磁盘能力，可以判断检查点是否会有磁盘瓶颈。
建议设置
由于业务上还需要读写磁盘，同时PG目前的版本还需要垃圾回收，FREEZE等会产生IO的操作。因此不能让计算得到的峰值与磁盘实际IO能力相当，应该有所保留。
1、磁盘IOPS指标，写吞吐。(假设给25%用作 刷脏离散写、WAL顺序写)
2、如果按经验参数，评估出来磁盘能力不足（检查点实际需要的IO能力，与磁盘厂商给出的IO能力的25%不匹配），怎么办？
首先调大checkpoint_timeout参数，如果到最大值（1 DAY），依旧无法满足，则需要降低shared_buffers，以及相应的max_wal_size。
3、建议设置 log_checkpoints=on ， 可以评估checkpoint的统计信息，用于帮助修正以上参数。
操作系统刷脏页设置
大内存机器，LINUX可能会遇到IO HANG的问题，原因也是刷脏页的配置不正确。
LINUX也有刷脏页的内核配置，默认是一个百分比，10%的脏页，后台进程开始刷脏页。如果产生脏页过快，到达20%时，用户进程也会帮助刷脏页。
因此如果内存越大，这个阈值就越大，而如果磁盘能力没有跟上，可能一次性会刷几十GB的脏页，导致磁盘的IO能力打爆，影响正常业务。
相关参数
1、os 内核参数
vm.dirty_background_bytes = 409600000
vm.dirty_background_ratio = 0
vm.dirty_bytes = 0
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 95
vm.dirty_writeback_centisecs = 100
2、数据库参数
shared_buffers = 8GB # min 128kB
# (change requires restart)
# - Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables
小结
为了尽量的降低数据库检查点带来的IO突增，影响业务。应该根据磁盘IO能力来修正检查点设置的经验值，避免IO HANG的问题。
同时OS层面的道理也一样，要设置好对应的OS刷脏页的调度参数。
参考
《PostgreSQL 9.6 检查点柔性优化(SYNC_FILE_RANGE) - 在单机多实例下的IO Hang问题浅析与优化》