这两天发现公司好几台阿里云ECS上的mysql生产服务器繁忙期间io等待高达百分之二三十（估计九成是没有write back），而且确定是mysql进程产生，由于跑的应用过多，开发和维护无法直接确定哪些表繁忙，哪些表不繁忙。。。
为了找到根源，我们需要知道哪些文件、表的io读写量最高，然后进行针对性的优化。
percona server原本提供了一工具pt-ioprofile，可是这工具是采用strace实现的，有可能在系统繁忙时导致进程被kill或者hang。。。所以还是通过performance_schema入手。
表中记录了针对每个文件的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: 7278810168SUM_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: 6710965560SUM_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: 7126760128SUM_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: 2400912924SUM_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: 2272031912SUM_NUMBER_OF_BYTES_WRITE: 135080448 COUNT_MISC: 23 SUM_TIMER_MISC: 67256157384 MIN_TIMER_MISC: 1294312 AVG_TIMER_MISC: 2924180710 MAX_TIMER_MISC: 117081671725 rows in set (0.00 sec)
在上面的查询中，我们可以看到，data/ioana/t1.ibd文件的写入是最多的。在我们的系统中，大部分情况下确实是写入的IO是瓶颈的情形比较多，主要是计算风险值实时写入所致。
找到具体的文件后，就可以根据业务模式和架构进行针对性的优化。