优化InnoDB磁盘I/O

如果你遵循数据库设计和调优技术的最佳做法，但由于磁盘I/O活动较大，你的数据库仍然很慢，请考虑这些磁盘I/O优化。如果top工具显示你的CPU使用率百分比低于70％，则你的工作负载可能是磁盘导致的。几种优化方式如下：

增加缓冲池大小

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

调整数据刷新方法

在某些版本的GNU/Linux和Unix中，使用Unix fsync()调用（ InnoDB默认情况下使用）将文件刷新到磁盘是非常缓慢的。如果数据库写入性能是一个问题，请使用innodb_flush_method参数设置为基准O_DSYNC。

这个参数控制着InnoDB数据文件及redo log的打开、刷写模式，对于这个参数，文档上是这样描述的：有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush，并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。

O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步，日志还是要经过OS缓冲。

注：在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的Buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的；O_SYNC方式表示以同步io的方式打开文件，任何写操作都将阻塞到数据写入物理磁盘后才返回。fsync(int filedes)函数只对由文件描述符filedes指定的单一文件起作用，并且等待写磁盘操作结束，然后返回。fdatasync(int filedes)函数类似于fsync，但它只影响文件的数据部分。而除数据外，fsync还会同步更新文件的元信息到磁盘。

在Linux上使用本机AIO的noop或者deadline I/O调度

InnoDB使用Linux上的异步I/O子系统（本机AIO）对数据文件页执行预读和写请求。此行为由innodb_use_native_aio配置选项控制，配置选项默认启用。使用本机AIO，I/O调度器的类型对I/O性能有更大的影响。通常，建议使用noop或deadline I/O调度进行基准测试，以确定哪些I/O调度程序为你的工作负载和环境提供最佳结果。

考虑非旋转存储

非旋转存储通常为随机I/O操作提供更好的性能，以及用于顺序I/O操作的旋转存储。在旋转和非旋转存储设备上分发数据和日志文件时，请主要考虑对每个文件执行的I/O操作的类型。面向随机的I/O的文件通常包括每表文件和一般表空间数据文件，撤销表空间文件和临时表空间文件。面向顺序的I/O的文件包括InnoDB系统表空间文件（双重缓冲和插入缓冲）以及日志文件，如二进制日志文件和重做日志文件。

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

innodb_checksum_algorithm

该crc32选项使用更快的校验和算法，建议用于快速存储系统。

innodb_flush_neighbors

该选项可优化旋转存储设备的I/O，禁用它用于非旋转存储或旋转和非旋转存储的混合。

innodb_io_capacity

对于低端非旋转存储设备，默认设置200通常就足够了。对于高端，总线连接的设备，请考虑更高的设置，如1000。

innodb_io_capacity_max

默认值为2000，适用于使用非旋转存储的工作负载。对于高端，总线附加的非旋转存储设备，考虑更高的设置，如2500。

innodb_log_compressed_pages

如果重做日志在非旋转存储器上，请考虑禁用此选项以减少日志记录。请参阅禁用压缩页面的日志记录。

innodb_log_file_size

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

innodb_page_size

考虑使用与磁盘的内部扇区大小相匹配的页面大小。早期的SSD设备通常具有4k扇区大小，一些较新的设备具有16k扇区大小。默认InnoDB页面大小为16k。保持页面大小接近存储设备块大小可将未更改的数据量重写到磁盘中。

binlog_row_image

如果二进制日志在非旋转存储上，并且所有表都具有主键，请考虑将此选项设置minimal为减少日志记录。确保为你的操作系统启用了TRIM支持。通常默认情况下启用。

增加I/O capacity以避免积压

如果吞吐量由于检查点操作而导致InnoDB周期性下降，请考虑增加innodb_io_capacity配置选项的值。当log cap（指未刷新到磁盘脏页的日志大小）大于整个日志空间的75%时，系统会异步的将log cap部分的日志涉及的脏页刷到磁盘上，但是此时事务提交不会终止，也就是说还允许有redo log的继续写入。但是如果log cap继续增加，当超过整个日志空间的90%时，MySQL会停止事务的更新，此时redo log也会停止写入，必须等到刷足够的脏页时，才能允许事务再次提交。本质上说，如果事务提交的速度大于脏页刷盘的速度，最终都会触发上述日志保护的功能，即最终系统停止事务的更新，来保证日志记录的脏页能够刷新到磁盘上。也就是说更高的值会导致更频繁的冲洗，避免积压的工作可能导致吞吐量下降。

在Fusion-io设备上存储系统表空间文件

通过在支持原子写入的Fusion-io设备上存储系统表空间文件（“ ibdata文件 ”），可以利用双写缓冲区相关的I/O优化。在这种情况下，双重缓冲（innodb_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以减少修改压缩数据的重做日志生成的工作负载。