在大内存配置下大量使用 DROP TABLE，系统性能会下降吗？

问题描述

如果数据库服务器在大内存配置下，大量使用 DROP TABLE，系统性能会下降吗？

问题解答

在大内存配置下大量使用 DROP TABLE，系统性能不会有明显的下降。

Redrock Postgres 在删除或截断一个表时，不会立即删除与该表关联的物理文件和回收占用空间。数据库会重命名表，并将其和任何关联对象放置到回收站中。

Redrock Postgres 定期自动清理回收站中的对象，它会遍历共享缓冲池中缓存的数据页面，丢弃那些需要清理的表上面的缓存。

问题验证

用于pgbench的测试脚本

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

使用一系列的shared_buffers设置，进行反复测试。

#!/bin/sh

DB=postgres

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

这是一个直接用 initdb 初始化的数据库，没有做任何参数调整，以下是测试结果：

$ ./test.sh | grep tps
tps for 8 MB
tps = 1677.356617 (including connections establishing)
tps = 1677.639309 (excluding connections establishing)
tps for 32 MB
tps = 1822.183495 (including connections establishing)
tps = 1822.540181 (excluding connections establishing)
tps for 128 MB
tps = 1822.571589 (including connections establishing)
tps = 1823.012897 (excluding connections establishing)
tps for 1 GB
tps = 1580.872282 (including connections establishing)
tps = 1581.187091 (excluding connections establishing)
tps for 8 GB
tps = 1398.936176 (including connections establishing)
tps = 1399.232128 (excluding connections establishing)

通过将 8GB 的测试放在中间，将顺序打乱，再次测试的结果：

$ ./test.sh | grep tps
tps for 8 MB
tps = 1806.202465 (including connections establishing)
tps = 1806.542239 (excluding connections establishing)
tps for 32 MB
tps = 1816.756097 (including connections establishing)
tps = 1817.082547 (excluding connections establishing)
tps for 8GB
tps = 1688.839596 (including connections establishing)
tps = 1689.206157 (excluding connections establishing)
tps for 128 MB
tps = 1639.884082 (including connections establishing)
tps = 1640.174407 (excluding connections establishing)
tps for 1 GB
tps = 1559.705001 (including connections establishing)
tps = 1560.020075 (excluding connections establishing)

上面的结果波动，并没有看到 TPS 结果和shared_buffers的直接关系。平时做正常的性能测试，也会有类似的波动，主要都是和检查点、后台 IO 相关配置有关。