長時間実行トランザクションと history list length (purge の遅延)

コマンド

-- Undo records waiting for purge (millions = trouble)
SELECT NAME, COUNT AS history_list_length
FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len';

-- Oldest transactions, their owner and last SQL
SELECT trx.trx_id, trx.trx_mysql_thread_id AS conn_id, trx.trx_state,
       TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS age_sec,
       trx.trx_rows_modified, trx.trx_rows_locked, trx.trx_isolation_level,
       p.USER, p.HOST, p.COMMAND, p.TIME AS state_sec,
       LEFT(COALESCE(trx.trx_query, esc.SQL_TEXT), 80) AS current_or_last_sql
FROM information_schema.INNODB_TRX trx
JOIN performance_schema.processlist p ON p.ID = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.threads th ON th.PROCESSLIST_ID = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current esc ON esc.THREAD_ID = th.THREAD_ID
ORDER BY trx.trx_started
LIMIT 10;

概要

purge の遅延量 (history list length) と、それを妨げている最も古いトランザクションの所有者、経過時間、変更行数、最後に実行した SQL を表示する。

使用する場面

undo 表領域が増大する場合、同じクエリが時間とともに遅くなる場合 (古い行バージョンをスキャンするため)、またはディスク容量アラートの発生時。

注意

最も一般的な原因は、トランザクションを開いたままの Sleep 状態のセッション、または REPEATABLE READ のもとで放置された一貫性スナップショット (例: 放棄された mysqldump / レポート) である。KILL 後のロールバックには、トランザクションが変更した行数に応じた時間がかかる。trx_rows_modified が大きいトランザクションを KILL すると、長く IO 負荷の高いロールバックとなり、再起動しても短縮されない。purge が追いつくと undo は自動的に切り詰められる (innodb_undo_log_truncate=ON)。