MySQL在线加索引引起的堵塞案例引出Online DDL锁分析
案例:
线上一个sql查询,开发事先没商量就自己上线了.这个sql很简单就是一个简单select一张表然后两个字段的条件过滤,但问题是这两个字段都没有索引,导致每次都全表扫描,很慢.
发现这个慢查询后,去观察了这两个字段的字段的过滤性,其中一个字段的过滤性基本上是唯一的,重复的很少,决定在这个字段上加一个索引.
加索引时,发现一直在等待中,等待MDL锁,等了一会,还是一直处于waiting mdl锁中,因为加索引处于等待中,导致后面的事务也跟着进不来,也处于等待中,为了不影响业务,只能先撤销加索引这个操作;
这里先引申一下,5.7开始的DDL基本上就是online(大部分是inplace模式,也有部分是copy模式,这里不累述).按官方的说法就是会和dml并行,不影响线上的业务操作.但在这个案例中ddl被堵了,后面的dml因为ddl的原因也堵了.那既然引申这些背景,那我们继续深入一下.来讲讲online DDL(inplace)的过程及锁的一些情况;
大家都知道在早期的版本mysql做DDL操作是会锁表的(具体什么锁,这里先不解释),所以大家在操作DDL的时候很多都会采用pt-online-shcema-change等类似的工具进行操作.从5.7开始,mysql就推出了inplace模式,in-place对于DDL的影响主要是在改变表机构时提高表的稳定和性能,允许部分DDL操作是online,意思是允许DML并发操作.
这是应用层面的信息,其实通过源码解读就很清晰的了解到即使是inplace模式下,online也只是相对的.这就涉及到mysql server层的MDL(metadata lock),至于MDL锁的源码信息,我也讲不好,可以参考八怪大神的blog,对于MDL有很清晰的源码解读(http://blog.itpub.net/7728585/viewspace-2143093/)
这里只简单讲述online ddl操作过程MDL锁的大致进程:
第一步,在进行online DDL前,mysql server 会去请求目的表上的MDL_SHARED_UPGRADABLE(SU),从名字上大概就能知其意,就是个共享锁.这个锁是与DML和select兼容的,这个时候的操作仍然还在mysql server层.这个锁很快就会转到下一步,猜测这一步只是为下一步做准备,具体干什么用,还有待研究.抱歉;
第二步,这一步MDL就要升级锁,将SU升级为MDL_EXCLUSIVE(X),X锁在任何层都是独占锁,这个时候就开始与其他锁有排斥了.这个时候如果目标表上有任何锁,都会排队等待锁释放,同时,因为X锁的优先权限,其他针对目标表的锁和操作都会排在这个锁的后面.再获得这个锁之后,就开始元数据的操作了,比如加字段或者修改字段等DDL操作.整个操作其实是很快的,打个不恰当的比喻:就好像更改一行数据一样,而且这行数据还是很小的.所以在正常情况下,对于后续的操作是感受不到堵塞的,也就是称为online ddl的原因.对于业务小,数据小的表,这个锁很快就获得,但也很快就会释放(下一步操作),所以这一步和下一步才是inplace的核心.
第三步:这一步就要降锁了,从MDL_EXCLUSIVE(X) 降为MDL_SHARED_UPGRADABLE(SU).前面讲过了,SU级别的锁与MDL和select操作是兼容的,所以到这一步,MDL就可以并发操作了.那降锁到这一步的目的是干嘛勒?很简单,大家都知道的copy表数据.在上一步已经修改元数据了,表结构已经修改为我们希望的结构,这个时候就要把原来表的数据copy到新表里面去了.copy这个操作就进入innodb层了,具体怎么copy的,这里就不详细叙述了,我们只讲MDL锁的进程.
在进入下一步之前,这个时间就得根据表数据的大小来决定了.但不影响MDL和select.前面已经讲过了兼容的问题.所以,就只剩下时间问题了.
第四步:在copy完数据后,这个时候回到mysql server层MDL又要升级锁了,将MDL_SHARED_UPGRADABLE(SU)升级为MDL_EXCLUSIVE(X),至于这个X锁前面已经详细描述了,不累述.至于升锁的目的,用过pt-online-schema-change的人都知道,在copy完数据后,要rename table,drop trigger等等操作,其实这里也是类似的.就是要把原表干掉,然后启用新表,所以才要加一个独占锁.同样的这个操作也是很快的(可以感受下rename table的速度),所以,从应用层和感觉上不存在堵塞,但其实因为独占锁的原因,需要经历排队等前面的锁释放,然后加锁,在释放锁三个阶段.
MDL锁在这个阶段也就结束了,整个DDL也就结束了.这里只是讲一个MDL锁的大致的过程,其实还有很多细节,比如独占锁的抢占,排队的检测,加锁的检测,很多很多.无奈本人技术有限,就这个过程也是从大牛八怪哪里学习过来的,属于抄袭.哈哈.因为是抄袭的,所以没有贴源码和注释,八怪大牛在上面提到的blog上写的很详细,想了解详细过程的请移步.
了解完online DDL加锁的过程,现在回到案例中来.就能很清晰的了解整个现象了.
因为在目标表上有事务未结束或者有锁未释放,导致在加索引时,独占锁没法获取锁资源,一直在等待前面的锁释放.这个时候的状态就是Waiting for table metadata lock.
同理,因为独占锁的优先权限,所有DML和select都排在加索引的后面.状态也是一样的Waiting for table metadata lock;
清楚原因了,就去寻找这个锁在哪?第一反应就是有未提交的事务导致该表上的锁一直不释放,为什么会一直未提交.通过show engine innodb status查看未提交的事务是哪一个.
然而这次落空了,通过show engine innodb status查看,并没有看到该目的表上有未提交的事务.
既然show engine看不到未提交事务,那就换一种思路在去找,看看MDL锁信息.5.7引入了两个视图performance_schema.setup_instruments和performance_schema.metadata_locks,setup_instruments可以控制且监控前台线程的运行情况,类似早期版本的show profile,metadata_locks是统计锁的情况视图.具体请参考官方文档.
开启监控: UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
查看MDL信息:select * from performance_schema.metadata_locks\G
刷了很久,也没发现这个目的表的MDL情况.那就说明这个事务在开启监控前就一直未提交,且在目的表上的锁也一直未释放.
ps:metadata_locks统计的是在触发了setup_instruments后的统计信息.在触发之前的MDL锁是不统计的.
在show engine innodb status里面看不到未提交的事务,就怀疑这个锁还没有进入innodb层.那就还在mysql server层,而且还一直存在,这个时候想的就是select了.select在server层会产生一个MDL共享锁,就是MDL_SHARED_READ(SR) 虽然这是个共享锁,与很多锁都兼容,但与独占锁是不兼容的.如果在请求独占锁的时候遇到SR锁,也是需要等到SR锁释放后才能获得的.放在这个案例中,就可以怀疑是有一个select未结束了.
开始寻找这个select,既然是未提交的事务,我们就去看看这个未提交的事务最后一次执行的sql是什么.
先找出这个未提交事务的thread id:
select trx_mysql_thread_id from information_schema.innodb_trx where trx_mysql_thread_id in (select id from information_schema.processlist where db ='db_name');
然后根据thread_id查出最后一次执行的sql是啥:
select a.thread_id,a.processlist_id,b.TIMER_WAIT,b.lock_time,b.sql_text
from performance_schema.threads a,performance_schema.events_statements_current b
where b.THREAD_ID=a.THREAD_ID and a.processlist_id in (4092334,4393666);
在这个案例中查出来的竟然是show warnings.我去,线上的sql干嘛取show warnings??? 但显然这个show warnings不是这个锁的罪魁祸首.这个时候就又走入死胡同了.没办法,只能求助开发同事了,让他们排查这个show warnings所在的事务.最后查出来是代码里面显示的begin,然后select了目的表,然后就是一大串show warnings.这就不知道是哪个坑了.
原因总算查到了,就是因为这个select导致堵塞了DDL.因为是线上环境,没办法修改sql,最后采用的方法是直接kill 这两个没用的线程,具体方法如下:
1.开启alter add index操作. 目的是让这个线程的优先级排到第一个,这样就能保证其他线程不会堵塞DDL;显然,这个时候的DDL是处于被堵塞状态的,因为那个select还存在.
2.接着就kill 这两个线程,结束这两个没用的事务.这样一来,DDL马上就可以加锁,然后降锁.其他DML也不会堵塞了.
3.在这个过程需要密切注意.因为第一,那个select线程虽然被强制kill了,被kill后,又会在开一个线程继续这个无用功.也就是在目的表继续加一个SR锁.前面讲过,在DDL copy完数据后,还会继续升级锁为X锁,这个时候仍然会被堵塞.所以还需要再kill一次这两个select线程.其次,如果表数据过大,服务器的压力还是会显著的提高的,所以要密切注意观察,做好预防.
总结:1.Online DDL 虽然在官方文档说的是不加锁,但从源码可以看出来,在mysql server层的MDL锁是一直存在的.具体请参考前面关于MDL锁的讲解.这个锁大致过程就是:SU->X->SU->X->释放.
2.select 也会在server层加一把共享锁(SR),共享锁与独占锁的排斥不细讲.
3.排查思路.线程的堵塞大多都是因为锁的原因,因为mysql分为server层和engine层.尤其是innodb的锁很多都是在server层的,通过mysql前台的状态信息,是很难看到的,需要结合server层的一些思路来思考.5.7在performance_schema里面提供了大量的视图来监控这些指标,平时为了保证性能我们一般都没有开启,在遇到问题的时候,可以短暂的开启后查看一下.
整个过程我尽量用文字描述简单一点,可能看起来有点费劲.有不足之处请多多赐教.
最后再次感谢八怪大神的指导.时刻关注八怪大神的blog,你的mysql技能会有飞速提升的.
八怪大神的blog:http://blog.itpub.net/7728585/
ps:八怪大神不仅是mysql大神,还是oracle OCM,C++大牛,linux内核专家.
案例:
线上一个sql查询,开发事先没商量就自己上线了.这个sql很简单就是一个简单select一张表然后两个字段的条件过滤,但问题是这两个字段都没有索引,导致每次都全表扫描,很慢.
发现这个慢查询后,去观察了这两个字段的字段的过滤性,其中一个字段的过滤性基本上是唯一的,重复的很少,决定在这个字段上加一个索引.
加索引时,发现一直在等待中,等待MDL锁,等了一会,还是一直处于waiting mdl锁中,因为加索引处于等待中,导致后面的事务也跟着进不来,也处于等待中,为了不影响业务,只能先撤销加索引这个操作;
这里先引申一下,5.7开始的DDL基本上就是online(大部分是inplace模式,也有部分是copy模式,这里不累述).按官方的说法就是会和dml并行,不影响线上的业务操作.但在这个案例中ddl被堵了,后面的dml因为ddl的原因也堵了.那既然引申这些背景,那我们继续深入一下.来讲讲online DDL(inplace)的过程及锁的一些情况;
大家都知道在早期的版本mysql做DDL操作是会锁表的(具体什么锁,这里先不解释),所以大家在操作DDL的时候很多都会采用pt-online-shcema-change等类似的工具进行操作.从5.7开始,mysql就推出了inplace模式,in-place对于DDL的影响主要是在改变表机构时提高表的稳定和性能,允许部分DDL操作是online,意思是允许DML并发操作.
这是应用层面的信息,其实通过源码解读就很清晰的了解到即使是inplace模式下,online也只是相对的.这就涉及到mysql server层的MDL(metadata lock),至于MDL锁的源码信息,我也讲不好,可以参考八怪大神的blog,对于MDL有很清晰的源码解读(http://blog.itpub.net/7728585/viewspace-2143093/)
这里只简单讲述online ddl操作过程MDL锁的大致进程:
第一步,在进行online DDL前,mysql server 会去请求目的表上的MDL_SHARED_UPGRADABLE(SU),从名字上大概就能知其意,就是个共享锁.这个锁是与DML和select兼容的,这个时候的操作仍然还在mysql server层.这个锁很快就会转到下一步,猜测这一步只是为下一步做准备,具体干什么用,还有待研究.抱歉;
第二步,这一步MDL就要升级锁,将SU升级为MDL_EXCLUSIVE(X),X锁在任何层都是独占锁,这个时候就开始与其他锁有排斥了.这个时候如果目标表上有任何锁,都会排队等待锁释放,同时,因为X锁的优先权限,其他针对目标表的锁和操作都会排在这个锁的后面.再获得这个锁之后,就开始元数据的操作了,比如加字段或者修改字段等DDL操作.整个操作其实是很快的,打个不恰当的比喻:就好像更改一行数据一样,而且这行数据还是很小的.所以在正常情况下,对于后续的操作是感受不到堵塞的,也就是称为online ddl的原因.对于业务小,数据小的表,这个锁很快就获得,但也很快就会释放(下一步操作),所以这一步和下一步才是inplace的核心.
第三步:这一步就要降锁了,从MDL_EXCLUSIVE(X) 降为MDL_SHARED_UPGRADABLE(SU).前面讲过了,SU级别的锁与MDL和select操作是兼容的,所以到这一步,MDL就可以并发操作了.那降锁到这一步的目的是干嘛勒?很简单,大家都知道的copy表数据.在上一步已经修改元数据了,表结构已经修改为我们希望的结构,这个时候就要把原来表的数据copy到新表里面去了.copy这个操作就进入innodb层了,具体怎么copy的,这里就不详细叙述了,我们只讲MDL锁的进程.
在进入下一步之前,这个时间就得根据表数据的大小来决定了.但不影响MDL和select.前面已经讲过了兼容的问题.所以,就只剩下时间问题了.
第四步:在copy完数据后,这个时候回到mysql server层MDL又要升级锁了,将MDL_SHARED_UPGRADABLE(SU)升级为MDL_EXCLUSIVE(X),至于这个X锁前面已经详细描述了,不累述.至于升锁的目的,用过pt-online-schema-change的人都知道,在copy完数据后,要rename table,drop trigger等等操作,其实这里也是类似的.就是要把原表干掉,然后启用新表,所以才要加一个独占锁.同样的这个操作也是很快的(可以感受下rename table的速度),所以,从应用层和感觉上不存在堵塞,但其实因为独占锁的原因,需要经历排队等前面的锁释放,然后加锁,在释放锁三个阶段.
MDL锁在这个阶段也就结束了,整个DDL也就结束了.这里只是讲一个MDL锁的大致的过程,其实还有很多细节,比如独占锁的抢占,排队的检测,加锁的检测,很多很多.无奈本人技术有限,就这个过程也是从大牛八怪哪里学习过来的,属于抄袭.哈哈.因为是抄袭的,所以没有贴源码和注释,八怪大牛在上面提到的blog上写的很详细,想了解详细过程的请移步.
了解完online DDL加锁的过程,现在回到案例中来.就能很清晰的了解整个现象了.
因为在目标表上有事务未结束或者有锁未释放,导致在加索引时,独占锁没法获取锁资源,一直在等待前面的锁释放.这个时候的状态就是Waiting for table metadata lock.
同理,因为独占锁的优先权限,所有DML和select都排在加索引的后面.状态也是一样的Waiting for table metadata lock;
清楚原因了,就去寻找这个锁在哪?第一反应就是有未提交的事务导致该表上的锁一直不释放,为什么会一直未提交.通过show engine innodb status查看未提交的事务是哪一个.
然而这次落空了,通过show engine innodb status查看,并没有看到该目的表上有未提交的事务.
既然show engine看不到未提交事务,那就换一种思路在去找,看看MDL锁信息.5.7引入了两个视图performance_schema.setup_instruments和performance_schema.metadata_locks,setup_instruments可以控制且监控前台线程的运行情况,类似早期版本的show profile,metadata_locks是统计锁的情况视图.具体请参考官方文档.
开启监控: UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
查看MDL信息:select * from performance_schema.metadata_locks\G
刷了很久,也没发现这个目的表的MDL情况.那就说明这个事务在开启监控前就一直未提交,且在目的表上的锁也一直未释放.
ps:metadata_locks统计的是在触发了setup_instruments后的统计信息.在触发之前的MDL锁是不统计的.
在show engine innodb status里面看不到未提交的事务,就怀疑这个锁还没有进入innodb层.那就还在mysql server层,而且还一直存在,这个时候想的就是select了.select在server层会产生一个MDL共享锁,就是MDL_SHARED_READ(SR) 虽然这是个共享锁,与很多锁都兼容,但与独占锁是不兼容的.如果在请求独占锁的时候遇到SR锁,也是需要等到SR锁释放后才能获得的.放在这个案例中,就可以怀疑是有一个select未结束了.
开始寻找这个select,既然是未提交的事务,我们就去看看这个未提交的事务最后一次执行的sql是什么.
先找出这个未提交事务的thread id:
select trx_mysql_thread_id from information_schema.innodb_trx where trx_mysql_thread_id in (select id from information_schema.processlist where db ='db_name');
然后根据thread_id查出最后一次执行的sql是啥:
select a.thread_id,a.processlist_id,b.TIMER_WAIT,b.lock_time,b.sql_text
from performance_schema.threads a,performance_schema.events_statements_current b
where b.THREAD_ID=a.THREAD_ID and a.processlist_id in (4092334,4393666);
在这个案例中查出来的竟然是show warnings.我去,线上的sql干嘛取show warnings??? 但显然这个show warnings不是这个锁的罪魁祸首.这个时候就又走入死胡同了.没办法,只能求助开发同事了,让他们排查这个show warnings所在的事务.最后查出来是代码里面显示的begin,然后select了目的表,然后就是一大串show warnings.这就不知道是哪个坑了.
原因总算查到了,就是因为这个select导致堵塞了DDL.因为是线上环境,没办法修改sql,最后采用的方法是直接kill 这两个没用的线程,具体方法如下:
1.开启alter add index操作. 目的是让这个线程的优先级排到第一个,这样就能保证其他线程不会堵塞DDL;显然,这个时候的DDL是处于被堵塞状态的,因为那个select还存在.
2.接着就kill 这两个线程,结束这两个没用的事务.这样一来,DDL马上就可以加锁,然后降锁.其他DML也不会堵塞了.
3.在这个过程需要密切注意.因为第一,那个select线程虽然被强制kill了,被kill后,又会在开一个线程继续这个无用功.也就是在目的表继续加一个SR锁.前面讲过,在DDL copy完数据后,还会继续升级锁为X锁,这个时候仍然会被堵塞.所以还需要再kill一次这两个select线程.其次,如果表数据过大,服务器的压力还是会显著的提高的,所以要密切注意观察,做好预防.
总结:1.Online DDL 虽然在官方文档说的是不加锁,但从源码可以看出来,在mysql server层的MDL锁是一直存在的.具体请参考前面关于MDL锁的讲解.这个锁大致过程就是:SU->X->SU->X->释放.
2.select 也会在server层加一把共享锁(SR),共享锁与独占锁的排斥不细讲.
3.排查思路.线程的堵塞大多都是因为锁的原因,因为mysql分为server层和engine层.尤其是innodb的锁很多都是在server层的,通过mysql前台的状态信息,是很难看到的,需要结合server层的一些思路来思考.5.7在performance_schema里面提供了大量的视图来监控这些指标,平时为了保证性能我们一般都没有开启,在遇到问题的时候,可以短暂的开启后查看一下.
整个过程我尽量用文字描述简单一点,可能看起来有点费劲.有不足之处请多多赐教.
最后再次感谢八怪大神的指导.时刻关注八怪大神的blog,你的mysql技能会有飞速提升的.
八怪大神的blog:http://blog.itpub.net/7728585/
ps:八怪大神不仅是mysql大神,还是oracle OCM,C++大牛,linux内核专家.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20892230/viewspace-2148853/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/20892230/viewspace-2148853/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
