Що таке undo-журнал і purge і чому довга транзакція сповільнює всю базу?

Undo-журнал зберігає попередні версії рядків. Коли транзакція змінює рядок, стара версія йде в undo. Це потрібно для двох речей: відкоту транзакції і узгодженого читання - інші транзакції відновлюють зі undo версію, яку вони мають бачити за своїм знімком.

Purge - фонова очистка: коли жодна активна транзакція вже не може побачити стару версію, purge-потоки видаляють її з undo і остаточно прибирають рядки, позначені як видалені (DELETE в InnoDB лише позначає рядок).

Довжина історії (history list length) - кількість ще не очищених записів undo:

SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS: History list length 1234567

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

Як одна транзакція шкодить усім. Purge не може видалити жодну версію, новішу за найстаріший відкритий знімок. Якщо транзакцію відкрито годину тому (навіть тільки для читання на REPEATABLE READ), за годину нагромаджуються всі старі версії всіх змінених рядків:

• зростає історія й розмір undo-табличних просторів;
• запити, що читають часто змінювані рядки, проходять довгі ланцюжки версій - звичайні SELECT сповільнюються;
• вторинні індекси розбухають від невичищених записів;
• коли транзакція нарешті закривається, purge довго «наздоганяє», створюючи навантаження.

Типові винуватці:

• забутий START TRANSACTION у консолі чи GUI;
• mysqldump --single-transaction великої бази на основному сервері;
• довгий звіт у транзакції;
• воркер, що відкрив транзакцію й чекає на зовнішній сервіс.

Як знайти:

SELECT trx_id, trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Що робити:

• моніторити довжину історії й вік найстарішої транзакції, ставити сповіщення;
• великі звіти й дампи виконувати на репліці;
• для аналітичних читань, яким не потрібен один знімок на кілька запитів, - READ COMMITTED: знімок береться на кожен оператор і не тримається довго;
• max_execution_time для SELECT і тайм-аут неактивних сесій.

Розмір undo на диску: undo-табличні простори автоматично усікаються (innodb_undo_log_truncate = ON), коли перевищують innodb_max_undo_log_size (1 ГБ) і purge їх звільнив. Але доки довга транзакція жива, усікання неможливе.

Докладніше в документації: Налаштування purge