PostgreSQL并行查询体验

IT那活儿发布于2023-01-11 13:20 / 1495人阅读

PostgreSQL并行查询体验

点击上方蓝字关注我们

背景介绍

数据库查询性能是评价数据库的核心指标，随着现代服务器的资源配置越来越强大，为充分发挥硬件性能提升数据库查询性能，可以通过把操作分解成多个并行执行来实现。

什么是并行查询？

并行查询可以利用多个CPU同时参与查询处理工作，充分发挥硬件性能，以提高运行速度。

如何使用并行查询？

在测试的版本（Version 12.1）中，数据库默认启用了2路并行。

此功能主要涉及以下几个参数和默认值：

max_worker_processes：数据库支持的最大后台进程数，默认值为8。

max_parallel_workers：数据库支持的最大并行查询数，实际分配的数量受上面的max_worker_processes参数限制，默认值为8。

max_parallel_workers_per_gather：目前可以启动的并行数，实际分配的数量受上面的max_parallel_workers参数限制，max_parallel_workers_per_gather默认值为2。

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

不是，目前在笔者的测试场景中发现，worker进程可以设置的很高，但是数据库会根据实际需要分配worker，比如设置了max_parallel_workers_per_gather=16，但实际上执行计划中可能分配不到这么多的worker。性能不会随着并行度的提升而线性增加。建议设置的数值不超过服务器CPU数量。

如果服务器资源比较紧张，建议在个别重要查询的会话级别（如夜间批量同步任务）中多带带提高并发数。

怎么看到并行的任务？

在Linux中可以通过ps和top命令查看到相应的进程和相关连的worker进程：

系统中有以下进程，第1个是leader进程，2个worker进程：

postgres 3279 2158 2 01:59 ? 00:04:51 postgres: postgres music [local] SELECT

postgres 12501 2158 93 04:47 ? 00:00:02 postgres: parallel worker for PID 3279

postgres 12502 2158 93 04:47 ? 00:00:02 postgres: parallel worker for PID 3279

top中也可以抓的到进程的性能数据：

top - 04:51:16 up 6:24, 2 users, load average: 0.10, 0.06, 0.10

Tasks: 147 total, 4 running, 143 sleeping, 0 stopped, 0 zombie

%Cpu(s): 46.0 us, 6.5 sy, 0.0 ni, 47.4 id, 0.0 wa, 0.0 hi, 0.1 si, 0.0 st

KiB Mem : 14.0/3861364 [|||||||||||||| ]

KiB Swap: 20.1/2097148 [|||||||||||||||||||| ]

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

3279 postgres 20 0 404368 144000 140976 R 71.4 3.7 5:00.69 postgres: postgres music [local] SELECT

12693 postgres 20 0 400824 6056 4608 R 71.4 0.2 0:02.15 postgres: parallel worker for PID 3279

12694 postgres 20 0 400824 6056 4608 R 70.8 0.2 0:02.13 postgres: parallel worker for PID 3279

下面用图来描述一下相关进程的关系，在多路并发的情况下，会根据并发数创建新的worker进程来分担并发任务：

能否看到更详细的执行情况？

能。在执行计划中可以看得到。

我们看一个实际的执行计划：

首先将并行度提升到6路：

music=# set max_parallel_workers_per_gather = 6;

SET

看一下实际的执行计划

music=# EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM rock WHERE name = Bon_Jovi;

QUERY PLAN

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

Gather (cost=1000.00..471830.76 rows=1 width=25) (actual time=0.270..2811.900 rows=1 loops=1)

Output: id, name, create_time

Workers Planned: 6

Workers Launched: 6

-> Parallel Seq Scan on public.rock (cost=0.00..470830.66 rows=1 width=25) (actual time=2374.703..2775.358 rows=0 loops=7)

Output: id, name, create_time

Filter: ((rock.name)::text = Bon_Jovi::text)

Rows Removed by Filter: 7142857

Worker 0: actual time=2756.906..2756.906 rows=0 loops=1

Worker 1: actual time=2765.581..2765.581 rows=0 loops=1

Worker 2: actual time=2769.584..2769.584 rows=0 loops=1

Worker 3: actual time=2778.855..2778.855 rows=0 loops=1

Worker 4: actual time=2752.952..2752.952 rows=0 loops=1

Worker 5: actual time=2799.014..2799.014 rows=0 loops=1

Planning Time: 0.048 ms

Execution Time: 2811.918 ms

从下往上看，最下层的节点，显示了6个Worker的执行信息信息，包括启动时间、总时间等。他们都属于并行顺序查询节点（Parallel Seq Scan开头那一行）。再往上提示，一共规划了6个节点，实际分配了6个节点。第一行是Gather节点的成本和时间等信息，Gather节点汇总了各个节点读取的数据。

下面在开头的图里进一步详细描述一下该语句的执行详情：

设置了并行就会启动并行查询？

有时候会出现设置了并发但是不会真正启动并发进程的情况：

music=# EXPLAIN SELECT * FROM rock WHERE id = 1;

QUERY PLAN

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

Index Scan using idx_rock_id on rock (cost=0.56..8.58 rows=1 width=25)

Index Cond: (id = 1)

(2 rows)

music=# EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM rock WHERE id = 1;

QUERY PLAN

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

Index Scan using idx_rock_id on public.rock (cost=0.56..8.58 rows=1 width=25) (actual time=0.087..0.088 rows=1 loops=1)

Output: id, name, create_time

Index Cond: (rock.id = 1)

Planning Time: 0.079 ms

Execution Time: 0.111 ms

(5 rows)

采用了索引扫描，由于cost的值比较低，尽管有设置并发，但是cost未触发到并发所需的条件，所以说不会启用并发。也有可能因为评估不需要那么多的并发而启动一部分数量的worker（后文有演示）

触发并发的条件有以下两个，当以查询的代价触发到以下两个参数的值时，才会触发并行：

parallel_setup_cost = 1000

parallel_tuple_cost = 0.1

在本次查询中，索引扫描的cost值总体才到8.58，因此未触发并行机制。

同样，在bitmap等查询场景中，成本达不到cost值触发条件的话，均不会启动并发查询，如：

在笔者使用体验过程中，发现也有并发度按需分配的情况：

music=# set max_parallel_workers_per_gather = 6;

SET

music=# EXPLAIN ANALYSE SELECT * FROM rock WHERE id = 1 OR id = 2;

QUERY PLAN

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

Bitmap Heap Scan on rock (cost=9.15..17.16 rows=2 width=25) (actual time=0.019..0.020 rows=2 loops=1)

Recheck Cond: ((id = 1) OR (id = 2))

Heap Blocks: exact=1

-> BitmapOr (cost=9.15..9.15 rows=2 width=0) (actual time=0.015..0.015 rows=0 loops=1)

-> Bitmap Index Scan on idx_rock_id (cost=0.00..4.57 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)

Index Cond: (id = 1)

-> Bitmap Index Scan on idx_rock_id (cost=0.00..4.57 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

Index Cond: (id = 2)

Planning Time: 0.059 ms

Execution Time: 0.040 ms

(10 rows)

索引并行扫描的场景触发并行的情况可以看一下以下案例：

music=# EXPLAIN (ANALYZE,VERBOSE) SELECT count(*) FROM rock WHERE id < 10000000;

QUERY PLAN

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

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

Finalize Aggregate (cost=279145.34..279145.35 rows=1 width=8) (actual time=3482.259..3482.259 rows=1 loops=1)

Output: count(*)

-> Gather (cost=279144.71..279145.32 rows=6 width=8) (actual time=3453.374..3487.724 rows=7 loops=1)

Output: (PARTIAL count(*))

Workers Planned: 6

Workers Launched: 6

-> Partial Aggregate (cost=278144.71..278144.72 rows=1 width=8) (actual time=3436.943..3436.943 rows=1 loops=7)

Output: PARTIAL count(*)

Worker 0: actual time=3427.536..3427.536 rows=1 loops=1

Worker 1: actual time=3440.951..3440.951 rows=1 loops=1

Worker 2: actual time=3433.833..3433.833 rows=1 loops=1

Worker 3: actual time=3438.579..3438.579 rows=1 loops=1

Worker 4: actual time=3438.378..3438.379 rows=1 loops=1

Worker 5: actual time=3426.513..3426.513 rows=1 loops=1

-> Parallel Index Only Scan using idx_rock_id on public.rock (cost=0.56..273988.62 rows=1662437 width=0) (

actual time=2.701..3281.001 rows=1428571 loops=7)

Output: id

Index Cond: (rock.id < 10000000)

Heap Fetches: 9999999

Worker 0: actual time=0.605..3271.022 rows=1466562 loops=1

Worker 1: actual time=0.170..3286.350 rows=1449726 loops=1

Worker 2: actual time=0.424..3291.647 rows=1301496 loops=1

Worker 3: actual time=3.191..3258.715 rows=1658712 loops=1

Worker 4: actual time=12.856..3273.552 rows=1509750 loops=1

Worker 5: actual time=1.608..3284.193 rows=1294323 loops=1

Planning Time: 2.203 ms

Execution Time: 3488.023 ms

(26 rows)

从下往上看分别进行了：并行索引扫描---并行聚合---最终聚合

首先启动了6个worker进程进行了索引条件扫描，在数据库估算开销时认为每一个worker的扫描成本都高于1000，因此触发了并发。当每一个worker将数据扫描后，进行分别处理后，最终给gather汇总整合输出。

执行过程可参考下图：

设置了并行参数后，一定会启动并行吗？

然后对几类常见的查询场景进行了测试，除了在上面的顺序扫描、索引扫描之外，在聚合、bitmap、嵌套循环、merge join、hash join和索引创建场景，在数据量足够大的场景下，均可触发启动并行。

一定会启动设定的并行度吗？

不一定，其中在嵌套循环（Nest loop）场景中，发现尽管我们设置了6路并发，但是实际根据需要仅启动了5路，在实战中遇到此类情况，不要疑惑，也属正常，勤俭节约是美德：

music=# show max_parallel_workers_per_gather;

max_parallel_workers_per_gather

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

(1 row)

music=# EXPLAIN (ANALYZE,VERBOSE) SELECT pop.name FROM rock,pop WHERE rock.id = pop.id AND pop.id < 10000;

QUERY PLAN

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

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

Gather (cost=1000.57..174222.54 rows=18002 width=13) (actual time=5.881..3276.311 rows=19998 loops=1)

Output: pop.name

Workers Planned: 5

Workers Launched: 5

-> Nested Loop (cost=0.56..171422.34 rows=3600 width=13) (actual time=3.189..3258.998 rows=3333 loops=6)

Output: pop.name

Worker 0: actual time=2.591..3259.895 rows=1850 loops=1

Worker 1: actual time=0.180..3251.631 rows=4081 loops=1

Worker 2: actual time=1.344..3261.433 rows=555 loops=1

Worker 3: actual time=8.603..3262.411 rows=3330 loops=1

Worker 4: actual time=0.821..3259.297 rows=4623 loops=1

-> Parallel Seq Scan on public.pop (cost=0.00..141721.20 rows=3600 width=17) (actual time=1.020..3223.285 rows=3333 loops

=6)

Output: pop.id, pop.name

Filter: (pop.id < 10000)

Rows Removed by Filter: 2663334

Worker 0: actual time=0.054..3237.921 rows=1850 loops=1

Worker 1: actual time=0.049..3215.862 rows=4081 loops=1

Worker 2: actual time=0.102..3236.592 rows=555 loops=1

Worker 3: actual time=0.296..3235.327 rows=3330 loops=1

Worker 4: actual time=0.055..3188.732 rows=4623 loops=1

-> Index Only Scan using idx_rock_id on public.rock (cost=0.56..8.24 rows=1 width=4) (actual time=0.008..0.010 r

ows=1 loops=19998)

Output: rock.id

Index Cond: (rock.id = pop.id)

Heap Fetches: 19998

Worker 0: actual time=0.011..0.011 rows=1 loops=1850

Worker 1: actual time=0.008..0.008 rows=1 loops=4081

Worker 2: actual time=0.044..0.044 rows=1 loops=555

Worker 3: actual time=0.007..0.007 rows=1 loops=3330

Worker 4: actual time=0.006..0.015 rows=1 loops=4623

Planning Time: 0.579 ms

Execution Time: 3277.727 ms

(31 rows)

以上语句在实际工作中一般需要添加以下索引，提高一下查询速度，当添加玩索引之后，发现查询的代价下降的很少，以至于不需要触发那么多并发进程了：

music=# CREATE INDEX idx_pop_id ON pop(id);

WARNING: concurrent insert in progress within table "b"

CREATE INDEX

music=# set max_parallel_workers_per_gather = 6;

SET

music=# EXPLAIN (ANALYZE,VERBOSE) SELECT pop.name FROM rock,pop WHERE rock.id = pop.id AND pop.id < 10000;

QUERY PLAN

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

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

Gather (cost=1388.64..108804.88 rows=20599 width=13) (actual time=1.316..35.770 rows=19998 loops=1)

Output: pop.name

Workers Planned: 3

Workers Launched: 3

-> Nested Loop (cost=388.64..105744.98 rows=6645 width=13) (actual time=0.341..27.475 rows=5000 loops=4)

Output: pop.name

Worker 0: actual time=0.095..26.337 rows=5365 loops=1

Worker 1: actual time=0.091..26.641 rows=5753 loops=1

Worker 2: actual time=0.101..28.578 rows=3145 loops=1

-> Parallel Bitmap Heap Scan on public.pop (cost=388.08..51229.82 rows=6645 width=17) (actual time=0.298..1.265 rows=5000 loops=4)

Output: pop.id, pop.name

Recheck Cond: (pop.id < 10000)

Heap Blocks: exact=31

Worker 0: actual time=0.044..1.111 rows=5365 loops=1

Worker 1: actual time=0.044..1.205 rows=5753 loops=1

Worker 2: actual time=0.049..0.681 rows=3145 loops=1

-> Bitmap Index Scan on idx_pop_id (cost=0.00..382.93 rows=20599 width=0) (actual time=1.012..1.012 rows=19998 loops=1)

Index Cond: (pop.id < 10000)

-> Index Only Scan using idx_rock_id on public.rock (cost=0.56..8.19 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=19998)

Output: rock.id

Index Cond: (rock.id = pop.id)

Heap Fetches: 19998

Worker 0: actual time=0.004..0.004 rows=1 loops=5365

Worker 1: actual time=0.003..0.004 rows=1 loops=5753

Worker 2: actual time=0.004..0.004 rows=1 loops=3145

Planning Time: 0.264 ms

Execution Time: 37.025 ms

(27 rows)

总结

1、并行查询在优化器评估执行计划的代价后，在设定的max_parallel_workers_per_gather参数值范围内按需启动（启动一部分或不启动）work并发进程进行查询。

2、并行查询对数据量大的查询效果明显。

3、并行创建索引的时候，除了设置max_parallel_workers_per_gather参数之外，还需要配置max_parallel_maintenance_workers参数来配合处理工作。

4、并行度不是越大越好，尽管可以强制设置并行，但是建议不要在生产环境中冒险。

5、可以在会话级别启动并行，主要用于数据量较大、并且及时性较强的任务中较好，如果第一次使用，需要密切关注服务器整体性能负载情况。

END

云服务器 GPU云服务器并行查询 postgresql 查询优化 postgresql 慢查询 postgresql 空间查询

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129987.html

PostgreSQL9.6：Parallel sequential scans 初体验

摘要：一关于参数这个参数配置决定了每个并行操作允许的最大后台进程数，这个值的设置受后台进程数参数限制。查看并行顺序扫描进程备注图中可看到出现了四个进程。三测试测试之前先把的数据库关了，在确保相等情况下进行测试。 Oracle 支持强大的并行功能，创建索引，表分析，数据加载时可以开启并行，这项功能让很多数据库产品垂涎，作为开源数据库 PostgreSQL 在并行方面也在努力尝试，很早之前 P...

wangdai 2019-07-25 12:34 评论0 收藏0
新书推荐 |《PostgreSQL实战》出版(提供样章下载)

摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 评论0 收藏0
构建可扩展的PostgreSQL解决方案

摘要：这可以通过负载平衡来实现数据分片当问题不是并发查询的数量，而是数据库的大小和单个查询的速度时，可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑愿码Slogan | 连接每个程序员的故事网站 | http://chaindesk.cn...

jonh_felix 2019-07-25 12:38 评论0 收藏0
构建可扩展的PostgreSQL解决方案

摘要：这可以通过负载平衡来实现数据分片当问题不是并发查询的数量，而是数据库的大小和单个查询的速度时，可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑愿码Slogan | 连接每个程序员的故事网站 | http://chaindesk.cn...

FrozenMap 2019-06-25 19:12 评论0 收藏0
你应该使用哪个云数据库？

摘要：云计算云计算的基本原则是采用一次性和可更换的多台机器，这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。云数据库属于相同的类别，而新系统明显倾向于并行优先。与非云系统相比，云计算系统向数据库应用程序公开资源利用控制要常见得多。云计算的基本原则是采用一次性和可更换的多台机器，这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。传统数据库大致可以分为并行优先(例如Mo...

wuaiqiu 2021-11-15 11:36 评论0 收藏0