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

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

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

Операции очистки выполняются в фоновом режиме одним или несколькими потоками очистки. Количество потоков очистки контролируется переменной innodb_purge_threads. Значение по умолчанию равно 1, если количество доступных логических процессоров меньше или равно 16, в противном случае по умолчанию используется 4.

Если операции DML сосредоточены на одной таблице, операции очистки для этой таблицы выполняются одним потоком очистки, что может привести к замедлению операций очистки, увеличению отставания очистки и увеличению размера файла табличного пространства, если операции DML включают значения больших объектов. Если значение innodb_max_purge_lag превышено, работа очистки автоматически перераспределяется между доступными потоками очистки. Слишком много активных потоков очистки в этом случае может привести к конфликтам с пользовательскими потоками, поэтому управляйте настройкой innodb_purge_threads соответственно. Переменная innodb_max_purge_lag по умолчанию установлена в 0, что означает, что по умолчанию нет максимального отставания очистки.

Если операции 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. Длина списка — это отставание очистки.

Задержка отставания очистки рассчитывается по следующей формуле:

(purge_lag/innodb_max_purge_lag - 0.9995) * 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 для управления частотой, с которой система очистки ищет табличные пространства отката для обрезки. Более подробную информацию можно найти в разделе Обрезка табличных пространств отката.