郑州做网站华久科技,wordpress有中文版没,绍兴手机网站制作,网站开发时间表前一篇文章提到了普通创建索引会阻塞DML操作
PostgreSQL创建索引的锁分析和使用注意
而PostgreSQL里可以使用create index concurrently 在线创建索引(CIC)功能#xff0c;降低创建索引在表上申请的锁的级别#xff0c;ShareUpdateExclusiveLock级别的锁和RowExclusiveLock…前一篇文章提到了普通创建索引会阻塞DML操作
PostgreSQL创建索引的锁分析和使用注意
而PostgreSQL里可以使用create index concurrently 在线创建索引(CIC)功能降低创建索引在表上申请的锁的级别ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突不会阻塞表上的DML操作。
1.1 在线创建索引(CIC)的原理
并发创建索引需要分多个步骤完成首先在一个事务中将相关索引信息记录到系统表中但是将索引信息标记为非法状态然后需要进行两次扫描并且在最后需要等待第二次扫描之前产生的所有具有快照信息的事务结束最后修改索引的状态信息为可用。
1.插入元数据
|在系统表中插入索引的元数据包括pg_class、pg_index索引信息标记为非法状态INVALID然后开启两个事务进行两次扫描2.第一次扫描|开启事务1拿到当前snapshot1|扫描test_tab1表前等待所有修改过test1表写入、删除、更新的事务结束|扫描test_tab1表并建立中间状态的索引INVALID|结束事务12.第二次扫描|开启事务2拿到当前snapshot2|再次扫描test_tab1表前等待所有修改过test_tab1表写入、删除、更新的事务结束|在snapshot2之后启动的事务对test_tab1表执行的DML会修改这个idx_1的索引|再次扫描test_tab1表更新索引。从tuple中可以拿到版本号在snapshot1到snapshot2之间变更的记录将其合并到索引|上一步更新索引结束后等待事务2之前开启的持有snapshot的事务结束|结束索引创建索引可见可以看到CREATE INDEX CONCURRENTLY在线创建索引(CIC)是需要借助snapshot去完成操作的所以其实如果有长事务占用了快照让它获取不到锁那么创建的时间就会很长。
1.2 在线创建索引(CIC)操作在表上获取的锁ShareUpdateExclusiveLock
如之前测试的现象在一张表上创建普通B-tree索引的时候会阻塞这张表上进行的DML操作。PostgreSQL支持在线创建索引CREATE INDEX CONCURRENTLY不堵塞其他会话对被创建索引表的DMLINSERT,UPDATE,DELETE操作。
postgres# begin;
BEGIN
postgres*# create index concurrently idx_111 on t1(id);
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
postgres!# 而create index concurrently操作不能在一个显式开启的事务里执行并且我自己的环境也比较有限就不造数据模拟了而是使用gdb在对应函数打上Breakpoint进行分析。
(gdb) b LockAcquireExtended
Breakpoint 1 at 0xaaaab094d094: file lock.c, line 765.
(gdb) info b
Num Type Disp Enb Address What
1 breakpoint keep y 0x0000aaaab094d094 in LockAcquireExtended at lock.c:765(gdb) c
Continuing.Breakpoint 1, LockAcquireExtended (locktaglocktagentry0xffffc42c3f08, lockmodelockmodeentry1, sessionLocksessionLockentryfalse, dontWaitdontWaitentryfalse, reportMemoryErrorreportMemoryErrorentrytrue, locallockplocallockpentry0xffffc42c3f00) at lock.c:765
765 {
(gdb) bt
#0 LockAcquireExtended (locktaglocktagentry0xffffc42c3f08, lockmodelockmodeentry1, sessionLocksessionLockentryfalse,dontWaitdontWaitentryfalse, reportMemoryErrorreportMemoryErrorentrytrue, locallockplocallockpentry0xffffc42c3f00) at lock.c:765
#1 0x0000aaaab0949ffc in LockRelationOid (relid3466, lockmode1) at lmgr.c:117
#2 0x0000aaaab058c1a8 in relation_open (relationIdrelationIdentry3466, lockmodelockmodeentry1) at relation.c:56
#3 0x0000aaaab0aa3fb4 in BuildEventTriggerCache () at evtcache.c:130
#4 EventCacheLookup (eventoptimized out, evententryEVT_SQLDrop) at evtcache.c:69
#5 0x0000aaaab07018c0 in trackDroppedObjectsNeeded () at event_trigger.c:1147
#6 EventTriggerBeginCompleteQuery () at event_trigger.c:1089
#7 0x0000aaaab096cd48 in ProcessUtilitySlow (pstatepstateentry0xaaaac6004f58, pstmtpstmtentry0xaaaac60827d0,queryStringqueryStringentry0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);, contextcontextentryPROCESS_UTILITY_TOPLEVEL,paramsparamsentry0x0, queryEnvqueryEnventry0x0, qcqcentry0xffffc42c4ab8, destoptimized out) at utility.c:1118
#8 0x0000aaaab096c0d4 in standard_ProcessUtility (pstmt0xaaaac60827d0, queryString0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);,readOnlyTreeoptimized out, contextPROCESS_UTILITY_TOPLEVEL, params0x0, queryEnv0x0, dest0xaaaac6083000, qc0xffffc42c4ab8) at utility.c:1078
#9 0x0000ffff90026270 in pgss_ProcessUtility (pstmt0xaaaac60827d0, queryString0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);,readOnlyTreefalse, contextPROCESS_UTILITY_TOPLEVEL, params0x0, queryEnv0x0, dest0xaaaac6083000, qc0xffffc42c4ab8) at pg_stat_statements.c:1145
#10 0x0000aaaab096a6cc in PortalRunUtility (portalportalentry0xaaaac6104e88, pstmtpstmtentry0xaaaac60827d0, isTopLevelisTopLevelentrytrue,setHoldSnapshotsetHoldSnapshotentryfalse, destdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8) at pquery.c:1158
#11 0x0000aaaab096a874 in PortalRunMulti (portalportalentry0xaaaac6104e88, isTopLevelisTopLevelentrytrue,setHoldSnapshotsetHoldSnapshotentryfalse, destdestentry0xaaaac6083000, altdestaltdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8)at pquery.c:1315
#12 0x0000aaaab096ae00 in PortalRun (portalportalentry0xaaaac6104e88, countcountentry9223372036854775807, isTopLevelisTopLevelentrytrue,run_oncerun_onceentrytrue, destdestentry0xaaaac6083000, altdestaltdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8) at pquery.c:791
#13 0x0000aaaab0966768 in exec_simple_query (query_stringquery_stringentry0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);)at postgres.c:1274
#14 0x0000aaaab0967648 in PostgresMain (dbnameoptimized out, usernameoptimized out) at postgres.c:4637
#15 0x0000aaaab08c0514 in BackendRun (port0xaaaac60b6c40, port0xaaaac60b6c40) at postmaster.c:4464
#16 BackendStartup (port0xaaaac60b6c40) at postmaster.c:4192
#17 ServerLoop () at postmaster.c:1782
#18 0x0000aaaab08c165c in PostmasterMain (argcargcentry1, argvargventry0xaaaac5fe9d40) at postmaster.c:1466
#19 0x0000aaaab0578464 in main (argc1, argv0xaaaac5fe9d40) at main.c:198上面停掉的第一个Breakpoint其可以看一下调用到LockAcquireExtended的堆栈relation_open (relationIdrelationIdentry3466, lockmodelockmodeentry1) at relation.c:56 这里oid对应是3466的并不是索引所在的表而是系统表因为创建索引过程也会访问系统表开始连续几个停的断点的位置打印的lockmode都是1获取的都是AccessShareLock也就是说这几个系统表都是进行了select的操作。这里我们就带着目的去看继续让他往下跑一直等到这个lockmode为4的时候relation_open (relationIdrelationIdentry16725因为我们创建索引的这张表tab_test_1它对应的oid是16725。lockmode为4对应的就是ShareUpdateExclusiveLock。
postgres# select oid,relname from pg_class where relnametab_test_1;oid | relname
-------------------16725 | tab_test_1
(1 row)765 {
(gdb) c
Continuing.Breakpoint 1, LockAcquireExtended (locktaglocktagentry0xffffc42c3d78, lockmodelockmodeentry1, sessionLocksessionLockentryfalse, dontWaitdontWaitentryfalse, reportMemoryErrorreportMemoryErrorentrytrue, locallockplocallockpentry0xffffc42c3d70) at lock.c:765
765 {
(gdb) c
Continuing.Breakpoint 1, LockAcquireExtended (locktaglocktagentry0xffffc42c3fd8, lockmodelockmodeentry4, sessionLocksessionLockentryfalse, dontWaitdontWaitentryfalse, reportMemoryErrorreportMemoryErrorentrytrue, locallockplocallockpentry0xffffc42c3fd0) at lock.c:765
765 {
(gdb) bt
#0 LockAcquireExtended (locktaglocktagentry0xffffc42c3fd8, lockmodelockmodeentry4, sessionLocksessionLockentryfalse,dontWaitdontWaitentryfalse, reportMemoryErrorreportMemoryErrorentrytrue, locallockplocallockpentry0xffffc42c3fd0) at lock.c:765
#1 0x0000aaaab0949ffc in LockRelationOid (relidrelidentry16725, lockmodelockmodeentry4) at lmgr.c:117
#2 0x0000aaaab0672290 in RangeVarGetRelidExtended (relation0xaaaac60825b8, lockmodelockmodeentry4, flagsflagsentry0,callback0xaaaab074e5d0 RangeVarCallbackOwnsRelation, callback_argcallback_argentry0x0) at namespace.c:390
#3 0x0000aaaab096d1f8 in ProcessUtilitySlow (pstatepstateentry0xaaaac61ba098, pstmtpstmtentry0xaaaac60827d0,queryStringqueryStringentry0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);, contextcontextentryPROCESS_UTILITY_TOPLEVEL,paramsparamsentry0x0, queryEnvqueryEnventry0x0, qcqcentry0xffffc42c4ab8, destoptimized out) at utility.c:1486
#4 0x0000aaaab096c0d4 in standard_ProcessUtility (pstmt0xaaaac60827d0, queryString0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);,readOnlyTreeoptimized out, contextPROCESS_UTILITY_TOPLEVEL, params0x0, queryEnv0x0, dest0xaaaac6083000, qc0xffffc42c4ab8) at utility.c:1078
#5 0x0000ffff90026270 in pgss_ProcessUtility (pstmt0xaaaac60827d0, queryString0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);,readOnlyTreefalse, contextPROCESS_UTILITY_TOPLEVEL, params0x0, queryEnv0x0, dest0xaaaac6083000, qc0xffffc42c4ab8) at pg_stat_statements.c:1145
#6 0x0000aaaab096a6cc in PortalRunUtility (portalportalentry0xaaaac6104e88, pstmtpstmtentry0xaaaac60827d0, isTopLevelisTopLevelentrytrue,setHoldSnapshotsetHoldSnapshotentryfalse, destdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8) at pquery.c:1158
#7 0x0000aaaab096a874 in PortalRunMulti (portalportalentry0xaaaac6104e88, isTopLevelisTopLevelentrytrue,setHoldSnapshotsetHoldSnapshotentryfalse, destdestentry0xaaaac6083000, altdestaltdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8)at pquery.c:1315
#8 0x0000aaaab096ae00 in PortalRun (portalportalentry0xaaaac6104e88, countcountentry9223372036854775807, isTopLevelisTopLevelentrytrue,run_oncerun_onceentrytrue, destdestentry0xaaaac6083000, altdestaltdestentry0xaaaac6083000, qcqcentry0xffffc42c4ab8) at pquery.c:791
#9 0x0000aaaab0966768 in exec_simple_query (query_stringquery_stringentry0xaaaac6081b98 create index concurrently idx_1 on tab_test_1(id);)at postgres.c:1274
#10 0x0000aaaab0967648 in PostgresMain (dbnameoptimized out, usernameoptimized out) at postgres.c:4637
#11 0x0000aaaab08c0514 in BackendRun (port0xaaaac60b73e0, port0xaaaac60b73e0) at postmaster.c:4464
#12 BackendStartup (port0xaaaac60b73e0) at postmaster.c:4192
#13 ServerLoop () at postmaster.c:1782
#14 0x0000aaaab08c165c in PostmasterMain (argcargcentry1, argvargventry0xaaaac5fe9d40) at postmaster.c:1466
#15 0x0000aaaab0578464 in main (argc1, argv0xaaaac5fe9d40) at main.c:198s单步执行然后p打印出变量的值 上面 *locktag里locktag_type是0结合LockTagType的定义第一个定义的enum值默认为0后续的值在前一个定义值的基础上加1可以知道是申请的LOCKTAG_RELATION表锁。
/** The LOCKTAG struct is defined with malice aforethought to fit into 16* bytes with no padding. Note that this would need adjustment if we were* to widen Oid, BlockNumber, or TransactionId to more than 32 bits.** We include lockmethodid in the locktag so that a single hash table in* shared memory can store locks of different lockmethods.*/
typedef struct LOCKTAG
{uint32 locktag_field1; /* a 32-bit ID field */uint32 locktag_field2; /* a 32-bit ID field */uint32 locktag_field3; /* a 32-bit ID field */uint16 locktag_field4; /* a 16-bit ID field */uint8 locktag_type; /* see enum LockTagType */uint8 locktag_lockmethodid; /* lockmethod indicator */
} LOCKTAG;/** LOCKTAG is the key information needed to look up a LOCK item in the* lock hashtable. A LOCKTAG value uniquely identifies a lockable object.** The LockTagType enum defines the different kinds of objects we can lock.* We can handle up to 256 different LockTagTypes.*/
typedef enum LockTagType
{LOCKTAG_RELATION, /* whole relation */LOCKTAG_RELATION_EXTEND, /* the right to extend a relation */LOCKTAG_DATABASE_FROZEN_IDS, /* pg_database.datfrozenxid */LOCKTAG_PAGE, /* one page of a relation */LOCKTAG_TUPLE, /* one physical tuple */LOCKTAG_TRANSACTION, /* transaction (for waiting for xact done) */LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */LOCKTAG_SPECULATIVE_TOKEN, /* speculative insertion Xid and token */LOCKTAG_OBJECT, /* non-relation database object */LOCKTAG_USERLOCK, /* reserved for old contrib/userlock code */LOCKTAG_ADVISORY /* advisory user locks */
} LockTagType;
1.3 在线创建索引(CIC)被阻塞的案例(等待vxid)
//session 1pid 557584
postgres# begin;
BEGIN
postgres*# select id from tab_test_2 for update;id
----1
(1 row)//session 2pid 557784
postgres# select id from tab_test_2 for update;
被阻塞//session 3pid 558431
postgres# create index concurrently idx_1 on tab_test_1(id);
被阻塞//session 4:1可以使用pg_blocking_pids查看被谁阻塞了
postgres# select pg_blocking_pids(558431);pg_blocking_pids
------------------{557784}
(1 row)postgres# select pg_blocking_pids(557784);pg_blocking_pids
------------------{557584}
(1 row)2也可以像下边查询锁的情况postgres# select* from pg_lockswhere pid in(557584,557784,558431);locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mod
e | granted | fastpath | waitstart
-----------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------virtualxid | | | | | 5/309 | | | | | 5/309 | 558431 | ExclusiveLock| t | t |relation | 13008 | 24929 | | | | | | | | 4/46 | 557784 | RowShareLock| t | t |relation | 13008 | 24929 | | | | | | | | 3/202 | 557584 | RowShareLock| t | t |virtualxid | | | | | 3/202 | | | | | 3/202 | 557584 | ExclusiveLock| t | t |transactionid | | | | | | 1719 | | | | 3/202 | 557584 | ExclusiveLock| t | f |virtualxid | | | | | 4/46 | | | | | 5/309 | 558431 | ShareLock| f | f | 2024-01-05 13:37:45.521408relation | 13008 | 24925 | | | | | | | | 5/309 | 558431 | ShareUpdateEx
clusiveLock | t | f |tuple | 13008 | 24929 | 0 | 1 | | | | | | 4/46 | 557784 | AccessExclusi
veLock | t | f |transactionid | | | | | | 1719 | | | | 4/46 | 557784 | ShareLock| f | f | 2024-01-05 13:37:42.61328808virtualxid | | | | | 4/46 | | | | | 4/46 | 557784 | ExclusiveLock| t | f |
(10 rows)virtualxid和virtualtransaction从字面上看都是虚拟事务ID的意思。它们的区别是位于pg_locks视图的不同部分virtualxid位于描述锁对象的部分 virtualtransaction位于描述持有锁或等待锁的部分。因此virtualxid表示这个锁对象是一个虚拟事务而virtualtransaction表示持有锁或等待锁的虚拟事务ID。
通过上图可以看出 5/309是建索引本身的vxid建索引需要等老事务结束所以用vxid等另外一个会话结束可以看到最后一行在请求别人的vxid 4/46。而这个vxid 4/46刚好是pid为557584的会话持有的所以这个阻塞不是等待获取表上的锁是在等待vxid的锁。
使用pstack 看一下现在被阻塞的这个会话的堆栈
rootubuntu-linux-22-04-desktop:~# pstack 558431
#0 0x0000ffff8faf5ea8 in epoll_pwait () from /lib/aarch64-linux-gnu/libc.so.6
#1 0x0000aaaae03e778c in WaitEventSetWait ()
#2 0x0000aaaae03e7b30 in WaitLatch ()
#3 0x0000aaaae040b428 in ProcSleep ()
#4 0x0000aaaae03fc064 in WaitOnLock ()
#5 0x0000aaaae03fd410 in LockAcquireExtended ()
#6 0x0000aaaae0400f54 in VirtualXactLock ()
#7 0x0000aaaae01c93b4 in WaitForOlderSnapshots ()
#8 0x0000aaaae01cd930 in DefineIndex ()
#9 0x0000aaaae041d270 in ProcessUtilitySlow.constprop.0 ()
#10 0x0000aaaae041c0d4 in standard_ProcessUtility ()
#11 0x0000ffff8f426270 in pgss_ProcessUtility () from /home/postgres/soft-16/lib/pg_stat_statements.so
#12 0x0000aaaae041a6cc in PortalRunUtility ()
#13 0x0000aaaae041a874 in PortalRunMulti ()
#14 0x0000aaaae041ae00 in PortalRun ()
#15 0x0000aaaae0416768 in exec_simple_query ()
#16 0x0000aaaae0417648 in PostgresMain ()
#17 0x0000aaaae0370514 in ServerLoop ()
#18 0x0000aaaae037165c in PostmasterMain ()
#19 0x0000aaaae0028464 in main ()DefineIndex()主要是处理索引创建的逻辑而常规锁的申请主要在接口 LockAcquire() 和 LockAcquireExtended()中实现。可以看到堆栈的最后处于等待的状态。 可以看一下其中的变量这个relationId24925对应的对象就是我们要创建的索引所在的表而对应的locktag_type 6结合LockTagType的定义可以知道这个6代表的是LOCKTAG_VIRTUALTRANSACTION对virtual transaction申请锁锁是5级表示ShareLock看到是不是有疑问了之前说CIC的锁相对于普通的建立索引降低了一个级别变成了4级锁但是这里是5级锁其实是不一样的这块获取的锁的locktag_type并不是针对relation的而是针对virtual transaction的源码注释里关于常规锁模式解析部分的也是针对于relation的。 postgres# select 24925::regclass;regclass
------------tab_test_1
(1 row)所以真正CIC过程表上的锁应该找到一个relation oid24925LockAcquireExtended()函数执行过程locktag-locktag_type为0时再查看lockmode的值。这里再次重新用gdb打上一个breakpoint抓到了表上申请的锁果然是4级锁即ShareUpdateExclusiveLock。 1.4 在线创建索引(CIC)操作的可能遇到的问题
PostgreSQL支持在线创建索引CREATE INDEX CONCURRENTLY不堵塞其他会话对被创建索引表的DMLINSERT,UPDATE,DELETE操作所以有时候为了不阻塞业务采用CIC而不是使用普通创建索引的方式。
问题
1.执行速度慢
从在线创建索引(CIC)的实现机制上可以看出它需要两次扫描表。所以不考虑锁阻塞的情况下它的执行时间可能会比正常创建索引慢很多。
2.执行失败后可能存在INVALID索引
因为第一次扫描并建立中间状态的索引INVALID后索引实际上就对后面的DML起作用了所以如果是在第二SCAN阶段索引创建失败了这个索引会一直影响DML性能、约束。
3.冲突不允许同时执行
create index concurrently 在线创建索引(CIC)功能无法并发执行因为这个操作在表上上的是ShareUpdateExclusiveLock锁四级锁自斥。
解决方法
第一个问题
扫描两次表的这个问题是其本身机制的问题只能尽量选取在业务不忙的时候除此之外尽量避免阻塞情况
尽量避免创建索引过程中两次SCAN之前对被创建索引表实施长事务并且长事务中包含修改被创建索引的表。在第二次SCAN前尽量避免开启长事务。
第二个问题
因为CIC的实现机制问题如果索引创建失败后可能会留下一个失效索引。失效索引不能被使用而且在进行DML的时候也会一并进行更改浪费主机资源。可以查看pg_index视图的indisvalid字段。如果为真此索引当前可以用于查询为假表示此索引可能不完整需要进行处理根据需求重建或者删除掉。
第三个问题
注意不要多个session同时对一张表做create index concurrently操作以防冲突。
