장기 실행 트랜잭션과 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에서 잊힌 consistent snapshot(예: 방치된 mysqldump/리포트)이다. KILL 이후 롤백은 트랜잭션이 변경한 행만큼 시간이 걸린다. trx_rows_modified가 큰 트랜잭션을 종료하면 길고 IO가 많은 롤백이 발생하며, 재시작해도 단축되지 않는다. purge가 따라잡으면 undo는 자동으로 truncate된다(innodb_undo_log_truncate=ON).