如何遏制PostgreSQL WAL的疯狂增长

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

1. 在checkpoint之后第一次修改页面，需要在WAL中输出整个page，即全页写(full page writes)。全页写的目的是防止在意外宕机时出现的数据块部分写导致数据库无法恢复。

2. 更新记录时如果新记录位置(ctid)发生变更，索引记录也要相应变更，这个变更也要记入WAL。更严重的是索引记录的变更又有可能导致索引页的全页写，进一步加剧了WAL写放大。

过量的WAL输出会对系统资源造成很大的消耗，因此需要进行适当的优化。

1. 磁盘IO
WAL写入是顺序写,通常再差的硬盘对付WAL的写入速度也是绰绰有余。所以一般可以忽略。
2. 网络IO
对局域网内的复制估计还不算问题，远程复制就难说了。
3. 磁盘空间
如果做WAL归档，需要的磁盘空间也是巨大的。
- full page image
如果是checkpoint之后第一次修改页面，则输出整个page的内容(即full page image，简称FPI)。但是page中没有数据的hole部分会被排除，如果设置了`wal_compression = on`还会对这page上的数据进行压缩。
- buffer data
不需要输出FPI时，就只输出page中指定的输出数据。
- full page image + buffer data
逻辑复制时，即使输出了FPI，也要指定的输出数据。
WAL的统计

PostgreSQL 9.5以后的pg_xlogdump都带有统计功能，可以查看不同类型的WAL记录的数量，大小以及FPI的比例。例子如下:
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



WAL的优化

在应用的写负载不变的情况下，减少WAL生成量主要有下面几种办法。
1、延长checkpoint时间间隔
FPI产生于checkpoint之后第一次变脏的page，在下次checkpoint到了之前，已经输出过PFI的page是不需要再次输出FPI。因此checkpoint时间间隔越长，FPI产生的频度会越低。增大checkpoint_timeout和max_wal_size可以延长checkpoint时间间隔。
2、增加HOT_UPDATE比例
普通的UPDATE经常需要更新2个数据块，并且可能还要更新索引page，这些又都有可能产生FPI。而HOT_UPDATE只修改1个数据块，需要写的WAL量也大大减少。
3、压缩
PostgreSQL9.5新增加了一个wal_compression参数，设为on可以对FPI进行压缩，削减WAL的大小。另外还可以在外部通过SSL/SSH的压缩功能减少主备间的通信流量，已经自定义归档脚本对归档的WAL进行压缩。
延长checkpoint时间

①首先优化checkpoint相关参数
postgres.conf:
shared_buffers = 32GB
checkpoint_completion_target = 0.1
checkpoint_timeout = 60min
min_wal_size = 4GB
max_wal_size = 64GB
wal_log_hints = on
wal_level = replica
wal_keep_segments = 1000



②增加HOT_UPDATE比例

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


设置fillfactor=90
postgres=# alter table pgbench_accounts set (fillfactor=90);
ALTER TABLE
postgres=# vacuum full pgbench_accounts;
VACUUM
postgres=# alter table pgbench_tellers set (fillfactor=90);
ALTER TABLE
postgres=# vacuum full pgbench_tellers;
VACUUM
postgres=# alter table pgbench_branches set (fillfactor=90);
ALTER TABLE
postgres=# vacuum full pgbench_branches;
VACUUM

③设置WAL压缩

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



仅仅调整wal_compression和fillfactor就削减了87%的WAL，这还没有算上延长checkpoint间隔带来的收益。
总结

PostgreSQL在未经优化的情况下，20倍甚至更高的WAL写放大是很常见的，适当的优化之后至少可以减少到3倍以下。引入SSL/SSH压缩或归档压缩等外部手段还可以进一步减少WAL的生成量。
如何判断是否需要优化WAL？

关于如何判断是否需要优化WAL，可以通过分析WAL，然后检查下面的条件，做一个粗略的判断：

FPI比例高于70%
HOT_UPDATE比例低于70%


以上仅仅是粗略的经验值，仅供参考。并且这个FPI比例可能不适用于低写负载的系统，低写负载的系统FPI比例一定非常高，但是，低写负载系统由于写操作很少，因此FPI比例即使高一点也没太大影响。
优化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资源进行压缩，但影响不大。

原文地址：   http://www.postgres.cn/index.php/news/viewone/1/273#comment