MySQL 磁盘IO优化

[sudotty]

磁盘寻址是一个巨大的性能瓶颈。当数据量开始增长到无法有效缓存时，这个问题就会变得更加明显。对于大型的数据库，你或多或少会随机访问数据，你可以肯定的是，你至少需要一次磁盘寻址来读取数据，同时需要几次磁盘寻道来写入数据。为了尽量减少这个问题，请使用低寻址时间的磁盘。

[sudotty]

通过将文件串联到不同的磁盘或将磁盘剥离，增加可用的磁盘主轴的数量（从而减少寻求开销）。

1. 使用 symbolic links 意思是，对于MyISAM表，你将索引文件和数据文件从数据目录中的惯常位置symlink到另一个磁盘（也可以是带状的）。这使得寻求和读取时间都变得更好，假设磁盘没有被用于其他目的。参见("使用符号链接")[https://dev.mysql.com/doc/refman/8.0/en/symbolic-links.html]。 符号链接不支持与InnoDB表一起使用。但是，可以将InnoDB数据和日志文件放在不同的物理磁盘上。更多信息，("优化InnoDB磁盘I/O")[https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html]。
2. 分块,分块的意思是你有很多个磁盘，把第一个块放在第一个磁盘上，把第二个块放在第二个磁盘上，把第N个块放在(N MOD数disks)磁盘上，以此类推。这意味着，如果你的正常数据大小小于分块大小（或者说是完全对齐），你的性能就会好很多。分块与操作系统和分块大小有很大的关系，所以用不同的分块大小对你的应用程序进行基准测试。 分块的速度差异很大程度上取决于参数。取决于你如何设置分块参数和磁盘数量，你可能会得到以数量级来衡量的差异。你必须选择随机访问或顺序访问进行优化。

[sudotty]

为了保证可靠性，你可能想使用RAID 0+1(分块加镜像)，但在这种情况下，你需要2×N个驱动器来容纳N个数据驱动器。如果你有足够的资金，这可能是最好的选择。不过，您可能还需要投资一些卷管理软件来有效地处理。

[sudotty]

一个很好的选择是根据数据类型的重要程度来改变RAID级别。例如，将半重要的数据存储在RAID 0磁盘上，这些数据可以再生，但将真正重要的数据如主机信息和日志等存储在RAID 0+1或RAID N磁盘上。如果你有很多写，RAID N可能是个问题，因为更新奇偶校验位需要时间。

[sudotty]

还可以设置数据库使用的文件系统的参数。 如果你不需要知道文件最后一次被访问的时间（这在数据库服务器上并没有什么用处），你可以用-o noatime选项挂载你的文件系统。这样可以跳过更新到文件系统中的inodes中的最后一次访问时间，这样就可以避免一些磁盘寻道。 在许多操作系统上，你可以通过使用 -o async 选项来设置文件系统的异步更新。如果你的计算机相当稳定，这应该会给你带来更好的性能而不会牺牲太多可靠性。(这个标志在Linux上默认是打开的。)

[sudotty]

如果你遵循了数据库设计的最佳实践和SQL操作的调优技术，但你的数据库仍然因为沉重的磁盘I/O活动而变得缓慢，请考虑这些磁盘I/O优化。如果Unix顶层工具或Windows任务管理器显示的CPU使用率与你的工作负载的比例低于70%，那么你的工作负载很可能是磁盘绑定的。

[sudotty]

增加缓冲池规模 当表数据被缓存在InnoDB缓冲池中时，可以被查询重复访问，而不需要任何磁盘I/O。使用 innodb_buffer_pool_size 选项指定缓冲池的大小。这个内存区域足够重要，通常建议将 innodb_buffer_pool_size 配置为系统内存的 50% 到 75%。

[sudotty]

调整flush方法 在 GNU/Linux 和 Unix 的某些版本中，使用 Unix 的 fsync() 调用 (InnoDB 默认使用的) 和类似的方法将文件刷新到磁盘的速度非常慢。如果数据库的写入性能是个问题，请将 innodb_flush_method 参数设置为 O_DSYNC 进行基准测试。

[sudotty]

配置一个fsync阈值 默认情况下，当InnoDB创建一个新的数据文件时，比如新的日志文件或表空间文件，该文件在被刷新到磁盘之前，会被完全写入操作系统缓存，这可能会导致大量的磁盘写入活动一次性发生。要从操作系统缓存中强制进行较小的、周期性的数据刷新，可以使用 innodb_fsync_threshold 变量来定义一个阈值，单位为字节。当达到字节阈值时，操作系统缓存中的内容将被刷新到磁盘。默认值为0，则强制执行默认行为，即只有在文件完全写入缓存后才会将数据刷新到磁盘。 在多个MySQL实例使用相同存储设备的情况下，指定一个阈值来强制进行较小的定期flush可能是有益的。例如，创建一个新的MySQL实例及其相关联的数据文件可能会导致磁盘写入活动的大量激增，阻碍使用相同存储设备的其他MySQL实例的性能。配置一个阈值有助于避免这种写活动的激增。

[sudotty]

在Linux上使用具有原生AIO的noop或deadline I/O调度器。 InnoDB使用Linux上的异步I/O子系统(native AIO)来执行数据文件页的预读和写入请求。该行为由 innodb_use_native_aio 配置选项控制，默认情况下是启用的。使用native AIO，I/O调度器的类型对I/O性能的影响更大。一般来说，推荐使用noop和 deadline I/O调度器。进行基准测试，以确定哪种I/O调度器为您的工作负载和环境提供了最佳结果。

[sudotty]

使用额外的存储设备 可以使用其他存储设备来设置RAID配置。 另外，InnoDB表空间数据文件和日志文件可以放在不同的物理磁盘上。

[sudotty]

考虑非旋转存储

非旋转存储通常为随机I/O操作提供更好的性能；而旋转存储则为顺序I/O操作提供更好的性能。当在旋转和非旋转存储设备之间分配数据和日志文件时，要考虑主要在每个文件上执行的I/O操作类型。

面向随机I/O的文件通常包括按表文件和一般表空间数据文件、撤销表空间文件和临时表空间文件。面向顺序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 如果重做日志是在非旋转存储上，请配置此选项以最大限度地提高缓存和写入合并。
• innodb_page_size 考虑使用与磁盘内部扇区大小相匹配的页面大小。早期的SSD设备通常有4KB的扇区大小。一些较新的设备的扇区大小为16KB。InnoDB的默认页面大小是16KB。使页面大小接近存储设备的块大小，可以最大限度地减少向磁盘重写的不变数据量
• binlog_row_image 如果二进制日志在非旋转存储上，且所有表都有主键，可以考虑将此选项设置为最小，以减少日志记录。

确保您的操作系统已启用TRIM支持。它通常是默认启用的。

[sudotty]

增加I/O容量，避免积压 如果由于InnoDB检查点操作导致吞吐量周期性下降，可以考虑增加innodb_io_capacity配置选项的值。更高的值会导致更频繁的flush，避免了可能导致吞吐量下降的积压工作。

[sudotty]

如果flush不落后，则I/O容量较低 如果系统的InnoDB flush操作没有落后，可以考虑降低innodb_io_capacity配置选项的值。通常情况下，你可以把这个选项的值保持在尽可能低的范围内，但不要太低，以至于导致吞吐量的周期性下降，就像前面的项目中提到的那样。在一个典型的情况下，您可以降低选项值，在 SHOW ENGINE INNODB STATUS 的输出中，您可能会看到这样的组合。