Как в 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 ограничивает производительность вашей системы. Увеличение его может обеспечить существенное улучшение производительности.

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

Innodb Log File Size 2

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

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

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

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

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

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

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

Резюме

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

ОСТАВЬТЕ ОТВЕТ

Пожалуйста, введите ваш комментарий!
пожалуйста, введите ваше имя здесь

+ 83 = 84

OK2WEB
OK2WEB
Более 12 лет занимаюсь разработкой и продвижением сайтов. Городские порталы, информационные и новостные сайты. Сейчас в основном разрабатываю интернет магазины и коммерческие сайты. Если у вас есть вопросы пишите, стараюсь оперативно ответить и помочь.

Прощай Юра Шатунов, мы любили твои песни

Даже трудно сказать когда я первый раз услышал песни Ласкового мая. В некотором роде они были настолько ясными для того возраста. Мне было лет 14 и конечно простые слова про любовь, жизнь, в исполнении мальчишки моего примерно возраста трогало и цепляло. Даже сейчас спустя...

Оптимизация графики в WordPress, отключаем лишние, сжатие, чистка размеров графики

Графика самый тяжелый элемент сайт Wordpress и поэтому правильный подход к их оптимизации очень важен. Это не сделать одной операцией, особенно если раньше вы не особо обращали на это внимание. Раздувание размеров сайта со временем становится проблемой, решать которую надо. Но как решить оптимально...

WordPress 6 изучаем что было добавлено, улучшения и дополнения

Поздоровайтесь с «Arturo» и новой версией WordPress 6.0, вдохновленными обладателем Грэмми джазовым музыкантом Артуро О'Фарриллом . Известный своим влиянием на современный латиноамериканский джаз, Артуро выпустил более 15 альбомов, охватывающих работу за пять десятилетий. Потратьте некоторое время на изучение WordPress 6.0, созданного, чтобы помочь вам раскрыть...

Нехватки памяти для работы сайта WordPress — увеличиваем память в конфиге сайта

Просматривая журналы работы сайта вы можете увидеть очень частую ошибку нехватки Wordpress. Обычно это этом пишет предупреждение: PHP Warning: Use of undefined constant ‘WP_MEMORY_LIMIT’ - assumed '‘WP_MEMORY_LIMIT’' (this will throw an Error in a future version of PHP) То есть Вы видите ошибку исчерпания...

Google консультирует что запросы и показы в поиске отличаются по цифрам

Адвокат Google по поиску Джон Мюллер говорит, что показы, которые ваш сайт получает по ключевому слову, не являются показателем объема поиска по этому ключевому слову. Об этом говорится в Twitter в ответ на вопрос о том, можно ли использовать данные о показах в Google Search...

Инструкция WordPress cron, запуск, управление, решение ошибок, основные параметры

WP-Cron выполняет определенные задачи для сайтов на WordPress. Название Cron происходит от системы Unix для планирования заданий, от одного раза в минуту до одного раза в год. Будь то плановое обслуживание или запланированные предупреждения, любая команда, которая может быть выполнена в Unix без вмешательства...