通过修改NUM_BUFFER_PARTITIONS提升PostgreSQL数据库的性能的一次测试

前言

中启乘数科技是一家专业的PostgreSQL和Greenplum数据库服务提供商，专注于极致的性能。一些复杂SQL出现很多buffer mapping的等待事件，中启乘数的工程师们通过修改NUM_BUFFER_PARTITIONS增加hash桶的个数看能否提升数据库的性能。

测试方法

测试的机器是2颗Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz，256GB的机器，数据都是在高速的SSD盘上。

数据库的版本是14.3，数据库的shared_buffers我们设置的是4GB。本次测试时数据库的块设置的时16k，即用以下编译选项编译的PostgreSQL数据库：

./configure --prefix=/home/postgres/pgsql-14.3-16k-256 --with-blocksize=16 --with-wal-blocksize=16

我们使用pgbench造测试数据：

pgbench -i -s 1000

这样小表pgbench_branches有1000条数据，而pgbench_accounts表有1亿条数据(表的大小大约为16G)。

同时把小表的pgbench_branches的主键删除掉，以便于更容易出来buffer mapping的等待事件：

alter table pgbench_branches drop constaint pgbench_branches_pkey;

测试的SQL脚本文件test3.sql

\set bid random(1, 1000)
SELECT a.* FROM pgbench_accounts a,pgbench_branches b where a.bid=b.bid and a.filler='bbb' and b.bid=:bid;

这条SQL的执行计划如下：

postgres=# explain analyze SELECT a.* FROM pgbench_accounts a,pgbench_branches b where a.bid=b.bid and a.filler='bbb' and b.bid=102;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.44..2888.49 rows=1 width=97) (actual time=27.442..27.443 rows=0 loops=1)
   ->  Index Scan using idx_pgbench_accounts_bid on pgbench_accounts a  (cost=0.44..2872.98 rows=1 width=97) (actual time=27.440..27.440 rows=0 loops=1)
         Index Cond: (bid = 102)
         Filter: (filler = 'bbb'::bpchar)
         Rows Removed by Filter: 100000
   ->  Seq Scan on pgbench_branches b  (cost=0.00..15.50 rows=1 width=4) (never executed)
         Filter: (bid = 102)
 Planning Time: 0.209 ms
 Execution Time: 27.470 ms
(9 rows)

测试命令为：

/home/postgres/pgsql-14/bin/pgbench -n -c {clients_nums} -f test3.sql -T 60 -P 2

测试的时候设置不同的并发压力，即改变“-c {clients_nums}”为不同的值的时候，看QPS的情况。

测试结果

测试结果如下表：

表格中的数据是QPS，即每秒查询数，并发数是按32、64、96、128、160、192、224、256得到的不同的QPS。

根据上述表格数据画折线图，如下：

从这个图中可以看出，改变NUM_BUFFER_PARTITIONS会提升一些性能。从理论上看，越好的CPU，把NUM_BUFFER_PARTITIONS设置的越大一些，可能效果会更明显一下，我这边的机器的配置是2颗Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz，256GB内存的机器上做的测试。有兴趣的同学可以在自己的机器上测试一下，看看与我们的这个测试结果有那些不同。

附：源码中修改NUM_BUFFER_PARTITIONS的方法

修改： src/include/storage/lwlock.h第83行：

#define NUM_BUFFER_PARTITIONS  128

可以把NUM_BUFFER_PARTITIONS设置成256、512、1024、2048、4096等不同的值。

还需要修改：src/backend/lib/dshash.c第61行：

#define DSHASH_NUM_PARTITIONS_LOG2 7

7是指2的7次方，刚好是 128，如果我们把改成8、9、10、11、12，分别对应的NUM_BUFFER_PARTITIONS是256、512、1024、2048、4096。