In Issue 3 of MySQL Magazine Peter Zaitsev writes, ” I would set [innodb_log_file_size] to 256 megabytes for small sized boxes up to 5% of the total memory on the big boxes.” I tend to set my log file size to around 128M. Depending on your system setting the log file size around a few hundred megs should be the right balance between preventing checkpointing and still having fast recovery times.

The manual states, “Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.” The sample my.cnf files that come with MySQL state to set the innodb_log_file_size to 25% of the innodb buffer pool size. I think this mistakenly advises people to set their log files much larger than they need to be causing unnecessarily long recovery times.

Crash recovery time is an important feature of InnoDB. In the event of a server crash large log files could mean waiting hours for InnoDB to recover data while your site is down. A log file size of a few hundred megs will enable InnoDB to perform recovery in a few minutes on reasonable hardware.

Leave a Reply