Properties:
| Applicable To | InnoDB |
| Server Startup Option | --innodb_log_file_size=<value> |
| Scope | Global |
| Dynamic | Yes |
| Possible Values | Integer: Range: 1M - 4G <1M will be adjusted to 1M |
| Default Value | 5M |
| Category | Performance, Maintenance |
Description:
This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB.
InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? As stated in MySQL manual "The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.", these logs help InnoDB in running more confidently as it knows that even if data is not written to the persistent storage often it can still have it.
Best Value:
A larger value helps you in performance but only up to some point. After a certain value, the performance gain will be minimal or it can be negative. Another issue to be considered is that a value too large will slow down recovery as there will be more and more logs to be scanned. But definitely the default is too small.
My usual recommendation is to set it to 256M or if you feel its big (because maybe you have too many crashes and of course crash recoveries) then 128M. Anything beyond this range should be tested properly and justified.
How to set?
If you just change the size of this variable, MySQL will crib about the changed log file size and start without the InnoDB engine. The safe way of re-setting this value is:
- Stop the MySQL server
- Backup your data and log files
- Delete log files
- Set the new value for innodb_log_file_size in my.cnf
- Start mysql server
