Как в Mysql и MariaDB установить оптимальный параметр innodb_log_file_size

В этом посте я дам несколько советов о том, как выбрать MySQL innodb_log_file_size. Как и многие системы управления базами данных, MySQL использует журналы для обеспечения устойчивости данных (при использовании механизма хранения InnoDB по умолчанию). Это гарантирует, что при совершении транзакции данные не будут потеряны в случае сбоя или потери питания.

Механизм хранения MySQL InnoDB использует пространство журнала Redo фиксированного размера (круговое). Размер контролируется innodb_log_file_size и innodb_log_files_in_group (по умолчанию 2). Вы  умножаете эти значения и получаете пространство журнала Redo, доступное для использования. Хотя технически это не должно иметь значения, изменяете ли вы переменную innodb_log_file_size или innodb_log_files_in_group для управления размером пространства возврата , большинство людей просто работают с innodb_log_file_size  и оставляют innodb_log_files_in_group в покое.

Настройка размера пространства повторения InnoDB является одним из наиболее важных параметров конфигурации для рабочих нагрузок с интенсивной записью. Тем не менее, это идет с компромиссами. Чем больше пространства повторения вы настроили, тем лучше InnoDB может оптимизировать ввод-вывод. Однако увеличение пространства повтора также означает более длительное время восстановления, когда система теряет питание или выходит из строя по другим причинам.

Нелегко или просто предсказать, сколько времени займет восстановление после сбоя системы для определенного значения innodb_log_file_size — это зависит от аппаратного обеспечения, версии MySQL и рабочей нагрузки. Он может широко варьироваться (разница в 10 и более раз, в зависимости от обстоятельств). Тем не менее, около пяти минут на 1 ГБ innodb_log_file_size — это достойный примерный номер. Если это действительно важно для вашей среды, я бы порекомендовал протестировать ее путем имитации сбоя системы при полной нагрузке (после полного прогрева базы данных).

Хотя время восстановления может служить ориентиром для ограничения размера файла журнала InnoDB, есть несколько других способов просмотра этого числа, особенно если у вас установлен Percona Monitoring and Management .

Проверьте панель мониторинга Percona «MySQL InnoDB Metrics». Если вы видите такой график: где Uncheckpointed Bytes выдвигается очень близко к максимальному возрасту контрольной точки, вы можете быть почти уверены, что ваш текущий innodb_log_file_size ограничивает производительность вашей системы. Увеличение его может обеспечить существенное улучшение производительности.

Если вы видите что-то вроде этого:

если число незафиксированных байтов значительно меньше максимального срока действия контрольной точки, то увеличение размера файла журнала не даст вам существенного улучшения.

Примечание : многие настройки MySQL взаимосвязаны. Хотя определенный размер файла журнала может быть достаточным для меньшего размера innodb_buffer_pool_size , большие значения пула буферов InnoDB могут гарантировать большие файлы журнала для оптимальной производительности.

Следует помнить еще одну вещь: время восстановления, о котором мы говорили ранее, действительно зависит от байтов, не отмеченных галочкой, а не от общего размера файла журнала. Если вы не видите увеличения времени восстановления при увеличении  innodb_log_file_size , посмотрите график возраста контрольной точки InnoDB — возможно, вы просто не можете полностью использовать большие файлы журнала с вашей рабочей нагрузкой и конфигурацией.

Другой способ посмотреть на размер файла журнала в контексте использования пространства журнала:

На этом графике показано количество данных, записываемых в файлы журнала InnoDB в час, а также общий размер файлов журнала InnoDB. На приведенном выше графике у нас есть 2 ГБ пространства журнала и около 12 ГБ, записываемых в файлы журнала в час. Это означает, что мы перебираем логи каждые десять минут.

InnoDB должен очищать каждую грязную страницу в пуле буферов, по крайней мере, один раз за время цикла файла журнала.

InnoDB получает лучшую производительность, когда он делает это реже, и на устройствах SSD меньше износ. Лучше смотреть не менее чем через 15 минут. Один час еще лучше.

Резюме

Получение размера innodb_log_file_file важно для достижения баланса между достаточно быстрым временем восстановления после сбоя и хорошей производительностью системы. Помните, что ваше время восстановления не так тривиально, как вы можете себе представить. Я надеюсь, что методы, описанные в этом посте, помогут вам найти оптимальное значение для вашей ситуации!