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 для управления частотой, с которой система очистки ищет пространства таблиц отката для обрезки. Дополнительную информацию см. в разделе Обрезка пространств таблиц отката.