10.5.8 优化 InnoDB 磁盘 I/O

如果您遵循了针对 SQL 操作的数据库设计和调优技术的最佳实践，但由于磁盘 I/O 活动频繁，数据库仍然很慢，请考虑这些磁盘 I/O 优化。如果 Unix top 工具或 Windows 任务管理器显示您的工作负载的 CPU 使用率低于 70%， 则您的工作负荷可能是受磁盘约束的。

增加缓冲池大小

当表数据缓存在 InnoDB 缓冲池中时，查询可以重复访问它，而不需要任何磁盘 I/O。 使用 innodb_buffer_pool_size 选项指定缓冲池的大小。此内存区域非常重要，因此通常建议将 innodb_buffer_pool_size 配置为系统内存的 50% 到 75%。 有关更多信息，请参阅第 10.12.3.1 节“ MySQL 如何使用内存”。

调整刷新方式

在 GNU/Linux 和 Unix 的某些版本中，使用 Unix fsync() 调用（ InnoDB 默认使用）和类似方法将文件刷新到磁盘的速度慢得惊人。如果数据库写入性能有问题，请将 innodb_flush_method 参数设置为 O_DSYNC 来执行基准测试。（O_DSYNC 似乎并不常用，它实际上使用的是 O_SYNC 和 fsync() ，并且 InnoDB 不直接使用 O_DSYNC，因为它在许多 Unix 上都存在问题。）

配置操作系统刷新的阈值

默认情况下，当 InnoDB 创建一个新的数据文件，如新的日志文件或表空间文件时，该文件在刷新到磁盘之前会被完全写入操作系统缓存，这可能会导致大量的磁盘写入活动同时发生。要强制从操作系统缓存中定期进行较小的数据刷新，可以使用 innodb_fsync_threshold 变量定义一个阈值（以字节为单位）。当达到字节阈值时，操作系统缓存的内容将刷新到磁盘。默认值 0 强制执行默认行为，即只有在文件完全写入缓存后才将数据刷新到磁盘。

在多个 MySQL 实例使用相同存储设备的情况下，指定阈值以强制较小的定期刷新可能是有益的。例如，创建一个新的 MySQL 实例及其相关数据文件可能会导致磁盘写入活动激增，从而阻碍使用相同存储设备的其他 MySQL 实例的性能。配置阈值有助于避免此类写入活动激增。

使用 fdatasync() 替代 fsync()

在支持 fdatasync() 系统调用的平台上，MySQL 8.0.26 中引入的 innodb_use_fdatasync 变量允许使用 fdatasync() 而不是 fsync() 进行操作系统刷新。除非后续数据检索需要，否则 fdatasync() 系统调用不会刷新对文件元数据的更改，从而提供潜在的性能优势。

innodb_flush_method 设置的子集（如 fsync 、O_DSYNC 和 O_DIRECT） 使用 fsync() 系统调用。innodb_use_fdatasync 变量在使用这些设置时可用。

在 Linux 上将 noop 或 deadline I/O 调度器与原生 AIO 一起使用

InnoDB 使用 Linux 上的异步 I/O 子系统（native AIO） 来执行数据文件页的预读和写入请求。此行为由 innodb_use_native_aio 配置选项控制，该选项在默认情况下处于启用状态。对于原生 AIO，I/O 调度器的类型对 I/O 性能有更大的影响。通常，建议使用 noop 和 deadline I/O 调度器。执行基准测试，以确定哪种 I/O 调度程序能为您的工作负载和环境提供最佳结果。有关更多信息，请参阅第 17.8.6 节“在 Linux 上使用异步 I/O”。

在 x86_64 架构的 Solaris 10 上使用 Direct I/O

在 x86_64 架构（ AMD Opteron）的 Solaris 10 上使用 InnoDB 存储引擎时，请对 InnoDB 相关文件使用 Direct I/O ， 以避免 InnoDB 性能下降。要对用于存储 InnoDB 相关文件的整个 UFS 文件系统使用直接 I/O， 请使用 forcedirectio 选项进行装载；请参见 mount_ufs(1M)。（Solaris 10/x86_64 上的默认设置是不使用此选项。）要仅将直接 I/O 应用于 InnoDB 文件操作而不是整个文件系统，请设置innodb_flush_method = O_DIRECT。 有了这个设置，InnoDB 调用 directio() 而不是 fcntl() 来进行数据文件的 I/O（ 不适用于日志文件的 I/O）。

使用 Solaris 2.6 或更高版本对数据和日志文件使用裸设备

当在任何 Solaris 2.6 及以上版本和任何平台（ sparc/x86/x64/amd64） 上使用拥有很大的 innodb_buffer_pool_size 值的 InnoDB 存储引擎时，请使用 forcedirectio 挂载选项，在裸设备或单独的直接 I/O UFS 文件系统上对 InnoDB 数据文件和日志文件进行基准测试。（如果要对日志文件进行直接 I/O， 则需要使用挂载选项，而不是设置 innodb_flush_method。）Veritas 文件系统 VxFS 的用户应使用 convosync=direct 挂载选项。

不要将其他 MySQL 数据文件（例如 MyISAM 表的数据文件）放置在直接 I/O 文件系统上。可执行文件或库不得放置在直接 I/O 文件系统上。

使用额外存储设备

可以使用额外存储设备来设置 RAID 配置。有关相关信息，请参阅第 10.12.1 节“优化磁盘 I/O”。

或者，InnoDB 表空间数据文件和日志文件可以放置在不同的物理磁盘上。有关更多信息，请参阅以下章节：

• 第17.8.1节，“InnoDB 启动配置”
• 第17.6.1.2节，“创建外部表”
• 创建常规表空间
• 第17.6.1.4节，“移动或复制InnoDB表”

考虑非旋转（non-rotational）存储

非旋转存储（即非机械硬盘 --译者注）通常为随机 I/O 操作提供更好的性能；旋转存储器（即机械硬盘 --译者注）用于顺序 I/O 操作。在旋转和非旋转存储设备之间分布数据和日志文件时，请考虑主要对每个文件执行的 I/O 操作类型。

面向 I/O 的随机文件通常包括单表表空间（file-per-table）和常规表空间数据文件、undo 表空间文件和临时表空间文件。顺序 I/O 文件包括 InnoDB 系统表空间文件（由于 MySQL 8.0.20 之前的双写缓冲和更改缓冲）、 MySQL 8.0.20 中引入的双写文件，以及二进制日志文件和重做日志文件等日志文件。

使用非旋转存储时，请查看以下配置选项的设置：

• innodb_checksum_algorithm

  crc32 选项使用一种更快的校验和算法，推荐用于高速存储系统。

• innodb_flush_neighbors

  优化旋转存储设备的 I/O。对于非旋转存储或旋转和非旋转存储的混合，禁用它。默认情况下，它处于禁用状态。

• innodb_idle_flush_pct

  允许在空闲期间限制页面刷新，这有助于延长非旋转存储设备的寿命。在 MySQL 8.0.18 中引入。

• innodb_io_capacity

  默认设置 200 通常对于低端非旋转存储设备来说就足够了。对于更高端的总线连接设备，请考虑更高的设置，如 1000 。

• innodb_io_capacity_max

  默认值 2000 适用于使用非轮换存储的工作负载。对于高端、总线连接的非旋转存储设备，请考虑更高的设置，如 2500 。

• innodb_log_compressed_pages

  如果重做日志位于非轮换存储上，请考虑禁用此选项以减少日志记录。请参阅禁用压缩页面的日志记录。

• innodb_log_file_size (在 MySQL 8.0.30 中弃用)

  如果重做日志位于非旋转存储上，请配置此选项以最大化缓存和写入合并。

• innodb_redo_log_capacity

  如果重做日志位于非旋转存储上，请配置此选项以最大化缓存和写入合并。

• innodb_page_size

  请考虑使用与磁盘内部扇区大小匹配的页面大小。早期一代 SSD 设备通常具有 4KB 的扇区大小。一些较新的设备具有 16KB 的扇区大小。默认的 InnoDB 页面大小为 16KB 。 保持页面大小接近存储设备块大小可以最大限度地减少重写到磁盘的未更改数据量。

• binlog_row_image

  如果二进制日志在非旋转存储上，并且所有表都有主键，请考虑将此选项设置为最小值以减少日志记录。

确保为您的操作系统启用了 TRIM 支持。它通常在默认情况下启用。

增加 I/O 容量以避免积压（backlog）

如果吞吐量由于 InnoDB 检查点操作而周期性下降，请考虑增加 innodb_io_capacity 配置选项的值。更高的值会导致更频繁的刷新，从而避免可能导致吞吐量下降的积压工作。

如果刷新不落后，降低 I/O 容量

如果系统没有落后于 InnoDB 刷新操作，请考虑降低 innodb_io_capacity 配置选项的值。通常，您会将此选项值保持在尽可能低的水平，但不要低到导致吞吐量周期性下降的程度，如前一条所述。在一个可以降低该选项值的典型场景中，您可能会在 SHOW ENGINE INNODB STATUS 的输出中看到这样的组合：

• 历史列表长度较低，低于几千。
• 插入缓冲合并接近于插入的行数。
• 缓冲池中修改的页面始终远低于缓冲池的 innodb_max_dirty_pages_pct 。（在服务器不进行批量插入时进行测量；在批量插入期间，修改的页面百分比显著上升是正常的。）
• Log sequence number - Last checkpoint 小于 InnoDB 日志文件总大小的 7/8 ，理想情况下小于 6/8 。

将系统表空间文件存储在 Fusion-IO 设备上

通过将包含双写存储区域的文件存储在支持原子写入的 Fusion-IO 设备上，您可以利用与双写缓冲相关的 I/O 优化。（在 MySQL 8.0.20 之前，双写缓冲存储位于系统表空间数据文件中。从 MySQL 8.0.20 开始，存储区域位于双写文件中。请参阅第 17.6.4 节“双写缓冲”。）当双写存储区域文件放置在支持原子写入的 Fusion-IO 设备上时，会自动禁用双写缓冲，Fusion-IO 原子写入被用于所有数据文件。此功能仅在 Fusion-IO 硬件上受支持，并且仅在 Linux 上为 Fusion-IO NVMFS 启用。为了充分利用此功能，建议将 innodb_flush_method 设置为 O_DIRECT 。

注意
由于双写缓冲设置是全局的，因此对于不位于 Fusion-IO 硬件上的数据文件，也会禁用双写缓冲。

禁用压缩页面的日志记录

当使用 InnoDB 表压缩功能时，当对压缩数据进行更改时，重新压缩页面的镜像会写入重做日志。此行为由 innodb_log_compressed_pages 控制，默认情况下启用该选项以防止在恢复过程中使用不同版本的 zlib 压缩算法时可能发生的损坏。如果您确信 zlib 版本不会更改，请禁用 innodb_log_compressed_pages， 以减少修改压缩数据的工作负载的重做日志生成。