开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建

简介:
+关注继续查看

标签

PostgreSQL , CONCURRENTLY index , snapshot , 两阶段 , 等待 , snapshot


背景

PostgreSQL支持在线创建索引(CREATE INDEX CONCURRENTLY),不堵塞其他会话对被创建索引表的DML(INSERT,UPDATE,DELETE)操作。特别适合于在线业务。

注意,传统的创建索引的方法,会堵塞其他会话的DML。

那么CREATE INDEX CONCURRENTLY的内部实现如何?有有一些什么需要注意的?

比如我们有一个这样的CASE,在执行CREATE INDEX CONCURRENTLY前,开启了一个事务(COPY),虽然操作的并不是创建索引的表,但是却导致了CREATE INDEX CONCURRENTLY迟迟不能结束。

为什么?需要研究一下CREATE INDEX CONCURRENTLY的原理。

复现

1、创建测试表

postgres=# create table a(id int);    
CREATE TABLE    
postgres=# create table b(id int);    
CREATE TABLE    

2、会话1

postgres=# begin;    
BEGIN    
postgres=# copy a from stdin;    
Enter data to be copied followed by a newline.    
End with a backslash and a period on a line by itself, or an EOF signal.    
>>     

3、会话2

postgres=# create index idx_b_1 on b (id);    
CREATE INDEX    
postgres=# create index CONCURRENTLY idx_b_2 on b (id);    
    
hang住(实际已结束)    

4、会话3,查看锁等待信息,建议用这个QUERY查看。

《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》

postgres=# select * from pg_locks where granted is not true;    
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted | fastpath     
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------    
 virtualxid |          |          |      |       | 33/500     |               |         |       |          | 61/48              | 18690 | ShareLock     | f       | f    
(17 rows)    
    
    
postgres=# select * from pg_locks where virtualxid='33/500';    
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted | fastpath     
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------    
 virtualxid |          |          |      |       | 33/500     |               |         |       |          | 33/500             | 17371 | ExclusiveLock | t       | f    
 virtualxid |          |          |      |       | 33/500     |               |         |       |          | 61/48              | 18690 | ShareLock     | f       | f    
(2 rows)    
    
    
postgres=# select * from pg_stat_activity where pid=17371;    
-[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------    
datid            | 13220    
datname          | postgres    
pid              | 17371    
usesysid         | 10    
usename          | postgres    
application_name | psql    
client_addr      |     
client_hostname  |     
client_port      | -1    
backend_start    | 2018-04-24 19:54:54.838402+08    
xact_start       | 2018-04-24 19:59:10.884774+08    
query_start      | 2018-04-24 19:59:10.884792+08    
state_change     | 2018-04-24 19:59:10.884792+08    
wait_event_type  |     
wait_event       |     
state            | active    
backend_xid      | 27958    
backend_xmin     | 4405    
query            | copy a from stdin;    
backend_type     | client backend    

5、查看pstack

pstack 18690    
#0  0x00007f44f4c4b903 in __epoll_wait_nocancel () from /lib64/libc.so.6    
#1  0x000000000070125e in WaitEventSetWait ()    
#2  0x0000000000701697 in WaitLatchOrSocket ()    
#3  0x000000000070fe76 in ProcSleep ()    
#4  0x000000000070af6f in WaitOnLock ()    
#5  0x000000000070c4f5 in LockAcquireExtended ()    
#6  0x000000000070ec0e in VirtualXactLock ()    
#7  0x00000000005a269e in DefineIndex ()    
#8  0x0000000000725eec in ProcessUtilitySlow.isra.2 ()    
#9  0x0000000000724ac6 in standard_ProcessUtility ()    
#10 0x0000000000722416 in PortalRunUtility ()    
#11 0x0000000000722e57 in PortalRunMulti ()    
#12 0x00000000007239bc in PortalRun ()    
#13 0x000000000071fb57 in exec_simple_query ()    
#14 0x0000000000720e02 in PostgresMain ()    
#15 0x000000000047a96b in ServerLoop ()    
#16 0x00000000006b9029 in PostmasterMain ()    
#17 0x000000000047b321 in main ()    

6、干掉会话,

postgres=# select pg_cancel_backend(17371);    
-[ RECORD 1 ]-----+--    
pg_cancel_backend | t    

7、索引结束

postgres=# create index CONCURRENTLY idx_b_2 on b (id);    
CREATE INDEX    

CREATE INDEX CONCURRENTLY 原理

为了搞明白前面那个原因,需要了解CREATE INDEX CONCURRENTLY的流程。

https://www.postgresql.org/docs/devel/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate. After the second scan, the index build must wait for any transactions that have a snapshot ( see Chapter 13 ) predating the second scan to terminate. Then finally the index can be marked ready for use, and the CREATE INDEX command terminates. Even then, however, the index may not be immediately usable for queries: in the worst case, it cannot be used as long as transactions exist that predate the start of the index build.

使用CREATE INDEX CONCURRENTLY创建索引,分为三个阶段,扫描两次TABLE。

create index CONCURRENTLY idx_b_2 on b (id);    

阶段如下:

1、开启事务1,拿到当前snapshot1。

2、扫描B表前,等待所有修改过B表(写入、删除、更新)的事务结束。

3、扫描B表,并建立索引。

4、结束事务1。

5、开启事务2,拿到当前snapshot2。

6、再次扫描B表前,等待所有修改过B表(写入、删除、更新)的事务结束。

7、在snapshot2之后启动的事务对B表执行的DML,会修改这个idx_b_2的索引。

8、再次扫描B表,更新索引。(从TUPLE中可以拿到版本号,在snapshot1到snapshot2之间变更的记录,将其合并到索引)

9、上一步更新索引结束后,等待事务2之前开启的持有snapshot的事务结束。

10、结束索引创建。索引可见。

前面复现的例子,问题出在哪里呢?

实际上create index CONCURRENTLY需要2次扫描,三次等待。三次等待分别是2次扫描表前,结束创建索引前。前面的例子,实际上是在结束创建索引前,等待第二次SCAN之前持有snapshot的事务结束。

小结

1、注意事项,为了减少等待的时间,需要1. 尽量避免创建索引过程中,两次SCAN之前对被创建索引表实施长事务,并且长事务中包含修改被创建索引的表。2. 在第二次SCAN前,尽量避免开启长事务。

2、最后的一次等待,应该还有改进的空间,减少等待。实际上最后一次等待是为了防止那些还存在的事务,在事务中可能查询B表,如果结束的话,这个索引实际上是SNAPSHOT2后的状态(可能有一些对snapshot2前的事务STALE的状态)。

3、注意,因为第一次扫描并建立中间状态的索引(INVALID)后,索引实际上就对后面的DML起作用了,所以如果是在第二SCAN阶段,索引创建失败了,这个索引会一直影响DML(性能、约束)。

Another caveat when building a unique index concurrently is that the uniqueness constraint is already being enforced against other transactions when the second table scan begins. This means that constraint violations could be reported in other queries prior to the index becoming available for use, or even in cases where the index build eventually fails. Also, if a failure does occur in the second scan, the “invalid” index continues to enforce its uniqueness constraint afterwards.

参考

https://www.postgresql.org/docs/devel/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
《SpringBoot篇》02.SpringBoot程序的打包与运行(jar包的运行原理)
《SpringBoot篇》02.SpringBoot程序的打包与运行(jar包的运行原理)
39 0
Java(JDK)13新特性之Reimplement the Legacy Socket API
Java(JDK)13新特性之Reimplement the Legacy Socket API
27 0
低代码开发的前后端联调——APICloud Studio 3 API管理工具结合数据云3.0使用教程
最近国内低代码平台APICloud 推出了拖拽式开发模式,通过拖拉组件,设置组件样式、属性、事件,可生成代码。我也是第一时间体验了一下,上一篇分享了一些学习经验。而且还发现新增了一个API管理工具,这篇文章即是分享API管理工具的学习体验。
84 0
阿里云Rocket MQ Java Http SDK发送消费消息示例Demo
消息队列 RocketMQ 版支持 RESTful 风格的 HTTP 协议通信,并提供了以下 7 种语言的 SDK,下面以最新的Java Http SDK为范例介绍RocketMQ消息的发送和接收。
2898 0
第一个将Palette Mode引入VVC(H.266),阿里云在JVET会议上引起关注
本文作者阿里云视频云高级技术专家睿柯如是说:阿里云在屏幕视频编码技术和应用上有世界领先的团队。6月中国AVS会议中,阿里云提出提案分析屏幕视频编码的应用需求,引起AVS组织关注,采纳阿里云的提案成为下一代AVS3的需求。
4560 0
Python将md批量转为docx
这两天写毕业论文, 发现了一个可以将markdown快速转为word格式的小工具pandoc, 非常好用, 比如我有一个名为毕业论文.md的文件, 我只需在命令行运行 pandoc 毕业论文.
1331 0
【前沿】何恺明大神ICCV2017最佳论文Mask R-CNN的Keras/TensorFlow/Pytorch 代码实现
何恺明大神的论文Mask R-CNN 获得ICCV最佳论文 ,而关于这篇论文的TensorFlow\Pytorch\Keras实现相继开源出来,让我们来看下。
12507 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
立即下载
Phoenix 全局索引原理与实践
立即下载
MySQL表和索引优化实战
立即下载