上面提到,排查 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 同时具有以下三种含义:一种 MySQL 自带的对 server 运行状态的监控机制
MySQL 中的一个数据库名
一种存储引擎
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 个表,主要包含:MySQL 执行的 SQL 语句记录
等待事件记录表
阶段事件记录表
事务事件记录表
监视文件系统调用记录表
监视内存使用记录表
实时配置表
sys 数据库
performance_schema 库中拥有大量的表以及复杂的关联关系,想要掌握其复杂的信息检索是非常难的,因此,MySQL 提供了 sys 库,将 performance_schema 数据库中的数据组合成视图,便于人工检索和使用,主要分为:sys_config -- 配置表,用于存储配置 sys 数据库的配置参数
host -- 以 IP 分组相关的统计信息
innodb -- innodb buffer 相关信息
io -- 数据内不同维度展的IO相关的信息
memory -- 以 IP,连接,用户,分配的类型分组及总的占用显示内存的使用
metrics -- DB 的内部的统计值
processlist -- 线程相关的信息(包含内部线程及用户连接)
ps_ -- 没有工具统计的一些变量(没看出来存在的价值)
schema -- 表结构相关的信息,例如-- 自增,索引, 表里的每个字段类型,等待的锁等等
session -- 用户连接相关的信息
statement -- 基于语句的统计信息(重店)
statements_ -- 出错的语句,进行全表扫描, 运行时间超长,排序相等(重点)
user_ -- 和host_开头的相似,只是以用户分组统计
wait -- 等待事件
waits -- 以IP,用户分组统计出来的一些延迟事件,有一定的参考价值
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;