When changes are made to InnoDB tables in MySQL, the changes are first stored in memory in the InnoDB log buffer, then written to the InnoDB log files which are often referred to as the redo logs. The innodb_log_buffer_size variable may be adjusted to optimize performance if there are signs that it is set too low for the environment. The default value of the InnoDB log buffer is version-dependent and is 16MB if version 5.7.6 or greater.
How does one determine whether the InnoDB log buffer size needs adjusting?
If disk I/O is causing performance issues, one area to observe is whether large transactions such as those involving many BLOB entries are the cause. The InnoDB log buffer is flushed to disk whenever it gets full therefore increasing the buffer size can reduce I/O.
Another area to analyze for database optimization is MySQL’s InnoDB redo logs. They are a set of files on disk containing a record of all recent changes made to InnoDB tables and are utilized during crash recovery. Depending on the environment, these files may require adjusting from the default settings, such as the total number and size of the logs. The default number of log files is two, named ib_logfile0 and ib_logfile1. The logs have fixed sizes and the default sizes vary depending on the version of MySQL. As of version 5.7, the default is 48MB each, with a maximum of 512GB total combined size as of MySQL 5.6.3.
How is it determined whether the size of the log files needs adjusting?
If the application is write-intensive, 48MB may be used quickly and given the logs work in a circular fashion, when the logs become full, a write to the data files on disk is necessary. Thus, if the log file size is too small it may cause frequent disk writes or even waits, drastically slowing performance. The frequency of flushing may be observed by viewing the log sequence number status variables log_lsn_current and log_lsn_last_checkpoint. By subtracting the two values, and comparing to the total size of redo log space, you can get an idea of whether flushing is occurring more often than is desired.
To increase the innodb_log_buffer_size or innodb_log_file_size variables, they must be defined explicitly in MySQL’s configuration file. Shut down the instance prior to making these changes ensuring that MySQL stops without error. If errors are present during the shutdown, the existing log files may not have completely written to data files and data may be lost.
Summary: The Innodb_log_buffer_size and Innodb_log_file_size are two variables to analyze when considering MySQL database performance. The InnoDB log buffer can be increased if large transactions are causing excessive disk I/O. The InnoDB log files are often increased from their default values for the same reason; if they become full too quickly, checkpoint flush activity is increased and may be causing slow performance.