实战 MySQL 锁等待问题的定位与排查

2019-10-01 17:30:06   最后更新: 2019-10-01 17:30:06   访问数量:29




在 MySQL 的实际使用中,常常会遇到一条 SQL 执行非常慢的情况,此前我们总结了一系列博客来排查相关的问题:

 

通过 Explain 语句查看 SQL 执行计划

通常 SQL 执行耗时异常是由于索引使用不合理或创建了临时表等操作,上一篇文章中,我们介绍了 MySQL 执行计划:

详解 MySQL 执行计划 -- explain

 

通过 Explain 语句可以详细分析具体的原因

 

通过 SQL 各状态的执行耗时具体分析背后的原因

但有时,耗时过多也可能是由于磁盘 IO 等资源问题,如果 Explain 无法一目了然的分析出原因,此时我们就要剖析 SQL 执行中具体的每一个步骤,查看 SQL 执行的各状态耗时,并具体分析:

SQL 某状态耗时过多的优化

 

上面这篇文章中,同时还介绍了临时表创建的条件,创建临时表是我们经常会遇到大量占用磁盘 IO 造成查询耗时的一个主要原因

 

锁等待

然而,此前的文章中详细介绍了 MySQL 的锁机制:

MySQL 锁机制(上) -- 全局锁与表级锁

MySQL 锁机制(下) -- 细说 InnoDB 行锁(记录锁、间隙锁与临键锁)

 

在实际的使用中,一个简单地 SQL 迟迟没有返回,多半就是陷入了锁等待,那么,上面介绍了这么多种锁的情况,我们应该如何去排查究竟我们正在执行的 SQL 在等待哪一种锁呢?

别急,本文我们就来详细介绍

 

 

上面提到,排查 SQL 执行超时的一个重要手段是通过 show processlist 命令查看 SQL 执行各状态的耗时情况,但这是通过 SQL 执行完成后的 queryID 来进行查询的

在 SQL 执行过程中,也可以通过 show processlist 命令查看当前 SQL 的执行状态:

 

 

如上图所示,可以看到,我们正在执行的 SQL 状态是 Waiting for table metadata lock,这就说明他正在等待 MDL 锁,可是如何找到是谁持有 MDL 锁的呢?

通过 performance_schema 和 sys 两个数据库中的数据我们可以看到数据库执行过程中的各项情况,包括 MDL 锁的情况等

 

performance_schema

performance_schema 是 mysql 提供的一种在数据库运行时监控 server 各项执行状态的机制,监控数据都保存在 performance_schema 数据库中,而该数据库使用的存储引擎为 performance_schema,因此 performance_schema 同时具有以下三种含义:

  1. 一种 MySQL 自带的对 server 运行状态的监控机制
  2. MySQL 中的一个数据库名
  3. 一种存储引擎

 

performance_schema 机制

通过 MySQL 启动前在配置文件中配置 performance_schema=on 开启,相比于未开启会有 10% 左右的性能损失,MySQL 5.7 以后是默认开启的

可以通过 MySQL 的 performance_schema 全局变量来查看是否已开启:

 

 

performance_schema 机制通过监听 server 事件实现对 server 的监控,事件中包含了函数调用、操作系统的等待、SQL语句执行的阶段(如sql语句执行过程中的parsing 或 sorting阶段)或者整个SQL语句与SQL语句集合等发生时相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息以及事件发生的耗时、调用次数等信息

 

performance_schema 存储引擎与数据库

performance_schema 存储引擎是一个纯内存的存储引擎,因此一旦服务器重启,其中所有数据都会丢失,而同时,所有对 performance_schema 数据库中表的操作都不会写入 binlog,因此也不会在集群中进行同步

performance_schema 数据库中大部分表可以直接通过 select 语句进行查询,以便直接获取对 server 的监控数据,以 setup_ 开头的一系列数据表用于存储数据收集时的配置信息,可以通过修改表中的数据项来实现对配置的调整,这些修改都是立即生效的

performance_schema 共有 87 个表,主要包含:

  1. MySQL 执行的 SQL 语句记录
  2. 等待事件记录表
  3. 阶段事件记录表
  4. 事务事件记录表
  5. 监视文件系统调用记录表
  6. 监视内存使用记录表
  7. 实时配置表

 

sys 数据库

performance_schema 库中拥有大量的表以及复杂的关联关系,想要掌握其复杂的信息检索是非常难的,因此,MySQL 提供了 sys 库,将 performance_schema 数据库中的数据组合成视图,便于人工检索和使用,主要分为:

  1. sys_config -- 配置表,用于存储配置 sys 数据库的配置参数
  2. host -- 以 IP 分组相关的统计信息
  3. innodb -- innodb buffer 相关信息
  4. io -- 数据内不同维度展的IO相关的信息
  5. memory -- 以 IP,连接,用户,分配的类型分组及总的占用显示内存的使用
  6. metrics -- DB 的内部的统计值
  7. processlist -- 线程相关的信息(包含内部线程及用户连接)
  8. ps_ -- 没有工具统计的一些变量(没看出来存在的价值)
  9. schema -- 表结构相关的信息,例如-- 自增,索引, 表里的每个字段类型,等待的锁等等
  10. session -- 用户连接相关的信息
  11. statement -- 基于语句的统计信息(重店)
  12. statements_ -- 出错的语句,进行全表扫描, 运行时间超长,排序相等(重点)
  13. user_ -- 和host_开头的相似,只是以用户分组统计
  14. wait --  等待事件
  15. waits -- 以IP,用户分组统计出来的一些延迟事件,有一定的参考价值
  16. x$开头 -- 适合工具采集数据的原始类数据

 

除 sys_config 外其他均为数据视图,我们可以方便的查询数据库当前的各项指标,当然也包括各个表的 MDL 锁使用情况

 

排查 MDL 锁的占用情况

在 sys 数据库的 schema_table_lock_waits 数据视图中就存有 MDL 锁的信息(5.7.9 版本加入):

 

 

接着我们可以通过 kill pid 命令来杀掉对应的执行,也可以通过下面语句查询对应 sql 的详细信息:

select * from information_schema.`PROCESSLIST` where ID = 4;

 

 

我们此前介绍过通过 flush 操作加表锁或全局锁:

flush tables test with read lock;

 

 

这个操作首先会关闭所有需要被锁的表,这通常是一个耗时非常短的操作,在此之中,对表的任何读写都会被阻塞

如果此时正在进行着一个对该表的慢查询,那么 flush 操作就会被阻塞,此后所有读写操作也都会被阻塞

同样,通过 show processlist 可以查看相应的情况:

 

 

我们找到那个罪魁祸首的慢查询,kill 掉即可(图中显示的是 id 为 14 的 select sleep(100) from test)

 

通过 show processlist 看到语句既不是在等待 MDL 锁,也不是在等待 flush,而是陷入 statistics 状态,则说明在等待行锁:

 

 

那么,我们如何找到持有行锁的是哪一条语句呢?

sys.innodb_lock_waits 表中可以查到行锁的各项信息:

 

 

通过查询结果,我们可以看到,我们需要查询的 id 为 10 的记录由被 id 为 142349 的事务持有写锁锁住了, 通过 infomation_schema.INNODB_TRX 表我们可以查看事务的相关信息:

 

 

结果中显示了事务的线程 id:trx_mysql_thread_id,执行 kill 线程id 即可杀死对应的事务

 

欢迎关注微信公众号,以技术为主,涉及历史、人文等多领域的学习与感悟,每周三到七篇推文,只有全部原创,只有干货没有鸡汤

 

 

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-reference.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html

https://time.geekbang.org/column/intro/139?code=4WJqZ1Li-NcKqeoaSUYtndaTkiX07wVYoGc8y9Fh9z8%3D

 






mysql      database      死锁      数据库      db      performance_schema     


京ICP备15018585号