MySQL:5.6 大事务show engine innodb status故障一例

导读

作者:高鹏(网名八怪),《深入理解MySQL主从原理32讲》系列文的作者。

《深入理解MySQL主从原理32讲》

扫码订阅:

今天遇到一个朋友的线上问题,大概意思就是说,我有一个线上的大事务大概100G左右,正在做回滚,当前看起来似乎影响了线上的业务,并且回滚很慢,是否可以减轻对线上业务的影响。并且朋友已经取消了双1设置,但是没有任何改观。版本MySQL 5.6

首先我们需要知道的是,MySQL并不适合大事务,大概列举一些MySQL中大事务的影响:

  • binlog文件作为一次写入,会在sync阶段消耗大量的IO,会导致全库hang主,状态大多为query end。

  • 大事务会造成导致主从延迟。

  • 大事务可能导致某些需要备份挂起,原因在于flush table with read lock,拿不到MDL GLOBAL 级别的锁,等待状态为 Waiting for global read lock。

  • 大事务可能导致更大Innodb row锁加锁范围,导致row锁等待问题。

  • 回滚困难。

基于如上一些不完全的列举,我们应该在线上尽可能的避免大事务。好了我们下面来进行问题讨论。

一、问题

前面已经说了,我们已经取消了双1设置,所谓的双1就是 sync_binlog=1和 innodb_flush_log_at_trx_commit=1。这两个参数线上要保证为1,前者保证binlog的安全,后者保证redo的安全,它们在数据库crash recovery的时候起到了关键做用,不设置为双1可能导致数据丢失。具体的参数含义不做过多讨论。但是这里的问题是即便取消了双1,没有任何改观,因此似乎说明IO问题不是主要瓶颈呢?下面我们来看几个截图:

  • vmstat 截图

  • iostat 截图

要找到 `SHOW ENGINE INNODB STATUS` 命令输出的 InnoDB 存储引擎状态信息中某个线程所在的事务 ID,可以按照以下步骤进行: 1. 执行 `SHOW ENGINE INNODB STATUS\G` 命令,将 InnoDB 存储引擎状态信息以详细的方式输出到终端或客户端。 2. 在输出的状态信息中,找到 `LATEST FOREIGN KEY ERROR` 或 `LATEST DETECTED DEADLOCK` 部分。在这个部分中,通常会列出最近发生的死锁或外键错误的相关信息。 3. 在这个部分中,找到被称为“TRANSACTION”的部分。该部分会列出当前正在运行或等待的事务的详细信息,包括事务 ID、事务状态、锁定的对象等。 4. 在事务列表中找到您要查找的线程所在的事务。您可以根据线程 ID 在列表中查找到该事务,并在该事务的信息中找到事务 ID。 例如,以下是 `SHOW ENGINE INNODB STATUS\G` 命令输出的状态信息中事务列表的示例: ``` ------------ TRANSACTIONS ------------ Trx id counter 0 123456 Purge done for trx's n:o < 0 123455 undo n:o < 0 0 History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 123455, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 123, OS thread handle 0x7fb3b5c1d700, query id 1234 localhost root updating UPDATE `test` SET `value` = '123' WHERE `id` = 1 ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1234 page no 1 n bits 72 index `PRIMARY` of table `test` trx id 0 123455 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ``` 在这个例子中,事务列表中包含了当前正在运行的事务和等待锁定的事务。您可以根据线程 ID 查找到对应的事务,例如这里线程 ID 为 `123`,则可以在事务列表中找到该线程所在的事务,该事务 ID 为 `0 123455`。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值