chongzh

uid：748

注册时间：2023-8-27 09:41 上次发表时间：2024-4-11 14:35

好友数 0 | 博客数 73 | 回帖数 29 | 主题数 3

MySQL 8.x ，查询performance_schema.data_locks造成整个实例 hang了？

chongzh 已有 581 次阅读2024-1-10 16:55 |个人分类:Mysql 原理|系统分类:运维实战

问题描述

MySQL 8.0.25 在 2023/12/22 10:17:36 左右出现线程堵塞，正常情况下原本执行很快的sql执行效率陡降（比如：原本执行0.001秒的sql执行7秒仍未结束），导致十几笔交易超时。

问题分析

根据AWR处于非Sleep状态的用户线程，Time由高到低排序，Top1为Command处于Query执行耗时达30秒，是对 performance_schema.data_locks 的查询，该查询需要返回3百多万数据。在查询 performance_schema.data_locks 之后执行的简单DQL&DML都没有按照正常速度完成。

performance_schema.data_locks 在慢日志的记录


# Time: 2023-12-22T10:17:47.968405+08:00
# User@Host: xxx[xxx] @ [127.0.0.1] Id: 10181232
# Query_time: 41.615127 Lock_time: 0.000372 Rows_sent: 7855322 Rows_examined: 7855322
SET timestamp=1703211426;
select engine, engine_lock_id, engine_transaction_id, thread_id, event_id, object_schema, object_name, partition_name, subpartition_name, index_name, object_instance_begin, lock_type, lock_mode, lock_status, lock_data 
from performance_schema.data_locks;

疑问1 为什么 performance_schema.data_locks 会有数百万数据？

MySQL 8.0 中，performance_schema.data_locks 包含这些数据：

InnoDB 事务已申请但未获得的锁。
InnoDB 事务正在持有的锁。

可能有事务，有大量正在持有锁或者已申请但未获得锁。

疑问2 什么操作持有百万数据量的行锁？

根据异常期间AWR与慢日志，发现问题sql UPDATE xxx_dtl xxx order by t.priority desc 执行过程累计扫描200多万数据且在一个未提交事务累计持有 400 多万 row locks，也就是造成performance_schema.data_locks 有百万数据量的原因。

AWR截图

慢日志截图

疑问3 为什么 MySQL整个实例的SQL执行会变慢？

SQL执行缓慢猜测有3种原因：1、SQL执行效率低 2、资源异常/瓶颈 3、无效等待

1、SQL执行效率低 （排除），没有表锁的情况下，简单 select & insert 执行很快。
2、资源异常/瓶颈（排除），查看监控没有硬件报错与没有资源使用率跑高。
3、无效等待（复现），根据AWR抓取的现场信息，模拟后能100%复现。

问题复现

	session1	session2	session3
t1	mysql> use dbtest; mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update sbtest1 set c='d'; Query OK, 0 rows affected (13.13 sec) Rows matched: 3000000 Changed: 0 Warnings: 0
t2			mysql> insert into t1 values(1113,1113); Query OK, 1 row affected (0.01 sec)
t3		mysql> select count() from performance_schema.data_locks;+----------+\| count(*) \|+----------+\| 3000001 \|+----------+1 row in set (27.34 sec)	mysql> insert into t1 values(1114,1114); Query OK, 1 row affected (18.58 sec)
t4			mysql> insert into t1 values(1115,1115); Query OK, 1 row affected (0.02 sec)
t5	commit
t6		mysql> select count() from performance_schema.data_locks;+----------+\| count(*) \|+----------+\| 0 \|+----------+1 row in set (0.01 sec)	mysql> insert into t1 values(1116,1116);Query OK, 1 row affected (0.02 sec)

用vscode查看mysqld的函数调用情况

正常执行dml语句的堆栈，可见执行iterator，代表dml操作正常执行

查询data_locks后，再执行的dml语句堆栈变更为条件等待，等待locksys::latches的释放

测试结论：performance_schema.data_locks有大量数据时，查询performance_schema.data_locks会严重影响其他并发SQL的执行效率，执行其他SQL需要等待查询 performance_schema.data_locks 持有的locksys::latches的释放，才能往下执行

问题总结

MySQL 8.x当performance_schema.data_locks有大量数据时，查询performance_schema.data_locks会严重影响其他并发SQL的执行效率，执行其他SQL需要等待查询 performance_schema.data_locks 持有的locksys::latches释放后，才能往下执行，可能会造成MySQL大量阻塞。

优化建议

1、业务问题sql UPDATE xxx_dtl xxx order by t.priority desc ，去掉order by t.priority desc 减少数据扫描量缩短锁占用时间提升执行效率
2、避免大事务，避免长时间未提交事务
3、禁止出现访问 performance_schema.data_locks/sys.innodb_lock_waits 的"慢查询"，最好不要查询performance_schema.data_locks/sys.innodb_lock_waits
4、MySQL 8.X sys.innodb_lock_waits视图依赖performance_schema.data_locks，特殊场景下也会产生阻塞问题。MySQL 8.X 中，如果只想要获取锁的阻塞情况，可以查询 performance_schema.data_lock_waits。而MySQL 5.7 查询information_schema.INNODB_LOCKS不会造成阻塞。

收藏 0 邀请举报