背景介绍
数据库查询性能是评价数据库的核心指标,随着现代服务器的资源配置越来越强大,为充分发挥硬件性能提升数据库查询性能,可以通过把操作分解成多个并行执行来实现。
什么是并行查询?
如何使用并行查询?
在测试的版本(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进程数量越多,查询性能越高吗?
怎么看到并行的任务?
系统中有以下进程,第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
能否看到更详细的执行情况?
首先将并行度提升到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
设置了并行就会启动并行查询?
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汇总整合输出。
执行过程可参考下图:
设置了并行参数后,一定会启动并行吗?
一定会启动设定的并行度吗?
music=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
6
(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)
总结
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129987.html
摘要:一关于参数这个参数配置决定了每个并行操作允许的最大后台进程数,这个值的设置受后台进程数参数限制。查看并行顺序扫描进程备注图中可看到出现了四个进程。三测试测试之前先把的数据库关了,在确保相等情况下进行测试。 Oracle 支持强大的并行功能,创建索引,表分析,数据加载时可以开启并行,这项功能让很多数据库产品垂涎, 作为开源数据库 PostgreSQL 在并行方面也在努力尝试,很早之前 P...
摘要:作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写,国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版,本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结,本书的另一位作者张文升拥有丰富的PostgreSQL运维经验,目前就职于探探科技任首席PostgreS...
摘要:这可以通过负载平衡来实现数据分片当问题不是并发查询的数量,而是数据库的大小和单个查询的速度时,可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑 愿码Slogan | 连接每个程序员的故事 网站 | http://chaindesk.cn...
摘要:这可以通过负载平衡来实现数据分片当问题不是并发查询的数量,而是数据库的大小和单个查询的速度时,可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑 愿码Slogan | 连接每个程序员的故事 网站 | http://chaindesk.cn...
摘要:云计算云计算的基本原则是采用一次性和可更换的多台机器,这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。云数据库属于相同的类别,而新系统明显倾向于并行优先。与非云系统相比,云计算系统向数据库应用程序公开资源利用控制要常见得多。 云计算的基本原则是采用一次性和可更换的多台机器,这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。传统数据库大致可以分为并行优先(例如Mo...
阅读 1229·2023-01-11 13:20
阅读 1536·2023-01-11 13:20
阅读 991·2023-01-11 13:20
阅读 1644·2023-01-11 13:20
阅读 3952·2023-01-11 13:20
阅读 2447·2023-01-11 13:20
阅读 1284·2023-01-11 13:20
阅读 3438·2023-01-11 13:20