Over the last few months, on different systems I have been modifying the ib_log_files and their sizes, increasing them due to the increase in load they have been experiencing. These files are used to contain information which has been committed, but not yet written to the ibdata files or the individual .ibd files if using innodb_file_per_table. And these files are used on recovery if the server shuts down unexpectedly during recovery processing to get the system back into a consistent state again.
The checkpointing in MySQL is well documented, but I was surprised just how much difference the throughput can change for heavy write applications if the sizes of these files is changed. It may seem obvious but given how disruptive it can be to shut down a system, change these values and start it up again, this process is not done frequently. These files help improve performance as changes are written linearly into the file, …
[Read more]