PostgreSQL调整检查点的基础

作者声明:本文是根据外文资料和作者的经验写的,如有错误请指出更正

PostgreSQL是依靠预写日志（WAL）的数据库之一,所有的更改首先被写入一个日志（一个变化的流）,然后再写入数据文件.这提高了数据的安全性,因为在崩溃的情况下,数据库会使用WAL执行恢复,从WAL读取更改并将其重新应用于数据文件.
虽然这可能会使写入量增加一倍，但实际上可能会提高性能,用户只需要等待WAL（刷新到磁盘）,而数据文件仅在内存中修改,然后在后台刷新. WAL写入本质上是顺序写入的，而对数据文件的写入往往是随机写入的.
非常频繁的检查点（比如几秒一次）.只保留少量的WAL，恢复速度非常快,但会将异步写入数据文件转换为同步数据文件,严重影响系统性能 （例如增加COMMIT延迟，降低吞吐量）.
因此在实践中,通常需要合理设置checkpoint_timeout和max_wal_size。

检查点可以触发的原因有：
1.直接执行CHECKPOINT命令
2.执行需要检查点的命令（例如pg_start_backup ， CREATE DATABASE或pg_ctl stop|restart等等）
3.达到检查点配置时间(checkpoint_timeout)
4.max_wal_size已满（"running out of WAL"或"filling WAL"）
其中1和2和配置无关的,需要手动触发的事件.本文主要配置3和4。

postgresql默认配置,默认值一般设置比较保守,以保证在大多数机器上可以运行。

    checkpoint_timeout = 5min
    max_wal_size = 1GB （PostgreSQL 9.5之前是checkpoint_segments ）

checkpoint_segments 和 max_wal_size之间的关系

    max_wal_size = (3 * checkpoint_segments) * 16MB 

注意:max_wal_size是总WAL大小的软限制,有两个后果.首先,数据库将尝试不超过它,但允许这样做,以便在分区上保留足够的可用空间并对其进行监视.其次,它不由"每个检查点"限制 ,而使用扩展检查点（稍后解释），WAL配额在2到3个检查点之间分配.因此使用max_wal_size数据库将在写入300-500 MB的WAL之后启动一个CHECKPOINT,具体取决于checkpoint_completion_target.

很难说checkpoint_timeout的"合理"值是什么,因为它取决于恢复时间（RTO）,即可接受的最大恢复间是多少。因为checkpoint_timeout是生成WAL所需的时间,而不是恢复时间.WAL通常由多个进程生成,而恢复由单个进程执行,这不仅影响本地恢复，还会影响到具有流复制的备机。 当恢复文件系统缓存时，通常会在重新引导之后立即进行恢复。但一般来说，默认值（5分钟）相当低,普遍采用30分钟到1小时之间,PostgreSQL 9.6甚至增加了最多1天,建议使用30分钟。

checkpoint_timeout=30min

现在需要估计数据库在30分钟内产生多少WAL,以便我们可以使用max_wal_size.有几种方法来确定生成多少WAL：
使用pg_current_xlog_insert_location()查看实际的WAL位置（基本上在文件中偏移pg_current_xlog_insert_location(),并计算每30分钟测量的位置之间的差异
启用log_checkpoints=on,然后从服务器日志中提取信息（每个完成的检查点将有详细的统计信息,包括WAL的数量）
使用pg_stat_bgwriter数据,其中还包括关于检查点数量的信息（可以结合当前的max_wal_size值的知识）

postgres=# SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3D/B4020A58
(1 row)

... after 5 minutes ...

postgres=# SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3E/2203E0F8
(1 row)

postgres=# SELECT pg_xlog_location_diff('3E/2203E0F8', '3D/B4020A58');
 pg_xlog_location_diff 
-----------------------
            1845614240
(1 row)

这表明在5分钟内,数据库产生了1.8GB的WAL,所以对于checkpoint_timeout = 30min,大约10GB(30/5*1.8G)的WAL.但是如前所述max_wal_size是多个进程生成,2- 3个检查点的配额，因此max_wal_size=30GB （3 x 10GB）,其他方法使用不同的数据来源，但原理是一样的。

checkpoint_completion_target
除checkpoint_timeout和max_wal_size外,还有一个参数叫做checkpoint_completion_target,但是要调整它,需要了解什么是“扩展检查点”的意思
在CHECKPOINT期间,数据库需要执行以下三个基本步骤：
1.识别共享缓冲区中的所有脏（修改）块
2.将所有这些缓冲区写入磁盘（或更改为文件系统缓存）
3.fsync()所有修改的文件写入磁盘
只有当所有这些步骤完成时,检查点才能被认为是完整的.您可以尽可能快地执行这些步骤,即一次性写入所有脏缓冲区,然后在文件上调用fsync,实际上这是PostgreSQL 8.2开始使用的.但是由于大量写入时文件系统的高速缓存饱和,会影响用户会话,导致I/O停顿.为了解决这个问题,PostgreSQL 8.3引入了“扩展检查点”的概念,而不是一次写入所有的数据,写入时间很长,这给了操作系统时间来刷新后台的脏数据,从而使降低fsync()调用成本.

根据下一个检查点的进度,写入被限制-数据库知道我们需要多少时间/WAL,直到需要另一个checkpoint,并且可以计算应该已经写出多少个缓冲区. 然而，数据库不得不直到最后才发出写入,这意味着最后一批写入仍将在文件系统缓存中，再次调用fsync()成本高昂（在启动下一个检查点之前发出）.
所以数据库需要给内核留有足够的时间,以便脏数据在后台被刷新到磁盘.而从页面缓存（Linux文件系统缓存）到期时间通常为系统内核参数,特别是这个内核参数：

vm.dirty_expire_centisecs = 3000

这表示数据在30秒后过期（默认情况下）
注意： 当涉及到内核参数时,需要调整vm.dirty_background_bytes.在具有大内存的系统上,默认值设高,允许内核在文件系统缓存累积的数据就越多.内核通常决定一次冲写入,从而减少了扩展检查点的好处。
返回到checkpoint_completion_target = 0.5这个配置参数说明所有的写入都要完成,到下一个检查点有多远.

checkpoint_completion_target = 0.5

例如,假设检查点仅通过checkpoint_timeout=5min触发,数据库将限制写入,以便最后一次写入在2.5分钟后完成.然后操作系统另外需要2.5分钟才能将数据刷新到磁盘,以便5分钟后调用fsync.
留给系统的2.5分钟可能看起来很多,考虑到超时时间(checkpoint_timeout)只有30秒.您可以将checkpoint_completion_target例如增加到0.85，这将留给系统大约45秒，比它需要的30秒多一点。这不建议,因为在密集写入的情况下,检查点可能比max_wal_size更快地触发,比5分钟之后,留给操作系统时间少于30秒。
然而,处理写入密集型工作负载的系统不太可能运行更高的checkpoint_timeouts值,默认的completion_target值显然太低.例如,超时设置为30分钟,它强制数据库在前15分钟（写入速率的两倍）中执行所有写入,然后在剩余的15分钟内空闲。
替代方案是可以尝试使用此公式大致设置checkpoint_completion_target

(checkpoint_timeout - 2min) / checkpoint_timeout

checkpoint_timeout=30分钟约为0.93.建议不要超过0.9,你不太可能观察到这两个值之间的显着差异.（当使用非常高的checkpoint_timeout值时,这可能会发生变化,现在在PostgreSQL 9.6上可以达到最多1天）.

概要
现在你应该知道检查点的目的是什么,也有了调整它们的基础.总结一下：
大多数检查点应该是基于时间的,即由checkpoint_timeout触发
性能（不频繁检查点）与恢复所需时间（频繁检查点）之间的妥协
值在15-30分钟之间是比例合适的,但到1小时不是什么坏事
在决定checkpoint_timeout后,通过估计WAL的数量选择max_wal_size
设置checkpoint_completion_target以便内核将数据刷新到磁盘的时间足够（但不是太多）
还需要调整内核参数vm.dirty_expire_centisecs,vm.dirty_background_bytes以防止内核在页面缓存中累积大量脏数据