PostgreSQL 并行計算解說 之26 - parallel gather | gathermerge - enable leader worker process

标簽

PostgreSQL , cpu 并行 , smp 并行 , 并行計算 , gpu 并行 , 并行過程支援

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#%E8%83%8C%E6%99%AF 背景

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 9.6 并行計算 優化器算法淺析》 《PostgreSQL 11 并行計算算法，參數，強制并行度設定》

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#parallel-gather--gathermerge---enable-leader-worker-process parallel gather | gathermerge - enable leader worker process

PG并行計算架構中，并行任務由計算程序執行，計算程序執行的結果，由GATHER（leader process）收集再轉交下一個節點。

PG有一個開關parallel_leader_participation，允許leader process參與計算任務，即不是空等所有計算程序。

換句話說說，有點像上司和小弟一起幹一樣的活，所有人活都幹完後，上司依舊需要把結果彙總進行下一個環節。

1、parallel_leader_participation設定為ON，表示上司和小弟一起幹活，再将結果彙總進行下一步。

2、parallel_leader_participation設定為OFF，表示上司不幹小弟的活，而是等所有小弟幹完，再将結果彙總進行下一步。

parallel_leader_participation (boolean)

Allows the leader process to execute the query plan under Gather and Gather Merge nodes instead of waiting for worker processes.

The default is on.

Setting this value to off reduces the likelihood that workers will become blocked because the leader is not reading tuples fast enough,

but requires the leader process to wait for worker processes to start up before the first tuples can be produced.

The degree to which the leader can help or hinder performance depends on the plan type, number of workers and query duration.

開啟1個并發時，很好觀察到這個現象。

parallel_leader_participation設定為ON

42915 digoal    20   0 16.844g 160428   4188 R 100.0  0.0   3:36.42 postgres: postgres postgres [local] EXPLAIN    
50013 digoal    20   0 16.829g 144000   2004 R 100.0  0.0   0:19.32 postgres: parallel worker for PID 42915   
           

parallel_leader_participation設定為OFF

50528 digoal    20   0 16.829g 144032   2016 R 100.0  0.0   0:05.26 postgres: parallel worker for PID 42915  
           

資料量：10億

場景	資料量	關閉并行	開啟并行	并行度	開啟并行性能提升倍數
parallel leader process	10億	186 秒	95 秒	1	2 倍

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#1%E5%85%B3%E9%97%AD%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-186-%E7%A7%92 1、關閉并行，耗時： 186 秒。

實際上不是關閉并行，而是關閉parallel_leader_participation

postgres=# set parallel_leader_participation=off;  
SET  
  
postgres=# explain analyze  select * from (select * from table2 order by i) t where i<1000 limit 1000000;  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=174402886.28..174415386.28 rows=1000000 width=4) (actual time=184773.400..185470.626 rows=1000000 loops=1)  
   Workers Planned: 1  
   Workers Launched: 1  
   Single Copy: true  
   ->  Limit  (cost=174402886.28..174415386.28 rows=1000000 width=4) (actual time=184770.319..185060.134 rows=1000000 loops=1)  
         ->  Sort  (cost=174402886.28..176902636.28 rows=999900000 width=4) (actual time=184770.316..184983.480 rows=1000000 loops=1)  
               Sort Key: table2.i  
               Worker 0:  Sort Method: top-N heapsort  Memory: 128466kB  
               ->  Seq Scan on table2  (cost=0.00..16924779.00 rows=999900000 width=4) (actual time=0.084..100683.624 rows=1000000000 loops=1)  
                     Filter: (i < 1000)  
 Planning Time: 0.421 ms  
 Execution Time: 185520.880 ms  
(12 rows)  
           

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#2%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E8%80%97%E6%97%B6-95-%E7%A7%92 2、開啟并行，耗時： 95 秒。

postgres=# set parallel_leader_participation=on;  
SET  
postgres=# explain analyze  select * from (select * from table2 order by i) t where i<1000 limit 1000000;  
                                                                      QUERY PLAN                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=102160553.98..102180553.98 rows=1000000 width=4) (actual time=95138.752..95489.131 rows=1000000 loops=1)  
   ->  Gather Merge  (cost=102160553.98..108042318.69 rows=588176471 width=4) (actual time=95138.750..95412.176 rows=1000000 loops=1)  
         Workers Planned: 1  
         Workers Launched: 1  
         ->  Sort  (cost=102160553.97..103630995.15 rows=588176471 width=4) (actual time=95135.326..95236.241 rows=500706 loops=2)  
               Sort Key: table2.i  
               Sort Method: top-N heapsort  Memory: 128531kB  
               Worker 0:  Sort Method: top-N heapsort  Memory: 128519kB  
               ->  Parallel Seq Scan on table2  (cost=0.00..11777720.18 rows=588176471 width=4) (actual time=0.015..50319.769 rows=500000000 loops=2)  
                     Filter: (i < 1000)  
 Planning Time: 0.097 ms  
 Execution Time: 95545.547 ms  
(12 rows)  
           

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#%E5%85%B6%E4%BB%96%E7%9F%A5%E8%AF%86 其他知識

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執行個體)》 《PostgreSQL dblink異步調用實作 并行hash分片JOIN - 含資料交、并、差 提速案例 - 含dblink VS pg 11 parallel hash join VS pg 11 智能分區JOIN》

暫時不允許并行的場景(将來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://github.com/digoal/blog/blob/master/201903/20190318_01.md#%E5%8F%82%E8%80%83 參考

https://www.postgresql.org/docs/11/parallel-plans.html 《PostgreSQL 11 preview - 并行計算 增強 彙總》 《PostgreSQL 10 自定義并行計算聚合函數的原理與實踐 - (含array_agg合并多個數組為單個一進制數組的例子)》

https://github.com/digoal/blog/blob/master/201903/20190318_01.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機