Расчет и изменение размера 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 тоже не стоит сбрасывать со счетов.

Более наглядные данные можно получить если проанализировать показатели «Log sequence number» и «Last checkpoint at» и представить их в виде графика, если мы получим график похожий на этот:

InnoDB Checkpoint Age

InnoDB Checkpoint Age

То тогда однозначно стоит увеличить размер 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

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


7 комментариев

  • Ответить Владимир |

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

  • Ответить Михаил |

    Владимир, прочитайте еще раз внимательно мою статью, в ней есть есть ответ на Ваш вопрос (почему большой журнал — это плохо и почему маленький, не соответствующей вашей нагрузке, тоже не очень хорошо).

  • Ответить Николай |

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

  • Ответить Михаил |

    Николай, что значит «Сервер начал уходить в спам.» ? Называйте вещи корректными именами. Какой у Вас InnoDB buffer pool и полный data size (см. одноименную строку в выводе mysqltuner), сколько установлен innodb_log_file_size, сколько в redo log пишется за 1 час ? Если бы Вы привели данные, то было бы проще ориентироваться, а сколько у вас там в каталоге товаров — это вообще к делу не имеет отношения.

    В статье описаны 3 метода расчета, но как я сказал, ни один не дает точного ответа. Самая правильная методика — это на основе LSN, но чтобы её воспользоваться нужно снимать данные «Log sequence number» и «Last checkpoint at» во времени за несколько дней, а лучше постоянно и следить за ними.

  • Ответить Николай |

    Речь о веб-сайте, информация о количестве товаров приводилась чтобы эмпирически можно было представить объём данных, с которыми может работать система.
    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 buffer pool ставить размер redo log 1 GB не имеет смысла, это даже без тестов очевидно. Другое дело когда InnoDB buffer pool = 128 GB и 25% это 32 GB — что очень много и при сбое восстановление с таким redo log будет длительным, поэтому для моего сервера и были проведены тесты, которые доказали, что руководствоваться цифрой 25% от InnoDB buffer pool не совсем правильно.

    И как я писал в статье, те цифры что выдадут мои скрипты и тот же mysqltuner не означает, что нужно бежать и делать как рекомендовано, нужно проводить анализ, плавно менять настройки и снова проводить анализ. И самое главное с базами данных — не нужно ожидать, что изменение параметров даст моментально прирост производительности, нужно выжидать (прогрев БД), собирать данные, анализировать.

  • Ответить Николай |

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

Хотите оставить комментарий?