Mysql 如何发现InnoDB大事务

什么是大事务

• 定义：运行时间比较长，操作的数据比较多的事务。
• 大事务风险：

1. 锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。
2. 执行时间长，容易造成主从延迟。

如何发现大事务

方法1、Use INFORMATION_SCHEMA

information_schema.innodb_trx表的trx_rows_modified列显示事务处理了多少行

(Sat Sep  2 11:21:53 2023)[root@GreatSQL][(none)]>select trx_id, trx_state,trx_started, trx_rows_modified from information_schema.innodb_trx order by trx_rows_modified desc limit  20;
+---------+-----------+---------------------+-------------------+
| trx_id  | trx_state | trx_started         | trx_rows_modified |
+---------+-----------+---------------------+-------------------+
| 1337785 | RUNNING   | 2023-09-02 11:12:11 |                 6 |
+---------+-----------+---------------------+-------------------+
1 row in set (0.00 sec)

方法2、Use INNODB STATUS

<strong>在INNODB状态的事务部分中，在“undo log entries”附近，也显示了按事务修改的行数，例如</strong>

mysql> SHOW ENGINE INNODB STATUS \G


---TRANSACTION 1339048, ACTIVE 35 sec
2 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 6
MySQL thread id 2819484, OS thread handle 139997412484864, query id 28178770 localhost root
TABLE LOCK table `andy`.`andy` trx id 1339048 lock mode IX
RECORD LOCKS space id 15 page no 4 n bits 80 index PRIMARY of table `andy`.`andy` trx id 1339048 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

方法3、Monitoring progress of ALTER commands in MySQL Server 5.7 using PERFORMANCE_SCHEMA

Read more in online documentation at https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html

方法4、 解析binlog

可参考博客  如何获取MySQL中的查询和事务大小 的方法

扩展：mysql 8  binlog有记录transaction_length，低版本不存在特性。

 
mysqlbinlog /db/mysql/3306/log/binlog/mysql-bin.001994 -vv | grep -o "transaction_length=[0-9]*"|awk -F= '{print $2}'| sort -rn 

mysqlbinlog  -vv | grep -o "transaction_length=[0-9]*"  
transaction_length=212
transaction_length=223
transaction_length=216
transaction_length=227
transaction_length=211
transaction_length=222
transaction_length=204
transaction_length=186
transaction_length=249
transaction_length=13055883

<br>