共计 5778 个字符,预计需要花费 15 分钟才能阅读完成。
作者:许祥
爱可生 MySQL DBA 团队成员,负责解决客户 MySQL 及我司自研 DMP 平台日常运维中的问题。
本文起源:原创投稿
- 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
背景
之前碰到客户征询定位 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;
+-------+-------------+---------------------+------+------------------+--------+---------------------------------------------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+---------------------+------+------------------+--------+---------------------------------------------------------------+-------------------------------------+
| 1 | universe_op | 127.0.0.1:28904 | NULL | Sleep | 12 | | NULL |
| 2 | universe_op | 127.0.0.1:28912 | NULL | Sleep | 12 | | NULL |
| 5752 | universe_op | 10.186.64.180:51808 | NULL | Binlog Dump GTID | 605454 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 28452 | root | 10.186.65.110:10756 | test | Sleep | 73 | | NULL |
| 28454 | root | 10.186.64.180:45674 | test | Query | 7 | Waiting for table metadata lock | alter table test add c1 varchar(25) |
| 28497 | root | 10.186.64.180:47026 | test | Query | 0 | starting | 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
*************************** 1. row ***************************
object_schema: test
object_name: test
waiting_thread_id: 28490
waiting_pid: 28454
waiting_account: root@10.186.64.180
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table test add c1 varchar(25)
waiting_query_secs: 179
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 28488
blocking_pid: 28452
blocking_account: root@10.186.65.110
blocking_lock_type: SHARED_READ_ONLY
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 28452
sql_kill_blocking_connection: KILL 28452
*************************** 2. row ***************************
object_schema: test
object_name: test
waiting_thread_id: 28490
waiting_pid: 28454
waiting_account: root@10.186.64.180
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table test add c1 varchar(25)
waiting_query_secs: 179
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 28490
blocking_pid: 28454
blocking_account: root@10.186.64.180
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 28454
sql_kill_blocking_connection: KILL 28454
2 rows in set (0.00 sec)
只有一个 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)';
+------------------------------+
| sql_kill_blocking_connection |
+------------------------------+
| KILL 28452 |
+------------------------------+
1 row in set (0.00 sec)
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 name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO | NO |
+----------------------------+---------+-------+
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/mdl=ON'
总结
- 执行
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 没有关上,可应用该办法。