PostgreSQL 并行查询概述

最新推荐文章于 2024-07-08 07:23:10 发布

weixin_34379433

最新推荐文章于 2024-07-08 07:23:10 发布

阅读量434

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/tianbing/blog/3029493

版权

2019独角兽企业重金招聘Python工程师标准>>>

PostgreSQL从9.6版本开始加入并行查询，并在PostgreSQL10和PostgreSQL11分别做了大量加强工作。下面从：

何时启用并行查询功能
并行查询是如何工作的
worker进程数量越多，查询性能越高吗

三个方面简单介绍下并行查询的机制。

--------------------------------------------------------------------------------

何时启用并行查询功能

是否启用并行查询实际上有诸多因素决定。总的来说，有以下两个方面：

并行查询开关等参数

在PostgreSQL系统中，并行查询的开关是max_parallel_workers_per_gather，它所表示的是单个Gather节点所能开启的最大worker进程数量。当设置为0时，是禁用并行查询，即没有可提供的并行查询的worker进程；该参数的默认值是2，即每个Gather节点最多可以有2个worker进程。需要注意的是，postgresql.conf文件中还有一个名为force_parallel_mode的参数（看参数名感觉像是并行查询的开关参数），其实它表示是否强制开启并行查询，多用于测试为目的。如果设置为on，则无论何时都会进行并行查询（实际上，这时并不见得会带来查询效率的提高，后面会有介绍），其默认值为off，这时系统会根据具体成本考虑是否启用并行查询。

在并行查询中，每个worker进程都会承担部分的查询工作。除了上面说的max_parallel_workers_per_gather参数外，系统还有一个参数max_parallel_workers，它决定系统所支持的最大的worker进程的数量，所以max_parallel_workers_per_gather的设定必须参考max_parallel_workers的值。当然，它们都得受限于max_worker_process（系统允许后台开启worker进程的最大数量）参数。

并行查询成本考虑

当参数决定可以进行并行查询后，实际上是否可以进行并行查询，还得进行一些成本上的考虑。因为并行查询除了能带来并行查询上的效率外，还会有一些成本的消耗，比如并行带来的数据分片，进程间的通信以及并发控制所带来的的系统的开销等。当数据量足够大时，并行查询带来的效率可以抵消这些成本考虑；但是数据量比较小时，并行查询可能不会带来效率的提升，反而会降低了原有的性能。而对于并行带来的成本，系统需要进行计算其代价，然后决定是否启用并行查询。

在配置文件中，有专门对并行查询成本的设置：

	parallel_tuple_cost = 0.1              # 后天进程传递tuple的代价
	parallel_setup_cost = 1000.0           # worker进程的启动成本
	min_parallel_table_scan_size = 8MB     # 并行查询规定表的最小数据量
	min_parallel_index_scan_size = 512kB   # 并行查询规定的索引的最小数据量

系统就是依托上面的参数进行计算并行查询的成本。这些参数基本上采用默认值就好，当然除非有特殊要求或特殊环境。

并行查询是如何工作

以普通查询为例，下面看一下简单查询的示例：

highgo=# explain analyze select * from test where n = 9999;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..11822.39 rows=3984 width=36) (actual time=2.014..196.880 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..10423.99 rows=1660 width=36) (actual time=76.604..141.282 rows=0 loops=3)
         Filter: (n = 9999)
         Rows Removed by Filter: 333333
 Planning time: 0.044 ms
 Execution time: 196.904 ms
(8 行记录)

通过EXPLAIN可以查看并行查询的执行计划。当用户输入一个查询语句时，查询分析，查询重写以及查询规划都和原来一样，只有到执行计划时，才开始真正进入并行查询环节。上面执行计划中，Gather节点只是执行计划的一个子节点，属于执行计划的一部分，当查询执行走到Gather节点时，会话进程会申请一定数量的worker进程（根据配置参数，以及成本确定）来进行并行查询过程。在这些worker进程中，有一个充当leader worker的角色，负责收集汇总各个worker进程查询的结果。该leader worker进程也会根据查询的数据量大小承担部分的并行查询部分。执行过程如下图所示：

Leader worker进程和其他worker进程通过动态共享内存进行通信。其他worker进程（包括leader worker进程）把分担查询的结果存储到共享内存中，然后由leader worker进程进行汇总整个查询的结果。所以需要注意的是，由于并行查询需要使用了动态共享内存，所以dynamic_shared_memory_type参数需要设置为none以外的值。

worker进程数量越多，查询性能越高吗

前面也提到，并不是所有的查询都能适用于并行查询。因为考虑到系统的开销以及进程占用资源的情况，也并不是开启的worker数量越多，查询效率越高。下面以对同一个表做同样的查询，然后开启不同的worker数量来测试一下。

开启 1 个worker 进程

highgo=# explain analyze select * from test where n = 4000000;
                                                     QUERY PLAN                                          
            
----------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..59888.81 rows=1 width=4) (actual time=244.328..301.458 rows=1 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on test  (cost=0.00..58888.71 rows=1 width=4) (actual time=268.845..297.372 rows=0 loops=2)
         Filter: (n = 4000000)
         Rows Removed by Filter: 2500000
 Planning time: 0.119 ms
 Execution time: 302.026 ms
(8 rows)

开启 2 个worker 进程

highgo=# explain analyze select * from test where n = 4000000;
                                                     QUERY PLAN                                          
            
----------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..49165.77 rows=1 width=4) (actual time=218.212..218.287 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..48165.67 rows=1 width=4) (actual time=200.619..213.684 rows=0 loops=3)
         Filter: (n = 4000000)
         Rows Removed by Filter: 1666666
 Planning time: 0.117 ms
 Execution time: 219.005 ms

开启 4 个worker 进程

设置4个worker，但是实际上基于成本考虑只启用了3个。

highgo=# explain analyze select * from test where n = 4000000;
                                                     QUERY PLAN                                          
            
---------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..43285.39 rows=1 width=4) (actual time=190.967..191.017 rows=1 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Parallel Seq Scan on test  (cost=0.00..42285.29 rows=1 width=4) (actual time=174.275..182.767 rows=0 loops=4)
         Filter: (n = 4000000)
         Rows Removed by Filter: 1250000
 Planning time: 0.119 ms
 Execution time: 191.817 ms
(8 rows)

通过create table test(n int) with (parallel_workers = 4);强制启动4个worker进程测试。

highgo=# explain analyze select * from test where n = 4000000;
                                                     QUERY PLAN                                          
            
----------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..38749.10 rows=1 width=4) (actual time=181.145..181.315 rows=1 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   ->  Parallel Seq Scan on test  (cost=0.00..37749.00 rows=1 width=4) (actual time=163.565..169.837 rows=0 loops=5)
         Filter: (n = 4000000)
         Rows Removed by Filter: 1000000
 Planning time: 0.050 ms
 Execution time: 185.391 ms

对比查询结果：