Обслуживание InnoDB: purge, усечение undo, OPTIMIZE TABLE и алгоритмы online DDL

После долгой транзакции в InnoDB история очищается, а диск остаётся занят: undo-файлы не уменьшаются, пока purge не отработает и не придёт очередь файла на усечение. С таблицей так же: удалил 90 % строк, а файл прежнего размера. У PostgreSQL для этого VACUUM (фазы и усечение хвоста), в InnoDB обслуживание разделено на три механизма: purge с усечением undo, перестроение таблицы и ALTER с выбором алгоритма. Что такое history list и почему он растёт под открытым снимком, показано в статье про MVCC в InnoDB; здесь нас интересует, что происходит после закрытия снимка, и сколько стоит каждая операция обслуживания для соседних запросов.

DBA / 01koddo

Undo после закрытия снимка: 80 МиБ сжимаются по одному файлу

Опыт 1: undo сжимается позже, чем очищается история

Поднимаем сервер и ждём, пока SELECT 1 пройдёт дважды подряд: после инициализации mysqld один раз перезапускается, и база, созданная до этого, пропадёт.

docker run -d --name dba-innodb-purge -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
docker exec -it dba-innodb-purge mysql -uroot -ppg

SELECT @@version, @@innodb_undo_log_truncate, @@innodb_max_undo_log_size,
       @@innodb_purge_threads, @@innodb_purge_rseg_truncate_frequency;
SELECT name, file_size/1048576 AS mib FROM information_schema.innodb_tablespaces WHERE name LIKE 'innodb_undo%';

вывод

+-----------+----------------------------+----------------------------+------------------------+----------------------------------------+
| @@version | @@innodb_undo_log_truncate | @@innodb_max_undo_log_size | @@innodb_purge_threads | @@innodb_purge_rseg_truncate_frequency |
+-----------+----------------------------+----------------------------+------------------------+----------------------------------------+
| 8.4.13    |                          1 |                 1073741824 |                      4 |                                    128 |
+-----------+----------------------------+----------------------------+------------------------+----------------------------------------+
+-----------------+---------+
| name            | mib     |
+-----------------+---------+
| innodb_undo_001 | 16.0000 |
| innodb_undo_002 | 16.0000 |
+-----------------+---------+

Усечение включено, а порог по умолчанию 1 ГиБ: на нашем объёме файлы не дошли бы до него, как и в опыте из статьи про MVCC. Опускаем порог до 20 МиБ (минимум допускает 10), включаем счётчик усечений и повторяем тот же сценарий: сессия B держит снимок, сессия A делает миллион обновлений одной строки процедурой bump и таблицей hot из опыта 2 той статьи (в нашем прогоне миллион обновлений занял 29 с).

SET GLOBAL innodb_max_undo_log_size = 20971520;
SET GLOBAL innodb_monitor_enable = 'undo_truncate_count';

-- сессия B
START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT n FROM hot;
-- сессия A
CALL bump(1000000);
-- сессия C, проба
SELECT (SELECT count FROM information_schema.innodb_metrics WHERE name='trx_rseg_history_len') AS history_len,
 (SELECT group_concat(round(file_size/1048576) ORDER BY name) FROM information_schema.innodb_tablespaces WHERE name LIKE 'innodb_undo%') AS undo_mib,
 (SELECT count FROM information_schema.innodb_metrics WHERE name='undo_truncate_count') AS truncations;

Проба под открытым снимком, затем после его закрытия (COMMIT в сессии B), каждые 5 секунд:

вывод

-- до
history_len | undo_mib | truncations
          1 | 16,16    | 0
-- снимок открыт, миллион UPDATE выполнен
       1002 | 32,48    | 0
-- 5 с ... 110 с после COMMIT
       1003 | 32,48    | 0
-- 120 с ... 240 с
          1 | 16,48    | 1
-- 250 с и дальше
          1 | 16,16    | 2

Миллион обновлений при открытом снимке раздул undo с 32 до 80 МиБ. После COMMIT история оставалась на 1003 около двух минут, потом очистилась разом, и ровно тогда первый файл (32 МиБ) вернулся к 16. Второй, 48 МиБ, ждал ещё около двух минут. Параметр innodb_purge_rseg_truncate_frequency = 1 ускоряет первое усечение: в повторном прогоне оно пришло за 10 с после COMMIT. А вот второй файл при простое не сжимался 170 с и сжался за 10 с после CALL bump(3000): усечению нужны рабочие проходы purge.

Устройство: один файл за раз и порог по размеру

Решение «этот файл велик» принимает Tablespace::needs_truncation (тег mysql-8.4.13, trx0purge.cc, строки 755–761):

  page_no_t trunc_size = std::max(
      static_cast<page_no_t>(srv_max_undo_tablespace_size / srv_page_size),
      fil_space_get_undo_initial_size(m_id));

  if (fil_space_get_size(m_id) > trunc_size) {
    return (true);
  }

srv_max_undo_tablespace_size — это переменная innodb_max_undo_log_size. Порог — большее из неё и начального размера файла; в опыте файлы после усечения вернулись к 16 МиБ. Выбор файла делает trx_purge_mark_undo_for_truncate (строки 1262–1271):

  if (normal_operation) {
    /* Skip truncate if there is only one active undo tablespace to check. */
    if (num_active == 1) {
      return (false);
    }

    /* Skip truncate if the caller has already truncated an undo space. */
    if (truncate_count > 0) {
      return (false);
    }

Первое условие требует минимум два активных undo-файла, чтобы пока один сжимается, транзакции писали во второй. Второе — «за проход не больше одного усечения». Оно объясняет лестницу 32+48 → 16+48 → 16+16: файлы усекаются последовательно, и каждое усечение ждёт собственного прохода purge. Помеченный файл переводится в неактивное состояние, и новые транзакции сегментов в нём не получают.

Опыт 2: три алгоритма ALTER и очередь metadata lock

ALGORITHM в ALTER TABLE выбирает цену. Проверяем три случая на таблице orders:

SET SESSION cte_max_recursion_depth = 1000000;
CREATE TABLE orders (id int AUTO_INCREMENT PRIMARY KEY, customer_id int NOT NULL, amount decimal(10,2) NOT NULL, note varchar(40)) ENGINE=InnoDB;
INSERT INTO orders (customer_id, amount, note)
WITH RECURSIVE s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i < 1000000)
SELECT i % 5000, i / 100, CONCAT('order-', i) FROM s;

Файл таблицы занимает 56 МиБ. Через 0,4 с после старта ALTER из другой сессии выполняется INSERT; для сравнения: одиночный INSERT без ALTER занимает 37–48 мс (почти всё — запуск клиента).

ALTER TABLE orders ADD COLUMN status tinyint NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders MODIFY amount decimal(12,2) NOT NULL, ALGORITHM=INPLACE;
ALTER TABLE orders MODIFY amount decimal(12,2) NOT NULL, ALGORITHM=COPY, LOCK=NONE;
ALTER TABLE orders MODIFY amount decimal(12,2) NOT NULL, ALGORITHM=COPY;

вывод

ADD COLUMN ... ALGORITHM=INSTANT            alter 48 мс      insert 40 мс
ADD INDEX ... INPLACE, LOCK=NONE            alter 1462 мс    insert 41 мс
MODIFY amount, ALGORITHM=INPLACE            ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
MODIFY amount, COPY, LOCK=NONE              ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
MODIFY amount, ALGORITHM=COPY               alter 3008 мс    insert 2612 мс

INSTANT меняет только метаданные: 48 мс на миллионе строк. INPLACE строит индекс 1,5 с и не мешает записи: вставка заняла те же 41 мс. Смена типа столбца ни тем, ни другим не делается: сервер сам отвечает «Try ALGORITHM=COPY», и COPY блокирует запись до конца, вставка ждала 2612 мс, то есть всё оставшееся время ALTER. Откуда берётся этот отказ, видно в ha_innobase::check_if_supported_inplace_alter (handler0alter.cc, строки 1062–1074):

  if (ha_alter_info->handler_flags &
      ~(INNOBASE_INPLACE_IGNORE | INNOBASE_ALTER_NOREBUILD |
        INNOBASE_ALTER_REBUILD)) {
    if (ha_alter_info->handler_flags &
        Alter_inplace_info::ALTER_STORED_COLUMN_TYPE) {
      if (ha_alter_info->alter_info->requested_algorithm ==
          Alter_info::ALTER_TABLE_ALGORITHM_INSTANT) {
        ha_alter_info->unsupported_reason = innobase_get_err_msg(
            ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_COLUMN_TYPE_INSTANT);
      } else {
        ha_alter_info->unsupported_reason = innobase_get_err_msg(
            ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_COLUMN_TYPE);
      }

Флаги, которых нет среди NOREBUILD и REBUILD, дают unsupported_reason; для смены типа столбца это COLUMN_TYPE, в выводе — Reason: Cannot change column type INPLACE.

Но даже INSTANT и INPLACE с LOCK=NONE бесплатны не всегда: ALTER просит эксклюзивный metadata lock. Сессия A открывает транзакцию, читает таблицу и молчит 14 с; сессия B запускает ALGORITHM=INSTANT, сессия C через 2 с — обычный SELECT:

SELECT id, state, LEFT(info,48) AS info FROM information_schema.processlist WHERE db='lab' AND command<>'Sleep';
SELECT owner_thread_id AS thr, lock_type, lock_status FROM performance_schema.metadata_locks
WHERE object_schema='lab' AND object_name='orders' ORDER BY owner_thread_id, lock_status;

вывод

| id | state                           | info                                             |
| 90 | Waiting for table metadata lock | ALTER TABLE orders DROP COLUMN status, ALGORITHM |
| 91 | Waiting for table metadata lock | SELECT id FROM orders WHERE id = 1               |

| thr  | lock_type         | lock_status |
|  157 | SHARED_READ       | GRANTED     |
|  158 | SHARED_UPGRADABLE | GRANTED     |
|  158 | EXCLUSIVE         | PENDING     |
|  159 | SHARED_READ       | PENDING     |

ALTER ждал 12,0 с, пока A не закончит транзакцию, а безобидный SELECT, пришедший позже, стоял в очереди за ALTER 10,0 с, хотя с читателем A совместим. Это та же очередь, что у ACCESS EXCLUSIVE в PostgreSQL (очередь за ALTER TABLE): новый запрос встаёт за ожидающим сильным режимом. Ограничить ожидание можно lock_wait_timeout, по умолчанию он 31 536 000 с (год); с SET SESSION lock_wait_timeout=3 тот же ALTER под открытой транзакцией упал через 3,0 с с ERROR 1205 (HY000): Lock wait timeout exceeded.

Опыт 3: OPTIMIZE TABLE после массового DELETE

Для таблицы место возвращает только перестроение. Берём ту же orders (после трёх ALTER выше её файл занимает 76 МиБ), удаляем 900 тысяч строк и смотрим файл до и после OPTIMIZE TABLE:

DELETE FROM orders WHERE id % 10 <> 0;
OPTIMIZE TABLE orders;
SELECT (SELECT COUNT(*) FROM orders) AS rows_n, file_size/1048576 AS file_mib
FROM information_schema.innodb_tablespaces WHERE name='lab/orders';

вывод

-- до DELETE
| rows_n  | file_mib |
| 1000000 |  76.0000 |
-- после DELETE, purge отработал (history list через 40 с: 6)
| 100000  |  76.0000 |
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| lab.orders | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| lab.orders | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+
-- после OPTIMIZE (0,5 с)
| 100000  |  13.0000 |

Файл сохранил 76 МиБ при десяти процентах строк, а перестроение уложило их в 13. Сообщение «recreate + analyze» говорит прямо: OPTIMIZE TABLE для InnoDB не отдельная операция, а перестроение таблицы. То же делает ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE: сервер принял его (0,55 с), тогда как ALGORITHM=INSTANT для него отвергнут с ошибкой 1845. Значит, перестроение идёт «на месте» и допускает конкурентную запись.

Что из этого следует

• Место undo возвращается лестницей, а не разом. После закрытия долгой транзакции история очищается примерно за две минуты, а файлы сжимаются по одному, и для этого purge должен продолжать работать. Диск под undo освобождается позже, чем уходят алерты по history list.
• Порог innodb_max_undo_log_size решает, будет ли усечение вообще. Файл в 48 МиБ ниже порога по умолчанию в 1 ГиБ, и усечение его не касается. Порог меняется на ходу командой SET GLOBAL, без перезапуска: в опыте это сработало.
• OPTIMIZE TABLE — это перестройка с копией данных. Файл вернул 63 из 76 МиБ, потому что таблица переписана целиком. По объёму работы это ближе к VACUUM FULL в PostgreSQL, чем к обычному VACUUM: обновления и удаления сами файл не уменьшают. Но, в отличие от VACUUM FULL, перестроение InnoDB принимает LOCK=NONE и пропускает запись.
• Алгоритм указывай явно. С ALGORITHM=INSTANT или INPLACE, LOCK=NONE сервер отказывается, если операция тяжелее, а не молча выбирает COPY.
• Online DDL не отменяет очередь metadata lock. Любой ALTER, даже INSTANT, ждёт чужую транзакцию, а следом за ним встают все запросы к таблице. Перед миграцией найди открытые транзакции в information_schema.innodb_trx и поставь lock_wait_timeout.
• Смена типа столбца в опыте возможна только через COPY с блокировкой записи. Для таких случаев существуют внешние инструменты pt-online-schema-change и gh-ost; их работа здесь не измерялась.

Чек-лист

1. Следи за trx_rseg_history_len и размерами innodb_undo_% в information_schema.innodb_tablespaces; включи undo_truncate_count в innodb_metrics, чтобы видеть усечения.
2. Если undo вырос, а долгих транзакций нет, проверь innodb_max_undo_log_size и дай purge работать: усечение идёт по одному файлу за проход.
3. После массового DELETE сравни file_size таблицы с числом строк; OPTIMIZE TABLE запускай, когда есть место под копию.
4. Перед ALTER пиши ALGORITHM= и LOCK= явно, ставь SET SESSION lock_wait_timeout и сначала проверь information_schema.innodb_trx на старые транзакции.
5. Убери контейнер вместе с томом: docker rm -f -v dba-innodb-purge.