在PG数据库中 shared_buffers会影响DROP TABLE的性能吗-51CTO.COM

前阵子一个朋友和我讨论一个PG性能问题，他们最近把几个小的PG数据库整合为一个大系统，换了台新服务器，搞了超豪华配置，有512GB的物理内存。他们配置了一个128GB的SHARED_BUFFERS，然后应用就出问题了。因为这套系统中经常要用到临时表，他们的临时表都是物理表，一般是create/insert/select/drop，一串操作。系统升级后，系统就变得特别慢了，经过分析，发现主要问题出在drop table上。在一顿乱试中，他们恢复了shared_buffers原来的配置后，系统性能居然恢复了！

在PG数据库中，过大的shared_buffers会影响DROP TABLE/TRUNCATE TABLE等的性能吗？这个问题乍一听好像天方夜谭，不过细想起来确实是有道理的。按理说PG数据库DROP TABLE直接删除文件系统中的文件，修改数据字典就OK了，应该比Oracle还更利索一些，不应该很慢。而DROP TABLE和SHARED BUFFERS的大小有关就更离谱了。

我在cybertec上找到了一篇Hans-Jürgen Schönig的博客，提供了一个测试用例，在我们实验室的一套PG 12.6上做了一个测试，还真的挺有意思的。先介绍一下这个测试用例。这个测试用例分为run.sql和tps.sh两个脚本，通过pgbench工具对DROP TABLE做了一个测试。先看run.sql的代码：

SET synchronous_commit TO off;
BEGIN;
CREATE TABLE x(id int);
INSERT INTO x VALUES (1);
DROP TABLE x;
COMMIT;

关闭同步提交的目的是为了尽可能降低IO性能对测试的影响。然后执行一个建表，写数据，删除表这个操作。tps.sh的代码如下：

#/bin/sh


for x in '8 MB' '32 MB' '128 MB' '1 GB' '8 GB'
do
      pg_ctl  -l /dev/null -o "--shared_buffers='$x'" start
      sleep 1
      echo tps for $x
      psql -c "SHOW shared_buffers"
      pgbench --file=run.sql -j 1 -c 1 -T 10  2> /dev/null
      pg_ctl -D /tmp/db stop
      sleep 1
done

根据不同的测试环境，大家需要对tps.sh做一些修改，比如psql和pgbench的参数。我做了一些调整。测试了一下。

图片

可以看出，随着shared_buffers的加大，TPS出现了明显的下降，最后居然降到了不足200。在本次测试中，系统中有足够的物理内存，确保系统不会出现SWAP的情况。在这种情况下，还是出现了此类情况，这是什么原因呢？

问题一般不会出在其他地方，我这个测试是单独进行的，也没有其他会话干扰，也不会有锁的问题。唯一出问题的地方是在shared_buffers上了。当drop table完成后，需要做一个类似checkpoint的工作，把shared_buffers中和这个表相关的所有buffer全部清理掉。正是这个工作拖慢了drop table的性能。

我不知道一些老的Oracle DBA还有没有这方面的印象，以前运维Oracle 9i或者更早版本的数据库的时候，有些时候truncate操作会特别慢，只要在等待object reuse。实际上就是在等待对象级的CKPT完成。为了解决这个问题，我们会用truncate table keep storage语法。为了解决这个问题，Oracle 也是折腾了很久的。

在8.0和7.3版本，Oracle一共设置了3条链：LRU链、LRU-W链和LRU-P链。LRU链是我们传统所说的replacement list,用于BUFFER的LRU替代，LRU-W是需要DBWR写入数据文件的链，LRU-P是当前正在写入的链表，当时所有的BUFFER都被PIN住，等写入完成后会降低锁定级别，并被重新链入LRU。8I开始引入了5条链：LRU、LRU-W、LRU-P，LRU-XO、LRU-XR。其中前面三条含义没变，LRU-XO是重用对象链，当实例发布需要重用某个对象的时候（比如TRUNCATE等DDL操作后），这个对象的所有当前块和脏块被链入这个队列，DBWR需要将脏块写入文件。CKPT会检查这个队列，当这个队列变空的时候认为本次对象级的CKPT结束。LRU-XR主要用于对某个数据文件或者表空间进行CKPT，把一个范围的脏块链入该链，让DBWR去写盘，CKPT检查这个链表，直到链表为空。

Oracle 9.2发现这样设计后，object reuse的性能还是无法彻底改善，甚至性能更差了，所以把LRU-XO链去掉了，5条链变又成了4条链。直到Oracle 10g这个才被彻底解决。OBJECT REUSE的算法再次改进，在WORKING SET中引入了Object hash chain结构kcbohtab * htabkcbwds，从而把一条LRU-XO链变成了一个HASH TABLE加N条链，每个对象有自己独立的OBJECT REUSE链表。这样对于一个对象的OBJECT REUSE操作就没有任何性能问题了。

图片

上面是11.1.0.7版本的Oracle的kcbwds结构，黄色部分就是我说的object queue hash table。在没有这个hash table的时候，如果要把某个drop/truncate的对象相关的cache清理出去的时候，就需要遍历shared_buffers，这样情况下，缓冲区越大性能就越差。如果有了这个hash table，那么这个操作的性能就只与该对象在缓冲区中的缓冲块的数量有关了。

实际上，对于这个问题，2015年的时候，就有人写了Patch，只不过当时PG社区的大多数大佬认为超大内存数据库系统只是小众场景，频繁做drop/truncate的系统也不多，因此没必要在这方面做优化。

图片

我围观了他们的讨论，因为大多数大佬都认为场景十分有限，没必要去优化这个操作，这件事就不了了之了。实际上在这件事上，这些PG大佬可能真的有点自负了，使用临时表（不论是全局临时表还是物理表充当临时表，这方面的操作模式是类似的）在一些复杂的系统，比如ERP、财务、供应链管理、审计、工业流程管理等是很常见的操作。频繁创建删除截断表对象也是很常见的操作。因此这个场景的优化还是十分必要的。

要想实现对此的优化其实也不难，因为PG没有使用类似Oracle的表空间管理机制，因此不存在Object Reuse的问题，只需要不写盘淘汰这些对象的shared buffers缓冲就可以了。这个操作完全可以做成延时的，backend删除表后直接结束操作，淘汰脏块的事情由CKPT后续延时完成就可以了。当某个对象被删除时，只要记录一下清单，当CKPT发现某个脏块属于这个对象时，直接抛弃就可以了。这个清单最好能写入WAL，这样系统故障恢复时可以比较快速地抛弃这些脏数据，如果WAL中没有这些数据，也没关系，只是效率低一点而已。

其实数据库就是这样一点点的在用户场景中完善起来的，商用数据库厂商十分注重收集这方面的需求，而开源数据库因为用户群体十分庞大，需求更为庞大，因此在实现某些功能的时候，选择比较困难，无法像商用数据库那么迅速的响应。