9.MySQL优化Optimizing InnoDB Disk IO

介绍

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

• 增大buffer pool size

  当表的数据被缓冲到InnoDB的buffer pool中时，可以在查询中重复访问这些数据而不用进行磁盘I/O，使用innodb_buffer_pool_size 选项指定缓冲池的大小 。此内存区域非常重要，通常建议将 innodb_buffer_pool_size其配置为系统内存的50％到75％。

• 调整刷新方式

  在某些版本的GNU/Linux和Unix中，使用Unix fsync刷新磁盘数据和类型方法的效率都较低。假如数据库写入性能非常低，可以考虑 innodb_flush_method设置为ON进行基准测试O_DSYNC。

• 配置写缓冲区的阈值大小

  默认情况下，在InnoDB创建新数据文件（如新日志文件或表空间文件）时，只有在完全写入文件后才会将写入缓冲区的内容刷新到磁盘，这可能导致瞬间发生大量磁盘写入活动。强制进行较小的定期刷新，请使用 innodb_fsync_threshold （在MySQL 8.0.13中引入）为写缓冲区定义阈值大小（以字节为单位）。达到阈值大小时，写缓冲区的内容将刷新到磁盘。默认值0强制使用默认行为。

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

• 在LINUX上使用noopp或deadlineI /O调度程序与native AIO

  LINUX使用异步I/O(native AIO)来执行数据文件页的 read-ahead 和 write 请求。这个行为被innodb_use_native_aio参数控制，默认是开启的。对于native AIO,I/O调度程序的类型对I/O性能的影响更大，通常，建议使用noop和deadline I/O调度程序。执行基准测试以确定哪个I/O调度程序为您的工作负载和环境提供最佳结果。