Настройка журнала транзакций в MySQL

innodb_log_buffer_size

Буфер log используется для записи транзакций в файл журнала транзакций. Значение по умолчанию - 8 или 16 мегабайт. Если транзакции часто и много обновляют данные, имеет смысл увеличить размер буфера записи, чтобы снизить частоту блокирования сервера в ожидании записи в журнал транзакций и снизить нагрузку на дисковую подсистему:

innodb_log_buffer_size = 8M

innodb_log_file_size и innodb_log_files_in_group

Опции позволяют настроить размер одного файла журнала транзакций и их количество. По умолчанию используется два файла.

innodb_log_file_size = 256M
innodb_log_files_in_group = 2

Чаще всего можно встретить рекомендацию использовать два журнала общим объёмом в половину от размера innodb_buffer_pool_size. До версии 5.6.3 общий размер файлов журналов не должен был превышать 4 гигабайт, а затем этот лимит был повышен до 512 гигабайт.

Для более точной оценки размера журнала нужно воспользоваться выводом команды:

SHOW ENGINE INNODB STATUS\G

Нам понадобятся значения из следующих строк:

  • Log sequence number - номер текущей записи в журнале,
  • Last checkpoint at - номер последней записи из журнала, зафиксированной в хранилище,
  • Max Checkpoint Age - максимальная разница между предыдущими двумя значениями.

Когда место в журнале подходит к концу, MySQL переносит записи из журнала в хранилище и таким образом освобождает место в журнале для новых записей. Поэтому Max Checkpoint Age не может превышать общий объём журналов. При корректном завершении работы MySQL переносит данные из журнала в хранилище. Если же работа MySQL была прервана неожиданно, то сервер в процессе запуска просматривает журнал и выполняет восстановление данных, перенося в хранилище из журнала данные зафиксированных транзакций и отбрасывая данные не зафиксированных транзакций.

Рекомендуется, чтобы объём журнала был такой, чтобы его объёма хватало для сохранения новых записей в течение часа. Разница между Log sequence number и Last checkpoint at как раз соответствует текущему объёму данных, хранящихся в журнале. Если эта разница постоянно близка к значению Max Checkpoint Age, то стоит подумать об увеличении размера журнала.

Если же она постоянно бывает существенно ниже (в два раза и более) размера журналов, то можно уменьшить объём журналов до значения, немного большего Max Checkpoint Age (например, взять запас в 20%). Уменьшение размера журнала позволит ускорить запуск MySQL после аварийного завершения его работы.

innodb_flush_log_at_trx_commit

У журнала транзакций имеется опция, аналогичная опции буферного пула innodb_flush_method, допустимыми значениями для которой могут быть следующие значения:

  • 1 - каждая транзакция после фиксации записывается на диск (наибольшая надёжность),
  • 2 - транзакция после фиксации записывается в кэш операционной системы (компромисс между надёжностью и производительностью),
  • 0 - нет требования специально сохранять данные транзакции после её фиксации, данные сохраняются по мере заполнения буфера транзакций (наибольшая производительность, но высокий риск потери данных).

По умолчанию настроено значение 1, рекомендуется настроить значение 2:

innodb_flush_log_at_trx_commit = 2

Использованные материалы