乐趣区

关于mysql:MySQL-升级到-80-变慢问题分析

1. 背景介绍

前段时间,客户线上 MySQL 版本从 5.7.29 降级到 8.0.25。

降级实现之后,放业务申请进来,没到一分钟就开始呈现慢查问,而后,慢查问越来越多,业务 SQL 呈现沉积。

整个过程继续了大略一个小时,直到给某条业务 SQL 对应的表加上索引,问题才失去解决。

有一个比拟奇怪的景象是:问题继续的过程中,服务器的零碎负载、CPU 使用率、磁盘 IO、网络都处于低峰期间的程度,也就是说,问题很可能不是因为硬件资源不够用导致的。

那么,根本原因到底是什么?让咱们一起来揭晓答案~

2. 起因剖析

客户线上环境有一个监控脚本,每分钟执行一次,这个脚本执行的 SQL 如下:

select ... from sys.innodb_lock_waits w inner join information_schema.innodb_trx b  on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r  on r.trx_id = w.waiting_trx_id;

对几个监控脚本的日志、SAR 日志、MySQL 的慢查问日志 & 谬误日志,以及死锁的源码,进行了全方位无死角的剖析,发现了可疑之处。

通过测试验证,最终确认罪魁祸首是 sys.innodb_lock_waits 视图援用的某个基表。

这个基表的名字和 MySQL 5.7 中不一样了,它的行为也产生了变动,就是这个行为的变动在某些场景下阻塞了业务 SQL,导致大量业务 SQL 执行变慢。

揭发这个邪恶的基表之前,咱们先来看一下 sys.innodb_lock_waits 视图的定义:

  • MySQL 5.7 中简化的视图定义
CREATE VIEW sys.innodb_lock_waits AS  SELECT ... FROM information_schema.innodb_lock_waits w  JOIN information_schema.innodb_trx b    ON b.trx_id = w.blocking_trx_id  JOIN information_schema.innodb_trx r    ON r.trx_id = w.requesting_trx_id  JOIN information_schema.innodb_locks bl    ON bl.lock_id = w.blocking_lock_id  JOIN information_schema.innodb_locks rl    ON rl.lock_id = w.requested_lock_id  ORDER BY r.trx_wait_started
  • MySQL 8.0 中简化的视图定义
CREATE VIEW sys.innodb_lock_waits (...) AS  SELECT ... FROM performance_schema.data_lock_waits w   JOIN information_schema.INNODB_TRX b     ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID  JOIN information_schema.INNODB_TRX r    ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID  JOIN performance_schema.data_locks bl    ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID  JOIN performance_schema.data_locks rl    ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID   ORDER BY r.trx_wait_started

5.7 中 sys.innodb_lock_waits 波及 3 个基表:

  • information_schema.innodb_lock_waits
  • information_schema.innodb_locks
  • information_schema.innodb_trx

8.0 中 sys.innodb_lock_waits 也波及 3 个基表:

  • performance_schema.data_lock_waits
  • performance_schema.data_locks
  • information_schema.INNODB_TRX

揭晓答案:引发问题的罪魁祸首就是 8.0 中的 performance_schema.data_locks 表。从两个版本的视图定义比照能够看到,performance_schema.data_locks 的前身是 information_schema.innodb_locks。咱们

再来看看这两个表的行为有什么不一样?
MySQL 5.7 中,information_schema.innodb_locks 蕴含这些数据:

  • InnoDB 事务已申请但未取得的锁。
  • InnoDB 事务已持有并且阻塞了其它事务的锁。

官网文档形容如下:

The INNODB_LOCKS table provides informationabout each lock that an InnoDB transactionhas requested but not yet acquired, and each lock that a transaction holdsthat is blocking another transaction.

MySQL 8.0 中,performance_schema.data_locks 蕴含这些数据:

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

官网文档形容如下:

The data_locks table
shows data locks held and requested

从官网文档的形容能够看到两个表的不同之处:

  • 5.7 的 innodb_locks 记录 InnoDB 事务已持有并且阻塞了其它事务的锁。
  • 8.0 的 data_locks 记录 InnoDB 事务正在持有的锁。

正是因为这个不同之处,导致 8.0 的 data_locks 表的数据量可能会十分大。

咱们再深挖一层,看看 data_locks 表的数据量大是怎么导致其它业务 SQL 阻塞的。

MySQL 线程读取 data_locks 表时,会持有全局事务对象互斥量(trx_sys->mutex),直到读完表中的所有数据,才会开释这个互斥量。

实际上,直到读完表中的所有数据,才会开释 trx_sys->mutex 互斥量的说法不精确。为了防止开展介绍读取 data_locks 表实现逻辑,咱们暂且应用这个说法。

data_locks 表的数据量越大,从表里读取数据破费的工夫就越长,读取这个表的线程持有 trx_sys->mutex 互斥量的工夫也就越长。

从 data_locks 表里读取数据的线程长时间持有 trx_sys->mutex 互斥量会有什么问题?

这个问题就大了,因为 trx_sys->mutex 互斥量十分吃香。

波及 InnoDB 的所有 SQL 都在事务中运行,每个事务启动胜利之后,都须要退出全局事务链表,而全局事务链表须要 trx_sys->mutex 互斥量的爱护。

也就是说,InnoDB 中每个事务退出全局事务链表之前,都须要持有 trx_sys->mutex 互斥量。

从 data_locks 表里读取数据的线程长时间持有 trx_sys->mutex 互斥量,就会长工夫阻塞其它 SQL 执行,导致其它 SQL 排队期待,呈现沉积,体现进去的状态就是 MySQL 整体都变慢了。

介绍分明逻辑之后,咱们回归事实,来看看客户线上的问题。

  1. 背景介绍大节中提到的那条业务 SQL 在执行过程中会对 300 万条记录加锁。

这条 SQL 只有执行一次,事务完结之前,data_locks 表中会有 300 万条加锁记录。

从 data_locks 表中读取记录之前,须要持有 trx_sys->mutex 互斥量,再读取 300 万条记录,最初开释互斥量。互斥量开释之前,其它业务 SQL 就得排队等着这个互斥量。

监控脚本执行一次的过程中,一堆业务 SQL 只能排队期待 trx_sys->mutex 互斥量,而后到了周期执行工夫,监控脚本又执行了一次,也在期待 trx_sys->mutex 互斥量,可怜的是,又来了一堆业务 SQL。

就这样,监控脚本和业务 SQL 相互影响,恶性循环,SQL 执行越来越慢 …,直到 DBA 在 1. 背景介绍大节中提到的那条业务 SQL 对应的表上创立了一个索引。

在那个表上创立索引之后,那条业务 SQL 执行过程中就不须要对 300 万条记录加锁了,而是只会对大量记录加锁,data_locks 表中的数据量也就变的很少了,不须要长时间持有 trx_sys->mutex 互斥量,打消了堵点,MySQL 整体就变的通顺了。

3. 测试验证

在 MySQL 5.7 和 8.0 的 test 库中都创立 t1 表,事务隔离级别为:READ-COMMITTED。表构造如下:

CREATE TABLE `t4` (`id` int unsigned NOT NULL AUTO_INCREMENT,  `e1` enum('长春', '沈阳', '福州', '成都', '杭州', '南昌', '苏州', '德清', '北京') NOT NULL DEFAULT '北京',  `i1` int unsigned NOT NULL DEFAULT '0',  `c1` char(11) DEFAULT '',  `d1` decimal(10,2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

数据如下:

3.1 MySQL 5.7 测试
第 1 步,在 session 1 中执行一条 SQL,锁住全表记录:

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from test.t1    -> for update;+----+--------+-------+---------------+------------+| id | e1     | i1    | c1            | d1         |+----+--------+-------+---------------+------------+|  1 | 长春   | 99999 | 1 测试 char    | 1760407.11 ||  2 | 沈阳   |     2 | 2 测试 char    | 3514530.95 ||  3 | 福州   |     3 | 3 测试 char    | 2997310.90 ||  4 | 成都   |     4 | 4 测试 char    | 8731919.55 ||  5 | 杭州   |     5 | 5 测试 char    | 2073324.31 ||  6 | 南昌   |     6 | 6 测试 char    | 3258837.89 ||  7 | 苏州   |     7 | 7 测试 char    | 2735011.35 ||  8 | 德清   |     8 | 8 测试 char    |  145889.60 ||  9 | 杭州   |     9 | 9 测试 char    | 2028916.63 || 10 | 北京   |    10 | 10 测试 char   | 3222960.80 |+----+--------+-------+---------------+------------+10 rows in set (0.00 sec)

第 2 步,在 session 2 中,执行另一条 SQL:

mysql> select * from test.t1    -> where id >= 5    -> for update;

第 3 步,session 2 的 SQL 期待获取锁的过程中,在 session 3 中查问锁的状况:

mysql> select * from information_schema.innodb_lock_waits;+-------------------+-------------------+-----------------+------------------+| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |+-------------------+-------------------+-----------------+------------------+| 263231            | 263231:473:3:6    | 263229          | 263229:473:3:6   |+-------------------+-------------------+-----------------+------------------+1 row in set, 1 warning (0.04 sec)mysql> select    ->  lock_id, lock_trx_id, lock_table, lock_data    -> from information_schema.innodb_locks;+----------------+-------------+-------------+-----------+| lock_id        | lock_trx_id | lock_table  | lock_data |+----------------+-------------+-------------+-----------+| 263231:473:3:6 | 263231      | `test`.`t1` | 5         || 263229:473:3:6 | 263229      | `test`.`t1` | 5         |+----------------+-------------+-------------+-----------+2 rows in set, 1 warning (0.01 sec)

从 innodb_lock_waits 的查问后果能够看到,事务 263231 申请持有锁被事务 263229 阻塞了。

innodb_locks 表中有 2 条记录:

  • lock_trx_id = 263231, lock_data = 5 的记录示意事务 263231 正在申请对 id = 5 的记录加锁。
  • lock_trx_id = 263229,lock_data = 5 的记录示意事务 263229 正在持有 id = 5 的记录上的锁,阻塞了事务 263231 对 id = 5 的记录加锁。

这和官网文档对 innodb_locks 表的行为的形容统一(后面已介绍过)。

3.2 MySQL 8.0 测试

第 1 步,在 session 1 中执行一条 SQL,锁住全表记录:

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from test.t1    -> for update;+----+--------+-------+---------------+------------+| id | e1     | i1    | c1            | d1         |+----+--------+-------+---------------+------------+|  1 | 长春   | 99999 | 1 测试 char    | 1760407.11 ||  2 | 沈阳   |     2 | 2 测试 char    | 3514530.95 ||  3 | 福州   |     3 | 3 测试 char    | 2997310.90 ||  4 | 成都   |     4 | 4 测试 char    | 8731919.55 ||  5 | 杭州   |     5 | 5 测试 char    | 2073324.31 ||  6 | 南昌   |     6 | 6 测试 char    | 3258837.89 ||  7 | 苏州   |     7 | 7 测试 char    | 2735011.35 ||  8 | 德清   |     8 | 8 测试 char    |  145889.60 ||  9 | 杭州   |     9 | 9 测试 char    | 2028916.63 || 10 | 北京   |    10 | 10 测试 char   | 3222960.80 |+----+--------+-------+---------------+------------+10 rows in set (0.00 sec)

第 2 步,在 session 2 中,执行另一条 SQL:

mysql> select * from test.t1    -> where id >= 5    -> for update;

第 3 步,session 2 的 SQL 期待获取锁的过程中,在 session 3 中查问锁的状况:

mysql> select    ->   engine_transaction_id as trx_id,    ->   lock_status, lock_data    -> from performance_schema.data_locks    -> where lock_type = 'RECORD';+--------+-------------+-----------+| trx_id | lock_status | lock_data |+--------+-------------+-----------+|  19540 | WAITING     | 5         ||  19522 | GRANTED     | 1         ||  19522 | GRANTED     | 2         ||  19522 | GRANTED     | 3         ||  19522 | GRANTED     | 4         ||  19522 | GRANTED     | 5         ||  19522 | GRANTED     | 6         ||  19522 | GRANTED     | 7         ||  19522 | GRANTED     | 8         ||  19522 | GRANTED     | 9         ||  19522 | GRANTED     | 10        |+--------+-------------+-----------+11 rows in set (0.00 sec)

从以上查问后果能够看到,data_locks 表里蕴含事务 19522 正在持有的 10 把锁(对应 10 条锁记录),以及事务 19539 已申请但未取得的 id = 5 的记录上的锁,这个行为也和官网文档的形容统一(后面介绍过)。

4. 总结

performance_schema.data_locks 表会记录所有事务正在持有的锁,如果某些 SQL 写的有问题,锁定记录十分多,这个表里的锁记录数量就会十分多。

data_locks 表里的锁记录数量十分多,读取这个表的线程就会长工夫持有 trx_sys->mutex 互斥量,这会阻塞其它 SQL 执行。

如果只想要获取锁的阻塞状况,能够查问 performance_schema.data_lock_waits。

本文关键字:#MySQL# #降级# #慢查问 #

退出移动版