InnoDB не физически удаляет строку из базы данных сразу после её удаления с помощью оператора SQL. Строка и её записи в индексе удаляются физически только тогда, когда InnoDB отбрасывает запись журнала отката, написанную для удаления. Эта операция удаления, которая выполняется только после того, как строка больше не требуется для управления одновременным доступом к данным нескольких версий (MVCC) или отката, называется очисткой.

Очистка выполняется по периодическому расписанию. Она анализирует и обрабатывает страницы журнала отката из списка истории, который представляет собой список страниц журнала отката для завершённых транзакций, поддерживаемый системой транзакций InnoDB. После обработки страницы освобождаются из списка истории.

Настройка потоков очистки

Операции очистки выполняются в фоновом режиме одним или несколькими потоками очистки. Количество потоков очистки контролируется переменной innodb_purge_threads. Значение по умолчанию равно 4. Если операции DML сконцентрированы на одной таблице, операции очистки для этой таблицы выполняются одним потоком очистки. Если операции DML сконцентрированы на нескольких таблицах, следует поддерживать низкое значение innodb_purge_threads, чтобы потоки не конкурировали друг с другом за доступ к занятым таблицам. Если операции DML распределены по многим таблицам, можно рассмотреть более высокое значение innodb_purge_threads. Максимальное количество потоков очистки равно 32.

Значение innodb_purge_threads — это максимальное количество разрешённых потоков очистки. Система очистки автоматически настраивает количество используемых потоков очистки.

Настройка размера пакета очистки

Переменная innodb_purge_batch_size определяет количество страниц журнала отката, которые очистка анализирует и обрабатывает в одном пакете из списка истории. Значение по умолчанию равно 300. В конфигурации очистки с несколькими потоками координирующий поток очистки делит innodb_purge_batch_size на innodb_purge_threads и назначает это количество страниц каждому потоку очистки.

Система очистки также освобождает страницы журнала отката, которые больше не требуются. Она делает это каждые 128 итераций по журналам отката. Помимо определения количества страниц журнала отката, анализируемых и обрабатываемых в пакете, переменная innodb_purge_batch_size определяет количество страниц журнала отката, которые очистка освобождает каждые 128 итераций по журналам отката.

Переменная innodb_purge_batch_size предназначена для продвинутой настройки производительности и экспериментов. Большинству пользователей не нужно изменять innodb_purge_batch_size от значения по умолчанию.

Настройка максимальной задержки очистки

Переменная innodb_max_purge_lag определяет желаемую максимальную задержку очистки. Когда задержка очистки превышает пороговое значение innodb_max_purge_lag, на операции INSERT, UPDATE и DELETE накладывается задержка, чтобы дать время операциям очистки «догнать». Значение по умолчанию равно 0, что означает отсутствие максимальной задержки очистки и задержки.

Система транзакций InnoDB поддерживает список транзакций, для которых операциями UPDATE или DELETE были помечены записи индекса для удаления. Длина списка — это задержка очистки. Задержка задержки очистки рассчитывается по следующей формуле, что приводит к минимальной задержке 5000 микросекунд:

(purge lag/innodb_max_purge_lag - 0.5) * 10000

Задержка рассчитывается в начале пакета очистки.

Типичное значение innodb_max_purge_lag для проблемной рабочей нагрузки может быть 1000000 (1 миллион), предполагая, что транзакции небольшие, всего 100 байт в размере, и допустимо наличие 100 МБ необработанных строк таблицы.

Задержка очистки представлена как значение History list length в разделе TRANSACTIONS вывода SHOW ENGINE INNODB STATUS.

mysql> SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
History list length 20

History list length обычно является низким значением, обычно меньше нескольких тысяч, но при интенсивной записи или длительных транзакциях оно может увеличиваться, даже для транзакций только для чтения. Причина, по которой длительная транзакция может привести к увеличению History list length, заключается в том, что при уровне изоляции транзакции «последовательное чтение» (таком, как REPEATABLE READ), транзакция должна возвращать тот же результат, что и при создании представления чтения для этой транзакции. Следовательно, система управления одновременным доступом к данным с несколькими версиями (MVCC) InnoDB должна сохранить копию данных в журнале отката до завершения всех транзакций, зависящих от этих данных. Вот примеры длительных транзакций, которые могут привести к увеличению History list length:

Чтобы предотвратить чрезмерные задержки в экстремальных ситуациях, когда задержка очистки становится огромной, можно ограничить задержку, установив переменную innodb_max_purge_lag_delay. Переменная innodb_max_purge_lag_delay задаёт максимальную задержку в микросекундах для задержки, накладываемой при превышении порога innodb_max_purge_lag. Установленное значение innodb_max_purge_lag_delay является верхним пределом периода задержки, рассчитанного по формуле innodb_max_purge_lag.

Очистка и обрезка табличного пространства отката

Система очистки также отвечает за обрезку табличных пространств отката. Вы можете настроить переменную innodb_purge_rseg_truncate_frequency, чтобы контролировать частоту, с которой система очистки ищет табличные пространства отката для обрезки. Дополнительную информацию см. в разделе Обрезка табличных пространств отката.