Расчет и изменение размера redo-log InnoDB (innodb_log_file_size)

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

Давайте рассмотрим алгоритм расчета правильного размера innodb_log_file_size и поменяем его на своем сервере.

Исходные данные: Oracle MySQL 5.7.22 на Ubuntu 16.04
Задача: Рассчитать правильный размер параметра innodb_log_file_size и изменить его.

Для начала немного теории. Что же такое redo-log или журнал повторного выполнения ?

Журнал повторного выполнения (redo-log) в MySQL — это определенная дисковая структура данных, используемая во время восстановления после сбоя. Во время работы движок InnoDB помещает в эту область данные по незаконченным транзакциям, которые в результате непредвиденного сбоя в работе могут быть потеряны. Если произойдет сбой, то InnoDB попробует восстановить эти незаконченные транзакции взяв данные из этой области.

Физически эта область представляется в виде набора файлов ib_logfile0 и ib_logfile1. По-умолчанию в группе redo-log всего 2 файла, располагаются они в рабочем каталоге MySQL (/var/lib/mysql, рабочий каталог определяется директивой datadir). Количество redo-логов, их местоположение и размер можно поменять. До версии MySQL 5.6.3 общий размер файлов был ограничен 4 GB, в версиях новее 5.6.3 этот предел увеличили до 512 GB (это общий предельный размер всех файлов журнала повторного выполнения, если их у Вас 2 файла, то каждый файл может быть не более 256 GB, если у вас их 4, то каждый не более 128 GB).

Для нас будут интересны следующие параметры конфигурации:
innodb_log_file_size — размер журнала повторного выполнения (redo-log);
innodb_log_group_home_dir — местоположение журнала если оно отличается от значения директивы datadir;
innodb_log_files_in_group — количество файлов в журнале;

На производительность журнала повторного выполнения (redo-log) оказывает влияние размер буфера этого журнала, который контролируется параметром innodb_log_buffer_size. Если у Вас существуют большие транзакции, которые удаляют/обновляют/добавляют огромное количество строк (огромное в моем понимании — это значит более 200 тыс. строк), то увеличение размера этого буфера может сократить количество дисковых операций по записи данных в журнала повторного выполнения (redo-log).

Так же важно понимать, что чем больше журнала повторного выполнения (redo-log), тем дольше MySQL будет запускаться и восстанавливать данные в случае сбоя. В то же время маленький размер журнала при большом количестве транзакций порождает большие дисковые операции и частые переключения журнала (об этом чуть ниже). Поэтому к выбору размера redo-log нужно подходить с умом. В блоге Percona есть отличная статья о том как размер redo-log влияет на производительность, рекомендую ее почитать для большего понимания сути вопроса.

Журнала повторного выполнения (redo-log) является кольцевым, то есть данные в файлы пишутся по кольцу. Если у нас 2 файла журнала, то когда первый файл журнала ib_logfile0 будет заполнен, данные будут записываться во второй файл ib_logfile1, как только ib_logfile1 так же будет заполнен — данные начнут записываться в первый файл ib_logfile0 и так по кругу.

По-умолчанию для Oracle MySQL 5.7 размер innodb_log_file_size равен 48 MB, что вполне подходит для небольших нагрузок и не требует изменения, но если у Вас большой innodb_buffer_pool_size и большие транзакции, то тогда маленький innodb_log_file_size может стать узким местом в производительности БД.

Прежде чем увеличивать размер журнала повторного выполнения (redo-log) нам нужно понять до какого размера его нужно увеличить, а это без сбора статистики весьма проблематично.
В сети Интернет можно встретить несколько методик расчета значения innodb_log_file_size. Я приведу эти методики расчета, а так же дам ссылку в конце статьи на написанный мной скрипт, который по этим методикам производит расчет занятости и скорости записи текущих redo-log и делает расчет размера.

Для начала возьмем популярную у всех утилиту MySQLTuner (v1.7.4), запустим ее и получим примерно такой результат:

....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0G/58.1G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 6.0G * 2/128.0G should be equal 25%
[OK] InnoDB buffer pool instances: 64
[--] Number of InnoDB Buffer Pool Chunk : 1024 for 64 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (150498336498 hits/ 150499990204 total)
[OK] InnoDB Write log efficiency: 99.93% (463547667 hits/ 463852647 total)
[OK] InnoDB log waits: 0.00% (0 waits / 304980 writes)
....
....
Variables to adjust:
    innodb_log_file_size should be (=16G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Как мы видим, на моем сервере innodb_buffer_pool_size = 128.0G, innodb_log_files_in_group = 2 и MySQLTuner предлагает сделать innodb_log_file_size размером в 25% от размера innodb_buffer_pool_size, то есть 16 GB. Логика MySQLTuner проста и… ошибочна. Конечно у меня уже выставлен корректный для моей системы и нагрузки размер innodb_log_file_size = 6 GB, но MySQLTuner не преклонен.

Вторая методика расчета размера redo-лога основана на анализе значения статусной переменной Innodb_os_log_written.
На официальной странице документации по MySQL — 5.1.9 Server Status Variables пишут, что Innodb_os_log_written — это количество байтов, записанных в файл журнала повторного выполнения (redo-log), то есть по сути казалось бы то, что нам и нужно.

Выполняем:

mysql -e "show global status like '%Innodb_os_log_written%'" && sleep 3600 && mysql -e "show global status like '%Innodb_os_log_written%'"
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 234628115968 |
+-----------------------+--------------+
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 247249977856 |
+-----------------------+--------------+

Мы выводим Innodb_os_log_written, потом ждем 1 час (3600 сек.), именно 1 час, а не 1 минуту как предлагают во многих статьях, потом снова выводим Innodb_os_log_written.
Теперь вычитаем из второго числа первое, это и будет количество байт записанных в журнал за 1 час.
Во многих статьях предлагают взять данные за 1 минуту и умножить их на 60, но это совершенно неправильно, потому что в течении часа нагрузка может сильно меняться и вы не получите реальных данных, вы можете просто пропустить пиковые минуты и посчитаете размер лога заведомо заниженным. Даже собрав данные за 1 час, они тоже могут отличать от данных за следующий час. По сути нужно собирать часовые данные на протяжении 24 часов и далее либо усреднять их, либо брать наибольший часовой результат.

Теперь давайте посчитаем размер журнала, он должен быть в innodb_log_files_in_group раз меньше нашей разности чисел, то есть, если у нас 2 журнала (innodb_log_files_in_group = 2), то размер лога в MB будет:

innodb_log_file_size = (247249977856 - 234628115968) / (2 * 1024 * 1024) = 6019 MB

При повторном исполнении команды за следующий час у меня записалось в журнал уже 10195 MB, то есть числа различаются довольно намного, что наталкивает на мысль, что это не очень правильная формула, но ее почему-то все копируют и приподносят как верный вариант расчета.

В интернете есть другая методика расчета размера журнала, по LSN, давайте попробуем посчитать размер по ней.

Алгоритм расчета по LSN такой:
1. Первым делом мы должны включить сбор определенных метрик для InnoDB, по-умолчанию они отключены, проверим этот факт:

mysql> SELECT NAME,COUNT,TYPE,STATUS,COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME IN ('log_lsn_current','log_lsn_last_checkpoint');"
+-------------------------+-------+-------+----------+------------------------+
| NAME                    | COUNT | TYPE  | STATUS   | COMMENT                |
+-------------------------+-------+-------+----------+------------------------+
| log_lsn_last_checkpoint |     0 | value | disabled | LSN at last checkpoint |
| log_lsn_current         |     0 | value | disabled | Current LSN value      |
+-------------------------+-------+-------+----------+------------------------+
2 rows in set (0,00 sec)

Давайте включим эти 2 метрики и проверим что они показывают:

SET GLOBAL innodb_monitor_enable = log_lsn_last_checkpoint;
SET GLOBAL innodb_monitor_enable = log_lsn_current;

mysql>  SELECT NAME,COUNT,TYPE,STATUS,COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME IN ('log_lsn_current','log_lsn_last_checkpoint');
+-------------------------+-----------------+-------+---------+------------------------+
| NAME                    | COUNT           | TYPE  | STATUS  | COMMENT                |
+-------------------------+-----------------+-------+---------+------------------------+
| log_lsn_last_checkpoint | 213726422117928 | value | enabled | LSN at last checkpoint |
| log_lsn_current         | 213727656760059 | value | enabled | Current LSN value      |
+-------------------------+-----------------+-------+---------+------------------------+
2 rows in set (0,00 sec)

mysql>  SELECT NAME,COUNT,TYPE,STATUS,COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME IN ('log_lsn_current','log_lsn_last_checkpoint');
+-------------------------+-----------------+-------+---------+------------------------+
| NAME                    | COUNT           | TYPE  | STATUS  | COMMENT                |
+-------------------------+-----------------+-------+---------+------------------------+
| log_lsn_last_checkpoint | 213726885432517 | value | enabled | LSN at last checkpoint |
| log_lsn_current         | 213728002745564 | value | enabled | Current LSN value      |
+-------------------------+-----------------+-------+---------+------------------------+
2 rows in set (0,00 sec)

Мы можем видеть как значения начинают меняться, значит можно приступить к расчету.

На самом деле эти цифры можно получить из результата вывода команды SHOW ENGINE INNODB STATUS\G

Я выведу только часть информации из секции LOG

mysql> SHOW ENGINE INNODB STATUS\G
....
---
LOG
---
Log sequence number 213728002745564
Log flushed up to   213728002744154
Pages flushed up to 213726885432517
Last checkpoint at  213726885432517
0 pending log flushes, 0 pending chkp writes
415251 log i/o's done, 2.15 log i/o's/second
....

Соответствия строк такие: ‘Log sequence number’ = log_lsn_current и ‘Last checkpoint at’ = log_lsn_last_checkpoint
Но вывод SHOW ENGINE INNODB STATUS\G не удобен для парсинга и автоматизации расчета, поэтому удобнее использовать SQL запрос к INFORMATION_SCHEMA.

Для расчета нам понадобится текущие innodb_log_file_size и innodb_log_files_in_group, у меня на сервере innodb_log_file_size = 6442450944, а innodb_log_files_in_group = 2

Теперь произведем расчет, формулы такие:

Used log = log_lsn_current - log_lsn_last_checkpoint
		 = 213728002745564 - 213726885432517
		 = 1117313047 B (1065 MB)
Used %   = (Used log / Total log size) * 100
		 = (1117313047 / (innodb_log_file_size * innodb_log_files_in_group)) * 100
		 = (1117313047/ (6442450944 * 2)) * 100
		 = 8,67 %

То есть исходя их расчета на основе LSN нам рекомендован размер журнала 1100 MB, в реальности нужно взять это число с 20% запасом, то есть 1300 MB.

Получается при расчета на основе Innodb_os_log_written мы получили 6019 MB, а при расчете по LSN всего 1100 MB, разница почти в 6 раз, что очень существенно.

Какой расчет верный ?

Эксперты из Percona провели расследование и написали хорошую статью о том какая из методик более правильная и почему данные различаются.

Если говорить кратко, то расчет на основе LSN более корректен, но и расчет на основе анализа Innodb_os_log_written тоже не стоит сбрасывать со счетов.

Более наглядные данные можно получить если проанализировать показатели «Uncheckpointed Bytes» (это разность «Log sequence number» — «Last checkpoint at») и «Max Checkpoint Age» (это «Last checkpoint at») и представить их в виде графика, если мы получим график похожий на этот (график из Percona Monitoring and Management):

InnoDB Checkpoint Age

InnoDB Checkpoint Age

где Uncheckpointed Bytes приближается к Max Checkpoint Age, то можно сказать однозначно, что текущее значение innodb_log_file_size ограничивает производительность Вашей системы. Увеличение innodb_log_file_size в данном случае может обеспечить значительное улучшение производительности.

Если мы видим график похожий на этот:

InnoDB Checkpoint Age

InnoDB Checkpoint Age

где количество Uncheckpointed Bytes значительно меньше Max Checkpoint Age, то тогда увеличение размера файла журнала (innodb_log_file_size) не даст Вам существенного улучшения производительности.

А до какого значения нужно увеличить innodb_log_file_size, спросите Вы? Увеличивать innodb_log_file_size нужно постепенно и анализировать показатели работы БД, а так же производить расчеты указанные выше.

И в заключении, для облегчения жизни себе и другим я написал скрипт на bash который автоматически производит расчеты описанных выше показателей и выводит данные в таком виде:

================================================
Connected MySQL v5.7.22-0ubuntu0.16.04.1-log
================================================
Checking InnoDB Monitor...
InnoDB Monitor (log_lsn_last_checkpoint): enabled
InnoDB Monitor (log_lsn_current): enabled
================================================
Calculate InnoDB redo-log used:
innodb_log_file_size = 6442450944
innodb_log_files_in_group = 2
innodb_total_log_size = 12884901888
Last checkpoint at: 213760042843783
Log sequence number: 213761145496755
Log used (byte): 1102652972
Log used (Mbyte): 1051
Log used (%): 8.00
================================================
Calculate InnoDB redo-log write speed:
Innodb_os_log_written = 338639640064
Sleeping 3600 second...
Innodb_os_log_written = 364889872384
Write speed (MB_per_1_hour): 25034.1
================================================
Note: Current redo-log write speed > innodb_log_file_size*innodb_log_files_in_group
Recomended set parameter innodb_log_file_size = 15020M
================================================

Данный скрипт протестирован на следующих дистрибутивах Linux: Debian 9, Ubuntu 16.04, Oracle Linux 6.1
Протестирован на следующих версиях MySQL: Oracle MySQL 5.7.x, MariaDB 10.3.x, Percona Server for MySQL 5.6.x/5.7.x
Скрипт сам включит нужные метрики InnoDB, сам все подсчитает, Вам лишь остается подождать 1 час для более точного расчета, об этом я говорил выше.
Так же скрипт проанализирует размер текущего журнала и расчетного и даст рекомендацию, например выше по тексту он рекомендует увеличить размер журнала, но повторюсь, рекомендовано — не значит, что нужно бежать и слепо делать.
Желательно запускать скрипт в screen чтобы не потерять результаты если соединение по SSH отключиться.

Прямая ссылка для скачивания скрипта mysql-innodb-log-file-size-calculator.sh

После того как Вы рассчитали нужный размер innodb_log_file_size и innodb_log_files_in_group настало время изменить настройки MySQL.

Процедура изменения innodb_log_file_size достаточно простая:
1. Запишите в файл конфигурации MySQL (/etc/my.cnf или другой) новое значение рассчитанных параметров;
2. Остановите MySQL, при этом проверьте по error.log, что произошло корректное завершение работы MySQL и не возникли ошибки, это очень важно!
3. Перейдите в каталог datadir (по-умолчанию /var/lib/mysql) и переименуйте файлы ib_logfile0 и ib_logfile1. Именно переименуйте, не удаляйте их, это вы сделаете после успешного старта MySQL;
4. Запустите MySQL, при этом так же наблюдайте за событиями в error.log, там Вы должны увидеть запуск процедуры создания новых файлов журнала;
5. Если MySQL успешно запустился и новые файлы журнала будут созданы, то удалите старые файлы журнала;

Полезные статьи:
MySQL Official Docs: Optimizing InnoDB Redo Logging
Percona blog: Measuring the amount of writes in InnoDB redo logs

На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.


9
Оставить комментарий

avatar
1000
8 Цепочка комментария
1 Ответы по цепочке
0 Последователи
 
Популярнейший комментарий
Цепочка актуального комментария
4 Авторы комментариев
МихаилСергейНиколайВладимир Авторы недавних комментариев

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

  Подписаться  
Уведомление о
Владимир
Гость
Владимир

А какой смысл в точном подборе размера журнала? Почему нельзя, например, задать размер заведомо больший, чем необходимо?

Николай
Гость
Николай

Попробовал данную методику, измерял 2 раза, вывел среднее, поставил.
Сервер начал уходить в спам.
Пришлось вернуть рекомендации mysqltuner’а.
По всей видимости, данная методика годится для проектов с относительно небольшим объёмом записи и большим объёмом чтения, или очень большими объёмами данных, не знаю, что именно определяет эффективность методики. У меня средненький магазин до 15 т. товаров с сильно плавающей нагрузкой, измерял как в часы пика, так и в часы затишья.

Николай
Гость
Николай

Речь о веб-сайте, информация о количестве товаров приводилась чтобы эмпирически можно было представить объём данных, с которыми может работать система. InnoDB buffer pool / data size: 1.2G/1.2G Ratio InnoDB log file size / InnoDB Buffer pool size: 160.0M * 2/1.2G should be equal 25% Пока что поставил эту рекомендацию. Я сделал 2 измерения с помощью вашего скрипта, один раз в спокойном состоянии, получил 1 гигабайт по приведённой методике, второй раз, под нагрузкой: 1.7 гига. Решил, что среднее из этого в любом случае много, поставил 1Гб. Наблюдал в течении дня невысокую производительность и разрастание спама. Проводить более частые наблюдения, в течении… Подробнее »

Николай
Гость
Николай

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

Сергей
Гость
Сергей

Здравствуйте! Не могли бы вы уточнить, что именно изображено на графике? Если я правильно понял, оранжевым показан «Log sequence number», но ведь значение LSN не может уменьшиться…
И какой именно участок говорит о том, что размер нужно увеличить?