怎样在MySQL中监控未提交事务占用的系统资源-mysql教程-PHP中文网

查innodb_trx表无法获取undo占用量，因其无undo_size字段；需结合长事务状态、sleep线程、history list长度交叉验证，并通过performance schema内存监控和undo文件物理增长定位真实问题。

怎样在mysql中监控未提交事务占用的系统资源

查 INNODB_TRX 表只能看状态，不能看 Undo 占用

直接执行 SELECT * FROM information_schema.INNODB_TRX 会返回事务 ID、状态、开始时间、线程 ID 和当前 SQL 等字段，但 INNODB_TRX 表里根本没有 undo_size 或类似字段。这不是遗漏，而是设计使然：Undo 段由 InnoDB 异步管理，其空间分配、复用和清理不与单个事务行级绑定，无法实时映射为一个可查询的字节数。

常见误判是把 trx_rows_modified 当成 Undo 大小——它只反映修改行数，而一次含 TEXT 或 BLOB 的 UPDATE 可能生成几十 KB Undo 记录；反之，10 万行纯整型更新可能才占 2MB 左右。所以这个值只能辅助估算下限，不能当作实际占用。

真正要判断“这个事务是否在挤压 Undo”，得结合以下三点交叉验证：

TRX_STATE = 'RUNNING' 且 TRX_STARTED 早于当前时间 10 分钟以上（NOW() - INTERVAL 600 SECOND）
该事务对应的线程在 PROCESSLIST 中 COMMAND = 'Sleep' 且 TIME 很大（说明应用端挂起未 commit/rollback）
全局 History list length 持续 > 5000（通过 SHOW ENGINE INNODB STATUS\G 查，说明 purge 被阻塞）

用 Performance Schema 追踪内存与 I/O 关联消耗

未提交事务本身不直接吃内存，但它会让相关线程长期持有锁结构、字典缓存、回滚段指针等资源，这些会体现在 performance_schema.memory_summary_by_thread_by_event_name 和 events_statements_history_long 中。

先确保内存监控已启用：

执行 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
执行 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('global_instrumentation', 'thread_instrumentation');
验证：SHOW ENGINE PERFORMANCE_SCHEMA STATUS 最后一行应为 OK

然后查具体线程内存占用：

SELECT thread_id, user, event_name, sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) AS memory_used
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 1024*1024
AND thread_id IN (
  SELECT THREAD_ID FROM performance_schema.threads 
  WHERE PROCESSLIST_ID IN (
    SELECT TRX_MYSQL_THREAD_ID FROM information_schema.INNODB_TRX 
    WHERE TRX_STATE = 'RUNNING' AND TIME_TO_SEC(TIMEDIFF(NOW(), TRX_STARTED)) > 600
  )
)
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

如果发现 memory/innodb/mem0mem 或 memory/sql/thd 显著偏高，基本可锁定是长事务导致的结构体膨胀。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

观察 Undo 文件物理增长才是最硬的证据

MySQL 不提供 “某事务占了多少 Undo” 的接口，但你可以直接看磁盘上 Undo 文件有没有失控增长——这才是资源被真实占用的铁证。

步骤很简单：

确认 Undo 路径：SELECT @@innodb_undo_directory;（默认是 /var/lib/mysql）
检查文件大小：ls -lh /var/lib/mysql/undo_*
重点关注 undo_001、undo_002 是否长期不缩容，且单个超过 2GB

若 innodb_undo_log_truncate = OFF（查 SELECT @@innodb_undo_log_truncate;），Undo 文件只会增长不会收缩，历史积压会放大单个长事务的实际影响。此时即使事务已提交，只要还有活跃读视图引用旧版本，对应 Undo 就无法释放——而你从 INNODB_TRX 里已经看不到它了。

别只盯着事务表，要顺藤摸瓜找源头

很多“未提交事务”其实是锁等待链的末端。比如 TRX_STATE = 'LOCK WAIT' 的事务，它卡住不是因为自己没提交，而是前面有个更早的 RUNNING 事务 hold 住了锁。

必须关联查锁等待关系：

SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query,
  b.trx_started blocking_start_time
FROM information_schema.INNODB_TRX b
INNER JOIN information_schema.INNODB_LOCK_WAITS w ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
ORDER BY b.trx_started;

拿到 blocking_thread 后，立刻去 PROCESSLIST 查它的 HOST、USER、CURRENT_STATEMENT，再翻应用日志——90% 的 case 都能定位到是某个服务实例在事务里调用下游超时后没 rollback，或者异常分支漏处理。

最容易被忽略的是 autocommit 设置：如果 @@global.autocommit = 0，所有新连接默认进手动事务模式，哪怕只执行一条 UPDATE，不显式 COMMIT 就会一直悬着。务必查清这个开关状态，比查任何指标都管用。