并行查询
并行查询
前言
PostgreSQL能设计出利用多 CPU 让查询更快的查询计划。这种特性被称为并行查询。由于现有实现的限制或者因为没有比连续查询计划更快的查询计划存在，很多查询并不能从并行查询获益。不过，对于那些可以从并行查询获益的查询来说，并行查询带来的速度提升是显著的。很多查询在使用并行查询时比之前快了超过两倍，有些查询是以前的四倍甚至更多的倍数。那些访问大量数据但只返回其中少数行给用户的查询最能从并行查询中获益。这一章介绍一些并行查询如何工作的细节以及哪些情况下可以使用并行查询，这样希望充分利用并行查询的用户可以理解他们能从并行查询得到什么。
并行查询如何工作
当优化器判断对于某一个特定的查询，并行查询是最快的执行策略时，优化器将创建一个查询计划。该计划包括一个 Gather或者Gather Merge节点。下面是一个简单的例子：
EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
QUERY PLAN
-------------------------------------------------------------------------------------
Gather (cost=1000.00..217018.43 rows=1 width=97)
Workers Planned: 2
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..216018.33 rows=1 width=97)
Filter: (filler ~~ '%x%'::text)
(4 rows)
在所有的情形下，Gather或Gather Merge节点都只有一个子计划，它是将被并行执行的计划的一部分。如果Gather或Gather Merge节点位于计划树的最顶层，那么整个查询将并行执行。如果它位于计划树的其他位置，那么只有查询中在它之下的那一部分会并行执行。在上面的例子中，查询只访问了一个表，因此除Gather节点本身之外只有一个计划节点。因为该计划节点是Gather节点的孩子节点，所以它会并行执行。
使用 EXPLAIN命令, 你能看到规划器选择的工作者数量。当查询执行期间到达Gather节点时，实现用户会话的进程将会请求和规划器选中的工作者数量一样多的后台工作者进程 。规划器将考虑使用的后台工作者的数量被限制为最多max_parallel_workers_per_gather个。任何时候能够存在的后台工作者进程的总数由max_worker_processes和max_parallel_workers限制。因此，一个并行查询可能会使用比规划中少的工作者来运行，甚至有可能根本不使用工作者。最优的计划可能取决于可用的工作者的数量，因此这可能会导致不好的查询性能。如果这种情况经常发生，那么就应当考虑一下提高max_worker_processes和max_parallel_workers的值，这样更多的工作者可以同时运行；或者降低max_parallel_workers_per_gather，这样规划器会要求少一些的工作者。
为一个给定并行查询成功启动的后台工作者进程都将会执行计划的并行部分。这些工作者的领导者也将执行该计划，不过它还有一个额外的任务：它还必须读取所有由工作者产生的元组。当整个计划的并行部分只产生了少量元组时，领导者通常将表现为一个额外的加速查询执行的工作者。反过来，当计划的并行部分产生大量的元组时，领导者将几乎全用来读取由工作者产生的元组并且执行Gather或Gather Merge节点上层计划节点所要求的任何进一步处理。在这些情况下，领导者所作的执行并行部分的工作将会很少。
当计划的并行部分的顶层节点是Gather Merge而不是Gather时，它表示每个执行计划并行部分的进程会产生有序的元组，并且领导者执行一种保持顺序的合并。相反，Gather会以任何方便的顺序从工作者读取元组，这会破坏可能已经存在的排序顺序。
何时会用到并行查询？
有几种设置会导致查询规划器在任何情况下都不生成并行查询计划。为了让并行查询计划能够被生成，必须配置好下列设置。
- max_parallel_workers_per_gather必须被设置为大于零的值。这是一种特殊情况，更加普遍的原则是所用的工作者数量不能超过max_parallel_workers_per_gather所配置的数量。
-- 此外，系统一定不能运行在单用户模式下。因为在单用户模式下，整个数据库系统运行在单个进程中，没有后台工作者进程可用。
-- 如果下面的任一条件为真，即便对一个给定查询通常可以产生并行查询计划，规划器都不会为它产生并行查询计划：
- 查询要写任何数据或者锁定任何数据库行。如果一个查询在顶层或者 CTE 中包含了数据修改操作，那么不会为该查询产生并行计划。一种例外是，CREATE TABLE ... AS、SELECT INTO以及CREATE MATERIALIZED VIEW这些创建新表并填充它的命令可以使用并行计划。
- 查询可能在执行过程中被暂停。只要在系统认为可能发生部分或者增量式执行，就不会产生并行计划。例如：用DECLARE CURSOR创建的游标将永远不会使用并行计划。类似地，一个FOR x IN query LOOP .. END LOOP形式的 PL/pgSQL 循环也永远不会使用并行计划，因为当并行查询进行时，并行查询系统无法验证循环中的代码执行起来是安全的。
- 使用了任何被标记为PARALLEL UNSAFE的函数的查询。大多数系统定义的函数都被标记为PARALLEL SAFE，但是用户定义的函数默认被标记为PARALLEL UNSAFE。参见第 15.4 节中的讨论。
- 该查询运行在另一个已经存在的并行查询内部。例如，如果一个被并行查询调用的函数自己发出一个 SQL 查询，那么该查询将不会使用并行计划。这是当前实现的一个限制，但是或许不值得移除这个限制，因为它会导致单个查询使用大量的进程。
- 即使对于一个特定的查询已经产生了并行查询计划，在一些情况下执行时也不会并行执行该计划。如果发生这种情况，那么领导者将会自己执行该计划在Gather节点之下的部分，就好像Gather节点不存在一样。上述情况将在满足下面的任一条件时发生：
- 因为后台工作者进程的总数不能超过max_worker_processes，导致不能得到后台工作者进程。
- 由于为并行查询目的启动的后台工作者数量不能超过max_parallel_workers这一限制而不能得到后台工作者。
- 客户端发送了一个执行消息，并且消息中要求取元组的数量不为零。执行消息可见扩展查询协议中的讨论。因为libpq当前没有提供方法来发送这种消息，所以这种情况只可能发生在不依赖 libpq 的客户端中。如果这种情况经常发生，那在它可能发生的会话中设置 max_parallel_workers_per_gather为零是一个很好的主意，这样可以避免产生连续运行时次优的查询计划。
二、并行查询开启条件
1、并行查询的适用条件
并行查询的适用条件
并行查询在PostgreSQL中是一项可以显著提高查询性能的功能，但其使用受到多种因素的限制。以下是一些关键的配置和条件，它们决定了并行查询是否可以被应用：
（1）max_parallel_workers_per_gather
max_parallel_workers_per_gather必须设置为大于零的值。
这意味着至少有一个并行工作者可以被用于并行查询计划的执行。
（2）系统不能处于单用户模式
系统不能处于单用户模式。在单用户模式下，整个数据库系统作为单一进程运行，因此无法启动背景工作者进程。
2、并行查询不适用情况
即使并行查询计划理论上可以生成，但如果出现以下情况之一，查询优化器将不会生成并行计划：
（1）查询涉及数据写入或行级锁
如果查询包含数据修改操作（无论是顶级操作还是公共表表达式（CTE）内的操作），则不会为该查询生成并行计划。
例外情况是创建新表并填充数据的命令，这些命令可以使用并行计划：
即使并行查询计划理论上可以生成，但如果出现以下情况之一，查询优化器将不会生成并行计划：
系统不能处于单用户模式。在单用户模式下，整个数据库系统作为单一进程运行，因此无法启动背景工作者进程。
CREATE TABLE … AS
SELECT INTO
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
（2）查询可能在执行过程中被挂起
如果系统认为查询的执行可能会被部分或增量的中断，那么不会生成并行计划。
例如，使用DECLARE CURSOR创建的游标永远不会使用并行计划。
同样地，形式如FOR x IN query LOOP … END LOOP的PL/pgSQL循环也不会使用并行计划，因为并行查询系统无法确保循环代码在并行查询活跃时安全执行。
（3）查询使用了标记为PARALLEL UNSAFE的函数
大多数系统定义的函数是PARALLEL SAFE的，但用户定义的函数默认被标记为PARALLEL UNSAFE。
（4）查询在另一个已经并行的查询内部运行
例如，如果一个并行查询调用的函数自身发出SQL查询，那么该查询将不会使用并行计划。
这是一个当前实现的限制，而且可能不希望移除这一限制，以免单个查询使用过多的进程。
3、执行时的限制
即使为特定查询生成了并行查询计划，在执行时也可能因以下情况之一而无法并行执行：
（1）背景工作者不足
如果由于max_worker_processes的限制，无法获取到足够的背景工作者。
（2）并行工作者数量超出限制
如果由于max_parallel_workers的限制，无法获取到足够的并行工作者。
（3）客户端发送带有非零获取计数的Execute消息
这通常发生在不依赖libpq的客户端中。如果这种情况频繁发生，可以考虑在可能发生串行执行的会话中将max_parallel_workers_per_gather设置为零，以避免生成在串行执行时可能次优的查询计划。
三、并行查询配置
1、修改postgresql.conf配置
cd /data/postgresql/pgdata
vim postgresql.conf
max_parallel_workers_per_gather = 4;
max_parallel_workers = 16;
max_worker_processes = 16;
parallel_setup_cost = 1000;
parallel_tuple_cost = 0.1;
min_parallel_table_scan_size = 8MB;
min_parallel_index_scan_size = 512KB;
jit = on;
2、重启数据库
su - mydba
pg_ctl restart
pg_ctl status
3、再次执行查询
-- 执行复杂查询并查看执行计划
EXPLAIN (ANALYZE, VERBOSE)
SELECT t1.random_int, AVG(t2.random_value) AS avg_value
FROM test_table t1
JOIN test_table2 t2 ON t1.id = t2.test_table_id
GROUP BY t1.random_int
ORDER BY avg_value DESC
LIMIT 10;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=376576.04..376576.07 rows=10 width=36) (actual time=3425.935..3454.169 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
-> Sort (cost=376576.04..378527.81 rows=780708 width=36) (actual time=3425.934..3454.167 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
Sort Key: (avg(t2.random_value)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=232414.56..359705.22 rows=780708 width=36) (actual time=1536.253..3322.661 rows=822508 loops=1)
Output: t1.random_int, avg(t2.random_value)
Group Key: t1.random_int
-> Gather Merge (cost=232414.56..326525.13 rows=3122832 width=36) (actual time=1536.246..2242.094 rows=1513177 loops=1)
Output: t1.random_int, (PARTIAL avg(t2.random_value))
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=232314.50..249573.30 rows=780708 width=36) (actual time=1460.530..1877.445 rows=302635 loops=5)
Output: t1.random_int, PARTIAL avg(t2.random_value)
Group Key: t1.random_int
Worker 0: actual time=1452.893..1829.912 rows=269927 loops=1
Worker 1: actual time=1447.372..1949.316 rows=345027 loops=1
Worker 2: actual time=1420.501..1809.073 rows=265385 loops=1
Worker 3: actual time=1445.882..1859.911 rows=285168 loops=1
-> Sort (cost=232314.50..234814.48 rows=999994 width=15) (actual time=1460.510..1574.450 rows=800000 loops=5)
Output: t1.random_int, t2.random_value
Sort Key: t1.random_int
Sort Method: external merge Disk: 24008kB
Worker 0: actual time=1452.868..1557.530 rows=675843 loops=1
Sort Method: external merge Disk: 16712kB
Worker 1: actual time=1447.354..1594.915 rows=929370 loops=1
Sort Method: external merge Disk: 22976kB
Worker 2: actual time=1420.478..1529.668 rows=683334 loops=1
Sort Method: external merge Disk: 16896kB
Worker 3: actual time=1445.860..1563.544 rows=740113 loops=1
Sort Method: external merge Disk: 18304kB
-> Parallel Hash Join (cost=63790.87..115566.80 rows=999994 width=15) (actual time=661.549..1104.918 rows=800000 loops=5)
Output: t1.random_int, t2.random_value
Inner Unique: true
Hash Cond: (t2.test_table_id = t1.id)
Worker 0: actual time=675.613..1100.772 rows=675843 loops=1
Worker 1: actual time=654.632..1117.668 rows=929370 loops=1
Worker 2: actual time=675.644..1097.117 rows=683334 loops=1
Worker 3: actual time=637.896..1088.461 rows=740113 loops=1
-> Parallel Seq Scan on public.test_table2 t2 (cost=0.00..35477.94 rows=999994 width=15) (actual time=0.025..135.608 rows=800000 loops=5)
Output: t2.random_value, t2.test_table_id
Worker 0: actual time=0.035..155.472 rows=828960 loops=1
Worker 1: actual time=0.030..132.829 rows=1002916 loops=1
Worker 2: actual time=0.030..118.347 rows=884021 loops=1
Worker 3: actual time=0.022..153.351 rows=538824 loops=1
-> Parallel Hash (cost=47383.94..47383.94 rows=999994 width=8) (actual time=316.469..316.470 rows=800000 loops=5)
Output: t1.random_int, t1.id
Buckets: 262144 Batches: 32 Memory Usage: 7008kB
Worker 0: actual time=317.067..317.068 rows=878596 loops=1
Worker 1: actual time=322.550..322.550 rows=688224 loops=1
Worker 2: actual time=297.070..297.071 rows=656017 loops=1
Worker 3: actual time=326.658..326.659 rows=1000450 loops=1
-> Parallel Seq Scan on public.test_table t1 (cost=0.00..47383.94 rows=999994 width=8) (actual time=0.016..120.297 rows=800000 loops=5)
Output: t1.random_int, t1.id
Worker 0: actual time=0.013..132.002 rows=878596 loops=1
Worker 1: actual time=0.019..95.735 rows=688224 loops=1
Worker 2: actual time=0.022..98.217 rows=656017 loops=1
Worker 3: actual time=0.015..138.631 rows=1000450 loops=1
Query Identifier: 1846774944933342204
Planning Time: 0.183 ms
Execution Time: 3454.233 ms
(62 rows)
浙公网安备 33010602011771号