一 目录
· 一 目录
· 二 背景
· 三 SSD 特性
· 四 基于 SSD 的数据库优化
· 五 A 项目 MySQL 主从关系图
· 六 程序切换之前调优
· 6.1 修改系统 IO 调度算法
· 6.2 修改 innodb_io_capacity = 4000
· 6.3 修改 innodb_max_dirty_pages_pct = 25
· 6.4 修改 innodb_io_capacity = 2000
· 6.5 修改 innodb_io_capacity = 1500
· 6.6 关闭 innodb_adaptive_flushing
· 6.7 打开 innodb_adaptive_flushing
· 6.8 设置 innodb_max_dirty_pages_pct = 20
· 6.9 设置 innodb_io_capacity = 1000
· 七 程序切换之后调优
· 7.1 设置 innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500
· 7.2 设置 innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000
· 7.3 分析
· 八 小结
二 背景
在阅读这篇文章之前，读者需要注意的是，为了维护隐私，用 MySQL 服务器的 D 段代替完整 IP，并且略去一些私密信息。
A 项目，因 I/O 出现规律性地剧烈波动。每 15 分钟落地一次，innodbBuffPoolPagesFlushed 参数监控波峰和波谷交替出现，磁盘 I/O 同样如此，并且 until 达到 100%。经过排查，排除了触发器、事件、存储过程、前端程序定时器、系统 crontab 的可能性。最终定位为 InnoDB 日志切换，但是否完全是日志造成的影响，还有待进一步跟踪和分析。
找到问题的可能所在，试图在 24 主库上做了如下调整：
关闭 Query Cache；
设置 InnoDB Log 大小为 1280M；
设置 innodb_max_dirty_pages_pct 为 30，innodb_io_capacity 保持 200 不变。
做了如上调整以后，I/O 趋于平稳，没有再出现大的波动。
为了保险起见，A 项目方面决定采用配有 SSD 的机型，对主库进行迁移，同时对 24 的从库 27 进行迁移。待迁移完成后，在新的主库 39 上，针对 SSD 以及 MySQL InnoDB 参数进行优化。待程序切换完成后，再次对针对 SSD 以及 MySQL InnoDB 参数进行优化。也就是说在上线前后进行优化，观察 I/O 状态。
三 SSD 特性
众所周知，SSD 的平均性能是优于 SAS 的。SSD 能解决 I/O 瓶颈，但互联网行业总要权衡收益与成本的。目前内存数据库是这个领域的一大趋势，一方面，越来越多的应用会往 NoSQL 迁移。另一方面，重要数据总要落地，传统的机械硬盘已经不能满足目前高并发、大规模数据的要求。总的来说，一方面，为了提高性能，尽可能把数据内存化，这也是 InnoDB 存储引擎不断改进的核心原则。后续的 MySQL 版本已经对 SSD 做了优化。另一方面，尽可能上 SSD。
SSD 这么神秘，接下来我们看看它有哪些特性：
随机读能力非常好，连续读性能一般，但比普通 SAS 磁盘好；
不存在磁盘寻道的延迟时间，随机写和连续写的响应延迟差异不大。
erase-before-write 特性，造成写入放大，影响写入的性能；
写磨损特性，采用 Wear Leveling 算法延长寿命，但同时会影响读的性能；
读和写的 I/O 响应延迟不对等（读要大大好于写），而普通磁盘读和写的 I/O 响应延迟差异很小；
连续写比随机写性能好，比如 1M 顺序写比 128 个 8K 的随即写要好很多，因为随即写会带来大量的擦除。
总结起来，也就是随机读性能较连续读性能好，连续写性能较随机写性能好，会有写入放大的问题，同一位置插入次数过多容易导致损坏。
四 基于 SSD 的数据库优化
基于 SSD 的数据库优化，我们可以做如下事情：
减少对同一位置的反复擦写，也就是针对 InnoDB 的 Redo Log。因为 Redo Log 保存在 ib_logfile0/1/2，这几个日志文件是复写，来回切换，必定会带来同一位置的反复擦写；
减少离散写入，转化为 Append 或者批量写入，也就是针对数据文件；
提高顺序写入的量。
具体来说，我们可以做如下调整：
修改系统 I/O 调度算法为 NOOP；
提高每个日志文件大小为 1280M（调整 innodb_log_file_size）；
通过不断调整 innodb_io_capacity 和 innodb_max_dirty_pages_pct 让落地以及 I/O 水平达到均衡；
关闭 innodb_adaptive_flushing，查看效果；
修改 innodb_write_io_threads 和 innodb_read_io_threads。
针对系统 I/O 调度算法，做如下解释。系统 I/O 调度算法有四种，CFQ(Complete Fairness Queueing，完全公平排队 I/O 调度程序)、NOOP(No Operation，电梯式调度程序)、Deadline（截止时间调度程序）、AS(Anticipatory，预料 I/O 调度程序)。
下面对上述几种调度算法做简单地介绍。
CFQ 为每个进程/线程，单独创建一个队列来管理该进程所产生的请求，也就是说每个进程一个队列，各队列之间的调度使用时间片来调度，以此来保证每个进程都能被很好的分配到 I/O 带宽，I/O 调度器每次执行一个进程的 4 次请求。
NOOP 实现了一个简单的 FIFO 队列，它像电梯的工作主法一样对 I/O 请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。
Deadline 确保了在一个截止时间内服务请求，这个截止时间是可调整的，而默认读期限短于写期限，这样就防止了写操作因为不能被读取而饿死的现象。
AS 本质上与 Deadline 一样，但在最后一次读操作后，要等待 6ms，才能继续进行对其它 I/O 请求进行调度。可以从应用程序中预订一个新的读请求，改进读操作的执行，但以一些写操作为代价。它会在每个 6ms 中插入新的 I/O 操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。
在 SSD 或者 Fusion IO，最简单的 NOOP 反而可能是最好的算法，因为其他三个算法的优化是基于缩短寻道时间的，而固态硬盘没有所谓的寻道时间且 I/O 响应时间非常短。
还是用数据说话吧，以下是 SSD 下针对不同 I/O 调度算法所做的 I/O 性能测试，均为 IOPS。
可以看到，整体来说，NOOP 算法略胜于其他算法。
接下来讲解需要调整的 InnoDB 参数的含义：
innodb_log_file_size：InnoDB 日志文件的大小；
innodb_io_capacity：缓冲区刷新到磁盘时，刷新脏页数量；
innodb_max_dirty_pages_pct：控制了 Dirty Page 在 Buffer Pool 中所占的比率；
innodb_adaptive_flushing：自适应刷新脏页；
innodb_write_io_threads：InnoDB 使用后台线程处理数据页上写 I/O（输入）请求的数量；
innodb_read_io_threads：InnoDB 使用后台线程处理数据页上读 I/O（输出）请求的数量。
五 A 项目 MySQL 主从关系图
A 项目 MySQL 主从关系如图一：
图一 A 项目 MySQL 主从关系图
