七的博客

PostgreSQL 数据库日志出现 checkpoints are occurring too frequently

PostgreSQL调优

PostgreSQL 数据库日志出现 checkpoints are occurring too frequently

1. 背景

最近有同事反馈在项目压测环境发现数据库服务器的 IO 一直特别高，需要上去排查分析下问题。

于是我立马上服务器上通过 iotop 命令查看进程的读写情况，但是却发现 PostgreSQL 的进程都是 IDLE 状态，但是很奇怪的是， IDLE 状态的进程还一直在写磁盘。于是想着去日志里面看看是不是有什么线索，果不其然在日志中看到如下内容：

2024-04-28 10:08:07.618 CST [1647] LOG:  checkpoints are occurring too frequently (25 seconds apart)
2024-04-28 10:08:07.618 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:09:07.314 CST [1647] LOG:  checkpoints are occurring too frequently (29 seconds apart)
2024-04-28 10:09:07.314 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:10:05.923 CST [1647] LOG:  checkpoints are occurring too frequently (28 seconds apart)
2024-04-28 10:10:05.923 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:11:05.184 CST [1647] LOG:  checkpoints are occurring too frequently (29 seconds apart)
2024-04-28 10:11:05.184 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:11:34.618 CST [1647] LOG:  checkpoints are occurring too frequently (29 seconds apart)
2024-04-28 10:11:34.618 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:12:02.580 CST [1647] LOG:  checkpoints are occurring too frequently (28 seconds apart)
2024-04-28 10:12:02.580 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".
2024-04-28 10:12:31.648 CST [1647] LOG:  checkpoints are occurring too frequently (29 seconds apart)
2024-04-28 10:12:31.648 CST [1647] HINT:  Consider increasing the configuration parameter "max_wal_size".

2. 分析

从上面的日志中，可以得到的几点问题：

  • 检查点频繁发生，日志中多次检查点 (checkpoints) 发生的间隔非常短（大约 25 到 29 秒）。在 PostgreSQL 中，检查点是数据库定期将内存中的数据（ 也就是我们俗称的 脏页 ）和 事务日志同步到磁盘上的过程。

  • 数据库提示我们增加 max_wal_size 这个参数的值。

HINT: Consider increasing the configuration parameter “max_wal_size”.

我们的应用是一个物联网设备相关的项目，设备会定时上报数据，然后系统会高频繁地对数据库进行插入更新的操作。 根据上面的提示信息第一时间猜测是数据库参数可能配置的不太对，导致检查点被频繁触发。查看数据库 postgresql.conf 中的配置如下：

#wal_sync_method = fsync                # the default is the first option
                                        # supported by the operating system:
                                        #   open_datasync
                                        #   fdatasync (default on Linux and FreeBSD)
                                        #   fsync
                                        #   fsync_writethrough
                                        #   open_sync
#full_page_writes = on                  # recover from partial page writes
#wal_compression = off                  # enable compression of full-page writes
#wal_log_hints = off                    # also do full page writes of non-critical updates
                                        # (change requires restart)
#wal_buffers = -1                       # min 32kB, -1 sets based on shared_buffers  # WAL 日志的缓冲区大小
                                        # (change requires restart)
#wal_writer_delay = 200ms               # 1-10000 milliseconds
#wal_writer_flush_after = 1MB           # measured in pages, 0 disables

#commit_delay = 0                       # range 0-100000, in microseconds
#commit_siblings = 5                    # range 1-1000

# - Checkpoints -

#checkpoint_timeout = 5min              # range 30s-1d
max_wal_size = 1GB   # 最小的WAL大小
min_wal_size = 80MB  # 最大的WAL大小
#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

可以看到 wal 相关的参数大部分都是用的系统默认的，只有 max_wal_size 、以及 min_wal_size 两个值是显示指定了参数值。 对于我们这种高频批量插入以及批量更新数据的业务场景，我们可以尝试对参数进行如下调整：

  • max_wal_size 参数调整成 8 GB 或更高， 这个参数限制了触发检查点之前可以积累的 WAL 数据的最大量。如果 WAL 增长到这个尺寸，就会触发检查点。我们的业务场景中，很快就可以达到默认的最大阈值，pg_wal目录的大小会急剧增加。 增加此值可以减少检查点的频率，从而提高系统性能。这里需要注意的是，这会增加数据库崩溃后恢复的时间。

  • checkpoint_completion_target 参数调整成 0.9 ，从配置文件中可以看出默认是 0.5 ，这意味着 PostgreSQL 会尝试在两次 checkpoint_timeout 时间段的一半时间内完成写入磁盘的操作。 在我们这种业务场景下，设置成 0.5 这个值太小了，数据库就会快速写入磁盘，会影响正常的业务功能执行。这个值可以设置成 0.7 - 0.9 。这个值调整后的问题是如果设置的太高，可以减少检查点期间的 IO 峰值，但是在检查点完成前如果发生故障，未同步的数据会更多，可能会影响数据的安全。

  • wal_compression 这个参数我尝试调整成 on ，但是效果不太明显。

修改完参数后，重启系统，观察 Grafana 中的监控可以明显看到 IO 降下去。

3. 术语解释

3.1 WAL

WAL 是 Write-Ahead Logging 的缩写，在修改数据库内容之前，先将这些修改记录到一个日志中。在关系型数据库中，基本都有它的身影。

通过这种机制可以确保即使在发生故障的情况下，所有已提交的事务也都可以从这些日志中恢复。

WAL 的流程大致如下：

开始
|
V---> 事务执行，开始写入数据
|
V---> WAL (Write-Ahead Logging) 文件增长
|     |
|     |---> WAL 文件大小是否超过 max_wal_size?
|            |              |
|            | No           | Yes
|            V              V
|         继续写入         触发检查点
|                          (Checkpoint)
|                          |
|                          V
|         检查点是否由 max_wal_size 触发?
|            | No           | Yes
|            V              V
|         继续操作         检查点完成后，WAL 文件大小减少
|                          |
|                          V
|         checkpoint_timeout 是否到达?
|            | No           | Yes
|            V              V
|         继续操作         触发定时检查点
|                          (由 timeout 控制)
|                          |
|                          V
|         检查点完成后，WAL 文件大小减少
|                          
V---> 事务持续进行，重复上述过程
|
结束

3.2 CheckPoint 检查点

在 PostgreSQL 数据库系统中，检查点就像是给数据库的数据做一个“快照”，保存当前的状态。当数据库运行时，很多数据最初只是暂时存储在内存中，这让数据处理得更快。但如果这时候电脑突然断电或者出现故障，那么内存中的数据就会丢失。为了防止这种情况，数据库会定期地把内存中的数据写入到硬盘上，这个过程就是一个检查点。

检查点可以由以下几种情况触发： 1. 时间间隔：由 checkpoint_timeout 参数控制，这是两个检查点之间的最大时间间隔。 2. WAL 文件大小：由 max_wal_size 参数控制，当 WAL 文件增长到一定大小时，触发检查点以避免 WAL 文件过大。 3. 服务器关闭：当数据库服务器正常关闭时，会自动执行检查点操作，以确保所有数据都安全地写入硬盘。 4. 手动触发：管理员可以手动触发检查点，例如通过 SQL 命令 CHECKPOINT;。

在检查点期间，系统会执行以下步骤：

  1. 标记检查点开始：系统记录当前的事务日志位置，这标志着检查点的开始。
  2. 刷新脏页：将所有修改过但尚未写入硬盘的数据页（脏页）写入到硬盘中。这确保了数据的持久性。
  3. 日志切割：完成数据刷新后，系统可能会执行 WAL 日志的切割，将旧的 WAL 文件归档或删除，释放空间供新的事务日志使用。
  4. 标记检查点完成：记录检查点完成的日志位置，这意味着所有之前的数据变更都已经安全地记录在硬盘上。

合理配置 max_wal_size 和 checkpoint_timeout 参数，以平衡系统性能和数据安全性是一个挑战。

4. 参考链接

◀ 上一篇
Kafka快速入门6-Kafka消费者实践
下一篇 ▶
一次Linux切换用户命令引发的思考