PostgreSQL 并行計算解說 之28 - parallel CREATE INDEX CONCURRENTLY - 不堵塞讀寫

标簽

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

https://github.com/digoal/blog/blob/master/201903/20190318_03.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 CREATE INDEX CONCURRENTLY - 不堵塞讀寫    
        
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_03.md#parallel-create-index-concurrently---%E4%B8%8D%E5%A0%B5%E5%A1%9E%E8%AF%BB%E5%86%99 parallel CREATE INDEX CONCURRENTLY - 不堵塞讀寫

支援并行建立索引，并且不堵塞讀寫操作。

資料量：10億

場景	資料量	關閉并行	開啟并行	并行度	開啟并行性能提升倍數
	10億	509.6 秒	355 秒	16	1.44 倍

drop table a1;    
create unlogged table a1(id int);    
insert into a1 select generate_series(1,1000000000);    
INSERT 0 1000000000    
alter table a1 set (parallel_workers =16);    
vacuum analyze a1;    
    
set min_parallel_index_scan_size =0;    
set max_parallel_workers=64;    
set max_parallel_workers_per_gather =16;    
set max_parallel_maintenance_workers =16;    
set parallel_setup_cost =0;    
set parallel_tuple_cost =0;    
set maintenance_work_mem ='4GB';    
set parallel_leader_participation=off;    
           

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

postgres=# create index concurrently idx_a1_id on a1(id);    
CREATE INDEX    
Time: 509594.515 ms (08:29.595)    
    
建立索引時不影響讀寫    
    
postgres=# select * from a1 where ctid='(1,5)';    
 id      
-----    
 231    
(1 row)    
    
Time: 1.988 ms    
    
postgres=# insert into a1 values (0) returning ctid;    
     ctid          
---------------    
 (4424778,176)    
(1 row)    
    
INSERT 0 1    
Time: 0.650 ms    
postgres=# select * from a1 where ctid='(4424778,176)';    
 id     
----    
  0    
(1 row)    
    
Time: 0.427 ms    
    
postgres=# delete from a1 where ctid='(1,5)' returning xmin,xmax,cmin,cmax,ctid,*;    
    xmin    |    xmax    | cmin | cmax | ctid  | id      
------------+------------+------+------+-------+-----    
 1328166328 | 1328166343 |    0 |    0 | (1,5) | 231    
(1 row)    
    
DELETE 1    
Time: 0.488 ms    
           

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

postgres=# create index concurrently idx_a1_id on a1(id);    
CREATE INDEX    
Time: 355070.593 ms (05:55.071)    
    
建立索引時不影響讀寫    
    
postgres=# insert into a1 values (0);    
INSERT 0 1    
postgres=# delete from a1 where ctid='(1,1)';    
DELETE 1    
postgres=# insert into a1 values (0);    
INSERT 0 1    
Time: 0.376 ms    
postgres=# insert into a1 values (0) returning ctid;    
     ctid          
---------------    
 (4424778,175)    
(1 row)    
INSERT 0 1    
Time: 0.372 ms    
postgres=# delete from a1 where ctid='(4424778,175)';    
DELETE 1    
Time: 0.324 ms    
postgres=# select * from a1 where ctid='(1,2)';    
 id      
-----    
 228    
(1 row)    
Time: 0.384 ms    
           

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