一个大幅提高PostgreSQL性能的诀窍

开源软件联盟PostgreSQL分会 2021-10-29

1084

作者：杭州美创科技有限公司

什么是postgresql的full_page_writes

如果说一个参数的控制能够实现数据库性能的巨大提升，那对于整个业务系统来说都是质的飞跃，postgresql中就存在这样一个参数full_page_writes

postgresql官方文档中这样描述full_page_writes。当这个参数为打开时，PostgreSQL服务器在一个检查点之后的页面的第一次修改期间将每个页面的全部内容写到 WAL 中。这么做是因为在操作系统崩溃期间正在处理的一次页写入可能只有部分完成，从而导致在一个磁盘页面中混合有新旧数据。在崩溃后的恢复期间，通常存储在 WAL 中的行级改变数据不足以完全恢复这样一个页面。存储完整的页面映像可以保证页面被正确存储。

可以了解到full_page_writes这个选项参数就是为了避免在脏页在写入数据文件时意外掉电等造成数据库实例crash，最终导致partial write（部分写）的情况。

那么，我们首先不考虑数据文件损坏的情况下，关闭这个选项对数据库性能影响多大呢？

postgresql开启关闭full_page_writes对性能的影响

在同一个postgresql实例下，我们使用pg内置的pgbench工具进行压力测试，以此来对比full_page_writes开启前后的tps。该选项可以在配置文件postgresql.conf中修改，也可以通过数据库终端使用ALTER SYSTEM SET full_page_writes = [on/off];

1.打开full_page_writes（默认为on）

重启实例，并使用pgbench工具进行tps测试，由于资源限制，这里我们采用50个连接数，30分钟测试时间进行测试

$ /usr/local/pgsql-12.2/bin/pgbench -p 5432 -U pgbench -c 50 -T 1800 -r pgbench

2.关闭full_page_writes

一样也是重启实例，然后再进行tps的测试

$ /usr/local/pgsql-12.2/bin/pgbench -p 5432 -U pgbench -c 50 -T 1800 -r pgbench

测试结果如下图所示：

比较结果如下：

可以明显看到，在关闭full_page_writes的情况下，数据库tps提升了36%左右，因此在配置条件相同的情况下，数据库性能是可以得到良好的提升的

为什么开启与否会产生这么大的性能影响

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

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

因为WAL重放总是从一个检查点开始，所以打开full_page_writes的代价是增加了被写入wal的数据量。也就是wal的写放大问题。这样会增加额外的IO和磁盘消耗。因此为了减少全页写带来的影响，我们可以调整checkpoint的频率，即增加检查点间隔参数值。

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

mysql与postgresql处理部分写区别

对于部分写造成块块损坏的情况，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对于部分写没有对应的防范机制，如果有类似的情况发生，oracle可以用DG库采用RMAN的方式对损坏块所在的文件进行介质恢复，得益于oracle强大的检查点机制，以及oracle对硬件规格的高要求，出现诸如此类块损坏的情况可能性极小。