【PostgreSQL 】PostgreSQL 中的并行性
PostgreSQL 是最好的对象关系数据库之一，它的架构是基于进程而不是基于线程的。虽然几乎所有当前的数据库系统都使用线程进行并行处理，但 PostgreSQL 基于进程的架构是在 POSIX 线程之前实现的。 PostgreSQL 在启动时启动一个进程“postmaster”，之后每当有新客户端连接到 PostgreSQL 时都会跨越新进程。
在版本 10 之前，单个连接中没有并行性。诚然，来自不同客户端的多个查询由于进程架构而具有并行性，但它们无法从彼此之间获得任何性能优势。换句话说，单个查询串行运行并且没有并行性。这是一个巨大的限制，因为单个查询不能利用多核。 PostgreSQL 中的并行性是从 9.6 版开始引入的。从某种意义上说，并行性是单个进程可以有多个线程来查询系统并利用系统中的多核。这为 PostgreSQL 提供了查询内并行性。
PostgreSQL 中的并行性是作为涵盖顺序扫描、聚合和连接的多个功能的一部分实现的。
PostgreSQL 中的并行组件
PostgreSQL 中有三个重要的并行性组件。这些是进程本身、聚集器和工人。如果没有并行性，进程本身会处理所有数据，但是，当规划器决定查询或其一部分可以并行化时，它会在计划的可并行化部分中添加一个 Gather 节点，并生成该子树的一个 Gather 根节点。查询执行从进程（领导者）级别开始，计划的所有串行部分都由领导者运行。但是，如果对查询的任何部分（或全部）启用并允许并行性，则为它分配带有一组工作人员的收集节点。工作者是与需要并行化的部分树（部分计划）并行运行的线程。关系的块在线程之间划分，使得关系保持顺序。线程数由 PostgreSQL 配置文件中的设置控制。工作人员使用共享内存进行协调/通信，工作人员完成工作后，将结果传递给领导者进行积累。
并行顺序扫描
在 PostgreSQL 9.6 中，添加了对并行顺序扫描的支持。 顺序扫描是对表的扫描，其中依次评估一系列块。 就其本质而言，这允许并行性。 因此，这是第一个实现并行性的自然候选者。 在这种情况下，整个表在多个工作线程中被顺序扫描。 这是一个简单的查询，我们查询 pgbench_accounts 表行 (63165)，它有 1,500,000,000 个元组。 总执行时间为 4,343,080ms。 由于没有定义索引，因此使用顺序扫描。 整个表在没有线程的单个进程中扫描。 因此，无论有多少内核可用，都使用 CPU 的单核。
db=# EXPLAIN ANALYZE SELECT *
FROM pgbench_accounts
WHERE abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..73708261.04 rows=1 width=97)
(actual time=6868.238..4343052.233 rows=63165 loops=1)
Filter: (abalance > 0)
Rows Removed by Filter: 1499936835
Planning Time: 1.155 ms
Execution Time: 4343080.557 ms
(5 rows)如果这 1,500,000,000 行在一个进程中使用“10”个工作人员并行扫描会怎样？ 它将大大减少执行时间。
db=# EXPLAIN ANALYZE select * from pgbench_accounts where abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Gather (cost=1000.00..45010087.20 rows=1 width=97)
(actual time=14356.160..1628287.828 rows=63165 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..45009087.10 rows=1 width=97)
(actual time=43694.076..1628068.096 rows=5742 loops=11)
Filter: (abalance > 0)
Rows Removed by Filter: 136357894
Planning Time: 37.714 ms
Execution Time: 1628295.442 ms
(8 rows)现在总执行时间是1,628,295ms； 使用 10 个工作线程进行扫描时，这提高了 266%。
用于基准的查询：SELECT * FROM pgbench_accounts WHERE abalance > 0;
表大小：426GB
表中的总行数：1,500,000,000
用于基准测试的系统：
CPU：2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
上图清楚地显示了并行性如何提高顺序扫描的性能。添加单个工作人员时，性能会降低，这是可以理解的，因为没有获得并行性，但是创建额外的收集节点和单个工作会增加开销。但是，使用多个工作线程时，性能会显着提高。此外，重要的是要注意性能不会以线性或指数方式增加。它会逐渐改善，直到添加更多工人不会带来任何性能提升；有点像接近水平渐近线。该基准测试是在 64 核机器上执行的，很明显，拥有 10 名以上的工作人员不会带来任何显着的性能提升。
并行聚合
在数据库中，计算聚合是非常昂贵的操作。在单个过程中进行评估时，这些需要相当长的时间。在 PostgreSQL 9.6 中，通过简单地将它们分成块（分而治之的策略）添加了并行计算这些的能力。这允许多个工作人员在基于这些计算的最终值由领导者计算之前计算聚合的部分。从技术上讲，PartialAggregate 节点被添加到计划树中，每个 PartialAggregate 节点从一个工作人员那里获取输出。然后将这些输出发送到 FinalizeAggregate 节点，该节点组合来自多个（所有）PartialAggregate 节点的聚合。如此有效地，并行部分计划包括根处的 FinalizeAggregate 节点和将 PartialAggregate 节点作为子节点的 Gather 节点。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (cost=73708261.04..73708261.05 rows=1 width=8)
(actual time=2025408.357..2025408.358 rows=1 loops=1)
-> Seq Scan on pgbench_accounts (cost=0.00..67330666.83 rows=2551037683 width=0)
(actual time=8.162..1963979.618 rows=1500000000 loops=1)
Planning Time: 54.295 ms
Execution Time: 2025419.744 ms
(4 rows)以下是要并行评估聚合时的计划示例。 您可以在这里清楚地看到性能改进。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Finalize Aggregate (cost=45010088.14..45010088.15 rows=1 width=8)
(actual time=1737802.625..1737802.625 rows=1 loops=1)
-> Gather (cost=45010087.10..45010088.11 rows=10 width=8)
(actual time=1737791.426..1737808.572 rows=11 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Partial Aggregate
(cost=45009087.10..45009087.11 rows=1 width=8)
(actual time=1737752.333..1737752.334 rows=1 loops=11)
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..44371327.68 rows=255103768 width=0)
(actual time=7.037..1731083.005 rows=136363636 loops=11)
Planning Time: 46.031 ms
Execution Time: 1737817.346 ms
(8 rows)使用并行聚合，在这种特殊情况下，当涉及 10 个并行工作人员时，由于 2,025,419.744 的执行时间减少到 1,737,817.346，我们的性能提升了 16% 以上。
用于基准的查询：SELECT count(*) FROM pgbench_accounts WHERE abalance > 0;
表大小：426GB
表中的总行数：1500000000
用于基准测试的系统：
CPU：2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
并行索引（B-Tree）扫描
对 B-Tree 索引的并行支持意味着索引页面是并行扫描的。 B-Tree 索引是 PostgreSQL 中最常用的索引之一。 在 B-Tree 的并行版本中，worker 扫描 B-Tree，当它到达其叶节点时，它会扫描块并触发阻塞的等待 worker 扫描下一个块。
使困惑？ 让我们看一个例子。 假设我们有一个包含 id 和 name 列的表 foo，有 18 行数据。 我们在表 foo 的 id 列上创建一个索引。 系统列 CTID 附加到表的每一行，用于标识该行的物理位置。 CTID 列中有两个值：块号和偏移量。
postgres=# <strong>SELECT</strong> ctid, id <strong>FROM</strong> foo; ctid | id --------+----- (0,55) | 200 (0,56) | 300 (0,57) | 210 (0,58) | 220 (0,59) | 230 (0,60) | 203 (0,61) | 204 (0,62) | 300 (0,63) | 301 (0,64) | 302 (0,65) | 301 (0,66) | 302 (1,31) | 100 (1,32) | 101 (1,33) | 102 (1,34) | 103 (1,35) | 104 (1,36) | 105 (18 rows)
让我们在该表的 id 列上创建 B-Tree 索引。
CREATE INDEX foo_idx ON foo(id)
假设我们要选择 id <= 200 且有 2 个工作人员的值。 Worker-0 将从根节点开始扫描，直到叶子节点 200。它将节点 105 下的下一个块移交给处于阻塞和等待状态的 Worker-1。如果还有其他工作人员，则将块划分为工作人员。重复类似的模式，直到扫描完成。
并行位图扫描
要并行化位图堆扫描，我们需要能够以非常类似于并行顺序扫描的方式在工作人员之间划分块。为此，对一个或多个索引进行扫描，并创建指示要访问哪些块的位图。这是由领导进程完成的，即这部分扫描是按顺序运行的。但是，当将识别出的块传递给工作人员时，并行性就会启动，这与并行顺序扫描中的方式相同。
平行连接
合并连接支持中的并行性也是此版本中添加的最热门功能之一。在这种情况下，一个表与其他表的内部循环哈希或合并连接。无论如何，内部循环不支持并行性。将整个循环作为一个整体进行扫描，当每个worker作为一个整体执行内部循环时，就会发生并行。每个连接发送到聚集的结果累积并产生最终结果。
概括
从我们已经在本博客中讨论过的内容可以明显看出，并行性可以显着提升某些情况的性能，而对另一些情况则略有提升，并且在某些情况下可能会导致性能下降。 确保正确设置了 parallel_setup_cost 或 parallel_tuple_cost，以使查询计划程序能够选择并行计划。 即使为这些 GUI 设置了较低的值，如果没有生成并行计划，请参阅 PostgreSQL 并行性文档以获取详细信息。
对于并行计划，您可以获得每个计划节点的每个工作人员的统计信息，以了解负载在工作人员之间的分布情况。 您可以通过 EXPLAIN (ANALYZE, VERBOSE) 做到这一点。 与任何其他性能特性一样，没有适用于所有工作负载的规则。 应根据需要仔细配置并行性，并且您必须确保获得性能的概率明显高于性能下降的概率。
原文：https://www.percona.com/blog/2019/07/30/parallelism-in-postgresql/
- 36 次浏览