Transações de longa duração e history list length (atraso do purge)

Comando

-- 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;

O que faz

Mostra quão atrasado está o purge (history list length) e as transações mais antigas que o seguram: dono, idade, linhas modificadas e o último SQL executado.

Quando usar

Quando o undo tablespace cresce, quando as mesmas consultas ficam mais lentas com o tempo (elas varrem versões antigas das linhas) ou em alertas de espaço em disco.

Atenção

A causa mais comum é uma sessão em Sleep com transação aberta, ou um consistent snapshot esquecido sob REPEATABLE READ (por exemplo, um mysqldump / relatório abandonado). Após o KILL, o rollback leva tanto quanto as linhas que a transação alterou; matar uma transação com trx_rows_modified alto significa um rollback longo e pesado em IO, e um restart não o encurta. O undo é truncado automaticamente quando o purge alcança (innodb_undo_log_truncate=ON).