南充企业网站建设,做网站要几个部门组成,一个虚拟机怎么做两个网站,黄冈网站制作爱可生开源社区.
爱可生开源社区,提供稳定的MySQL企业级开源工具及服务,每年1024开源一款优良组件,并持续运营维护。 背景
之前碰到客户咨询定位DDL阻塞的相关问题,整理了一下方法,如何解决DDL被阻塞的问题。下面,就这个问题,整理了一下思路#xff1a;
怎么判断一个 DDL 是… 爱可生开源社区.
爱可生开源社区,提供稳定的MySQL企业级开源工具及服务,每年1024开源一款优良组件,并持续运营维护。 背景
之前碰到客户咨询定位DDL阻塞的相关问题,整理了一下方法,如何解决DDL被阻塞的问题。下面,就这个问题,整理了一下思路
怎么判断一个 DDL 是不是被阻塞了
当 DDL 被阻塞时,怎么找出阻塞它的会话
1. 如何判断一个DDL是不是被阻塞了
测试过程如下
mysql use test;Database changed mysql CREATE TABLE test ( - id int(11) AUTO_INCREMENT PRIMARY KEY, - name varchar(10) - );Query OK, 0 rows affected (0.01 sec) # 插入数据 mysql insert into test values (1,aaa),(2,bbb),(3,ccc),(4,ddd); Query OK, 1 row affected (0.01 sec) mysql begin; Query OK, 0 rows affected (0.01 sec) mysql select * from test; ---------- | id | name | ---------- | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | ---------- 4 rows in set (0.00 sec)# 模拟元数据锁阻塞 # 会话 1 mysql lock tables test read; Query OK, 0 rows affected (0.00 sec) # 会话 2 mysql alter table test add c1 varchar(25); 阻塞中 # 会话 3 mysql show processlist;
DDL一旦被阻塞了,后续针对该表的所有操作都会被阻塞,都会显示 Waiting for table metadata lock。
上述情况的解决方案Kill DDL 操作或 Kill 阻塞 DDL 的会话。 下面对于DDL的操作,我们需要获取元数据库锁的阶段有两个方面DDL开始之初和DDL结束之前。如果是后者,就意味着之前的操作都要回滚,成本相对较高。所以,碰到类似情况,我们一般都会Kill阻塞DDL的会话。 2. 怎么知道是哪些会话阻塞了DDL
sys.schema_table_lock_waits 是 MySQL 5.7 引入的,用来定位 DDL 被阻塞的问题。
针对上面这个情况。可以查看 sys.schema_table_lock_waits 的输出。
mysql select * from sys.schema_table_lock_waits\G;
只有一个alter操作,却产生了两条记录,而且两条记录的Kill对象还不一样。如果对表结构不熟悉或不仔细看记录内容的话,难免会kill错对象。
两条记录的 blocking_lock_type 类型分别为 shared_read_only 和 shared_upgradable。我们需要kill掉的是 shared_read_only。
在ddl操作被阻塞后,如果后续有多个查询被ddl操作堵塞,还会产生2n多个条记录。
在定位问题时,这2n条记录看起来就比较难以定位了。这个时候,我们需要对上述2n条记录进行过滤。过滤的关键是 blocking_lock_type 不等于 shared_upgradable。
shared_upgradable 是一个可升级的共享元数据锁,加锁期间,允许并发查询和更新。所以,阻塞ddl的不会是 shared_upgradable。
针对上面这个场景,我们可以通过下面这个查询来精确地定位出需要 Kill 的会话。
mysql SELECT sql_kill_blocking_connection FROM sys.schema_table_lock_waits
WHERE blocking_lock_type SHARED_UPGRADABLE
AND waiting_query alter table test add c1 varchar(25); MySQL 5.7中使用sys.schema_table_lock_waits的注意事项
sys.schema_table_lock_waits 视图依赖了一张MDL相关的表 performance_schema.metadata_locks。该表是MySQL 5.7引入的,会显示MDL的相关信息,包括作用对象、锁的类型及锁的状态等。但在MySQL 5.7中,该表默认为空,因为与之相关的 instrument 默认没有开启,MySQL 8.0才默认开启。
mysql select * from performance_schema.setup_instruments
where namewait/lock/metadata/sql/mdl; 1 row in set (0.00 sec) 所以,在MySQL 5.7中,如果我们要使用 sys.schema_table_lock_waits,必须首先开启MDL 相关的 instrument。
开启方式 直接修改 performance_schema.setup_instruments 表即可。具体SQL如下。
mysql UPDATE PERFORMANCE_SCHEMA.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME wait/lock/metadata/sql/mdl; 但这种方式是临时生效,实例重启后,又会恢复为默认值。
建议同步修改配置文件或者在部署 MySQL 集群时一开始配置文件的参数就修改成功。
[mysqld]performance-schema-instrument wait/lock/metadata/sql/mdlON 总结
执行 show processlist,如果DDL的状态是 Waiting for table metadata lock ,则意味着这个DDL被阻塞了。
定位导致DDL被阻塞的会话,常用的方法如下sys.schema_table_lock_waits
select sql_kill_blocking_connection from sys.schema_table_lock_waits WHERE blocking_lock_type SHARED_UPGRADABLE and (waiting_query like alter% OR waiting_query like create% OR waiting_query like drop% OR waiting_query like truncate% OR waiting_query like rename%);
这种方法适用于MySQL 5.7和8.0。
注意,MySQL 5.7中,MDL相关的instrument默认没有打开。
Kill DDL之前的会话。
select concat(kill,i.trx_mysql_thread_id,;) from information_schema.innodb_trx i, (select max(time) as max_time from information_schema.processlist
where state Waiting for table metadata lock
and (info like alter%
OR info like create%
OR info like drop%
OR info like truncate%
OR info like rename%)) p
WHERE timestampdiff(second, i.trx_started ,now()) p.max_time; 如果MySQL 5.7中MDL相关的instrument没有打开,可使用该方法。
