PostgreSQL 并行计算解说 之28 - parallel CREATE INDEX CONCURRENTLY - 不堵塞读写

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计,已支持27余种场景的并行计算。 parallel seq scan parallel index scan parall

标签

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 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 并行计算算法,参数,强制并行度设置》

parallel CREATE INDEX CONCURRENTLY - 不堵塞读写

支持并行创建索引,并且不堵塞读写操作。

数据量:10亿

场景 数据量 关闭并行 开启并行 并行度 开启并行性能提升倍数
parallel CREATE INDEX CONCURRENTLY - 不堵塞读写 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;    

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    

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    

其他知识

1、优化器自动并行度算法 CBO

《PostgreSQL 9.6 并行计算 优化器算法浅析》

《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实例)》

《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://www.postgresql.org/docs/11/parallel-plans.html

《PostgreSQL 11 并行计算算法,参数,强制并行度设置》

《PostgreSQL 11 preview - 并行计算 增强 汇总》

《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含array_agg合并多个数组为单个一元数组的例子)》

《PostgreSQL 9.6 并行计算 优化器算法浅析》

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
SQL 移动开发 关系型数据库
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
|
SQL 存储 Oracle
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan),游标
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan),游标
3842 0
|
SQL 存储 弹性计算
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan),游标
标签 PostgreSQL , 数据离散性 , 扫描性能 , 重复扫 , bitmap index scan , 排序扫描 , 扫描方法 , 顺序 背景 一个这样的问题: 为什么select x from tbl offset x limit x; 两次查询连续的OFFSET,会有重复数据呢? select ctid,* from tbl where ... offset 0 li
2104 0
|
关系型数据库 测试技术 数据库
PostgreSQL pg_rewind,时间线修复,脑裂修复,flashback - 从库开启读写后,回退为只读从库。异步主从发生角色切换后,主库rewind为新主库的从库
PostgreSQL pg_rewind,时间线修复,脑裂修复,flashback - 从库开启读写后,回退为只读从库。异步主从发生角色切换后,主库rewind为新主库的从库
2167 1
|
SQL Oracle 关系型数据库
PostgreSQL pg_rewind,时间线修复,脑裂修复,flashback - 从库开启读写后,回退为只读从库。异步主从发生角色切换后,主库rewind为新主库的从库
标签 PostgreSQL , pg_rewind , 主从切换 , 时间线修复 , 脑裂修复 , 从库开启读写后,回退为只读从库 , 异步主从发生角色切换后,主库rewind为新主库的从库 背景 1、PG物理流复制的从库,当激活后,可以开启读写,使用pg_rewind可以将从库回退为只读从库的角色。而不需要重建整个从库。 2、当异步主从发生角色切换后,主库的wal目录中可能还有没完全
1117 0
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之13 - parallel OLAP : 中间结果 parallel with unlogged table
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计,已支持27余种场景的并行计算。 parallel seq scan parallel
643 0
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之14 - parallel index scan
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计,已支持27余种场景的并行计算。 parallel seq scan paral
1186 0
|
SQL 监控 安全
PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids
PostgreSQL 谁堵塞了谁(锁等待检测)- pg_blocking_pids, pg_safe_snapshot_blocking_pids
1951 0
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
365 0
|
存储 缓存 关系型数据库
下一篇
无影云桌面