基于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。

注：以下数据来自于陈广钊，他是我的师兄，在此致谢。

I/O Type	NOOP	Anticipatory	Deadline	CFQ
Sequential Read	22256	7955	22467	8652
Sequential Write	4090	2560	1370	1996
Sequential RW Read	6355	760	567	1149
Sequential RW Write	6360	760	565	1149
Random Read	17905	20847	20930	20671
Random Write	7423	8086	8113	8072
Random RW Read	4994	5221	5316	5275
Random RW Write	4991	5222	5321	5278

可以看到，整体来说，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 主从关系图

五、程序切换之前调优

程序切换之前，39 只是 24 的从库，所以 IO 压力不高，以下的调整也不能说明根本性的变化。需要说明一点，以下调整的平均间隔在 30 分钟左右。

5.1 修改系统 IO 调度算法

系统默认的 I/O 调度算法 是 CFQ，我们试图先修改之。至于为什么修改，可以查看第四节。

具体的做法如下，需要注意的是，请根据实际情况做调整，比如你的系统中磁盘很可能不是 sda。

echo "noop" > /sys/block/sda/queue/scheduler

如果想永久生效，需要更改 /etc/grup.conf，添加 elevator，示例如下：

kernel /vmlinuz-x.x.xx-xxx.el6.x86_64 ro root=UUID=e01d6bb4-bd74-404f-855a-0f700fad4de0 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun1
6 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=noop rhgb quiet

此步调整做完以后，查看 39 I/O 状态，并没有显著的变化。

5.2 修改 innodb_io_capacity = 4000

在做这个参数调整之前，我们来看看当前 MySQL 的配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 200
innodb_max_dirty_pages_pct 30
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

修改方法如下：

SET GLOBAL innodb_io_capacity = 4000;

网络上的文章，针对 SSD 的优化，MySQL 方面需要把 innodb_io_capacity 设置为 4000，或者更高。然而实际上，此业务 UPDATE 较多，每次的修改量大概有 20K，并且基本上都是离散写。innodb_io_capacity 达到 4000，SSD 并没有给整个系统带来很大的性能提升。相反，反而使 IO 压力过大，until 甚至达到 80% 以上。

5.3 修改 innodb_max_dirty_pages_pct = 25

修改方法如下：

SET GLOBAL innodb_max_dirty_pages_pct = 25;

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 4000
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

之前已经将 innodb_max_dirty_pages_pct 设置为 30，此处将 innodb_max_dirty_pages_pct 下调为 25%，目的为了查看脏数据对 I/O 的影响。修改的结果是，I/O 出现波动，innodbBuffPoolPagesFlushed 同样出现波动。然而，由于 39 是 24 的从库，暂时还没有切换，所有压力不够大，脏数据也不够多，所以调整此参数看不出效果。

5.4 修改 innodb_io_capacity = 2000

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

因为 innodb_io_capacity 为 4000 的情况下，I/O 压力过高，所以将 innodb_io_capacity 调整为 2000。调整后，w/s 最高不过 2000 左右，并且 I/O until 还是偏高，最高的时候有 70%。我们同时可以看到，I/O 波动幅度减小，innodbBuffPoolPagesFlushed 同样如此。

5.5 修改 innodb_io_capacity = 1500

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

I/O 持续出现波动，我们接着继续下调 innodb_io_capacity，调整为 1500。I/O until 降低，I/O 波动幅度继续减小，innodbBuffPoolPagesFlushed 同样如此。

5.6 关闭 innodb_adaptive_flushing

修改方法如下：

SET GLOBAL innodb_adaptive_flushing = OFF;

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing OFF
innodb_write_io_threads 4
innodb_read_io_threads 4

既然落地仍然有异常，那我们可以试着关闭 innodb_adaptive_flushing，不让 MySQL 干预落地。调整的结果是，脏数据该落地还是落地，并没有受 I/O 压力的影响，调整此参数无效。

5.7 打开 innodb_adaptive_flushing

修改方法如下：

SET GLOBAL innodb_adaptive_flushing = ON;

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

经过以上调整，关闭 innodb_adaptive_flushing 没有效果，还是保持默认打开，让这个功能持续起作用吧。

5.8 设置 innodb_max_dirty_pages_pct = 20

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 20
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

接着我们将 innodb_max_dirty_pages_pct 下调为 20，观察脏数据情况。由于 InnoDB Buffer Pool 设置为 40G，20% 也就是 8G，此时的压力达不到此阀值，所以调整参数是没有效果的。但业务繁忙时，就可以看到效果，落地频率会增高。

5.9 设置 innodb_io_capacity = 1000

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1000
innodb_max_dirty_pages_pct 20
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

经过以上调整，我们需要的是一个均衡的 IO，给其他进程一些余地。于是把 innodb_io_capacity 设置为 1000，此时可以看到 I/O until 维持在 10% 左右，整个系统的参数趋于稳定。

后续还要做进一步的监控、跟踪、分析和优化。

六、程序切换之后调优

在业务低峰，凌晨 1 点左右，配合研发做了切换。切换之后的主从关系可以查看第五节。

6.1 设置 innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 30
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

在 innodb_io_capacity 为 1000，innodb_max_dirty_pages_pct 为 20 的环境下，I/O until 有小幅波动，而且波峰和波谷持续交替，这种情况是不希望看到的。innodbBuffPoolPagesFlushed 比较稳定，但 innodbBuffPoolPagesDirty 持续上涨，没有下降的趋势。故做了如下调整：innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500。调整完成后，innodbBuffPoolPagesDirty 趋于稳定，I/O until 也比较稳定。

6.2 设置 innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000

修改方法不赘述。

修改之后的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 40
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

针对目前这种 I/O 情况，做了如下调整：innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000。

6.3 分析

针对以上两个调整，我们通过结合监控数据来分析 I/O 状态。

以下是高速缓冲区的脏页数据情况，如图二：

图二 主库的脏数据情况

以下是脏数据落地的情况，如图三

图三 主库的脏数据落地情况

28 号早 8 点到下午 7 点，当脏数据上升，也就是在内存中的数据更多，那么落地就会很少，呈现一个平稳的趋势；当脏数据维持不变，也就是脏数据达到了 innodb_max_dirty_pages_pct 的限额（innodb_buffer_pool_size 为 40G，innodb_max_dirty_pages_pct 为 40%，也就是在内存中的脏数据最多为 16G，每个 Page 16K，则 innodbBufferPoolDirtyPages 最大为 1000K），落地就会增多，呈现上升的趋势，所以才会出现上述图片中的曲线。

这是最后的配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 40
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

七、小结

此次针对 SSD 以及 MySQL InnoDB 参数优化，总结起来，也就是以下三条：

• 修改系统 I/O 调度算法；

• 分析 I/O 情况，动态调整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；

• 试图调整 innodb_adaptive_flushing，查看效果。

针对 innodb_write_io_threads 和 innodb_read_io_threads 的调优我们目前没有做，我相信调整为 8 或者 16，系统 I/O 性能会更好。

还有，需要注意以下几点：

• 网络文章介绍的方法有局限性和场景性，不能亲信，不能盲从，做任何调整都要以业务优先。保证业务的平稳运行才是最重要的，性能都是其次；

• 任何一个调整，都要建立在数据的支撑和严谨的分析基础上，否则都是空谈；

• 这类调优是非常有意义的，是真正能带来价值的，所以需要多下功夫，并且尽可能地搞明白为什么要这么调整。

文末，说一点比较有意思的。之前有篇文章提到过 SSDB。SSDB 底层采用 Google 的 LevelDB，并支持 Redis 协议。LevelDB 的设计完全是贴合 SSD 的设计思想的。首先，尽可能地转化为连续写；其次，不断新增数据文件，防止同一位置不断擦写。另外，SSDB 的名字取得也很有意思，也很有水平。我猜想作者也是希望用户将 SSDB 应用在 SSD 上吧。