mysql innodbiocapacity_【参数】Innodb_io_capacity 对于IO稳定性的一些研究

背景：最近在做一台线上服务器IO负载情况的时候发现了以下现象：

24小时的IO_UTIL 的曲线看似风平浪静，毛刺较少

但当图片放大到半小时级别的时候发现IO_UTIL即磁盘使用率出现了规律性的波动，见下图：

本文就将从这个现象触发，探究出现这样规律性波动的原因。

Step1： 服务器上进行实时IO负载查看

通过iostat -x 1 每隔一秒对IO使用情况进行一次负载查看。可以看到UTIL有规律性的波动(10秒1次)。

且负载的主要来源在于写请求(负载高时，wsec/s 也同步升高)

又由于服务器是MySQL独占，所以比较容易的就可以将原因归结为是MySQL的数据刷写导致(log/data)。

看到这里大神们应该已经不难猜到是MySQL内部 src_master_thread 中10_seconds 循环捣的鬼了。

为了保证诊断思路的连贯性，接下来还是把当时的操作复述一遍。

Step2： 确定MySQL write IO 来源

由于目前只知道是MySQL数据刷写导致，那么究竟是redo-log还是data page flush造成的现在还不知道。

innodb内部关于write的statistics还是比较有限的。因此比较粗暴的写了个类似于top的perl脚本 ，监控这些参数。

脚本如下，有兴趣的同学可以展开阅读

use strict;

use warnings;

use utf8;

use DBI;

my $CONFIG_SERVER_IP ='127.0.0.1';

my $CONFIG_SERVER_DB='test';

my $CONFIG_SERVER_PORT='3306';

my $CONFIG_SERVER_USER='user';

my $CONFIG_SERVER_PASS='password';

my $conf_dbh = DBI->connect("DBI:mysql:$CONFIG_SERVER_DB;host=$CONFIG_SERVER_IP;port=$CONFIG_SERVER_PORT", $CONFIG_SERVER_USER, $CONFIG_SERVER_PASS,{RaiseError => 1}) || die "Could not connect to database: $DBI::errstr";

my %last_value_hash;

print "data_write\tdata_written\tdblwr_pages_written\tdblwr_writes\tlog_write_req\tos_log_fsync\tos_log_written\tpages_written\n";

while(1){

my $conf_sth = $conf_dbh->prepare("show global status like '%innodb%'") || die "Prepare error";

$conf_sth->execute();

while(my $row=$conf_sth->fetchrow_hashref){

my $name=$row->{Variable_name};

my $value=$row->{Value};

if( $name eq 'Innodb_data_writes' || $name eq 'Innodb_data_written'

|| $name eq 'Innodb_dblwr_pages_written' || $name eq 'Innodb_dblwr_writes'

|| $name eq 'Innodb_log_write_requests' || $name eq 'Innodb_os_log_fsyncs'

|| $name eq 'Innodb_os_log_written' || $name eq 'Innodb_pages_written'){

$last_value_hash{$name}=0 if( !defined($last_value_hash{$name}) );

my $value_step=$value-$last_value_hash{$name};

$last_value_hash{$name}=$value;

print "$value_step\t";

}

}

print "\n";

sleep 1;

}

最后得到参数的波动情况如下：

可以看到innodb_data_writes / innodb_data_written / innodb_dblwr_pages_written / innodb_pages_written 都有和IO_UTIL一样有10秒一次的波动。

再结合上iostat中 wsec/s 较大的数值，基本可以确定IO高负载的元凶是data page的flush，而不是redo log的flush(如果是redo log的flush 应该是1秒一刷新了)

data_write data_written dblwr_pages_written dblwr_writes log_write_req os_log_fsync os_log_written pages_written

1 79360 0 0 169 1 79360 0

1 72192 0 0 127 1 72192 0

1 67072 0 0 139 1 67072 0

1 69120 0 0 149 1 69120 0

1 74752 0 0 128 1 74752 0

1 61952 0 0 134 1 61952 0

1 71168 0 0 131 1 71168 0

1 62976 0 0 126 1 62976 0

1 71168 0 0 109 1 71168 0

1388 44870144 1367 14 125 6 75776 1367

1 66048 0 0 158 1 66048 0

1 73728 0 0 144 1 73728 0

1 69632 0 0 126 1 69632 0

1 75776 0 0 172 1 75776 0

1 88576 0 0 151 1 88576 0

1 67584 0 0 134 1 67584 0

1 80384 0 0 155 1 80384 0

1 86528 0 0 191 1 86528 0

1 72704 0 0 135 1 72704 0

1525 49385984 1504 13 154 5 102400 1504

1 74752 0 0 158 1 74752 0

Step3 ： Innodb 什么时候做dirty data page的flush

由于之前对于innodb的数据刷写也只是略知一二，网上对于刷写策略也是众说纷纭，诊断到了这里貌似就无法深入了。

其实真相就静静的躺在那里：源代码阅读。

Innodb写磁盘的代码路径非常清晰，比较容易阅读。主要代码在 storage/innodb_plugin/Buf/Buf0flu.c 中

代码比较冗长这里就不贴了，主要叙述下大致的调用关系。

buf_flush_batch 调用 buf_flush_try_neighbors (尝试刷写neighbor page)

buf_flush_try_neighbors 调用 buf_flush_page (刷写单个page)

buf_flush_page调用buf_flush_write_block_low (实际刷写单个page)

buf_flush_write_block_low调用buf_flush_post_to_doublewrite_buf (将page放到double write buffer中，并准备刷写)

buf_flush_post_to_doublewrite_buf 调用 fil_io ( 文件IO的封装)

fil_io 调用 os_aio (aio相关操作)

os_aio 调用 os_file_write (实际写文件操作)

其中buf_flush_batch 只有两种刷写方式： BUF_FLUSH_LIST 和 BUF_FLUSH_LRU 两种方式的方式和触发时机简介如下：

BUF_FLUSH_LIST: innodb master线程中 1_second / 10 second 循环中都会调用。触发条件较多(下文会分析)

BUF_FLUSH_LRU: 当Buffer Pool无空闲page且old list中没有足够的clean page时，调用。刷写脏页后可以空出一定的free page，供BP使用。

从触发频率可以看到 10 second 循环中对于 buf_flush_batch( BUF_FLUSH_LIST ) 的调用是10秒一次IO高负载的元凶所在。

我们再来看10秒循环中flush的逻辑：

通过比较过去10秒的IO次数和常量的大小，以及pending的IO次数，来判断IO是否空闲，如果空闲则buf_flush_batch( BUF_FLUSH_LIST,PCT_IO(100));

如果脏页比例超过70，则 buf_flush_batch( BUF_FLUSH_LIST,PCT_IO(100) );

否则 buf_flush_batch( BUF_FLUSH_LIST,PCT_IO(10) );

可以看到由于SSD对于随机写的请求响应速度非常快，导致IO几乎没有堆积。也就让innodb误认为IO空闲，并决定全力刷写。

其中PCT_IO(N)  = innodb_io_capacity *N% ，单位是页。因此也就意味着每10秒，innodb都至少刷10000个page或者刷完当前所有脏页。

updated on 2013/10/31: 在5.6中官方的adaptive flush算法有所改变，但是空闲状态下innodb_io_capacity对于刷写page数量的影响仍然不改变。

具体见：文章链接

Step4： 进一步分析找到解决方案

在多次调整 innodb_adaptive_flushing 和 innodb_adaptive_flushing_method 后发现现象没有任何变化。

这一点也比较容易解释：因为大批量刷写的原因是在于10秒循环中，innodb认为IO比较空闲，所以根据innodb_io_capacity 全力刷写；而adaptive只发生在1秒循环中。

所以，调整adaptive相关参数实际上不解决问题。

从Step3中的分析可以得出一个比较明显的结论，减小innodb_io_capacity 后就能很大程度上解决问题。

当我们把innodb_io_capacity从10000调整到200后，并且添加以下配置后：

innodb_adaptive_flush=OFF;

innodb_adaptive_checkpoint=keep_average

可以看到mysql的data written写入量大量减少，且保持稳定(见下图，7:31后是参数调整后的结果)

同时从flashcard的硬件写入量来看，也大量的减少(见下图)

在5.1.X版本中，最多只会刷新100个脏页到磁盘、合并20个插入缓冲，即使磁盘有能力处理更多的请求，只能会处理这么多，这样在更新量较大的时候，脏页刷新就可能跟不上，导致性能下降。

但在5.5.X版本里，innodb_io_capacity参数可以动态调整刷新脏页的数量，这在一定程度上解决了这一问题。

innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒每秒的输入输出量(或读写次数)。

可以动态调整参数:set global innodb_io_capacity=2000;

磁盘配置与innodb_io_capacity参数值

innodb_io_capacity

磁盘配置

200

单盘SAS/SATA

2000

SAS*12  RAID  10

5000

SSD

50000

FUSION-IO

13.7.7.11. Controlling the Master Thread I/O Rate

The master thread in InnoDB is a thread that performs various tasks in the background. Most of these tasks are I/O related, such as flushing dirty pages from the buffer cache or writing changes from the insert buffer to the appropriate secondary indexes. The master thread attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the free I/O bandwidth available and tune its activities to take advantage of this free capacity. Historically, InnoDB has used a hard coded value of 100 IOPs (input/output operations per second) as the total I/O capacity of the server.

Beginning with InnoDB storage engine 1.0.4, a new configuration parameter indicates the overall I/O capacity available to InnoDB. The new parameterinnodb_io_capacity should be set to approximately the number of I/O operations that the system can perform per second. The value depends on your system configuration. When innodb_io_capacity is set, the master threads estimates the I/O bandwidth available for background tasks based on the set value. Setting the value to 100 reverts to the old behavior.

You can set the value of innodb_io_capacity to any number 100 or greater, and the default value is 200. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

参考：

http://mp.weixin.qq.com/s/fsZ0uwNBbBoXJNmv4Y462A

http://www.cnblogs.com/cenalulu/p/3272606.html --原文