两学一做网站是多少钱,wordpress 获取文章作者,平面设计大师,怎么建设自己的卡盟网站1.1 创建普通B-tree索引的整体流程
如下是梳理的创建普通B-tree索引的大概流程#xff0c;可供参考。
1.校验新索引的Catalog元数据|语法解析 ---将创建索引的sql解析成IndexStmt结构#xff5c;校验B-Tree的handler -----校验内核是否支持该类型的索引,在pg_am中查找可供参考。
1.校验新索引的Catalog元数据|语法解析 ---将创建索引的sql解析成IndexStmt结构校验B-Tree的handler -----校验内核是否支持该类型的索引,在pg_am中查找btree对应的handler校验索引列及比较函数 ----查找pg_attribute校验create index中指定的索引列是否存在如果存在记录attno并且根据atttypid表示表中列的字段类型在pg_opclass里查找对应的比较函数2.在文件系统中创建索引文件生成oid ---为新的索引文件生成唯一oid过程是生成一个新的oid然后查找pg_class的索引如果不存在就返回这个oid更新本地的relcache ---正在创建的relation添加到relcache创建索引文件以及写xlog ----文件系统中生成新的文件 base/xxx/xxx。xlog记录类型为XLogInsert(RM_SMGR_ID, XLOG_SMGR_CREATE | XLR_SPECIAL_REL_UPDATE)3.创建新索引的元数据索引作为对象的元数据写入到pg_class 索引文件引用的列插入pg_attribute索引本身相关信息插入pg_index|relcache失效invalid所有heap相关的元数据 ---为了使catalog元数据的变更对所有进程生效记录该索引对heap的依赖对opclass的依赖等等插入pg_depend使得新索引文件相关的relcache生效4.用函数btbuild构建B-tree索引通过index的索引列构建排序时需要用到的sortkey扫描tuple生成索引元组数组 构建B树叶节点对索引元组执行排序将排序成功的结点依次插入到B-Tree中自下向上构建B-Tree索引page --依次读取排好序的tuple填充到B-Tree的叶子节点上自下向上插入B-Tree
1.2 锁相关介绍
PostgreSQL里有很多可以加锁的对象表、单个页、单个元组、事务ID包括虚拟和永久ID和普通数据库对象等等常规锁的locktype主要有以下几种。有时候通过pg_locks查询的时候根据pid会查到许多的锁但是这些锁并不一定都是加在表上的根据locktype以及relation过滤出不同对象上的锁。
/** 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;如下是PostgreSQL里的常规锁其中AccessShareLock、RowShareLock、RowExclusiveLock属于弱锁ShareLock、ShareRowExclusiveLock、ExclusiveLock 、AccessExclusiveLock属于强锁。
/* NoLock is not a lock mode, but a flag value meaning dont get a lock */
#define NoLock 0
#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL),ANALYZE, CREATE * INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6 /* like EXCLUSIVE MODE, but allows ROW * SHARE */
#define ExclusiveLock 7 /* blocks ROW SHARE/SELECT...FOR * UPDATE */
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM * FULL, and unqualified LOCK TABLE */1.3 创建普通索引表上需要获取的锁(ShareLock)
使用如下的语句进行测试
postgres# create table tab_test_1(id int);
CREATE TABLE
postgres# insert into tab_test_1 values(1);
INSERT 0 1
postgres# insert into tab_test_1 values(2);
INSERT 0 1
postgres# insert into tab_test_1 values(3);
INSERT 0 1
postgres# begin;
BEGIN
postgres*# create index idx_1 on tab_test_1(id);
CREATE INDEX
postgres*#普通创建索引获取ShareLock是5级锁这里通过SQL可以查询到其实在创建索引的过程中不仅原始的表上会申请锁。对应的几张系统表和系统表索引上也会申请锁因为创建索引也涉及到系统表元数据的校验和更改这里访问pg_namespace的意义在于索引将与其父表位于同一命名空间中。
当有多个事务同时对表进行读操作时如果不做任何锁定会导致数据不一致。例如一个事务在读取数据时另一个事务做了修改然后第一个事务读到的数据已经不是最新的。这时候就需要使用AccessShareLock来控制并发的读取操作保持数据的一致性。
postgres# select l.locktype,ns.nspname,a.relname,a.relkind,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p,pg_class a,pg_namespace ns where l.locktyperelation and l.pidp.pid and query not like %pg_stat_activity% and l.relationa.oid and a.relnamespacens.oid;locktype | nspname | relname | relkind | pid | mode | granted | query_start |query | state
----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------relation | public | tab_test_1 | r | 409706 | ShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_class | r | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_namespace | r | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_namespace_nspname_index | i | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_namespace_oid_index | i | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_class_oid_index | i | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_class_relname_nsp_index | i | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transactionrelation | pg_catalog | pg_class_tblspc_relfilenode_index | i | 409706 | AccessShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); | idle in transaction
(8 rows)1.4 关于ShareLock的其他场景
在源码的注释部分可以看到CREATE INDEX (WITHOUT CONCURRENTLY) 的时候会在表上申请ShareLock这和我们的测试结果相符合但是ShareLock不仅仅创建索引的时候会获取当多个事务更新同一行的时候也会申请ShareLock不过这个ShareLock不是在表级别申请的而是在分配事务ID时对这个事务ID进行加锁 用于元组并发更新时做事务等待。
分别用两个session做如下操作
//session 1,pid434865postgres# begin;
BEGIN
postgres*# update tab_test_1 set id7 where id1;
UPDATE 1
postgres*#//session 2,pid433290postgres# begin;
BEGIN
postgres*# update tab_test_1 set id7 where id1;
然后用另一个session查询锁的状态
postgres# select * from pg_locks where pid434865;locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode| granted | fastpath | waitstart
-----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------relation | 13008 | 16725 | | | | | | | | 3/16 | 434865 | RowExclusiveL
ock | t | t |virtualxid | | | | | 3/16 | | | | | 3/16 | 434865 | ExclusiveLock| t | t |transactionid | | | | | | 1699 | | | | 3/16 | 434865 | ExclusiveLock| t | f |
(3 rows)postgres# select * from pg_locks where pid433290;locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode| granted | fastpath | waitstart
-----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------relation | 13008 | 16725 | | | | | | | | 4/3 | 433290 | RowExclusiveLock | t | t |virtualxid | | | | | 4/3 | | | | | 4/3 | 433290 | ExclusiveLock| t | t |transactionid | | | | | | 1700 | | | | 4/3 | 433290 | ExclusiveLock| t | f |tuple | 13008 | 16725 | 0 | 1 | | | | | | 4/3 | 433290 | ExclusiveLock| t | f |transactionid | | | | | | 1699 | | | | 4/3 | 433290 | ShareLock| f | f | 2024-01-04 13:33:16.66788908
(5 rows)可以通过上述测试看到pid为433290的这个session它的pg_locks的最后一行 的granted为’f’,说明该进程被阻塞并且是在申请类型为tansactionid的锁时被阻塞了对应tansactionid1699的事务。从表上可以看出这个tansactionid已经被进程pid为434865的session1会话持有了。
行锁的阻塞信息是通过tansactionid类型的锁体现的行锁是会在数据行上加自己的tansactionid的另一个进程读到这一行时如果发现上一个操作该行的事务未结束会把上一个事务的tansactionid读出来然后申请在这个tansactionid上加上ShareLock等待上一个事务结束再获得ExclusiveLock。而持有行锁的进程已经在此tansactionid上加了ExclusiveLock所以后面要更新这行的进程会被阻塞。
ShareLock即读锁ExclusiveLock即写锁。对事务ID加ShareLock是为了事务不提交其他人看不到修改后的行而ExclusiveLock是防止并发操作的。
1.5 创建普通索引时阻塞的一些操作
如下是对创建普通索引申请的ShareLock后的一些阻塞情况
1.5.1 建普通索引阻塞DML操作
//开一个session,开启事务创建索引然后不提交postgres# begin;
BEGIN
postgres*# create index idx_1 on tab_test_1(id);
CREATE INDEX
postgres*#//新开一个session查询这张表,可以正常访问不阻塞读
postgres# select * from tab_test_1 ;id
----123
(3 rows)//新开多个session分别做dml操作
//session A
postgres# insert into tab_test_1 values(6);
//session B
postgres# update tab_test_1 set id7 where id1;
//session C
postgres# delete from tab_test_1 where id1;//另开一个session查看获取的锁postgres# select l.locktype,ns.nspname,a.relname,a.relkind,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p,pg_class a,pg_namespace ns where l.locktyperelation and l.pidp.pid and query not like %pg_stat_activity% and l.relationa.oid and a.relnamespacens.oid and a.relnametab_test_1;locktype | nspname | relname | relkind | pid | mode | granted | query_start | query |state
--------------------------------------------------------------------------------------------------------------------------------------------------
---------------------relation | public | tab_test_1 | r | 409706 | ShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id); |idle in transactionrelation | public | tab_test_1 | r | 410009 | RowExclusiveLock | f | 2024-01-04 12:48:34.64065508 | insert into tab_test_1 values(6); |activerelation | public | tab_test_1 | r | 420570 | RowExclusiveLock | f | 2024-01-04 12:48:37.45063208 | update tab_test_1 set id7 where id1; |activerelation | public | tab_test_1 | r | 420581 | RowExclusiveLock | f | 2024-01-04 12:48:39.82859808 | delete from tab_test_1 where id1; |active
(4 rows)//以及查看阻塞源可以看到表上的dml操作都是被create index 给阻塞了。pid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd | que
ry
------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------409706 | {} | idletx | Client:ClientRead | | 01:02:44 | 5 | | postgres | postgres | 3 | [409706] create index idx_1 on tab_test_1(id);410009 | {409706} | waiting | Lock:relation | 00:01:57 | 00:01:57 | | 2,147,483,642 | postgres | postgres | 0 | [410009] . insert into tab_test_1 values(6);420570 | {409706} | waiting | Lock:relation | 00:01:54 | 00:01:54 | | 2,147,483,642 | postgres | postgres | 0 | [420570] . update tab_test_1 set id7 where id1;420581 | {409706} | waiting | Lock:relation | 00:01:51 | 00:01:51 | | 2,147,483,642 | postgres | postgres | 0 | [420581] . delete from tab_test_1 where id1;
(4 rows)1.5.2 建普通索引阻塞DDL操作
//开一个session,开启事务创建索引然后不提交postgres# begin;
BEGIN
postgres*# create index idx_1 on tab_test_1(id);
CREATE INDEX
postgres*#//新开一个session执行DDL操作alter table加列postgres# alter table tab_test_1 add column name varchar(20);//另开一个session查看获取的锁
postgres# select l.locktype,ns.nspname,a.relname,a.relkind,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p,pg_class a,pg_namespace ns where l.locktyperelation and l.pidp.pid and query not like %pg_stat_activity% and l.relationa.oid and a.relnamespacens.oid and a.relnametab_test_1;locktype | nspname | relname | relkind | pid | mode | granted | query_start | query| state
---------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------relation | public | tab_test_1 | r | 409706 | ShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id);| idle in transactionrelation | public | tab_test_1 | r | 422658 | AccessExclusiveLock | f | 2024-01-04 12:51:52.94843208 | alter table tab_test_1 add column nam
e varchar(20); | active
(2 rows)//查看阻塞源可以看到表上alter table加列的DDL操作被create index给阻塞了。pid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd |query
------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------409706 | {} | idletx | Client:ClientRead | | 01:04:09 | 6 | | postgres | postgres | 1 | [409706] create index idx_1 on tab_test_1(id);422658 | {409706} | waiting | Lock:relation | 00:00:03 | 00:00:03 | 1 | 2,147,483,641 | postgres | postgres | 0 | [422658] . alter table tab_test_1 add column name varchar(20);
(2 rows)//新开一个session执行DDL操作drop table删除表
postgres# drop table tab_test_1;//另开一个session查看获取的锁postgres# select l.locktype,ns.nspname,a.relname,a.relkind,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p,pg_class a,pg_namespace ns where l.locktyperelation and l.pidp.pid and query not like %pg_stat_activity% and l.relationa.oid and a.relnamespacens.oid and a.relnametab_test_1;locktype | nspname | relname | relkind | pid | mode | granted | query_start | query| state
---------------------------------------------------------------------------------------------------------------------------------------------------
----------------------relation | public | tab_test_1 | r | 409706 | ShareLock | t | 2024-01-04 11:48:11.80781608 | create index idx_1 on tab_test_1(id);| idle in transactionrelation | public | tab_test_1 | r | 422669 | AccessExclusiveLock | f | 2024-01-04 12:53:17.66405308 | drop table tab_test_1;| active
(2 rows)//查看阻塞源可以看到表上drop table删除表的DDL操作被create index给阻塞了。pid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd | quer
y
------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------409706 | {} | idletx | Client:ClientRead | | 01:05:43 | 7 | | postgres | postgres | 1 | [409706] create index idx_1 on tab_test_1(id);422669 | {409706} | waiting | Lock:relation | 00:00:12 | 00:00:12 | 1 | 2,147,483,640 | postgres | postgres | 0 | [422669] . drop table tab_test_1;
(2 rows)1.5.3 阻塞vacuum,vacuum full,analyze
//开一个session,开启事务创建索引然后不提交postgres# begin;
BEGIN
postgres*# create index idx_1 on tab_test_1(id);
CREATE INDEX
postgres*#//新开一个sessionvacuum该表postgres# vacuum tab_test_1;//另开一个session查看获取的锁postgres# select l.locktype,ns.nspname,a.relname,a.relkind,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p,pg_class a,pg_namespace ns where l.locktyperelation and l.pidp.pid and query not like %pg_stat_activity% and l.relationa.oid and a.relnamespacens.oid;locktype | nspname | relname | relkind | pid | mode | granted | query_start | query| state
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------relation | public | tab_test_1 | r | 452619 | ShareLock | t | 2024-01-04 14:50:00.52449708 | create index idx_1 on tab_test_1
(id); | idle in transactionrelation | public | tab_test_1 | r | 452585 | ShareUpdateExclusiveLock | f | 2024-01-04 14:50:35.24464408 | vacuum tab_test_1 ;| active
(2 rows)//查看阻塞源可以看到表vacuum操作被create index给阻塞了。pid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd | quer
y
------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------452619 | {} | idletx | Client:ClientRead | | 00:00:40 | 1 | | postgres | postgres | 1 | [452619] create index id
x_1 on tab_test_1(id);452585 | {452619} | waiting | Lock:relation | 00:00:02 | 00:00:02 | | 2,147,483,646 | postgres | postgres | 0 | [452585] . vacuum tab_te
st_1 ;
(2 rows)可以看到vacuum改表被create index阻塞了vacuum fullanalyze测试方法类似也是一样被阻塞了。
//阻塞vacuum fullpid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd | quer
y
------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------452619 | {} | idletx | Client:ClientRead | | 00:12:38 | 2 | | postgres | postgres | 1 | [452619] create index id
x_1 on tab_test_1(id);452585 | {452619} | waiting | Lock:relation | 00:00:07 | 00:00:07 | 1 | 2,147,483,645 | postgres | postgres | 0 | [452585] . vacuum full t
ab_test_1 ;
(2 rows)//阻塞analyzepid | blocked_by | state | wait | wait_age | tx_age | xid_age | xmin_ttf | datname | usename | blkd | quer
y
------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------452619 | {} | idletx | Client:ClientRead | | 00:14:04 | 2 | | postgres | postgres | 1 | [452619] create index id
x_1 on tab_test_1(id);452585 | {452619} | waiting | Lock:relation | 00:00:07 | 00:00:07 | | 2,147,483,645 | postgres | postgres | 0 | [452585] . analyze tab_t
est_1;
(2 rows)1.6 创建普通B-tree索引的可能遇到的问题
问题
创建普通B-tree索引在表上申请的是ShareLockShareLock和RowExclusiveLock是冲突的所以create index会等待表上所有的DML增删改结束。但是实际在生产环境下如果业务不停运行涉及到要加索引的表不停的有DML操作那么执行了create index操作后可能会发现长时间都没有反应因为create index操作可能长时间获取不到锁然后一直处于锁的等待队列里。
就算能获取到了锁可以创建索引了但是在创建索引期间也会阻塞所有的DML增删改如果需要加索引的表是一个大表并且需要加索引的这列数据也比较复杂那么可能执行时间比较长那么对DML的阻塞时间也会比较长这对于某些业务场景可能是不能接受的。除了阻塞时间长一个问题外如果建索引期间业务较多被阻塞的DML大量累积有可能导致pg_locks里累积的越来越多最后导致OOM。
解决方法
1对于建索引的操作尽量选择业务量较少的时候执行或者有条件在停业务的窗口内完成。
2设置lock_timeout不让其长时间的获取锁阻塞业务。
3可以使用create index concurrently 在线创建索引(CIC)降低了创建索引在表上申请的锁的级别ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突不会阻塞DML操作。
创建索引慢 至于常见的创建索引慢的原因也可以参考我的这篇文章常见的创建索引慢的原因
