Как известно, при коммите 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 Мб по умолчанию, но часто не нужно устанавливать их настолько большими, как вы можете думать.

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

Оригинал: Baron Schwartz, How to calculate a good InnoDB log file size