Как в 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 важно для достижения баланса между достаточно быстрым временем восстановления после сбоя и хорошей производительностью системы. Помните, что ваше время восстановления не так тривиально, как вы можете себе представить. Я надеюсь, что методы, описанные в этом посте, помогут вам найти оптимальное значение для вашей ситуации!

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

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

22 − 19 =

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

Обновление Woodmart 6.1.3 что вышло нового: обзор всех изменений

Улучшение функционала и возможностей WoodMart WordPress продолжается и было выпущено новое значительное обновление 6.1. Основное направление сейчас - оптимизация как с точки зрения скорости, так и с точки зрения удобства пользователей. Много также изменений некоторых настроек темы, чтобы помочь людям находить некоторые настройки и...

Обзор визуального конструктора WordPress Zion Builder, легкий и пока бесполезный

Многие люди знают WordPress как инструмент для ведения блогов. Не ошибаюсь, но WordPress выходит за рамки этого. WordPress - это программное обеспечение CMS, которое можно использовать для создания веб-сайтов любого типа. Хотя WordPress очень помог в создании веб-сайта, плагин для создания страниц делает это еще проще. Вы можете использовать конструктор страниц, чтобы...

Быстрые покупки на Woocommerce – плагин Экспресс шоп, продажа товаров с 1 страницы

Express Shop - это надстройка WooCommerce для отображения всех продуктов на одной странице с помощью кнопки AJAX «Добавить в корзину». Покупатели могут быстро отфильтровать товары и ускорить процесс покупки. Функции включают в себя несколько шаблонов стилей , загружать больше, бесконечную прокрутку, быструю плавающую корзину,...

Как убрать вкладки на странице товаров Woocommerce (вариант Амазона без табов)

Если вам нравится макет страницы одного продукта Amazon, вам, вероятно, не нравится макет вкладок страницы одного продукта WooCommerce по умолчанию. В этом разделе по умолчанию отображаются «Описание», «Обзоры», «Дополнительная информация» и другое настраиваемое содержимое… в виде вкладок под изображением и кратким описанием. К счастью, есть очень...

Аналог Contact Form 7 – настройка и описание премиум плагина Form Builder

Начну с того почему я всегда использую аналог Contact Form 7 плагина для Wordpress и не рекомендую использовать этот плагин. Слишком большое распространение и бесплатность сыграли злую шутку с сайтами где этот плагин установлен. На него написано много инструментов автоматического спама. Вы еще не...

Структура базы данных WooCommerce: хранение данных, где что находится

Термин «база данных» широко используется и известен миллионам людей, но доля людей, которые имеют его в своем словарном запасе, не понимая, что он описывает, весьма значительна. Базы данных сегодня лежат в основе столь многих цифровых сервисов, что их легко полностью упустить из виду, но независимо...