Postgresql之数据库并行查询

原创 _ All China Database Union 2024-03-20

977

一、运行原理

PostgreSQL的并行查询通过将查询的执行分散到多个进程或线程上来提高查询性能。它的工作原理如下:

查询分析和计划:当收到一个查询时,PostgreSQL的查询规划器分析查询并创建一个执行计划。在这个过程中,规划器决定是否可以使用并行执行,以及使用多少个工作进程(worker)。
启动工作进程:如果规划器决定使用并行执行,它会要求启动一定数量的工作进程。每个工作进程都是一个单独的PostgreSQL进程,负责执行计划的一部分。
并行执行:每个工作进程执行计划的并行部分。这通常涉及扫描表的不同部分,或者对数据的不同子集进行聚合或排序操作。
结果合并:当工作进程完成它们的工作时,它们将结果发送回主进程(也称为领导者进程)。领导者进程负责合并来自工作进程的结果,并执行任何需要在合并结果上完成的最终操作。
返回结果:一旦领导者进程完成结果的最终处理,它将结果返回给客户端。

二、控制参数

max_worker_processes:
- 这个参数设置了PostgreSQL可以启动的最大后台工作进程数。
- 这包括用于并行查询的工作进程,以及用于其他目的的进程,如自动清理(autovacuum)。
- 默认值因版本而异,但通常是8。

postgres=# show max_worker_processes;
 max_worker_processes
----------------------
 128
(1 row)

max_parallel_workers_per_gather:
- 这个参数控制每个Gather或Gather Merge节点可以使用的最大工作进程数。
- Gather和Gather Merge节点是并行查询计划中负责协调工作进程的节点。
- 默认值是2,这意味着每个Gather或Gather Merge节点最多可以使用2个工作进程。

postgres=# show max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 2
(1 row)

max_parallel_workers:
- 这个参数控制整个系统中可以同时活动的最大并行工作进程数。
- 它的默认值是8。
- 这个值应该小于或等于max_worker_processes。

postgres=# show max_parallel_workers;
 max_parallel_workers
----------------------
 8
(1 row)

max_parallel_maintenance_workers:
- 这个参数控制并行维护操作(如CREATE INDEX或VACUUM)可以使用的最大工作进程数。
- 默认值是2。

postgres=# show max_parallel_maintenance_workers;
 max_parallel_maintenance_workers
----------------------------------
 2
(1 row)

parallel_leader_participation:
- 这个参数控制领导者进程(即执行查询的原始进程)是否应该参与并行查询的执行。
- 如果设置为on(默认值),领导者进程将执行查询计划中不需要并行化的部分,同时协调工作进程。
- 如果设置为off,领导者进程将只负责协调工作进程,不直接参与查询执行。

postgres=# show parallel_leader_participation;
 parallel_leader_participation
-------------------------------
 on
(1 row)

parallel_setup_cost:
- 这个参数设置启动并行执行所需的预计成本。
- 查询规划器会将此成本与查询的预计总成本进行比较,以决定是否使用并行执行。
- 默认值是1000。

postgres=# show parallel_setup_cost;
 parallel_setup_cost
---------------------
 1000
(1 row)

parallel_tuple_cost:
- 这个参数设置从工作进程传输每个元组(数据行)到领导者进程的预计成本。
- 查询规划器会使用此成本来估计并行执行的总成本。
- 默认值是0.1。

postgres=# show parallel_tuple_cost;
 parallel_tuple_cost
---------------------
 0.1
(1 row)

三、模拟并行查询

t1=# create table t2 as select * from pg_settings;
SELECT 352
t1=# insert into t2 select * from t2;
INSERT 0 352
t1=# insert into t2 select * from t2;
INSERT 0 704
t1=# insert into t2 select * from t2;
INSERT 0 1408
t1=# insert into t2 select * from t2;
INSERT 0 2816
t1=# insert into t2 select * from t2;
INSERT 0 5632
t1=# insert into t2 select * from t2;
INSERT 0 11264
t1=# insert into t2 select * from t2;
INSERT 0 22528
t1=# insert into t2 select * from t2;
^[[AINSERT 0 45056
t1=# insert into t2 select * from t2;
^[[A
INSERT 0 90112
t1=# insert into t2 select * from t2;
^[[AINSERT 0 180224
t1=# insert into t2 select * from t2;
INSERT 0 360448
t1=# commit;

t1=# explain select count(*) from t2;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=22958.39..22958.40 rows=1 width=8)
   ->  Gather  (cost=22958.17..22958.38 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=21958.17..21958.18 rows=1 width=8)
               ->  Parallel Seq Scan on t2  (cost=0.00..21639.94 rows=127294 width=0)
(5 rows)

Gather节点部分指示该执行计划使用了并行。并且并行工作进程数量为2;

t1=# alter system set max_parallel_workers_per_gather=8;
ALTER SYSTEM
t1=# show max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 2
(1 row)

t1=# \q
[postgres@db01 ~]$ pg_ctl reload
server signaled
2024-03-20 16:00:54.597 CST [123561] LOG:  received SIGHUP, reloading configuration files
2024-03-20 16:00:54.597 CST [123561] LOG:  parameter "max_parallel_workers_per_gather" changed to "8"

t1=# show max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 8
(1 row)


t1=# explain (ANALYZE, VERBOSE, BUFFERS) select count(*) from t2;
                                                                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24274.16..24274.17 rows=1 width=8) (actual time=122.557..122.614 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=16147 read=4220
   ->  Gather  (cost=24273.84..24274.15 rows=3 width=8) (actual time=101.922..122.605 rows=4 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 3
         Workers Launched: 3
         Buffers: shared hit=16147 read=4220
         ->  Partial Aggregate  (cost=23273.84..23273.85 rows=1 width=8) (actual time=79.846..79.847 rows=1 loops=4)
               Output: PARTIAL count(*)
               Buffers: shared hit=16147 read=4220
               Worker 0:  actual time=65.226..65.227 rows=1 loops=1
                 Buffers: shared hit=4448
               Worker 1:  actual time=77.821..77.822 rows=1 loops=1
                 Buffers: shared hit=3394 read=526
               Worker 2:  actual time=74.583..74.584 rows=1 loops=1
                 Buffers: shared hit=4304
               ->  Parallel Seq Scan on public.t2  (cost=0.00..22692.47 rows=232547 width=0) (actual time=0.098..63.804 rows=180224 loops=4)
                     Output: name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart
                     Buffers: shared hit=16147 read=4220
                     Worker 0:  actual time=0.006..58.850 rows=157470 loops=1
                       Buffers: shared hit=4448
                     Worker 1:  actual time=0.022..38.851 rows=138780 loops=1
                       Buffers: shared hit=3394 read=526
                     Worker 2:  actual time=0.007..68.083 rows=152352 loops=1
                       Buffers: shared hit=4304
 Planning Time: 0.054 ms
 Execution Time: 122.640 ms
(28 rows)

四、使用并行

postgresql目前没有hint语法提示，只能通过调整优化器相关参数或成本估算参数引导优化器生成并行执行计划

force_parallel_mode

off(默认值):
查询优化器将根据查询的特点和系统的配置自主地决定是否使用并行执行。它会对并行执行的成本和收益进行估算,只有在并行执行看起来有益时才会选择并行计划。

on:
查询优化器将尽可能地生成并行执行计划,即使并行执行可能并不总是有益。这可能导致某些查询使用并行执行,即使串行执行可能更高效。

regress:
这个设置主要用于回归测试。它与on类似,但额外禁用了一些可能在并行执行期间导致结果不确定性的优化。

t1=# show force_parallel_mode;
 force_parallel_mode
---------------------
 off
(1 row)


t1=# EXPLAIN  SELECT  count(*) FROM t3;
                        QUERY PLAN
-----------------------------------------------------------
 Aggregate  (cost=13.06..13.07 rows=1 width=8)
   ->  Seq Scan on t3  (cost=0.00..12.65 rows=165 width=0)
(2 rows)


t1=# show force_parallel_mode;
 force_parallel_mode
---------------------
 on
(1 row)

t1=# EXPLAIN  SELECT  count(*) FROM t3;
                           QUERY PLAN
-----------------------------------------------------------------
 Gather  (cost=1015.40..1015.51 rows=1 width=8)
   Workers Planned: 1
   Single Copy: true
   ->  Aggregate  (cost=15.40..15.41 rows=1 width=8)
         ->  Seq Scan on t3  (cost=0.00..14.52 rows=352 width=0)
(5 rows)

成本估算或阈值参数

parallel_setup_cost
parallel_tuple_cost
min_parallel_table_scan_size
min_parallel_index_scan_size
parallel_tuple_cost
parallel_setup_cost

这些参数影响优化器对并行执行成本的估算。

墨力计划

最后修改时间：2024-03-29 17:50:51

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Postgresql之数据库并行查询

一、运行原理

二、控制参数

三、模拟并行查询

四、使用并行

评论