MySQL:5.7.25 truncate table导致的系统hang住问题简析

仅记录一个故障的分析过程，如果有误请见谅。

最近一个朋友在做大表truncte的时候出现了堵塞。SEMAPHORES几乎都是DICT_SYS

对于show processlist显示如下，并且killed标记已经不做响应了。

从时间来看我们发现truncate命令执行的时间最长，那么可能是它造成的问题，但是为什么会造成问题呢。结合pstack简单分析一下

这里先贴出堵塞源头的truncate语句的栈，如下：

实际上我们的truncate有很多步骤，其中步骤9为

这一步中包含3个代价较高的部分，从DEBUG DICT_SYC的加锁和释放来看，5.7.22版本如下：

如上故障的栈正是在进行AHI的维护，并且在dict_drop_index_tree中开头就对ut_ad(mutex_own(&dict_sys->mutex))进行了断言，也就是说这里肯定是在持有dict_sys锁的情况下进行的，那么我们来看一下到底发生了什么，我贴出大概的执行的步骤如下：

稍微总结一下如下，下面做这些的目的是精准的清理掉AHI中的信息：

我们发现这实际上是一个5层循环，代价最高的落在最后的循环每个page中每一行的每个字段上，如果page存在于这正是这个栈带给我们的信息。

这些步骤需要持有DICT_SYS这样一个全局字典结构的保护锁进行，而在很多地方比如如下的函数中都会持有这个锁进行互斥保护：

这2个函数也是本例中等待的会话，并且在进行mutex的带的时候并不会被kill标记唤醒，因此kill是不能生效的。我在查询BUG的发现也有不少人遇到类似的问题如下：

https://bugs.mysql.com/bug.php?id=91977

但是bug的状态一直没有变为closed，在这个BUG的最后也有人问出问题：

其次我们来也简单讨论下truncate在innodb buffer维护上的代价，对于5.7来讲这个地方和drop是有区别的，函数接口 buf_LRU_flush_or_remove_pages 用于确认是否维护 LRU list，其中有三种类型：

因此5.7最好使用drop+create代替trucnate。

随后我对一个稍微大的表truncate，测试发现代价如我们描述：

正如我们描述的，我这里实际上没有建立AHI，但是全表扫描了一次，那么大量的page加载到了innodb buffer。这里btr_search_drop_page_hash_when_freed实际就是AHI维护的上层接口，如果有大量的AHI的存在，代价确实客观，并且这个是持有DICT_SYS的 *** 作。

对于AHI的维护方面直到8.0.21当时我看一个增加分区慢的case的时候依旧存在一些问题提交的BUG如下：

https://bugs.mysql.com/bug.php?id=101900

因此建议如下：

我主要使用了如下断点

5.7 drop栈

8.0 为BUF_REMOVE_NONE

以下五种方法可以快速定位全局锁的位置，仅供参考。

方法1：利用 metadata_locks 视图

此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks，如果上锁前启用了元数据锁的探针（默认是未启用的），可以比较容易的定位全局锁会话。

方法2：利用 events_statements_history 视图此方法适用于 MySQL 5.6 以上版本，启用 performance_schema.eventsstatements_history（5.6 默认未启用，5.7 默认启用），该表会 SQL 历史记录执行，如果请求太多，会自动清理早期的信息，有可能将上锁会话的信息清理掉。

方法3：利用 gdb 工具如果上述两种都用不了或者没来得及启用，可以尝试第三种方法。利用 gdb 找到所有线程信息，查看每个线程中持有全局锁对象，输出对应的会话 ID，为了便于快速定位，我写成了脚本形式。也可以使用 gdb 交互模式，但 attach mysql 进程后 mysql 会完全 hang 住，读请求也会受到影响，不建议使用交互模式。

方法4：show processlist

如果备份程序使用的特定用户执行备份，如果是 root 用户备份，那 time 值越大的是持锁会话的概率越大，如果业务也用 root 访问，重点是 state 和 info 为空的，这里有个小技巧可以快速筛选，筛选后尝试 kill 对应 ID，再观察是否还有 wait global read lock 状态的会话。

方法5：重启试试！

查看MySQL数据库的死锁日志

1. 使用终端或命令提示符登录到MySQL，输入命令：mysql -h xxxx.xxx.xxx -P 3306 -u username -p 解释：xxxx.xxx.xxx是数据库IP地址，username是数据库用户名，输入命令后，会让你输入username对应的密码，就可以登录了

2. 如何查看MySQL数据库的死锁信息 在MySQL客户端下输入命令： show engine innodb status \G

3. 如何定位MySQL数据库的死锁信息 在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容，看图中红线

4. 如何分析日志，定位死锁原因 看3里面的图，紫色划线部分 分析： 事务1，等待 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`，这个位置的X锁 事务2，持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁 事务2，等待这个地方的X锁 理论上这个事务2是可以提交的不会，死锁，但是这个事务日志只打印最后一部分死锁，信息，这里面隐含的条件是，事务1也持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁，这样，事务2不能加X锁，同时事务1也不能加X锁，产生死锁。

---