Михаил Григорьев Сисадмин - это не профессия, это призвание.

Процедура изменения размера 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

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

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

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

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