关于postgres数据checkpoints are occurring too frequently解决思路

今天日常巡检发现postgres数据库的后台日志有出现checkpoints are occurring too frequently (11 seconds apart)",,"Consider increasing the configuration parameter ""max_wal_size"".",,,,,,,""的提示，根据提示说明：检查点发生频率过高（间隔11秒）”，，“请考虑增加配置参数”“max_wal_size”“。",,,,,,,""，为什么会导致提示检查点发生频率，是什么原因导致的呢？我们带着疑问来讲解一下。

数据库日志：

2024-03-22 11:02:33.344 CST,,,107444,,65aaa43e.1a3b4,26278,,2024-01-20 00:33:02 CST,,0,LOG,00000,"checkpoints are occurring too frequently (11 seconds apart)",,"Consider increasing the configuration parameter ""max_wal_size"".",,,,,,,"","checkpointer"
2024-03-22 11:02:44.046 CST,,,107444,,65aaa43e.1a3b4,26279,,2024-01-20 00:33:02 CST,,0,LOG,00000,"checkpoints are occurring too frequently (11 seconds apart)",,"Consider increasing the configuration parameter ""max_wal_size"".",,,,,,,"","checkpointer"

检查点发生频率过高的原因

1.通常诱发checkpoint过快的原因是当max_wal_size较低且数据库具有大量更新或快速插入时，生成WAL的速度往往会比其归档快，并且超出标准检查点进程的速度导致。

2.还可能导致的原因是因为参数规划不合理导致，checkpoint_warning，max_wal_size，min_wal_size

系统测试

antdb(user:[antdb] 11:05:12)=# select name,setting from pg_settings where name like '%wal_size%' or name like '%checkpoint%' order by name;
-[ RECORD 1 ]-------------------------
name    | checkpoint_completion_target
setting | 0.9
-[ RECORD 2 ]-------------------------
name    | checkpoint_flush_after
setting | 32
-[ RECORD 3 ]-------------------------
name    | checkpoint_timeout
setting | 900
-[ RECORD 4 ]-------------------------
name    | checkpoint_warning
setting | 30
-[ RECORD 5 ]-------------------------
name    | log_checkpoints
setting | off
-[ RECORD 6 ]-------------------------
name    | max_wal_size
setting | 2048
-[ RECORD 7 ]-------------------------
name    | min_wal_size
setting | 2048

 参数说明：

checkpoint_completion_target：指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。 这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

checkpoint_flush_after：当执行检查点时写入的数据量超过此数量时，就尝试强制 OS 把这些写发送到底层存储。 这样做将会限制内核页面高速缓存中的脏数据数量，降低在检查点末尾发出fsync或者 OS 在后台大批量写回数据时被卡住的可能性。 那常常会导致大幅度压缩的事务延迟，但是也有一些情况（特别是负载超过shared_buffers但小于 OS 页面高速缓存）的性能会降低。 这种设置可能会在某些平台上没有效果。 如果指定值时没有单位，则以块为单位，即为BLCKSZ 字节，通常为8kB。 合法的范围在0（禁用强制写回）和2MB之间。Linux 上的默认值是256kB，其他平台上是0（如果BLCKSZ不是8kB，则默认值和最大值会按比例缩放到它）。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。

checkpoint_timeout：自动 WAL 检查点之间的最长时间。如果指定值时没有单位，则以秒为单位。 合理的范围在 30 秒到 1 天之间。默认是 5 分钟（5min）。增加这个参数的值会增加崩溃恢复所需的时间。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

checkpoint_warning：如果由于填充WAL段文件导致的检查点之间的间隔低于这个参数表示的时间量，那么就向服务器日志写一个消息（它建议增加max_wal_size的值）。 如果指定值时没有单位，则以秒为单位。默认值是 30 秒（30s）。零则关闭警告。如果checkpoint_timeout低于checkpoint_warning，则不会有警告产生。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

log_checkpoints：导致检查点和重启点被记录在服务器日志中。一些统计信息也被包括在日志消息中，包括写入缓冲区的数据和写它们所花的时间。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是关闭。

max_wal_size：在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制， 在特殊的情况下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。如果指定值时没有单位，则以兆字节为单位。默认为 1 GB。增加这个参数 可能导致崩溃恢复所需的时间。这个参数只能在postgresql.conf 或者服务器命令行中设置。

min_wal_size：只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL 文件总是 被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。 如果指定值时没有单位，则以兆字节为单位。默认是 80 MB。这个参数只能在postgresql.conf 或者服务器命令行中设置。

问题解决：

根据后台日志提示，要增加max_wal_size参数值；我们先从后台观察观察在报数据库2024-03-22 11:02:33.344 CST,,,107444,,65aaa43e.1a3b4,26278,,2024-01-20 00:33:02 CST,,0,LOG,00000,"checkpoints are occurring too frequently (11 seconds apart)",,"Consider increasing the configuration parameter ""max_wal_size"".",,,,,,,"","checkpointer"期间，数据库所在的系统IO很高，并且写操作比较大（这里系统分析IO工具可以根据自己喜好来监控），同时观察到短时间内数据库wal日志增量比较快；侧面说明目前数据库写比较繁忙，证明我们wal日志归档在没有触发checkpoint_warning参数值前就触发checkpoint动作，排除人为执行checkpoint触发因素外，那就要考虑max_wal_size参数设置问题，接下来我们要适当的调整数据库max_wal_size参数。

antdb=# select name,setting from pg_settings where name like '%wal_size%' or name like '%checkpoint%' order by name;
             name             | setting 
------------------------------+---------
 checkpoint_completion_target | 0.9
 checkpoint_flush_after       | 32
 checkpoint_timeout           | 900
 checkpoint_warning           | 30
 log_checkpoints              | off
 max_wal_size                 | 4096
 min_wal_size                 | 2048

max_wal_size特殊说明

max_wal_size设置自动检查点之间WAL日志可以增长的最大值。这是一个软限制。在特殊情况下，例如超负载、归档命令失败、或者wal_keep_segments设置太高，WAL大小可能会超过max_wal_size设定的值。

但也要注意，增加此参数可能会增加崩溃恢复所需的时间。默认值为1GB（1024 MB）。

PostgreSQL的默认配置值通常是保守的，因此在大型服务器上与在资源受限的小型开发机器上同样可以正常工作。因此，对于这里我们已经看到错误消息的系统，在此观察到的max_wal_size的默认值可能太低。

另外增加max_wal_size值，以匹配系统的工作负载，这样它可以容纳至少一个小时的日志。但是，这里需要注意的是，您不希望将此值设置得过高，因为它可能会增加崩溃恢复所需的时间。如果需要，还可以增加min_wal_size，这样系统就可以处理批处理作业和其他异常情况下wal使用量的峰值。在进行适当的配置更改并重新加载Postgres之后，我们可以验证是否应用了新设置，并时刻关注数据库后台日志和系统负载。