作者
digoal
日期
2019-03-17
标签
PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持
背景
PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持27余种场景的并行计算。
```
parallel seq scan
parallel index scan
parallel index only scan
parallel bitmap scan
parallel filter
parallel hash agg
parallel group agg
parallel cte
parallel subquery
parallel create table
parallel create index
parallel select into
parallel CREATE MATERIALIZED VIEW
parallel 排序 : gather merge
parallel nestloop join
parallel hash join
parallel merge join
parallel 自定义并行聚合
parallel 自定义并行UDF
parallel append
parallel append merge
parallel union all
parallel fdw table scan
parallel partition join
parallel partition agg
parallel gather
parallel gather merge
parallel rc 并行
parallel rr 并行
parallel GPU 并行
parallel unlogged table
lead parallel
```
接下来进行一一介绍。
关键知识请先自行了解：
1、优化器自动并行度算法 CBO
《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
parallel union all
多段并行执行，或者多段并行执行并排序
parallel union all 实际上用到是parallel append优化method。
如果多段执行的结果需要排序，那么优化器可以在每个段内返回有序结果，可以使用归并排序（类似merge sort, gather merge）(parallel append merge)。
《PostgreSQL 并行计算解说 之23 - parallel append merge》
数据量：10亿
场景 | 数据量 | 关闭并行 | 开启并行 | 并行度 | 开启并行性能提升倍数
---|---|---|---|---|---
parallel union all | 10亿 | 99 秒 | 5.6 秒 | 24 | 17.68 倍
```
postgres=# show max_worker_processes ;
max_worker_processes
128
(1 row)
postgres=# set min_parallel_table_scan_size =0;
postgres=# set min_parallel_index_scan_size =0;
postgres=# set parallel_tuple_cost =0;
postgres=# set parallel_setup_cost =0;
postgres=# set max_parallel_workers=128;
postgres=# set max_parallel_workers_per_gather =24;
postgres=# set enable_parallel_hash =on;
postgres=# set enable_parallel_append =on;
postgres=# set enable_partitionwise_aggregate =off;
postgres=# set work_mem ='128MB';
```
1、关闭并行，耗时： 99 秒。
```
postgres=# set max_parallel_workers_per_gather =0;
postgres=# set enable_parallel_append =off;
explain select * from (
select * from ccc0 union all
select * from ccc1 union all
select * from ccc2 union all
select * from ccc3 union all
select * from ccc4 union all
select * from ccc5 union all
select * from ccc6 union all
select * from ccc7 union all
select * from ccc8 union all
select * from ccc9 union all
select * from ccc10 union all
select * from ccc11 union all
select * from ccc12 union all
select * from ccc13 union all
select * from ccc14 union all
select * from ccc15 union all
select * from ccc16 union all
select * from ccc17 union all
select * from ccc18 union all
select * from ccc19 union all
select * from ccc20 union all
select * from ccc21 union all
select * from ccc22 union all
select * from ccc23
) as t
order by order_id limit 10;
QUERY PLAN复制
Limit (cost=42015064.65..42015064.67 rows=10 width=48)
-> Sort (cost=42015064.65..44515064.93 rows=1000000114 width=48)
Sort Key: ccc0.order_id
-> Append (cost=0.00..20405421.71 rows=1000000114 width=48)
-> Seq Scan on ccc0 (cost=0.00..641839.96 rows=41663296 width=48)
-> Seq Scan on ccc1 (cost=0.00..625842.88 rows=40624888 width=48)
-> Seq Scan on ccc2 (cost=0.00..722107.36 rows=46873636 width=48)
-> Seq Scan on ccc3 (cost=0.00..545575.32 rows=35414332 width=48)
-> Seq Scan on ccc4 (cost=0.00..657705.92 rows=42693192 width=48)
-> Seq Scan on ccc5 (cost=0.00..609836.16 rows=39585616 width=48)
-> Seq Scan on ccc6 (cost=0.00..625934.32 rows=40630732 width=48)
-> Seq Scan on ccc7 (cost=0.00..673876.80 rows=43742880 width=48)
-> Seq Scan on ccc8 (cost=0.00..601729.04 rows=39059604 width=48)
-> Seq Scan on ccc9 (cost=0.00..609919.96 rows=39591296 width=48)
-> Seq Scan on ccc10 (cost=0.00..674124.76 rows=43758976 width=48)
-> Seq Scan on ccc11 (cost=0.00..529544.24 rows=34373924 width=48)
-> Seq Scan on ccc12 (cost=0.00..818443.04 rows=53127004 width=48)
-> Seq Scan on ccc13 (cost=0.00..674104.80 rows=43757680 width=48)
-> Seq Scan on ccc14 (cost=0.00..786195.28 rows=51033728 width=48)
-> Seq Scan on ccc15 (cost=0.00..609709.04 rows=39577604 width=48)
-> Seq Scan on ccc16 (cost=0.00..633745.96 rows=41137896 width=48)
-> Seq Scan on ccc17 (cost=0.00..673951.76 rows=43747376 width=48)
-> Seq Scan on ccc18 (cost=0.00..802394.72 rows=52085272 width=48)
-> Seq Scan on ccc19 (cost=0.00..529621.20 rows=34378920 width=48)
-> Seq Scan on ccc20 (cost=0.00..642042.32 rows=41676432 width=48)
-> Seq Scan on ccc21 (cost=0.00..401251.50 rows=26046150 width=48)
-> Seq Scan on ccc22 (cost=0.00..673891.04 rows=43743804 width=48)
-> Seq Scan on ccc23 (cost=0.00..642033.76 rows=41675876 width=48)
(28 rows)
select * from (
select * from ccc0 union all
select * from ccc1 union all
select * from ccc2 union all
select * from ccc3 union all
select * from ccc4 union all
select * from ccc5 union all
select * from ccc6 union all
select * from ccc7 union all
select * from ccc8 union all
select * from ccc9 union all
select * from ccc10 union all
select * from ccc11 union all
select * from ccc12 union all
select * from ccc13 union all
select * from ccc14 union all
select * from ccc15 union all
select * from ccc16 union all
select * from ccc17 union all
select * from ccc18 union all
select * from ccc19 union all
select * from ccc20 union all
select * from ccc21 union all
select * from ccc22 union all
select * from ccc23
) as t
order by order_id limit 10;
order_id | cust_id | status
----------+---------+--------
1 | 649 |
2 | 226 |
3 | 816 |
4 | 844 |
5 | 827 |
6 | 456 |
7 | 810 |
8 | 365 |
9 | 49 |
10 | 75 |
(10 rows)
Time: 98991.924 ms (01:38.992)
```
2、开启并行，耗时： 5.6 秒。
```
postgres=# set max_parallel_workers_per_gather =24;
postgres=# set enable_parallel_append =on;
explain
/+
Parallel(ccc0 24 hard)
Parallel(ccc1 0 hard)
Parallel(ccc2 0 hard)
Parallel(ccc3 0 hard)
Parallel(ccc4 0 hard)
Parallel(ccc5 0 hard)
Parallel(ccc6 0 hard)
Parallel(ccc7 0 hard)
Parallel(ccc8 0 hard)
Parallel(ccc9 0 hard)
Parallel(ccc10 0 hard)
Parallel(ccc11 0 hard)
Parallel(ccc12 0 hard)
Parallel(ccc13 0 hard)
Parallel(ccc14 0 hard)
Parallel(ccc15 0 hard)
Parallel(ccc16 0 hard)
Parallel(ccc17 0 hard)
Parallel(ccc18 0 hard)
Parallel(ccc19 0 hard)
Parallel(ccc20 0 hard)
Parallel(ccc21 0 hard)
Parallel(ccc22 0 hard)
Parallel(ccc23 0 hard)
/
select * from (
select * from ccc0 union all
select * from ccc1 union all
select * from ccc2 union all
select * from ccc3 union all
select * from ccc4 union all
select * from ccc5 union all
select * from ccc6 union all
select * from ccc7 union all
select * from ccc8 union all
select * from ccc9 union all
select * from ccc10 union all
select * from ccc11 union all
select * from ccc12 union all
select * from ccc13 union all
select * from ccc14 union all
select * from ccc15 union all
select * from ccc16 union all
select * from ccc17 union all
select * from ccc18 union all
select * from ccc19 union all
select * from ccc20 union all
select * from ccc21 union all
select * from ccc22 union all
select * from ccc23
) as t
order by order_id limit 10;
DEBUG: pg_hint_plan:
used hint:
Parallel(ccc0 24 hard)
Parallel(ccc1 0 hard)
Parallel(ccc10 0 hard)
Parallel(ccc11 0 hard)
Parallel(ccc12 0 hard)
Parallel(ccc13 0 hard)
Parallel(ccc14 0 hard)
Parallel(ccc15 0 hard)
Parallel(ccc16 0 hard)
Parallel(ccc17 0 hard)
Parallel(ccc18 0 hard)
Parallel(ccc19 0 hard)
Parallel(ccc2 0 hard)
Parallel(ccc20 0 hard)
Parallel(ccc21 0 hard)
Parallel(ccc22 0 hard)
Parallel(ccc23 0 hard)
Parallel(ccc3 0 hard)
Parallel(ccc4 0 hard)
Parallel(ccc5 0 hard)
Parallel(ccc6 0 hard)
Parallel(ccc7 0 hard)
Parallel(ccc8 0 hard)
Parallel(ccc9 0 hard)
not used hint:
duplication hint:
error hint:
QUERY PLAN复制
Limit (cost=1927178.78..1927179.04 rows=10 width=48)
-> Gather Merge (cost=1927178.78..27750629.70 rows=1000000128 width=48)
Workers Planned: 24
-> Sort (cost=1927178.20..2031344.88 rows=41666672 width=48)
Sort Key: ccc12.order_id
-> Parallel Append (cost=0.00..1026776.40 rows=41666672 width=48)
-> Seq Scan on ccc12 (cost=0.00..818443.04 rows=53127004 width=48)
-> Seq Scan on ccc18 (cost=0.00..802394.72 rows=52085272 width=48)
-> Seq Scan on ccc14 (cost=0.00..786195.28 rows=51033728 width=48)
-> Seq Scan on ccc2 (cost=0.00..722107.36 rows=46873636 width=48)
-> Seq Scan on ccc10 (cost=0.00..674124.76 rows=43758976 width=48)
-> Seq Scan on ccc13 (cost=0.00..674104.80 rows=43757680 width=48)
-> Seq Scan on ccc17 (cost=0.00..673951.76 rows=43747376 width=48)
-> Seq Scan on ccc22 (cost=0.00..673891.04 rows=43743804 width=48)
-> Seq Scan on ccc7 (cost=0.00..673876.80 rows=43742880 width=48)
-> Seq Scan on ccc4 (cost=0.00..657705.92 rows=42693192 width=48)
-> Seq Scan on ccc20 (cost=0.00..642042.32 rows=41676432 width=48)
-> Seq Scan on ccc23 (cost=0.00..642033.76 rows=41675876 width=48)
-> Seq Scan on ccc16 (cost=0.00..633745.96 rows=41137896 width=48)
-> Seq Scan on ccc6 (cost=0.00..625934.32 rows=40630732 width=48)
-> Seq Scan on ccc1 (cost=0.00..625842.88 rows=40624888 width=48)
-> Seq Scan on ccc9 (cost=0.00..609919.96 rows=39591296 width=48)
-> Seq Scan on ccc5 (cost=0.00..609836.16 rows=39585616 width=48)
-> Seq Scan on ccc15 (cost=0.00..609709.04 rows=39577604 width=48)
-> Seq Scan on ccc8 (cost=0.00..601729.04 rows=39059604 width=48)
-> Seq Scan on ccc3 (cost=0.00..545575.32 rows=35414332 width=48)
-> Seq Scan on ccc19 (cost=0.00..529621.20 rows=34378920 width=48)
-> Seq Scan on ccc11 (cost=0.00..529544.24 rows=34373924 width=48)
-> Seq Scan on ccc21 (cost=0.00..401251.50 rows=26046150 width=48)
-> Parallel Seq Scan on ccc0 (cost=0.00..0.00 rows=1735971 width=48)
(30 rows)
postgres=# /+
Parallel(ccc0 24 hard)
Parallel(ccc1 0 hard)
Parallel(ccc2 0 hard)
Parallel(ccc3 0 hard)
Parallel(ccc4 0 hard)
Parallel(ccc5 0 hard)
Parallel(ccc6 0 hard)
Parallel(ccc7 0 hard)
Parallel(ccc8 0 hard)
Parallel(ccc9 0 hard)
Parallel(ccc10 0 hard)
Parallel(ccc11 0 hard)
Parallel(ccc12 0 hard)
Parallel(ccc13 0 hard)
Parallel(ccc14 0 hard)
Parallel(ccc15 0 hard)
Parallel(ccc16 0 hard)
Parallel(ccc17 0 hard)
Parallel(ccc18 0 hard)
Parallel(ccc19 0 hard)
Parallel(ccc20 0 hard)
Parallel(ccc21 0 hard)
Parallel(ccc22 0 hard)
Parallel(ccc23 0 hard)
/
select * from (
select * from ccc0 union all
select * from ccc1 union all
select * from ccc2 union all
select * from ccc3 union all
select * from ccc4 union all
select * from ccc5 union all
select * from ccc6 union all
select * from ccc7 union all
select * from ccc8 union all
select * from ccc9 union all
select * from ccc10 union all
select * from ccc11 union all
select * from ccc12 union all
select * from ccc13 union all
select * from ccc14 union all
select * from ccc15 union all
select * from ccc16 union all
select * from ccc17 union all
select * from ccc18 union all
select * from ccc19 union all
select * from ccc20 union all
select * from ccc21 union all
select * from ccc22 union all
select * from ccc23
) as t
order by order_id limit 10;
DEBUG: pg_hint_plan:
used hint:
Parallel(ccc0 24 hard)
Parallel(ccc1 0 hard)
Parallel(ccc10 0 hard)
Parallel(ccc11 0 hard)
Parallel(ccc12 0 hard)
Parallel(ccc13 0 hard)
Parallel(ccc14 0 hard)
Parallel(ccc15 0 hard)
Parallel(ccc16 0 hard)
Parallel(ccc17 0 hard)
Parallel(ccc18 0 hard)
Parallel(ccc19 0 hard)
Parallel(ccc2 0 hard)
Parallel(ccc20 0 hard)
Parallel(ccc21 0 hard)
Parallel(ccc22 0 hard)
Parallel(ccc23 0 hard)
Parallel(ccc3 0 hard)
Parallel(ccc4 0 hard)
Parallel(ccc5 0 hard)
Parallel(ccc6 0 hard)
Parallel(ccc7 0 hard)
Parallel(ccc8 0 hard)
Parallel(ccc9 0 hard)
not used hint:
duplication hint:
error hint:
order_id | cust_id | status
----------+---------+--------
1 | 649 |
2 | 226 |
3 | 816 |
4 | 844 |
5 | 827 |
6 | 456 |
7 | 810 |
8 | 365 |
9 | 49 |
10 | 75 |
(10 rows)
Time: 5623.939 ms (00:05.624)
```
union all的parallel append没有很好的并行度控制，如果要消除内部查询的并行度，将所有分段并行起来，分区表的话使用hint可以解决。而union all的情况下，外部 alias无法强制或影响parallel append的并行度。
所以使用的并行度这样来设置：
1、有且只有一个内部表设置并行度
2、并行度可以设置为UNION ALL子句数一致，不建议超过CPU核数的一半。
其他知识
1、优化器自动并行度算法 CBO
《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
2、function, op 识别是否支持parallel
postgres=# select proparallel,proname from pg_proc;
proparallel | proname
-------------+----------------------------------------------
s | boolin
s | boolout
s | byteain
s | byteaout
3、subquery mapreduce unlogged table
对于一些情况，如果期望简化优化器对非常非常复杂的SQL并行优化的负担，可以自己将SQL拆成几段，中间结果使用unlogged table保存，类似mapreduce的思想。unlogged table同样支持parallel 计算。
4、vacuum，垃圾回收并行。
5、dblink 异步调用并行
《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》
《PostgreSQL 相似搜索分布式架构设计与实践 - dblink异步调用与多机并行(远程 游标+记录 UDF实例)》
暂时不允许并行的场景(将来PG会继续扩大支持范围)：
1、修改行，锁行，除了create table as , select into, create mview这几个可以使用并行。
2、query 会被中断时，例如cursor , loop in PL/SQL ，因为涉及到中间处理，所以不建议开启并行。
3、paralle unsafe udf ，这种UDF不会并行
4、嵌套并行(udf (内部query并行))，外部调用这个UDF的SQL不会并行。（主要是防止large parallel workers ）
5、SSI 隔离级别
参考
https://www.postgresql.org/docs/11/parallel-plans.html
《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
《PostgreSQL 11 preview - 并行计算 增强 汇总》
《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
