• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 优化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%。有关更多信息,请参见“ MySQL如何使用内存”。

    • 调整冲洗方法

      在某些版本的GNU / Linux和Unix中,使用Unix fsync()调用(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调度程序为您的工作负载和环境提供最佳结果。有关更多信息,请参见“在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 ,则必须使用mount选项,而不是进行设置。)Veritas文件系统VxFS的用户应使用convosync=directmount选项。

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

    • 使用其他存储设备

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

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

      • “ InnoDB启动配置”
      • “在外部创建表”
      • 创建通用表空间
      • “移动或复制InnoDB表”
    • 考虑非旋转存储

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

      面向随机I / O的文件通常包括每表文件和常规表空间数据文件,撤消表空间文件以及临时表空间文件。面向I / O的顺序文件包括InnoDB系统表空间文件(由于 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设备上

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

      注意

      由于doublewrite缓冲区设置是全局的,因此,对于非Fusion-io硬件上驻留的数据文件,也禁用doublewrite缓冲。

    • 禁用压缩页面的日志记录

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