加字段慢的一个原因是数据‘搬迁’慢,另外一个重要因素是锁粒度特别大,容易产生阻塞。
先来看下PostgreSQL锁相关文档:https://www.postgresql.org/docs/10/explicit-locking.html
从锁冲突矩阵和锁定义看出ALTER TABLE属于AccessExclusiveLock类型,也就是常说的排它锁,它的锁粒度非常大,和所有其他锁冲突,所以给生产环境表加索引的时候非常容易出现被阻塞的现象。
而另外一个常见容易造成锁等待场景是创建索引,在PostgreSQL中CREATE INDEX属于SHARELock,与非'SHARE'锁冲突。
原因1:上一章节讲到,加字段和加索引操作会伴随着数据拷贝动作,会导致操作系统IO或者负载变高,在业务高峰期可能会影响性能;
原因2:Alter Table操作会拿表级排它锁,排它锁会阻塞其他访问该表的SQL操作,造成业务请求堆积,大量请求堆积可能导致连接数上涨、实例负载升高等问题;
使用CONCURRENTLY加索引时,持有锁降为ShareUpdateExclusiveLock,它和行级读写锁不冲突,换句话说就是不影响业务正常读写操作。
原因在上一章节已经讲了。
既然DDL操作这么难,那么我停业务后再执行总不会受其他操作影响了吧?
//事实上95%以上场景不必如此,剧情需要构造的例子
第一节在PostgreSQL核心进程中提到【autovacuum】进程,它是数据库中核心的进程之一,用于清理数据库死元组,具体用途和优化在后面章节展开。
来看下面场景:
构造测试数据:
stone=# create table test(id int,text text);
CREATE TABLE
stone=# insert into test(id,text) select i,'text:'||i from generate_series(1,10000000) as i;
INSERT 0 10000000
构造阻塞场景:
session 1 加索引:
stone=# create index CONCURRENTLY idx_test_text on test(text) ;
CREATE INDEX
session 2 vaccum表,锁粒度和autovaccum一样:
stone=# vacuum test;
VACUUM
session 3 使用pg_blocking_pids查看阻塞情况:
上图可以看出vaccum表操作被create index concurrently操作阻塞了,操作顺序反过来也是成立的,由此可以看出即使停掉业务DDL操作也可能被阻塞。可以通pg_blocking_pids函数排查。
###这个场景中,vacuum操作阻塞数组里是两个相同PID,有兴趣同学可以找下原因
--------------------------------掌握小原理,解决大问题!---------------------------------------
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。