8.5.4 Optimizing InnoDB Redo Logging (original) (raw)

8.5.4 Optimizing InnoDB Redo Logging

Consider the following guidelines for optimizing redo logging:

• Make your redo log files big, even as big as thebuffer pool. WhenInnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in acheckpoint. Small redo log files cause many unnecessary disk writes. Although historically big redo log files caused lengthy recovery times, recovery is now much faster and you can confidently use large redo log files.
  The size and number of redo log files are configured using the innodb_log_file_size andinnodb_log_files_in_group configuration options. For information about modifying an existing redo log file configuration, seeChanging the Number or Size of InnoDB Redo Log Files.
• Consider increasing the size of thelog buffer. A large log buffer enables largetransactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. Log buffer size is configured using theinnodb_log_buffer_size configuration option.
• Configure theinnodb_log_write_ahead_size configuration option to avoid “read-on-write”. This option defines the write-ahead block size for the redo log. Setinnodb_log_write_ahead_size to match the operating system or file system cache block size. Read-on-write occurs when redo log blocks are not entirely cached to the operating system or file system due to a mismatch between write-ahead block size for the redo log and operating system or file system cache block size.
  Valid values forinnodb_log_write_ahead_size are multiples of the InnoDB log file block size (2n). The minimum value is the InnoDB log file block size (512). Write-ahead does not occur when the minimum value is specified. The maximum value is equal to theinnodb_page_size value. If you specify a value forinnodb_log_write_ahead_size that is larger than theinnodb_page_size value, theinnodb_log_write_ahead_size setting is truncated to theinnodb_page_size value.
  Setting theinnodb_log_write_ahead_size value too low in relation to the operating system or file system cache block size results in read-on-write. Setting the value too high may have a slight impact onfsync performance for log file writes due to several blocks being written at once.