[转]SSD 下的 MySQL IO 优化

- 日理万妓 2022-08-17 15:19 204阅读 0赞

作者：温国兵

转自：http://dbarobin.com/2015/08/29/mysql-optimization-under-ssd/

### 一  目录 ###

*  一 目录
 *  二 背景
 *  三 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。

<table style="border: 1px solid rgb(232, 232, 232); margin: 0px 0px 20px; padding: 0px 5px; font-size: 15px; border-collapse: collapse; border-spacing: 0px; width: 599px; color: rgb(46, 46, 46); font-family: 'Microsoft YaHei', 宋体, 'Myriad Pro', Lato, 'Helvetica Neue', Helvetica, Arial, sans-serif; line-height: 24px;"> 
 <thead style="border: 0px; margin: 0px; padding: 0px;"> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <th style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center; color: rgb(102, 102, 102);">I/O Type</th> 
   <th style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center; color: rgb(102, 102, 102);">NOOP</th> 
   <th style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center; color: rgb(102, 102, 102);">Anticipatory</th> 
   <th style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center; color: rgb(102, 102, 102);">Deadline</th> 
   <th style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center; color: rgb(102, 102, 102);">CFQ</th> 
  </tr> 
 </thead> 
 <tbody style="border: 0px; margin: 0px; padding: 0px;"> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Sequential Read</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">22256</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">7955</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">22467</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">8652</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Sequential Write</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">4090</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">2560</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">1370</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">1996</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Sequential RW Read</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">6355</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">760</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">567</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">1149</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Sequential RW Write</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">6360</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">760</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">565</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">1149</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Random Read</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">17905</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">20847</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">20930</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">20671</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Random Write</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">7423</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">8086</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">8113</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">8072</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Random RW Read</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">4994</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5221</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5316</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5275</td> 
  </tr> 
  <tr style="border-width: 0px 0px 1px; border-bottom-style: solid; border-bottom-color: rgb(232, 232, 232); margin: 0px; padding: 0px;"> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">Random RW Write</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">4991</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5222</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5321</td> 
   <td style="border: 0px; margin: 0px; padding: 5px; font-size: 15px; vertical-align: middle; text-align: center;">5278</td> 
  </tr> 
 </tbody> 
</table>

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

![5e4d414cgw1evm2bp62nxj20kn07amxs.jpg][]

###### 图一 A 项目 MySQL 主从关系图 ######

### 六 程序切换之前调优 ###

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

6.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 状态，并没有显著的变化。

6.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% 以上。

6.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 的从库，暂时还没有切换，所有压力不够大，脏数据也不够多，所以调整此参数看不出效果。

6.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 同样如此。

6.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 同样如此。

6.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 压力的影响，调整此参数无效。

6.7 打开 innodb\_adaptive\_flushing

修改方法如下：

> SET GLOBAL innodb\_adaptive\_flushing = ON;

修改之后的 MySQL 配置：

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

6.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，此时的压力达不到此阀值，所以调整参数是没有效果的。但业务繁忙时，就可以看到效果，落地频率会增高。

6.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 点左右，配合研发做了切换。切换之后的主从关系可以查看第五节。

7.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 也比较稳定。

7.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。

7.3 分析

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

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

![5e4d414cgw1evm2bpwm78j20fj07oq3u.jpg][]

###### 图二 主库的脏数据情况 ######

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

![5e4d414cgw1evm2bpjubmj20ff07pmxo.jpg][]

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

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

这是最后的配置：

### 八 小结 ###

此次针对 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 上吧。

九 参考

[8.5 Optimizing for InnoDB Tables][]

–EOF–

来源：[http://blog.jobbole.com/90267/][http_blog.jobbole.com_90267]

[5e4d414cgw1evm2bp62nxj20kn07amxs.jpg]: /images/20220731/0662e33e40624985aef1b6efbddba301.png
[5e4d414cgw1evm2bpwm78j20fj07oq3u.jpg]: /images/20220731/08a0b537c66a4df4ac9138ee733dff03.png
[5e4d414cgw1evm2bpjubmj20ff07pmxo.jpg]: /images/20220731/0da67bb02c79449b93e54366d59609dd.png
[8.5 Optimizing for InnoDB Tables]: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html
[http_blog.jobbole.com_90267]: http://blog.jobbole.com/90267/