[ACDU 翻译] MySQL 8.5.8优化InnoDB磁盘I / O

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

• 增加缓冲池大小

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

• 调整冲洗方法

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

• 配置fsync阈值

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

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

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

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

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

  InnoDB在Solaris 10的x86_64体系结构（AMD Opteron）上 使用存储引擎时，请对InnoDB相关文件使用直接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使用具有较大innodb_buffer_pool_size价值 的存储引擎时，请 InnoDB对原始设备或单独的直接I / O UFS上的数据文件和日志文件进行基准测试 文件系统，使用forcedirectio如前所述的安装选项。（innodb_flush_method如果要对日志文件进行直接I / O ，则必须使用安装选项而不是设置 。）Veritas文件系统VxFS的用户应使用 convosync=direct安装选项。

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

• 使用其他存储设备

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

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

  • 第15.8.1节“ InnoDB启动配置”
  • 第15.6.1.2节“在外部创建表”
  • 创建通用表空间
  • 第15.6.1.4节“移动或复制InnoDB表”

• 考虑非旋转存储

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

  面向随机I / O的文件通常包括 每表文件 和常规表空间数据文件， 撤消表空间 文件以及 临时表空间文件。面向I / O的顺序文件包括InnoDB 系统表空间文件（由于 MySQL 8.0.20之前的doublewrite缓冲和 更改缓冲），MySQL 8.0.20中引入的doublewrite文件以及诸如二进制日志 文件和重做日志 文件之类的日志文件。

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

  • 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

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

  • innodb_page_size

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

  • binlog_row_image

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

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

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

  如果由于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设备上

  通过将包含doublewrite存储区的文件存储在支持原子写入的Fusion-io设备上，可以利用与doublewrite缓冲区相关的I / O优化。（在MySQL 8.0.20之前，doublewrite缓冲区存储位于系统表空间数据文件中。从MySQL 8.0.20开始，该存储区位于doublewrite文件中。请参见 第15.6.4节“ Doublewrite Buffer”。）将doublewrite存储区域文件放在支持原子写入的Fusion-io设备上时，doublewrite缓冲区将自动禁用，并且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 以减少修改压缩数据的工作负载的重做日志生成。