PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建-阿里云开发者社区

开发者社区> 德哥> 正文

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

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

相关文章
Java基础-22总结登录注册IO版,数据操作流,内存操作流,打印流,标准输入输出流,转换流,随机访问流,合并流,序列化流,Properties
你需要的是什么,直接评论留言。 获取更多资源加微信公众号“Java帮帮” (是公众号,不是微信好友哦) 还有“Java帮帮”今日头条号,技术文章与新闻,每日更新,欢迎阅读 学习交流请加Java帮帮交流QQ群553841695 分享是一种美德,分享更快乐! 1:登录注册IO版本案例(掌握) 要求,对着写一遍。 cn.i
1727 0
为什么不走索引, PostgreSQL
我明明创建了索引字段,为什么不选择索引扫描呢?我该注意什么,怎么解决
4192 0
《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》——2.10 初始化缓冲区管理结构
本节书摘来自华章计算机《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》一书中的第2章,第2.10节,作者:新设计团队著, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1134 0
java创建二叉树并实现非递归中序遍历二叉树
java创建二叉树并递归遍历二叉树前面已有讲解:http://www.cnblogs.com/lixiaolun/p/4658659.html。 在此基础上添加了非递归中序遍历二叉树: 二叉树类的代码: package binarytree; import linkedstack.
909 0
《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》——2.11 初始化硬盘
本节书摘来自华章计算机《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》一书中的第2章,第2.11节,作者:新设计团队著, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
817 0
聊聊Mysql索引和redis跳表
聊聊Mysql索引和redis跳表 摘要 面试时,交流有关mysql索引问题时,发现有些人能够涛涛不绝的说出B+树和B树,平衡二叉树的区别,却说不出B+树和hash索引的区别。这种一看就知道是死记硬背,没有理解索引的本质。
5187 0
《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》——2.9 初始化进程0
本节书摘来自华章计算机《Linux内核设计的艺术:图解Linux操作系统架构设计与实现原理》一书中的第2章,第2.9节,作者:新设计团队著, 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1469 0
高效操作字串的String Reference类
如下面的代码中一个函数接受一个std::string常量引用,在其函数内部需要使用std::string的一些函数操作字串。 void foo(const std::string& param) { ...... } 参数使用的是常量引用,如果传入一个std::string就不需要额外的拷贝。
880 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载