Изменение размера переменной innodb_log_file_size

При коммите InnoDB записывает данные не сразу в файлы данных, а сначала записывает изменения в innodb_log_file. Дело в том что записать данные непосредственно в таблицу - существенно более дорогая операция, чем записать изменения в бинарный лог. 

Ведение innodb_log_file позволяет проводить оптимизацию i/o: записывать данные большими последовательными кусками, а также более быстрее обслуживать клиентов (клиент быстро сделал коммит, а данные в табличное пространство записываются в фоне). 

При старте после неожиданного отключения MySQL просматривает innodb_log_file, откатывая транзакции, которые не успели завершиться перед крахом и отмечая коммиты, которые успели (и были полностью записаны в innodb_log_file). Чем больше файл, тем больше времени требуется серверу, чтобы просмотреть его.

mysql> show variables like 'innodb_log_file_size';

+---------------------------+--------------+

| Variable_name        | Value       |

+---------------------------+--------------+

| innodb_log_file_size | 5242880 |

По умолчанию в mysql 5.5 размер innodb_log_file_size установлен в 5M. 

Как же определить наиболее оптимальный размер?

Выполните эти команды во время наиболее интенсивной нагрузки вашего сервера:

mysql> pager grep sequence
PAGER SET TO 'grep sequence'
mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G

Подробности смотрите в ссылках в конце статьи.

Собственно, процедура. Как оказалось, нельзя просто поменять значение в конфигурационном файле. Нужно, чтобы mysql заново создал эти файлы после остановки. Т.е. нужно остановить сервер, изменить значения директивы innodb_log_file_size, удалить или переместить старые файлы ib_logfile0/ib_logfile1, затем стартовать mysql. Приступим:

1. sudo service mysqld stop

2. Отредактировать /etc/my.cnf, изменив/добавив в секцию [mysqld]

innodb_log_file_size = 64M

3. cd /var/lib/mysql; sudo mv ib_logfile0 ib_logfile0_1; sudo mv ib_logfile1 ib_logfile1_1

4. sudo service mysqld start

5. Проверить лог-файлы, что все стартануло нормально:
*****
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
111210 15:18:33  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
*****


Литература:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size
http://www.mysqlperformanceblog.com/2011/11/21/should-mysql-update-the-default-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
http://greenmice.info/ru/node/92