MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Consider the following guidelines for optimizing redo logging:
Make your redo log files big, even as big as the
buffer pool. When
InnoDB
has written the redo log files
full, it must write the modified contents of the buffer pool
to disk in a
checkpoint. 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
and
innodb_log_files_in_group
configuration options. For information about modifying an
existing redo log file configuration, see
Changing the Number or Size of InnoDB Redo Log Files.
Consider increasing the size of the
log buffer. A large
log buffer enables large
transactions 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 the
innodb_log_buffer_size
configuration option.
Configure the
innodb_log_write_ahead_size
configuration option to avoid “read-on-write”.
This option defines the write-ahead block size for the redo
log. Set
innodb_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 for
innodb_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 the
innodb_page_size
value. If
you specify a value for
innodb_log_write_ahead_size
that is larger than the
innodb_page_size
value, the
innodb_log_write_ahead_size
setting is truncated to the
innodb_page_size
value.
Setting the
innodb_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 on
fsync
performance for log file writes due
to several blocks being written at once.