pg_wal日志增长迅速的原因及处理方法

原创福娃筱欢恩墨学院 2023-08-29

3687

1.背景

凌晨10分群里开发小伙伴反馈服务器磁盘爆满，经排查发现是数据库数据目录下，pg_wal目录占用过大导致。

2.pg_wal说明

pg_wal（Write-AHead Logging）
wal日志位置：
$PGDATA/pg_wal(pg10之前叫pg_xlog)

#
#wal日志文件命名规则：
#我们看到的wal日志是这样的：000000010000000100000092
#其中前8位:00000001表示timeline；
#中间8位：00000001表示logid；
#最后8位：00000092表示logseg

3.wal文件饱满的原因

3.1.归档配置不当

如果开启了归档，那么归档命令成功了，WAL 才会被 remove/recycle，所以需要注意，如果归档命令是失效的，那么 pg_wal 目录会一直增长，不会自动删除 WAL，此目录会持续增长，进而有撑爆磁盘的风险。
关闭或修改归档模式步骤如下：

--关闭归档
archive_mode=off
--修改/启用归档
archive_mode=on 
archive_command=/usr/bin/lz4 -q -z %p $BASEPATH/pg_archive/%f.lz4
archive_timeout=30min #建议调大 archive_timeout参数来降低 WAL文件的强制切换频率，WAL 产生过快的问题得以优化。
--重新加载配置文件
pg_ctl reload

3.2.WAL 参数

wal_segment_size：单个 WAL 文件的大小，默认为16MB，一般不做更改，且在 pg11 之前，只能在编译 pg 时指定，很明显这个参数的影响可以排除。
wal_keep_segments：这个参数配置的是 standby 复制所需的在 pg_wal 目录中最少保留的 WAL 文件的数目，一般情况下，这大概就是 wal 尺寸范围，如果这个参数配置为0，那么此时 WAL 文件的数量还取决于如下其他参数。PG13改为wal_keep_size
min_wal_size：这个是 WAL 文件保留的最小尺寸，当 WAL 的使用低于这个配置，那么 WAL 将会在 checkpoint 时候对 WAL 以 recycle 的形式处理，而不是直接 remove。当然这个参数也是有配置范围限制的：min_wal_size 至少是 wal_segment_size 的2倍。
max_wal_size：这个参数指定是 checkpoint 和 checkpoint 之间产生的 WAL 最大量。当然这只是一个软限制，在集群负载高或者一些其他异常情况下可能会超过这个值。结合 checkpoint_completion_target 参数，当新产生的 wal 文件到达特定的数据量后便会触发 checkpoint。
archive_timeout：如果 DB 实例写入量很少，到达 archive_timeout 会触发 WAL 文件的强制切换。因此如果 archive_timeout 如果太短就会产生很多新的 WAL，从而产生大量归档。

3.3.复制槽

在创建了复制槽后，如果通过复制槽不能正常消费掉，则会造成 WAL 堆积，导致主库的 WAL 文件数量一直增加，pg_wal 目录也会一直增长。

4.分析过程

4.1.归档和WAL参数检查

--查看参数
postgres=# show wal_keep_size;  #PG13版本wal_keep_segments改为了wal_keep_size
 wal_keep_size 
---------------
 0
(1 row)

postgres=# show min_wal_size;
 min_wal_size 
--------------
 8GB
(1 row)

postgres=# show max_wal_size;
 max_wal_size 
--------------
 32GB
(1 row)

postgres=# show wal_segment_size;
 wal_segment_size 
------------------
 16MB
postgres=# show checkpoint_completion_target;
 checkpoint_completion_target  #写的WAL的日志量超过: max_wal_size的1/3～1/2时，就会发生一次checkpoint。
------------------------------
 0.9
(1 row)

postgres=# select * from pg_stat_archiver;
 archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time |          stats_reset          
----------------+-------------------+--------------------+--------------+-----------------+------------------+-------------------------------
              0 |                   |                    |            0 |                 |                  | 2023-08-29 09:45:33.058559+08
(1 row)

4.2.复制槽检查

 
postgres=# select * from pg_replication_slots ;
         slot_name         | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 harbor_database_cluster_0 |        | physical  |        |          | f         | f      |      10586 |      |              | 0/D7000000  |
 harbor_database_cluster_2 |        | physical  |        |          | f         | f      |      10585 |      |              | 0/D7000000  |
(2 rows)

复制槽的激活状态"active = f"，需人工介入，可以确定其不是被某些进程正在使用的此复制槽。
查看 restart_lsn 对应的 WAL 文件名

postgres=# select pg_walfile_name('0/D7000000');
     pg_walfile_name
--------------------------
 0000001200000000000000D6
(1 row)

基本上可以判断 WAL 保留这么多是物理槽占用导致的。
确认下当前 wal_lsn 和上面开始堆积的 lsn 差异与现在的 pg_wal 尺寸是否吻合

postgres=# select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/D7000000'));
 pg_size_pretty
----------------
 0 bytes
(1 row)

总尺寸也对上了！

5.解决方法

5.1.手动清理归档文件(pg_wal)文件

pg_wal 目录的文件轻易不要人工处理，但归档目录需要定期将历史 WAL 备份走然后删掉，在磁盘紧张的情况下可以先将归档这部分处理掉。
继续观察，由于 failed_count 是0，且同时检查 DB log 没有任何异常信息，可以排除归档失败导致的 pg_wal 目录一直增加。
通过上述信息及分析可断定 WAL 堆积不是由于 wal_keep_segments 参数配置过大或归档失败导致，接下来重点分析下 pg_replication_slots。

#读取控制文件，找到哪个文件是可以被清理的
[postgres@Server ~]$ pg_controldata $PGDATA  
Latest checkpoint location:           7B/3E8F05C0
Latest checkpoint's REDO location:    7B/3E8EF7A0
Latest checkpoint's REDO WAL file:    000000010000007B0000003E  
  
# 表示000000010000007B0000003E  之前的pg_wal文件可以删除 (pg10以前的叫做pg_xlog)
[postgres@Server ~]$ pg_archivecleanup -d $PGDATA/pg_wal 000000010000007B0000003E
pg_archivecleanup: keep WAL file "/server/data/pgdb/data/pg_wal/000000010000007B0000003E" and later  
pg_archivecleanup: removing file "/server/data/pgdb/data/pg_wal/000000010000007B0000000D" 
......

5.2.复制槽删除

首先确认下这个复制槽是干什么用的，如果这个复制槽有用，那么把这些堆积的 WAL 消费掉就可以了，如果这个复制槽没啥用，那么把可以直接 drop 掉这个物理复制槽。

popostgres=# select pg_drop_replication_slot('harbor_database_cluster_0');
 pg_drop_replication_slot
 
-------------------------
 
(1 row)
popostgres=# select pg_drop_replication_slot('harbor_database_cluster_2');
 
 pg_drop_replication_slot
 
--------------------------
 
(1 row)

正常情况下，数据库在做 checkpoint 时或在数据库重启时会随着 recovery 的 checkpoint 而自动 remove/recycle。接下来，选择手动执行一个 checkpoint 或等待下一个系统自动的 checkpoint 后 pg_wal 目录的尺寸就会降下来。

postgres=# checkpoint;

6.如何遏制WAL疯狂增长

6.1.前言

在写入频繁的场景中，会产生大量的WAL日志，而且WAL日志量会远远超过实际更新的数据量。可以把这种现象叫做“WAL写放大”，造成WAL写放大的主要原因有2点。

在checkpoint之后第一次修改页面，需要在WAL中输出整个page，即全页写(full page writes)。全页写的目的是防止在意外宕机时出现的数据块部分写导致数据库无法恢复。
更新记录时如果新记录位置(ctid)发生变更，索引记录也要相应变更，这个变更也要记入WAL。更严重的是索引记录的变更又有可能导致索引页的全页写，进一步加剧了WAL写放大。
过量的WAL输出会对系统资源造成很大的消耗，因此需要进行适当的优化。

6.2.优化方式

PostgreSQL在未经优化的情况下，WAL写放大是很常见的，引入SSL/SSH压缩或归档压缩等外部手段还可以进一步减少WAL的生成量。

6.3.如何判断是否需要优化WAL？

关于如何判断是否需要优化WAL，可以通过分析WAL，然后检查下面的条件，做一个粗略的判断：
FPI比例高于70%
HOT_UPDATE比例低于70%
以上仅仅是粗略的经验值，仅供参考。并且这个FPI比例可能不适用于低写负载的系统，低写负载的系统FPI比例一定非常高，但是，低写负载系统由于写操作很少，因此FPI比例即使高一点也没太大影响。

6.4.WAL的优化

在应用的写负载不变的情况下，减少WAL生成量主要有下面几种办法。

延长checkpoint时间间隔

FPI产生于checkpoint之后第一次变脏的page，在下次checkpoint到了之前，已经输出过PFI的page是不需要再次输出FPI。因此checkpoint时间间隔越长，FPI产生的频度会越低。增大checkpoint_timeout和max_wal_size可以延长checkpoint时间间隔。

增加HOT_UPDATE比例

普通的UPDATE经常需要更新2个数据块，并且可能还要更新索引page，这些又都有可能产生FPI。而HOT_UPDATE只修改1个数据块，需要写的WAL量也大大减少。

压缩

PostgreSQL9.5新增加了一个wal_compression参数，设为on可以对FPI进行压缩，削减WAL的大小。另外还可以在外部通过SSL/SSH的压缩功能减少主备间的通信流量，已经自定义归档脚本对归档的WAL进行压缩。

6.4.1.延长checkpoint时间

优化checkpoint相关参数
postgres.conf:
shared_buffers = 32GB
checkpoint_completion_target = 0.9  #0-1，越大，写入磁盘速度就低；
checkpoint_timeout = 60min
min_wal_size = 4GB
max_wal_size = 64GB
wal_log_hints = on
wal_level = replica
wal_keep_segments = 1000

6.4.2.增加HOT_UPDATE比例

HOT_UPDATE比例过低的一个很常见的原因是更新频繁的表的fillfactor设置不恰当。fillfactor的默认值为100%，可以先将其调整为90%。
对于宽表，要进一步减小fillfactor使得至少可以保留一个tuple的空闲空间。可以查询pg_class系统表估算平均tuple大小，并算出合理的fillfactor值。

postgres=# select 1 - relpages/reltuples max_fillfactor from pg_class where relname='big_tb';
    max_fillfactor    
----------------------
 0.69799901185770750988
(1 row)

再上面估算出的69%的基础上，可以把fillfactor再稍微设小一点，比如设成65% 。

6.4.3.设置WAL压缩

修改postgres.conf，开启WAL压缩
wal_compression = on

参考链接：https://blog.csdn.net/shipeng1022/article/details/108734662

6.5.优化WAL的副作用

3种优化手段，如果设置不当，也会产生副作用，具体如下：

延长checkpoint时间间隔

导致crash恢复时间变长。crash恢复时需要回放的WAL日志量一般小于max_wal_size的一半，WAL回放速度(wal_compression=on时)一般是50MB/s~150MB/s之间。可以根据可容忍的最大crash恢复时间，估算出允许的max_wal_size的最大值。

调整fillfactor

过小的设置会浪费存储空间，这个不难理解。另外，对于频繁更新的表，即使把fillfactor设成100%，每个page里还是要一部分空间被dead tuple占据，不会比设置成一个合适的稍小的fillfactor更节省空间。

设置wal_compression=on

需要额外占用CPU资源进行压缩，但影响不大。

6.6.postgres.conf配置参考

--未经特别优化的配置
shared_buffers = 32GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 5min
min_wal_size = 1GB
max_wal_size = 4GB
wal_log_hints = on
wal_level = replica
wal_keep_segments = 1000

--优化后的配置
shared_buffers = 32GB
checkpoint_completion_target = 1 #和checkpoint_timeout(checkpoint_timeout默认值为5min)配合使用。
checkpoint_timeout = 60min
min_wal_size = 4GB
max_wal_size = 64GB
wal_log_hints = on
wal_level = replica
wal_keep_segments = 1000
wal_compression = on

注意：实际的生产环境，机器所能支撑的写入速度为500M/s-1200M/s，通过数据写入速度来看“性能”的话，在checkpoint_completion_target设置的越高的情况下，写入速度越低，对客户而言，体验越好，性能越高。反之，较低的值可能会引起I/O峰值，导致“卡死”的现象。

checkpoint_completion_target是postgresql数据库中一个至关重要的参数，主要与
参数checkpoint_timeout(checkpoint_timeout默认值为5min)配合使用。

举个简单的例子来说：

情况一：
checkpoint_completion_target=0.5
checkpoint_timeout = 5min
100G数据（需要刷进磁盘的数据量）
1G/s
100/（0.5*5*60）*1024≈670M/s  （数据写入速度）

情况二：
checkpoint_completion_target=0.9
checkpoint_timeout = 5min
100G数据（需要刷进磁盘的数据量）
1G/s
100/（0.5*5*60）*1024≈380M/s  （数据写入速度）

7.总结

几乎没有业务的数据库 WAL 量也会增加是因为周期性强制切换 WAL，如果想要 WAL 切换速度降低，可以适当调大 archive_timeout，比如从1min 增大为30min 甚至更长时间
DBMS 有参数控制 WAL 的增长幅度和总尺寸，但是很多都是软限制，而非强制。出现异常的情况下，pg_wal 确实是会无限膨胀下去将磁盘撑爆，DBMS 无法自动处理，需要人工介入。
产生 WAL 堆积在排除参数配置且归档成功的情况下，是因为物理复制槽占用导致。可见在此极端情况下，WAL 文件确实会一直堆积，进而出现占满磁盘的情况，导致 DB 实例异常 shutdown 而产生故障。
DBMS 正常情况下是可以对 WAL 进行自动 remove/recycle，如果出现 WAL 清理不掉的情况，且参数无可疑之处，多半是其他原因引起的，需要仔细分析原因，切忌“头痛医头脚痛医脚”。比如此例中是因为废弃的物理复制槽导致，其实废弃的逻辑复制槽亦可导致此问题。
最后强调: 任何时候不要手工删除$PGDATA/pg_wal目录下的WAL日志文件。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

PostgreSQL故障案例（共6篇）

PostgreSQL故障案例