硬盘的读写通常是对数据库性能最大的因素之一.

这里介绍几个影响硬盘读写的重要参数

● innodb_flush_log_at_trx_commit

● sync_binlog

● innodb_flush_method

● innodb_io_capacity和innodb_io_capacity_max

innodb_flush_log_at_trx_commit

默认值 1 可选值: 0 1 2

控制提交操作的严格ACID合规性和更高的性能之间的平衡, 当重新安排和批量完成与提交相关的I/O操作时, 这两种性能是可能的. 通过更改默认值可以获得更好的性能, 但在崩溃时可能会丢失事务.

设置为默认 1 时: 是完全符合ACID要求所必需的. 在每次事务提交时都会将日志写入并刷新到磁盘. 如果发生系统崩溃, 数据是零丢失, 这种方式对数据是最安全的, 但性能是最慢的, 因为把数据从缓存同步到磁盘的成本很高. 这种方式适用于对数据安全性要求高的行业, 如银行业. 但很多互联网的应用, 对数据的安全性要求不太高, 而对性能的要求很高, 设置成 0 或 2 会更合适

设置为 0 时: 日志每秒写入并刷新到磁盘一次, 同时还会进行文件系统到磁盘的同步操作. 事务提交的时候不会触发写日志文件的操作, 尚未刷新日志的事务可能会在崩溃中丢失.

设置成 2 时, 日志在每次事务提交后写入, 并每秒刷新一次到磁盘. 尚未刷新日志的事务可能会在崩溃中丢失. 0 和 2 都是每秒进行一次文件系统到磁盘的同步, 因此这两种方式的性能都差不多, 当系统崩溃时, 最多丢失1秒的数据.

但 0 和 2 还有细微的不同, 当设置成 2 时, 每次事务提交都写日志文件, 因此数据已经从MySQL的日志缓存刷新到了操作系统的文件缓存, 如果只是 MySQL 崩溃, 而操作系统没有崩溃, 将不会丢失数据. 因此 0 和 2 比较起来, 通常设置为 2 比较好

对于设置 0 和 2, 不能100%保证每秒刷新一次. 由于DDL更改和其他内部InnoDB活动导致日志被独立于 InnoDB_flush_log_at_trx_commit 设置进行刷新, 刷新频率可能会更高, 有时由于调度问题而降低. 如果日志每秒刷新一次, 那么在崩溃中可能会丢失多达一秒钟的事务. 如果刷新日志的频率高于或低于每秒一次, 则可能丢失的事务量也会相应变化.

日志刷新频率由 innodb_flush_Log_at_timeout 控制, 它允许您将日志刷新频率设置为 N 秒（其中N为1…2700, 默认值为1）. 然而, 任何意外的mysqld进程退出都可能擦除长达N秒的事务.

DDL更改和其他内部InnoDB活动独立于 InnoDB_flush_log_at_trx_commit 设置刷新日志.

InnoDB 崩溃恢复工作与 InnoDB_flush_log_at_trx_commit 设置无关. 事务要么被完全应用, 要么被完全擦除

为了在将InnoDB与事务一起使用的复制设置中实现持久性和一致性：如果启用了二进制日志记录, 则将sync_binlog设置为1. 始终将innodb_flush_log_at_trx_commit设置为1.

许多操作系统和一些磁盘硬件欺骗了磁盘刷新操作. 他们可能会告诉mysqld已经进行了刷新, 尽管还没有. 在这种情况下, 即使使用推荐的设置, 也无法保证事务的持久性, 在最坏的情况下, 停电可能会损坏InnoDB数据. 在SCSI磁盘控制器或磁盘本身中使用电池备份磁盘缓存可以加快文件刷新速度, 并使操作更安全. 您也可以尝试禁用硬件缓存中磁盘写入的缓存.

sync_binlog

默认值为 1 最小 0 最大 4294967295

控制MySQL服务器将二进制日志同步到磁盘的频率.

sync_binlog=0：禁用MySQL服务器将二进制日志同步到磁盘. 相反, MySQL服务器依赖于操作系统不时地将二进制日志刷新到磁盘, 就像它对任何其他文件所做的那样. 此设置可提供最佳性能, 但在电源故障或操作系统崩溃的情况下, 服务器可能已提交未同步到二进制日志的事务.

sync_binlog=1：启用在提交事务之前将二进制日志同步到磁盘. 这是最安全的设置, 但由于磁盘写入次数的增加, 可能会对性能产生负面影响. 在电源故障或操作系统崩溃的情况下, 二进制日志中缺少的事务仅处于准备状态. 这允许自动恢复例程回滚事务, 从而保证二进制日志中不会丢失任何事务.

sync_binlog=N, 其中N是0或1以外的值：在收集了N个二进制日志提交组后, 将二进制日志同步到磁盘. 在电源故障或操作系统崩溃的情况下, 服务器可能已提交未刷新到二进制日志的事务. 由于磁盘写入次数的增加, 此设置可能会对性能产生负面影响. 较高的值可以提高性能, 但同时会增加数据丢失的风险.

为了在使用InnoDB和事务的复制设置中获得最大可能的持久性和一致性, 请使用以下设置：sync_binlog＝1. innodb_flush_log_at_trx_commit=1.

许多操作系统和一些磁盘硬件欺骗了磁盘刷新操作. 他们可能会告诉mysqld已经进行了刷新, 尽管还没有. 在这种情况下, 即使使用推荐的设置, 也无法保证事务的持久性, 在最坏的情况下, 停电可能会损坏InnoDB数据. 在SCSI磁盘控制器或磁盘本身中使用电池备份磁盘缓存可以加快文件刷新速度, 并使操作更安全. 您也可以尝试禁用硬件缓存中磁盘写入的缓存.

这两个参数都设置成 1 对性能的负面影响很大, 为了提高性能, 这时使用的存储应该是带缓存的, 并且设置成 Write-back(指的是只写入存储的缓存), 而不是 Write-through(指同时写入存储缓存Cache和后端硬盘), 这样数据只写入到存储的缓存中即返回. 但存储的缓存应该是带电池的, 如果缓存不带电池, 或者电池没有电, 突然发生掉电的时候, 不仅数据会丢失, 而且会造成数据库损坏, 无法启动, 这种情况要比丢失一秒钟的数据要糟糕得多

写二进制日志的成本比写重做日志的成本要高得多, 因为重做日志的大小和文件名是固定的, 重做日志循环写入日志文件. 而每次写二进制日志时, 文件都会进行扩展, 如果写满了还要新建文件, 这样每次写二进制日志不但要写数据, 还要修改二进制日志文件的元数据, 因此把 sync_binlog 设置成 1 比把 innodb_flush_log_at_trx_commit 设置成 1 对性能负面影响还要大得多

innodb_flush_method

定义用于将数据刷新到InnoDB数据文件和日志文件的方法, 这可能会影响I/O吞吐量. 在MySQL 8.0中, innodb_flush_method选项可以用数字指定.

在类Unix系统上, 默认值是fsync, 可选值有:

fsync或0: InnoDB使用 fsync() 系统调用来刷新数据和日志文件. fsync是默认设置.

O_DSYNC或1: InnoDB使用 O_SYNC 打开和刷新日志文件, 使用 fsync() 刷新数据文件. InnoDB不直接使用O_DSYNC, 因为它在许多Unix上都存在问题.

littlesync或2：此选项用于内部性能测试, 目前不受支持. 使用时风险自负.

nosync或3：此选项用于内部性能测试, 目前不受支持. 使用时风险自负.

O_DIRECT或4: InnoDB 使用O_DIRECT(或Solaris上的directio() ) 打开数据文件, 并使用 fsync() 刷新数据和日志文件. 此选项在某些GNU/Linux版本、FreeBSD和Solaris上可用.

O_DIRECT_NO_FSYNC: InnoDB在刷新I/O期间使用 O_DIRECT, 但在每次写入操作后跳过 fsync() 系统调用.

在MySQL 8.0.14之前, 此设置不适用于XFS和EXT4等文件系统, 因为它们需要 fsync() 系统调用来同步文件系统元数据更改. 如果您不确定您的文件系统是否需要 fsync() 系统调用来同步文件系统元数据更改, 请改用O_DIRECT.

从MySQL 8.0.14开始, 在创建新文件、增加文件大小和关闭文件后调用 fsync() , 以确保文件系统元数据更改同步. 在每次写入操作之后, 仍会跳过 fsync() 系统调用.

如果重做日志文件和数据文件位于不同的存储设备上, 并且在从没有电池备份的设备缓存中刷新数据文件写入之前发生意外退出, 则可能会丢失数据. 如果您使用或打算使用不同的存储设备来存储重做日志文件和数据文件, 并且您的数据文件位于缓存不带电池的设备上, 请改用O_DIRECT.

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

在Windows上, 默认值是unbuffered. 可选值有: unbuffered normal

unbuffered or 0: InnoDB使用模拟异步I/O和无缓冲I/O.

normal or 1: InnoDB使用模拟异步I/O和缓冲I/O.

每个设置如何影响性能取决于硬件配置和工作负载. 对您的特定配置进行基准测试, 以决定使用哪个设置, 或者是否保留默认设置. 检查 Innodb_data_fsyncs 状态变量, 查看每个设置的 fsync() 调用总数（innodb_use_fdatasync, 则为 fdatasync() 调用）. 工作负载中读写操作的混合会影响设置的执行方式.

例如, 在具有硬件RAID控制器和电池备份写缓存的系统上, O_DIRECT 可以帮助避免 InnoDB 缓冲池和操作系统文件系统缓存之间的双重缓冲. 在一些 InnoDB 数据和日志文件位于 SAN上 的系统上, 默认值或 O_DSYNC 对于主要使用 SELECT 语句的读取繁重的工作负载可能会更快. 始终使用生产环境的硬件和工作负载来测试此参数.

innodb_io_capacity

定义了每秒可用于innodb后台任务的I/O操作数（IOPS）, 例如刷新缓冲池中的页面和合并更改缓冲区中的数据

InnoDB主线程和其他线程在后台执行各种任务, 其中大多数与I/O相关, 例如从缓冲池中清除脏页, 以及将更改从更改缓冲区写入适当的二级索引. InnoDB试图以一种不会对服务器的正常工作产生不利影响的方式来执行这些任务. 它试图估计可用的I/O带宽, 并调整其活动以利用可用容量.

innob_io_capacity变量定义了innodb可用的总体I/O容量. 它应该设置为大约系统每秒可以执行的I/O操作数（IOPS）. 当设置innodb_io_cability时, innodb会根据设置的值估计后台任务可用的I/O带宽.

您可以将innob_io_capacity设置为100或更大的值. 默认值为200. 通常, 100左右的值适用于消费者级存储设备, 例如高达7200 RPM的硬盘驱动器. 更快的硬盘驱动器、RAID配置和固态驱动器（SSD）受益于更高的价值.

理想情况下, 尽可能地保持低设置, 但不要低到后台活动落后. 如果该值过高, 则会从缓冲池中删除数据, 并过快地更改缓冲区以进行缓存, 从而带来显著的好处. 对于能够实现更高I/O速率的繁忙系统, 可以设置更高的值来帮助服务器处理与高行更改速率相关的后台维护工作. 通常, 您可以将该值作为InnoDB I/O所用驱动器数量的函数来增加. 例如, 您可以增加使用多个磁盘或SSD的系统的值.

对于低端SSD来说, 默认设置200通常就足够了. 对于更高端的总线连接SSD, 请考虑更高的设置, 例如1000. 对于具有单个5400 RPM或7200 RPM驱动器的系统, 您可以将该值降低到100, 这表示可执行约100 IOPS的旧一代磁盘驱动器每秒I/O操作（IOPS）的估计比例.

尽管您可以指定一个高值, 例如一百万, 但在实践中, 这样大的值几乎没有好处. 通常, 不建议使用高于20000的值, 除非您确信较低的值不足以满足您的工作负载.

在调优innodb_io_cability时, 请考虑写入工作负载. 具有较大写入工作负载的系统可能会从更高的设置中受益. 对于具有较小写入工作负载的系统来说, 较低的设置可能就足够了.

innob_io_capacity设置不是每个缓冲池实例的设置. 可用的I/O容量在用于刷新活动的缓冲池实例之间平均分配.

innodb_flush_sync

innodb_flush_sync变量在默认情况下启用, 它会导致在检查点发生I/O活动突发时忽略 innodb_io_cability 设置. 要遵守 innodb_io_cability 设置定义的I/O速率, 请禁用innodb_flush_sync.

innodb_io_capacity_max

如果刷新活动落后, InnoDB可以更积极地进行刷新, 每秒I/O操作数（IOPS）高于InnoDB_io_cability变量定义的速率. innodb_io_capacity_max变量定义了innodb后台任务在这种情况下执行的IOPS的最大数量.

如果您在启动时指定了innodb_io_cability设置, 但没有指定innodb_o_capacity_max的值, 则innodb_io _cability_max默认为innodb_i_capacity值的两倍或2000, 以较大值为准.

当配置innodb_io_cability_max时, 两倍于innodb_io的容量通常是一个很好的起点. 默认值2000适用于使用SSD或多个常规磁盘驱动器的工作负载. 对于不使用SSD或多个磁盘驱动器的工作负载来说, 2000的设置可能太高, 并且可能允许过多的刷新. 对于单个常规磁盘驱动器, 建议设置在200到400之间. 对于高端、总线连接的SSD, 请考虑更高的设置, 如2500. 与innob_io_capacity设置一样, 尽可能地将设置保持在较低的水平, 但不要太低, 以至于innodb无法将IOPS的速率充分扩展到innodb_io_capartment设置之外.

在调优innodb_io_capacity_max时, 请考虑写入工作负载. 具有较大写入工作负载的系统可能会受益于更高的设置. 对于具有较小写入工作负载的系统来说, 较低的设置可能就足够了.

innodb_io_cability_max不能设置为低于innodb_io容量值的值.

使用SET语句将innob_io_capacity_max设置为DEFAULT（SET GLOBAL innob_io_capacity_max=DEFAULT）将innob_ido_capacity.max设置为最大值.

innodb_io_capacity_max限制适用于所有缓冲池实例. 它不是按缓冲池实例设置的.