Optimizing InnoDB Redo Logging •The  autocommit  setting is turned on  so that the transaction is guaranteed to be a single statement  and the single statement making up the transaction is a “non-locking” SELECT  statement. That is  a SELECT  that does not use a  FOR UPDATE  or  LOCK IN SHARED MODE  clause. •The transaction is started without the  READ ONLY  option  but no updates or statements that explicitly lock rows have been executed yet. Until updates or explicit locks are required  a transaction stays in read-only mode. Thus  for a read-intensive application such as a report generator  you can tune a sequence of  InnoDB queries by grouping them inside  START TRANSACTION READ ONLY  and  COMMIT   or by turning on the autocommit  setting before running the  SELECT  statements  or simply by avoiding any data change statements interspersed with the queries. For information about  START TRANSACTION  and  autocommit   see Section 13.3.1  “START TRANSACTION  COMMIT  and ROLLBACK Statements” . Transactions that qualify as auto-commit  non-locking  and read-only (AC-NL-RO) are kept out of certain internal  InnoDB  data structures and are therefore not listed in  SHOW ENGINE INNODB STATUS  output. Consider the following guidelines for optimizing redo logging: 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 Redo Log Files. Note 8.5.4 Optimizing InnoDB 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. •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  which can be configured dynamically in MySQL 8.0. •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 (2$^{n}$). 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. 1748