Как известно, при коммите InnoDB записывает данные не сразу в файлы данных, а сначала записывает изменения в innodb_log_file. Дело в том что записать данные непосредственно в таблицу — существенно более дорогая операция, чем записать изменения в бинарный лог.
Ведение innodb_log_file позволяет проводить оптимизацию i/o: записывать данные большими последовательными кусками, а также более быстрее обслуживать клиентов (клиент быстро сделал коммит, а данные в табличное пространство записываются в фоне). Поэтому чем больше файл, тем больше возможности для InnoDB оптимизировать ввод/вывод. В настоящее время суммарный размер innodb_log_file ограничен 4 Гб, что более чем достаточно для большинства случаев.
При старте после неожиданного отключения MySQL просматривает innodb_log_file, откатывая транзакции, которые не успели завершиться перед крахом и отмечая коммиты, которые успели (и были полностью записаны в innodb_log_file). И естественно, чем больше файл, тем больше времени требуется серверу чтобы просмотреть его.
Как же определить наиболее оптимальный размер?
Выполните эти команды во время наиболее интенсивной нагрузки вашего сервера:
mysql> pager grep sequence PAGER SET TO 'grep sequence' mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G Log sequence number 84 3836410803 1 row IN SET (0.06 sec) 1 row IN SET (1 min 0.00 sec) Log sequence number 84 3838334638 1 row IN SET (0.05 sec)
Обратите внимание на номер log sequence. Это общее количество байт записанных в лог. Так что мы можем узнать сколько Мб было записано за минуту. (Эти команды будут работать в любой версии MySQL, начиная с 5.0 можно воспользоваться метрикой Innodb_os_log_written из SHOW GLOBAL STATUS.)
mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min; +------------+ | MB_per_min | +------------+ | 1.83471203 | +------------+
Хорошим правилом будет установка такого размера, чтобы там могли уместиться около часа логов. Тогда InnoDB сможет весьма эффективно спланировать записи в файлы данных, и вместе с тем это хороший компросс для скорости запуска. Округляем до 128 Мб и поскольку по умолчанию файлов два устанавливаем innodb_log_file_size=64M.
Не слишком ли это мало? Возможно. Часто я вижу размеры логов транзакций в несколько гигабайт, но обычно это ошибка. Сервер который я использовал для измерений — большой и делающий много работы, это не игрушка для тестов. Размер лог файлов не стоит оставлять в 5 Мб по умолчанию, но часто не нужно устанавливать их настолько большими, как вы можете думать.
Если воспользовавшись этим парвилом вы получули цифру в несколько гигабайн, значит видимо вы очень активно пишете в базу. В этом случае вы можете попробовать установить размер поменьше, чтобы минимизировать время восстановления. Но учтите: время восстановления зависит не только от размера лога транзцакций, но и от количества записей в нем. Если у вас много больших транзакций, можно установить размер побольше. И наоборот: если у вас много маленьких транзакций, стоит установить размер поменьше.
В любом случаев, цифра полученая этим правилом — хорошая отправная точка.
Источник:
• GreenMice: Как определить оптимальный размер innodb_log_file_size
• Обсуждение на хабре
