mysql服务器io等待高定位与分析
这两天发现公司好几台阿里云ECS上的mysql生产服务器繁忙期间io等待高达百分之二三十（估计九成是没有write back），而且确定是mysql进程产生，由于跑的应用过多，开发和维护无法直接确定哪些表繁忙，哪些表不繁忙。。。
为了找到根源，我们需要知道哪些文件、表的io读写量最高，然后进行针对性的优化。
percona server原本提供了一工具pt-ioprofile，可是这工具是采用strace实现的，有可能在系统繁忙时导致进程被kill或者hang。。。所以还是通过performance_schema入手。
file_summary_by_instance表中记录了针对每个文件的Io读写情况，如下所示：
mysql> select * from file_summary_by_instance order by SUM_TIMER_WAIT desc limit 5\G;
*************************** 1. row ***************************
FILE_NAME: /usr/local/mysql-5.6.19-linux-glibc2.5-x86_64/data/ioana/t1.ibd
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OBJECT_INSTANCE_BEGIN: 139999261742528
COUNT_STAR: 11739
SUM_TIMER_WAIT: 1617275634994
MIN_TIMER_WAIT: 5797000
AVG_TIMER_WAIT: 137769394
MAX_TIMER_WAIT: 100739635708
COUNT_READ: 1
SUM_TIMER_READ: 34699788
MIN_TIMER_READ: 34699788
AVG_TIMER_READ: 34699788
MAX_TIMER_READ: 34699788
SUM_NUMBER_OF_BYTES_READ: 16384
COUNT_WRITE: 11472
SUM_TIMER_WRITE: 1184834714832
MIN_TIMER_WRITE: 5797000
AVG_TIMER_WRITE: 103280406
MAX_TIMER_WRITE: 7278810168
SUM_NUMBER_OF_BYTES_WRITE: 377339904
COUNT_MISC: 266
SUM_TIMER_MISC: 432406220374
MIN_TIMER_MISC: 8252820
AVG_TIMER_MISC: 1625586835
MAX_TIMER_MISC: 100739635708
*************************** 2. row ***************************
FILE_NAME: /usr/local/mysql-5.6.19-linux-glibc2.5-x86_64/data/ibdata1
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OBJECT_INSTANCE_BEGIN: 139999261496128
COUNT_STAR: 1709
SUM_TIMER_WAIT: 814764332152
MIN_TIMER_WAIT: 3623652
AVG_TIMER_WAIT: 476748969
MAX_TIMER_WAIT: 33581165152
COUNT_READ: 166
SUM_TIMER_READ: 22098794292
MIN_TIMER_READ: 3623652
AVG_TIMER_READ: 133124943
MAX_TIMER_READ: 10389786028
SUM_NUMBER_OF_BYTES_READ: 4784128
COUNT_WRITE: 1215
SUM_TIMER_WRITE: 488756864260
MIN_TIMER_WRITE: 5788568
AVG_TIMER_WRITE: 402268586
MAX_TIMER_WRITE: 6710965560
SUM_NUMBER_OF_BYTES_WRITE: 364969984
COUNT_MISC: 328
SUM_TIMER_MISC: 303908673600
MIN_TIMER_MISC: 7460212
AVG_TIMER_MISC: 926550320
MAX_TIMER_MISC: 33581165152
*************************** 3. row ***************************
FILE_NAME: /usr/local/mysql-5.6.19-linux-glibc2.5-x86_64/data/ioana/t2.ibd
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OBJECT_INSTANCE_BEGIN: 139999261741120
COUNT_STAR: 12011
SUM_TIMER_WAIT: 678760914098
MIN_TIMER_WAIT: 5073956
AVG_TIMER_WAIT: 56511264
MAX_TIMER_WAIT: 7126760128
COUNT_READ: 6309
SUM_TIMER_READ: 65882736360
MIN_TIMER_READ: 5073956
AVG_TIMER_READ: 10442505
MAX_TIMER_READ: 68216988
SUM_NUMBER_OF_BYTES_READ: 103366656
COUNT_WRITE: 5510
SUM_TIMER_WRITE: 434740598494
MIN_TIMER_WRITE: 5778028
AVG_TIMER_WRITE: 78899805
MAX_TIMER_WRITE: 7126760128
SUM_NUMBER_OF_BYTES_WRITE: 184696832
COUNT_MISC: 192
SUM_TIMER_MISC: 178137579244
MIN_TIMER_MISC: 8811440
AVG_TIMER_MISC: 927799837
MAX_TIMER_MISC: 2063390504
*************************** 4. row ***************************
FILE_NAME: /usr/local/mysql-5.6.19-linux-glibc2.5-x86_64/data/ib_logfile0
EVENT_NAME: wait/io/file/innodb/innodb_log_file
OBJECT_INSTANCE_BEGIN: 139999261496832
COUNT_STAR: 258
SUM_TIMER_WAIT: 213773061014
MIN_TIMER_WAIT: 594456
AVG_TIMER_WAIT: 828577331
MAX_TIMER_WAIT: 14386901848
COUNT_READ: 6
SUM_TIMER_READ: 54982964
MIN_TIMER_READ: 594456
AVG_TIMER_READ: 9163476
MAX_TIMER_READ: 46464536
SUM_NUMBER_OF_BYTES_READ: 69632
COUNT_WRITE: 141
SUM_TIMER_WRITE: 64075588012
MIN_TIMER_WRITE: 10415628
AVG_TIMER_WRITE: 454436316
MAX_TIMER_WRITE: 2400912924
SUM_NUMBER_OF_BYTES_WRITE: 149283328
COUNT_MISC: 111
SUM_TIMER_MISC: 149642490038
MIN_TIMER_MISC: 1692724
AVG_TIMER_MISC: 1348130294
MAX_TIMER_MISC: 14386901848
*************************** 5. row ***************************
FILE_NAME: /usr/local/mysql-5.6.19-linux-glibc2.5-x86_64/data/ib_logfile1
EVENT_NAME: wait/io/file/innodb/innodb_log_file
OBJECT_INSTANCE_BEGIN: 139999261497536
COUNT_STAR: 71
SUM_TIMER_WAIT: 128004164104
MIN_TIMER_WAIT: 1294312
AVG_TIMER_WAIT: 1802875432
MAX_TIMER_WAIT: 11708167172
COUNT_READ: 0
SUM_TIMER_READ: 0
MIN_TIMER_READ: 0
AVG_TIMER_READ: 0
MAX_TIMER_READ: 0
SUM_NUMBER_OF_BYTES_READ: 0
COUNT_WRITE: 48
SUM_TIMER_WRITE: 60748006720
MIN_TIMER_WRITE: 9237256
AVG_TIMER_WRITE: 1265583122
MAX_TIMER_WRITE: 2272031912
SUM_NUMBER_OF_BYTES_WRITE: 135080448
COUNT_MISC: 23
SUM_TIMER_MISC: 67256157384
MIN_TIMER_MISC: 1294312
AVG_TIMER_MISC: 2924180710
MAX_TIMER_MISC: 11708167172
5 rows in set (0.00 sec)
在上面的查询中，我们可以看到，data/ioana/t1.ibd文件的写入是最多的。在我们的系统中，大部分情况下确实是写入的IO是瓶颈的情形比较多，主要是计算风险值实时写入所致。
找到具体的文件后，就可以根据业务模式和架构进行针对性的优化。
