8.5.8 优化 InnoDB 磁盘 I/O

如果您遵循数据库设计的最佳实践和 SQL 操作的调整技术，但是由于磁盘 I/O 活动繁重，数据库仍然很慢，请考虑这些磁盘 I/O 优化。如果 Unix top工具或 Windows 任务 Management 器显示您的工作负载中的 CPU 使用百分比小于 70％，则您的工作负载可能是磁盘绑定的。

• 增加缓冲池大小

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

• 调整冲洗方法

在某些版本的 GNU/Linux 和 Unix 中，使用 Unix fsync()调用(默认情况下InnoDB使用)和类似方法将文件刷新到磁盘上的过程令人惊讶地缓慢。如果数据库写性能是一个问题，请使用innodb_flush_method参数设置为O_DSYNC进行基准测试。

• 在 Linux 上将 noop 或截止日期 I/O 调度程序与本机 AIO 一起使用

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

• 在 Solaris 10 上对 x86_64 体系结构使用直接 I/O

在 Solaris 10 上的 x86_64 体系结构(AMD Opteron)上使用InnoDB存储引擎时，请对与InnoDB相关的文件使用直接 I/O，以避免InnoDB性能下降。要对用于存储与InnoDB相关的文件的整个 UFS 文件系统使用直接 I/O，请使用forcedirectio选项安装它；参见mount_ufs(1M)。 (在 Solaris 10/x86_64 上，默认值为* not *来使用此选项.)要将直接 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存储引擎时，请对原始设备或单独的直接 I 上的InnoDB数据文件和日志文件进行基准测试/ O UFS 文件系统，使用forcedirectio挂载选项，如前所述。 (如果要为日志文件直接 I/O，则必须使用 mount 选项，而不是设置innodb_flush_method。)Veritas 文件系统 VxFS 的用户应使用convosync=direct mount 选项。

请勿将其他 MySQL 数据文件(例如MyISAMtable 的数据文件)放置在直接 I/O 文件系统上。可执行文件或库不得放置在直接 I/O 文件系统上。

• 使用其他存储设备

其他存储设备可用于设置 RAID 配置。有关相关信息，请参见第 8.12.2 节“优化磁盘 I/O”。

或者，可以将InnoDBtable 空间数据文件和日志文件放置在不同的物理磁盘上。有关更多信息，请参考以下部分：

• 第 14.8.1 节“ InnoDB 启动配置”

  • 第 14.6.1.2 节“在外部创建 table”

  • 创建通用 table 空间

  • 第 14.6.1.4 节“移动或复制 InnoDBtable”

• 考虑非旋转存储

非旋转存储通常为随机 I/O 操作提供更好的性能。以及用于 SequencesI/O 操作的旋转存储。在旋转和非旋转存储设备上分发数据和日志文件时，请考虑主要在每个文件上执行的 I/O 操作的类型。

面向 I/O 的随机文件通常包括file-per-table和general tablespace数据文件，undo tablespace文件和temporary tablespace文件。面向 I/O 的 Sequences 文件包括InnoDB system tablespace文件(由于doublewrite buffering和change buffering)和日志文件(例如binary log文件和redo log文件)。

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

• innodb_checksum_algorithm

crc32选项使用更快的校验和算法，建议用于快速存储系统。

• innodb_flush_neighbors

优化旋转存储设备的 I/O。禁止将其用于非旋转存储或旋转与非旋转存储的混合。

• innodb_io_capacity

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

• innodb_io_capacity_max

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

• innodb_log_compressed_pages

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

• innodb_log_file_size

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

• innodb_page_size

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

• binlog_row_image

如果二进制日志位于非循环存储上，并且所有 table 都具有主键，请考虑将此选项设置为minimal以减少日志记录。

确保为您的 os 启用了 TRIM 支持。通常默认情况下启用它。

• 增加 I/O 容量以避免积压

如果由于InnoDB checkpoint操作而导致吞吐量周期性下降，请考虑增加innodb_io_capacity配置选项的值。较高的值会导致flushing的出现频率更高，从而避免了积压的工作量(可能会导致吞吐量下降)。

• 如果不落后冲洗，则 I/O 容量会降低

如果系统没有通过InnoDB flushing操作落后，请考虑降低innodb_io_capacity配置选项的值。通常，您将此选项值保持尽可能低，但又不要太低，以至于导致周期性的吞吐量下降，如前面的项目符号所述。在可能降低选项值的典型情况下，您可能会在显示引擎的 INNODB 状态的输出中看到这样的组合：

• 历史记录列 table 长度很低，低于几千。

  • 插入缓冲区合并到插入的行附近。

  • 缓冲池中的已修改页面始终低于缓冲池的innodb_max_dirty_pages_pct。 (在服务器不执行批量插入时测量；在批量插入期间，正常情况是修改后的页面百分比显着上升.)

  • Log sequence number - Last checkpoint小于InnoDB log files的总大小的 7/8 或理想的是小于 6/8.

• 将系统 table 空间文件存储在 Fusion-io 设备上

通过在支持原子写入的 Fusion-io 设备上存储系统 table 空间文件(“ ibdata 文件”)，可以利用与 Doublewrite 缓冲区相关的 I/O 优化。在这种情况下，自动禁用双写缓冲(innodb_doublewrite)，并且 Fusion-io 原子写入将用于所有数据文件。此功能仅在 Fusion-io 硬件上受支持，并且仅在 Linux 上的 Fusion-io NVMFS 中启用。要充分利用此功能，建议将innodb_flush_method设置为O_DIRECT。

• 禁用记录压缩页面

使用InnoDBtablecompression功能时，对压缩数据进行更改时，将重新压缩的pages的图像写入redo log。此行为由innodb_log_compressed_pages控制，默认情况下已启用innodb_log_compressed_pages，以防止在恢复过程中使用不同版本的zlib压缩算法时可能发生的损坏。如果确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少修改压缩数据的工作负载的重做日志生成。