mysql5.7官网直译InnoDB表优化--磁盘I/O的优化

8.5.8 Optimizing InnoDB Disk I/O 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%。更多信息，请看8.12.4.1的mysql怎么样使用内存。
>调整刷新方法
在一些版本的GUN/Liunx 和Unix系统中，刷出文件到磁盘是通过Unix的fsync()调用(也是InnoDB的默认使用)但是类似的方法会特别慢。如果数据库写性能是一个问题，调整基准参数innodb_flush_method的值为O_DSYNC.
>使用Linux自带的AIO用一个noop or deadline I/O
 InnoDB使用Linux系统的异步I/O子系统来完成读和写请求关于数据文件页。这种行为可以通过配置innodb_use_native_aio配置来控制，这也是默认值。通过自然AIO,I/O方案类型极大影响了I/O性能。总的来说，noop和deadline I/O方案是推荐的。进行基准测试来决定那一种方案能够给你提供更好的结果在你的环境和负载中。更多信息请看14.6.8的在Linux中使用异步I/O.
>使用 direct I/O在Solaris10系统对应x86_64架构。
当在Solaris 10 对应x86_64架构(AMD处理器)中使用InnoDB存储引擎，使用direct I/O对于InnoDB 相关文件从而避免恶化InnoDB的性能。为了使用整个UF文件系统存储InnoDB 相关文件，并使用direct I/O,和forcedirectio选项一起操作；看mount_ufs(1M).（默认情况下，在Solaris系统里不会使用这个）为了应用direct I/O只在InnoDB 文件操作有效而不是整个文件系统，设置innodb_flush_method=O_DIRECT.通过这个设置，InnoDB调用directio()代替fcntl()对数据文件的I/O操作(日志文件没有)。
>在Solaris2.6或者更晚的系统中使用原始数据格式保存数据和日志文件。
当使用InnoDB存储引擎并且有一个大的innodb_buffer_pool_size值在任何版本的Solaris2.6和以上的任何(sparc/x86/x64/amd64),基准测试数据文件和日志文件用原始格式或者是独特的direct I/O UFS文件系统，使用forcedirectio挂载选项之前描述的。(必须要使用挂载而不是设置innodb_flush_method如果你想direct I/O方式在日志文件)Veritas file system VxFS的用户应该使用convosync-direct挂载选项。
不要放置其他的mysql数据文件，例如myISAM表，在direct I/O 文件系统。执行或者引用必须不能取代在一个direct I/O 文件系统。
>使用外部存储设备
外部存储设备应该被使用设置一个RAID配置。对于相关信息请看8.12.2的优化磁盘I/O.
或者，InnoDB表空间数据和日志文件能够放入不同的物理磁盘。更多信息请看下面的部分
 >>14.6.1 innoDB的启动设置
 >>14.7.5 在外部数据目录中创建File-Per-Table表空间
 >>创建一个通用的表空间
 >>14.8.1.3 移动和复制InnoDB表。
>考虑非旋转存储
非旋转存储通常能够给随机I/O操作提供更好的性能；并且对于序列I/O操作旋转存储。
当分布式数据和日志文件跨越旋转和非旋转存储设备，考虑占主导性能优势地位的I/O类型操作在每一个文件中。
随机I/O文件典型的包括file-per-table,大多数表空间的数据文件，undo表空间文件，和临时表空间文件，序列I/O文件包括InnoDB系统表空间文件(因为重复写缓存和改变缓存)和日志文件如二进制日志文件和redo日志文件。
当使用非旋转存储时考虑如下的配置选项：
 1）innodb_checksum_algorithm
    crc32选项用于一个快速检查算法并且推荐在快速存储系统中使用
 2）innodb_flush_neighbors
    这个选项是对旋转存储设备上的I/O优化用的。在非旋转存储设备应该将其不可用或者是在一个混合存储中也应该不可用。
 3）innodb_io_capacity
    对于一个低端通信非旋转存储设备来说默认的200还可以接受。对于高端或者是总线通信的设备，考虑设置一个更高数字比方说1000
 4）innodb_io_capacity
    在非旋转设备中默认的负载值是2000.对于高端或者是总线设备考虑设置为2500或者更高
 5）innodb_log_compressed_pages
    如果redo日志在一个非旋转存储，考虑不使用这个选项从而减少日志。具体看无效日志对于压缩页
 6）innodb_log_file_size
    如果redo日志在一个非旋转存储，配置这个选项到最大缓存和写一起。
 7）innodb_page_size
    考虑使用页大小和磁盘的扇形大小匹配。早期的SSD设备通常是4K扇形。一些新设备有16K的扇形。默认的InnoDB页大小为16K。保持页大小接近存储设备块的大小尽量来减少数据改变从而引起的磁盘重写。
 8）binlog_row_image
    如果二进制日志是在非旋转存储并且所有的表有主键，考虑设置这个选项为minimal来减少日志。
  保证你的操作系统支持TRIM。一般情况下默认支持。


>增加I/O 容量来避免日志积压
如果吞吐量周期下降因为InnoDB 检查点操作，考虑增加innodb_io_capacity选项配值的值。值越高刷出越频繁，避免堆积日志工作影响吞吐量。
>如果刷出不能落后，则调小I/O 容量
如果系统并没有落后InnoDB的刷出操作，考虑调整降低innodb_io_capacity配置值。典型的是，你尽量保持该值和实际一样低，但是绝不能低到影响吞吐量。在一个典型的场景中你能够有更低的值，你可能看到这种结合在SHOW ENGINE INNODB STATUS输出中：
  1）历史列表长度小，在少数千数以下
  2）插入缓存合并接近行大小。
  3）修改的页在缓存中明显低于innodb_max_dirty_pages_pct在缓存中。(测量时间当服务不是批量插入时；在批量操作时修改页比例高于实际是正常的)
  4）日志序列number-last检测点 小雨7/8或者是理想状态下低于6/8占InnoDB日志文件的大小。
>存储系统表空间文件在Fusion-io设备
你存储文件在支持自动写的Fusion-io设备中会有双写的优势。在这种情况下双写缓存默认不可用，并且Fusion-io 的自动写会在整个数据文件中使用。这个特性只有在Fusion-io 硬件并且是Fusion-io NVMFS on Linux系统才可以。为了发挥完整的优势, 建议设置innodb_flush_method=O_DIRECT.
  注意：
  因为双写缓存的设置是全局的，双写缓存是不可用的对于数据文件在非Fusion-io设备中存储的。
>对压缩页不再记录日志
  当使用InnoDB表的压缩特性，关于二次压缩的页面镜像被写在redo日志当改变发生在压缩数据中时。这种行为可以通过innodb_log_compressed_pages来控制，它能够默认阻止可能发生的变体如果不同版本的zlib压缩算法被使用在恢复中的话。如果你已经确定了zlib版本不会改变，innodb_log_compressed_pages不能减少redo日志的产生对于修改后的压缩数据。