天天看點
傳回

PostgreSQL 并行計算解說 之9 - parallel 自定義并行聚合

标簽

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

https://github.com/digoal/blog/blob/master/201903/20190317_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 union                  
                  
parallel fdw table scan                  
                  
parallel partition join                  
                  
parallel partition agg                  
                  
parallel gather          
  
parallel gather merge  
                  
parallel rc 并行                  
                  
parallel rr 并行                  
                  
parallel GPU 并行                  
                  
parallel unlogged table

接下來進行一一介紹。

關鍵知識請先自行了解：

1、優化器自動并行度算法 CBO

《PostgreSQL 9.6 并行計算 優化器算法淺析》 《PostgreSQL 11 并行計算算法，參數，強制并行度設定》

https://github.com/digoal/blog/blob/master/201903/20190317_01.md#parallel-%E8%87%AA%E5%AE%9A%E4%B9%89%E5%B9%B6%E8%A1%8C%E8%81%9A%E5%90%88 parallel 自定義并行聚合

自定義并行聚合

資料量：10億。

場景 資料量 關閉并行 開啟并行 并行度 開啟并行性能提升倍數
自定義并行聚合1(求 distinct 數組 字段元素、以及count distinct) 10 億 298.8 秒 8.7 秒 36 34.3 倍
自定義并行聚合2(求 distinct 普通 字段元素、以及count distinct) 96.5 秒 3.43 秒 28 倍

https://github.com/digoal/blog/blob/master/201903/20190317_01.md#%E8%87%AA%E5%AE%9A%E4%B9%89%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0%E4%BE%8B%E5%AD%901%E5%90%88%E5%B9%B6%E6%95%B0%E7%BB%84%E5%B9%B6%E5%8E%BB%E9%87%8D 自定義聚合函數例子1，合并數組，并去重：

數組去重函數：

postgres=# create or replace function uniq(int2[]) returns int2[] as $$    
  select array( select unnest($1) group by 1);    
$$ language sql strict parallel safe;    
CREATE FUNCTION

數組合并與去重函數：

postgres=# create or replace function array_uniq_cat(anyarray,anyarray) returns anyarray as $$    
  select uniq(array_cat($1,$2));     
$$ language sql strict parallel safe;    
CREATE FUNCTION

有combinefunc，同時支援并行聚合，并行掃描。

create aggregate arragg (anyarray) (sfunc = array_uniq_cat, stype=anyarray, COMBINEFUNC = array_uniq_cat, PARALLEL=safe);

https://github.com/digoal/blog/blob/master/201903/20190317_01.md#%E8%87%AA%E5%AE%9A%E4%B9%89%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0%E4%BE%8B%E5%AD%902%E6%95%B0%E6%8D%AE%E8%81%9A%E5%90%88%E6%8F%92%E4%BB%B6count_distinct-%E8%87%AA%E5%AE%9A%E4%B9%89%E5%B9%B6%E8%A1%8C%E8%81%9A%E5%90%88%E5%8A%A0%E9%80%9F 自定義聚合函數例子2，資料聚合插件：count_distinct 自定義并行聚合加速

https://github.com/tvondra/count_distinct 
git clone https://github.com/tvondra/count_distinct  
  
cd count_distinct/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
create extension count_distinct ;

并行聚合函數例子

/* Create the aggregate functions */  
CREATE AGGREGATE count_distinct(anyelement) (  
       SFUNC = count_distinct_append,  
       STYPE = internal,  
       FINALFUNC = count_distinct,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE array_agg_distinct(anynonarray) (  
       SFUNC = count_distinct_append,  
       STYPE = internal,  
       FINALFUNC = array_agg_distinct,  
       FINALFUNC_EXTRA,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE count_distinct_elements(anyarray) (  
       SFUNC = count_distinct_elements_append,  
       STYPE = internal,  
       FINALFUNC = count_distinct,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);  
  
CREATE AGGREGATE array_agg_distinct_elements(anyarray) (  
       SFUNC = count_distinct_elements_append,  
       STYPE = internal,  
       FINALFUNC = array_agg_distinct,  
       FINALFUNC_EXTRA,  
       COMBINEFUNC = count_distinct_combine,  
       SERIALFUNC = count_distinct_serial,  
       DESERIALFUNC = count_distinct_deserial,  
       PARALLEL = SAFE  
);

https://github.com/digoal/blog/blob/master/201903/20190317_01.md#%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE10%E4%BA%BF 測試資料10億 

create unlogged table table3 (i int2[]);  
insert into table3 values (array[1,2,3]), (array[3,3,4,6,100,2,3]);  
insert into table3 select array[109,209,3223] from generate_series(1,1000000000);

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

postgres=# explain select array_agg_distinct_elements(i) from table3;  
                                 QUERY PLAN                                    
-----------------------------------------------------------------------------  
 Aggregate  (cost=19852943.60..19852943.61 rows=1 width=32)  
   ->  Seq Scan on table3  (cost=0.00..17352943.28 rows=1000000128 width=27)  
(2 rows)  
  
postgres=# explain select array_agg_distinct(i) from table1;  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Aggregate  (cost=16924779.00..16924779.01 rows=1 width=32)  
   ->  Seq Scan on table1  (cost=0.00..14424779.00 rows=1000000000 width=2)  
(2 rows)  
  
  
postgres=# select array_agg_distinct_elements(i) from table3;  
 array_agg_distinct_elements    
------------------------------  
 {1,2,3,4,6,100,109,3223,209}  
(1 row)  
  
Time: 298794.796 ms (04:58.795)  
  
postgres=# select array_agg_distinct(i) from table1;  
 array_agg_distinct   
--------------------  
 {1}  
(1 row)  
  
Time: 96477.082 ms (01:36.477)

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

postgres=# explain select array_agg_distinct_elements(i) from table3;  
                                          QUERY PLAN                                             
-----------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=7700164.46..7700164.47 rows=1 width=32)  
   ->  Gather  (cost=7700164.27..7700164.28 rows=36 width=32)  
         Workers Planned: 36  
         ->  Partial Aggregate  (cost=7700164.27..7700164.28 rows=1 width=32)  
               ->  Parallel Seq Scan on table3  (cost=0.00..7630719.81 rows=27777781 width=27)  
(5 rows)  
  
postgres=# explain select array_agg_distinct(i) from table1;           
                                          QUERY PLAN                                            
----------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=4772001.42..4772001.43 rows=1 width=32)  
   ->  Gather  (cost=4772001.23..4772001.24 rows=36 width=32)  
         Workers Planned: 36  
         ->  Partial Aggregate  (cost=4772001.23..4772001.24 rows=1 width=32)  
               ->  Parallel Seq Scan on table1  (cost=0.00..4702556.78 rows=27777778 width=2)  
(5 rows)  
  
  
postgres=# select array_agg_distinct_elements(i) from table3;  
 array_agg_distinct_elements    
------------------------------  
 {1,2,3,4,6,100,109,3223,209}  
(1 row)  
  
Time: 8752.115 ms (00:08.752)  
  
postgres=# select array_agg_distinct(i) from table1;  
 array_agg_distinct   
--------------------  
 {1}  
(1 row)  
  
Time: 3427.029 ms (00:03.427)

自定義聚合的效率取決于自定義聚合代碼本身的效率，SQL語言寫的自定義聚合效率比較低，建議使用C語言寫這種需要進行大資料量運算的FUNCTION。

https://github.com/digoal/blog/blob/master/201903/20190317_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/20190317_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/20190317_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虛拟機

PostgreSQL 并行計算解說 之9 - parallel 自定義并行聚合
sql 分布式計算 并行計算 算法 關系型資料庫 c語言 postgresql c++ 異構計算 rds parallel并行計算 jdk7并行計算 并行計算parallel自定義 jdk7并行計算架構介紹 并行計算parallel scan
上一篇: PostgreSQL 并行計算解說 之1 - parallel seq scan
下一篇: PostgreSQL 列印詳細錯誤調用棧 - pg_backtrace

繼續閱讀