PostgreSQL 超越百万 tpmc

作者：胡森

导语

随着 PostgreSQL9.6大版本的 release，许多新的特性也横空出世，其中不乏一些让众多用户期待已久的特性，如同步复制(多同步备机)，FDW 支持 remote joins、sorts，并行查询更是千呼万唤始出来。除了新的特性之外，PG9.6还对性能以及锁做了很多优化，这些优化在大并发 oltp 的场景表现尤为突出。

1、并行查询(olap)

PG9.6 的并行查询是通过在执行过程中，根据并行执行计划，启动多个 worker(多进程) 同时计算，并将结果汇总的方式提升查询性能。

目前支持的并行算子有：seqscan, nestloop join, hash join 以及简单的 aggregation。

虽然支持了并行，但是也并不是所有查询都会并行。并行需要满足一个最基本的条件，就是表大小一定要大于 min_parallel_relation_size（全局参数，可设置），这样才有可能进行并行查询。

满足了最基本的条件之后，就需要根据表大小以及 min_parallel_relation_size 计算出并行查询过程中需要的 worker 个数：

然后生成并行的可执行路径，并计算相关的代价。可执行路径的代价通常由三部分组成：启动代价，cpu 代价，io 代价。非并行的执行路径代价：

并行执行由于会启动多个 worker 进行计算，所以在 cpu 上的消耗会由多个 worker 进行均摊；但是由于需要启动额外进程，所以也会需要额外的代价，并行执行路径的代价：

优化器会对各个可执行路径的代价进行比较，选择代价最小的路径；也就是说，虽然生成了并行的可执行路径，但是如果代价大于非并行的路径，也不会走并行。

以 seqscan 为例，看一下并行查询的流程：根据并行查询 worker 的个数对表进行划分（按页划分），每一个 worker 负责自己的那部分数据页，读取数据并进行条件判断，将符合结果的数据放到一个共享队列中，由一个 gather node 进行数据的收集与返回。

PG9.6 的并行到底会对性能带来多大的提升呢？

这里使用了业界 OLAP 标准 TPC-H 进行了相关测试，测试的机型为 Z3，PG 代码为 9.6.1，TPC-H 数据量为 10G：

从测试结果可以看出，对某些 sql，如 1/4/7/12/19 等，并行性能提升的比较明显；但是对于另外一些，如 8/9/10/13/20 等，并行和非并行的性能相差无几。并行并不是适用于所有场景，只是对那些适用的场景会提升查询性能。

2、大并发 oltp

PG9.6 对锁进行了优化，使其在大并发的场景下拥有更好的扩展性。这里主要介绍WALInsertLock和ProcArrayLock，并以PG9.3.5和PG9.6.1为例进行对比说明。

在 PostgreSQL 中，每一个对数据库的修改操作，都需要写一个 XLog。在9.3.5的版本中，写 XLog 需要以排他形式获取 WALInsertLock 这个全局锁，然后写入 XLog ，释放锁，再由其他进程获取锁进行操作。由于每个进程都是获取排它锁，导致写 XLog 的过程是串行进行的。在大并发写的场景下，对单一锁的竞争比较激烈，影响整体性能。

为了有效降低对 WALInsertLock 这个全局锁的竞争，9.6版本中对 WALInsertLock 进行了 partition ，将其划分成多个分区锁。每个进程根据自己的 id 选取一个分区锁，然后在 XLogBuffer 中为自己预留一个空位，再进行写入，实现了多个进程并行写 XLog 。（其实并行写 XLog 的优化在 PG9.4 版本中就已经有了）

在 PostgreSQL 中，每一个 select、DML 操作都需要获取一个全局快照（ snapshot ）来进行数据的可见性判断（ MVCC ），每个获取快照的动作都需要以共享形式获取ProcArrayLock 。在每个事务结束（commit/abort）的时候，都需要清除自己的事务信息，目的是为了保证其他事务可以正确的获取到快照信息。由于这个操作需要进行修改操作，所以以排他形式获得 ProcArrayLock 。

以 PG9.3.5 为例，看一下ProcArrayLock锁竞争关系：

获取快照的过程是获取共享锁，所以所有需要获取快照进行查询的进程可以进行并发操作，只会影响到需要进行事务提交或者回滚的进程。

事务提交或者回滚的时候需要获取排它锁，所有其他需要获取快照或者结束事务的进程都需要进行阻塞，等待当前锁释放，这里对锁的竞争比较激烈，影响到并发。

9.6 对这部分进行了优化，当一个进程获取排它锁进行事务提交或者回滚时，其他需要在同一时刻进行事务处理的进程，组成一个等待队列，由队列头去获取锁。当队列头获取锁之后，为队列中所有成员完成事务处理，并唤醒成员。这种方式有效地将多进程对锁的竞争降低了，减少了锁冲突，提升了扩展性。

9.6 做了这么多优化，到底性能如何？

这里使用了业界 OLTP 标准 TPC-C 进行了相关测试，测试的机型分别为 Z3，intel(96cores, 760GB，3*4TB nvme), power(160cores, 512GB，3*1TB nvme)。TPC-C 数据量为 200 仓库，20GB，并发 100。测试结果如图：

从图中可以看出，9.6 的性能要明显好于 9.3.5，尤其是在 Intel 的 96cores 的机器上，tpmc 高达 113 万，一台机器就可以满足大多数的 OLTP 业务需求。从 cpu 使用率也可以看出，9.6 的优化提升了并发能力，使得资源得到更充分的利用。

除了 TPC-C 以外，还在 power 和 Intel(96 cores) 上面测试了 9.6 的 qps(select only)，结果如图：

PostgreSQL 9.6 已经可以很好地支持大并发 OLTP 查询，甚至单机就可以满足大部分 OLTP 业务。并且现在已经有了最基本的并行查询，后续版本也会对并行查询进行增强和优化。相信在不久的未来，PostgreSQL 会成为一个能同时承载 OLTP OLAP 的强大数据库。