天天看點
傳回

PostgreSQL 并行計算解說 之10 - parallel 自定義并行函數(UDF)

标簽

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

https://github.com/digoal/blog/blob/master/201903/20190317_02.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_02.md#parallel-%E8%87%AA%E5%AE%9A%E4%B9%89%E5%B9%B6%E8%A1%8C%E5%87%BD%E6%95%B0udf parallel 自定義并行函數(UDF)

自定義并行函數(UDF)

資料量：10億。

場景 資料量 關閉并行 開啟并行 并行度 開啟并行性能提升倍數
10 億 456 秒 16.5 秒 30 27.6 倍

UDF例子，取模，求絕對值。

create or replace function udf1(int4, int4) returns int4 as $$  
  select abs(mod($1,$2));  
$$ language sql strict parallel safe;

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

postgres=# explain select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
                                    QUERY PLAN                                      
----------------------------------------------------------------------------------  
 GroupAggregate  (cost=168911543.27..191411543.27 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Sort  (cost=168911543.27..171411543.27 rows=1000000000 width=4)  
         Sort Key: (abs(mod(i, 10)))  
         ->  Seq Scan on table2  (cost=0.00..19424779.00 rows=1000000000 width=4)  
(5 rows)  
  
postgres=# explain select udf1(i,10),count(*) from table2 group by udf1(i,10);  
                                    QUERY PLAN                                      
----------------------------------------------------------------------------------  
 GroupAggregate  (cost=168911543.27..191411543.27 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Sort  (cost=168911543.27..171411543.27 rows=1000000000 width=4)  
         Sort Key: (abs(mod(i, 10)))  
         ->  Seq Scan on table2  (cost=0.00..19424779.00 rows=1000000000 width=4)  
(5 rows)  
  
  
postgres=# select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
 abs |   count     
-----+-----------  
   0 |  99996445  
   1 | 100000179  
   2 | 100000876  
   3 | 100012873  
   4 | 100009015  
   5 |  99999050  
   6 |  99992767  
   7 | 100000912  
   8 | 100009862  
   9 |  99978021  
(10 rows)  
  
Time: 456897.647 ms (07:36.898)  
  
postgres=# select udf1(i,10),count(*) from table2 group by udf1(i,10);  
 udf1 |   count     
------+-----------  
    0 |  99996445  
    1 | 100000179  
    2 | 100000876  
    3 | 100012873  
    4 | 100009015  
    5 |  99999050  
    6 |  99992767  
    7 | 100000912  
    8 | 100009862  
    9 |  99978021  
(10 rows)  
  
Time: 456254.222 ms (07:36.254)

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

postgres=# explain select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=9089856.77..57110837.99 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Gather Merge  (cost=9089856.77..37110838.04 rows=999999990 width=12)  
         Workers Planned: 30  
         ->  Partial GroupAggregate  (cost=9089856.00..9839855.99 rows=33333333 width=12)  
               Group Key: (abs(mod(i, 10)))  
               ->  Sort  (cost=9089856.00..9173189.33 rows=33333333 width=4)  
                     Sort Key: (abs(mod(i, 10)))  
                     ->  Parallel Seq Scan on table2  (cost=0.00..4924779.00 rows=33333333 width=4)  
(9 rows)  
  
postgres=# explain select udf1(i,10),count(*) from table2 group by udf1(i,10);  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=9089856.77..57110837.99 rows=1000000000 width=12)  
   Group Key: (abs(mod(i, 10)))  
   ->  Gather Merge  (cost=9089856.77..37110838.04 rows=999999990 width=12)  
         Workers Planned: 30  
         ->  Partial GroupAggregate  (cost=9089856.00..9839855.99 rows=33333333 width=12)  
               Group Key: (abs(mod(i, 10)))  
               ->  Sort  (cost=9089856.00..9173189.33 rows=33333333 width=4)  
                     Sort Key: (abs(mod(i, 10)))  
                     ->  Parallel Seq Scan on table2  (cost=0.00..4924779.00 rows=33333333 width=4)  
(9 rows)  
  
  
postgres=# select abs(mod(i,10)),count(*) from table2 group by abs(mod(i,10));  
 abs |   count     
-----+-----------  
   0 |  99996445  
   1 | 100000179  
   2 | 100000876  
   3 | 100012873  
   4 | 100009015  
   5 |  99999050  
   6 |  99992767  
   7 | 100000912  
   8 | 100009862  
   9 |  99978021  
(10 rows)  
  
Time: 16500.058 ms (00:16.500)  
  
postgres=# select udf1(i,10),count(*) from table2 group by udf1(i,10);  
 udf1 |   count     
------+-----------  
    0 |  99996445  
    1 | 100000179  
    2 | 100000876  
    3 | 100012873  
    4 | 100009015  
    5 |  99999050  
    6 |  99992767  
    7 | 100000912  
    8 | 100009862  
    9 |  99978021  
(10 rows)  
  
Time: 16490.091 ms (00:16.490)

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

https://github.com/digoal/blog/blob/master/201903/20190317_02.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_02.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_02.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 并行計算解說 之10 - parallel 自定義并行函數(UDF)
sql 分布式計算 并行計算 算法 關系型資料庫 c語言 postgresql c++ 異構計算 rds jdk7并行計算架構介紹 parallel并行計算 jdk7并行計算 c程式設計并行計算 cte并行計算parallel
上一篇: PostgreSQL 并行計算解說 之3 - parallel agg
下一篇: PostgreSQL sharding extension citus 優化器 Query Processing 之 - Distributed Query Planner、Executor (Real-time Executor, Router Executor, Task Tracker Executor)

繼續閱讀