运维日记丨一个大幅提高PostgreSQL性能的诀窍(下)

接着上期讲到，为什么单单一个参数的开启与否就能够产生如此的性能影响，那我们首先需要了解到的是，PostgreSQL的WAL日志是怎么记录数据库操作的。

我们可以借助下面这张图先来了解一下脏页记录到WAL的过程。

T1时刻触发了checkpoint操作，紧接着在T2时刻开始将DML产生的操作刷入WAL buffer，直到T3时刻commit，DML的操作记录已经全部落盘在WAL日志中，然后开始将数据刷入数据文件中，若此时数据库crash后，并不会造成数据丢失的问题，由于PostgreSQL默认启用了full_page_writes，会将整个脏页记录到WAL日志中。

因为WAL重放总是从一个检查点开始，所以打开full_page_writes的代价是增加了被写入WAL的数据量。也就是WAL的写放大问题。这样会增加额外的IO和磁盘消耗。因此为了减少全页写带来的影响，除了关闭full_page_writes选项，我们还可以有以下对策：

（1）调整checkpoint的频率

即增加检查点间隔参数值来降低FPI（完整页面镜像）产生的频度。

（2）增加HOT_UPDATE比例

对于频繁更新操作的数据表，我们可以设置数据块填充因子，让表数据填充到一定程度（例如50%）后便开辟一个新的数据块空间，使得新老数据尽可能的都在同一个数据块内以减少wal的开销。

（3）启用WAL压缩

在PostgreSQL 9.5版本后增加了wal_compression参数，设为on会压缩写入到 WAL 中的完整页面镜像，可以减小 WAL 所占的空间且无需承受不可恢复的数据损坏风险， 但是代价是需要额外的 CPU 开销（用于解压缩）。

（4）硬件改善

如果硬件方面可以减少partial write的风险，或者文件系统支持原子写，亦或者在发生故障时打算用备份恢复，可以考虑将其关闭来获得性能的提升。

对于部分写造成块损坏的情况，MySQL使用的是innodb的doublewrite机制

如图所示，MySQL的双写机制是先将脏页复制到double write buffer中，然后double write buffer分两次，每次1M顺序地写入到共享表空间中，并且同步将脏页刷入磁盘，完成了double write操作后，开始将脏页数据刷入数据文件中。

对于恢复过程，如果在double write时发生了crash，此时数据文件仍是干净的，数据库实例重新启动后，直接用redo进行恢复，但如果是在写入数据文件时发生了crash，造成部分写，那可以首先使用double write的副本应用到数据文件对应的页中（Recovery Step1），然后在通过redo进行数据恢复（Recovery Step2）。

因为MySQL的binlog是逻辑日志，没有办法对页进行备份恢复，所以MySQL必须使用双写，但是PostgreSQL因为full_page_writes的特性是可以对页进行恢复，开启full_page_writes，就是对页的完整备份的实现。

Oracle对于部分写没有对应的防范机制，但并不是不会出现partial write的问题，如果有类似的情况发生，Oracle可以用DG库采用RMAN的方式对损坏块所在的文件进行介质恢复，得益于Oracle 本身的数据块的完整校验机制，写失败就直接回滚，甚至在Oracle 11gR2版本还有写数据文件发现IO异常直接crash 实例的特性。另外，Oracle对硬件规格的高要求，出现诸如此类块损坏的情况可能性极小。

最后，我们试想一下，假如在PostgreSQL中使用4k的数据页，就和文件系统拥有一样大小的数据块了，是不是就不需要full_page_writes了？2ndQuadrant编译了采用4k数据页的软件包。测试其带来的影响，会不会因为缩小了一半的数据页大小而减少一半的wal写入量。

同样地使用一张表为带主键的bigserial列，另一张表为普通表的uuid列。根据统计的测试数据来看，虽然没有减少50%的wal开销，但是wal的开销从140GB降到了90GB~100GB区间，因此是有着不错的性能表现的。

究其一些历史原因，我们不禁可以思考，full_page_writes是不是还有存在的必要，以及它存在的真正意义。