增加重做日志文件的大小。当InnoDB已将重做日志文件写满时，它必须在检查点中将缓冲池的修改内容写入磁盘。小重做日志文件会导致许多不必要的磁盘写入。Increase the size of your redo log files. When InnoDB has written 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.

重做日志文件大小由 innodb_redo_log_capacity 决定。 InnoDB 尝试维护 32 个相同大小的重做日志文件，每个文件等于 1/32 * innodb_redo_log_capacity。因此，更改 innodb_redo_log_capacity 设置会更改重做日志文件的大小。The redo log file size is determined by innodb_redo_log_capacity. InnoDB tries to maintain 32 redo log files of the same size, with each file equal to 1/32 * innodb_redo_log_capacity. Therefore, changing the innodb_redo_log_capacity setting changes the size of the redo log files.

有关修改重做日志文件配置的信息，请参阅第 17.6.5 节“重做日志”。For information about modifying your redo log file configuration, see Section 17.6.5, “Redo Log”.

考虑增加日志缓冲区的大小。大型日志缓冲区允许大型事务运行，而无需在事务提交之前将日志写入磁盘。因此，如果您有更新、插入或删除许多行的事务，则增大日志缓冲区可以节省磁盘 I/O。日志缓冲区大小使用 innodb_log_buffer_size 配置选项进行配置，该选项可以动态配置。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 dynamically configured.

配置 innodb_log_write_ahead_size 配置选项以避免“写时读”。该选项定义重做日志的预写块大小。设置 innodb_log_write_ahead_size 以匹配操作系统或文件系统缓存块大小。当重做日志块由于重做日志的预写块大小与操作系统或文件系统缓存块大小不匹配而未完全缓存到操作系统或文件系统时，就会发生写时读。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.

innodb_log_write_ahead_size 的有效值为 InnoDB 日志文件块大小 (2ⁿ) 的倍数。最小值是 InnoDB 日志文件块大小 (512)。当指定最小值时，不会发生预写。最大值等于 innodb_page_size 值。如果您为 innodb_log_write_ahead_size 指定的值大于 innodb_page_size 值，innodb_log_write_ahead_size 设置被截断为 innodb_page_size 值。Valid values for innodb_log_write_ahead_size are multiples of the InnoDB log file block size (2ⁿ). 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.

设置innodb_log_write_ahead_size值过低相对于操作系统或文件系统缓存块大小会导致写时读取。值设置过高可能会对日志文件写入的fsync性能产生轻微影响，因为会一次写入多个块。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.

MySQL 提供专用的日志写入线程，用于将重做日志记录从日志缓冲区写入到系统缓冲区，并将系统缓冲区刷新到重做日志文件。您可以使用 innodb_log_writer_threads 变量启用或禁用日志编写器线程。专用日志写入线程可以提高高并发系统上的性能，但对于低并发系统，禁用专用日志写入线程可以提供更好的性能。MySQL provides dedicated log writer threads for writing redo log records from the log buffer to the system buffers and flushing the system buffers to the redo log files. You can enable or disable log writer threads using the innodb_log_writer_threads variable. Dedicated log writer threads can improve performance on high-concurrency systems, but for low-concurrency systems, disabling dedicated log writer threads provides better performance.

优化等待刷新重做的用户线程对自旋延迟的使用。旋转延迟有助于减少延迟。在低并发期间，减少延迟可能不太重要，而在这些期间避免使用旋转延迟可能会减少能耗。在高并发期间，您可能希望避免在旋转延迟上消耗处理能力，以便将其用于其他工作。以下系统变量允许设置定义旋转延迟使用边界的高水位线值和低水位线值。Optimize the use of spin delay by user threads waiting for flushed redo. Spin delay helps reduce latency. During periods of low concurrency, reducing latency may be less of a priority, and avoiding the use of spin delay during these periods may reduce energy consumption. During periods of high concurrency, you may want to avoid expending processing power on spin delay so that it can be used for other work. The following system variables permit setting high and low watermark values that define boundaries for the use of spin delay.