mysql os wait过高_mysqld服务器cpu/iowait瞬间出现峰值的问题

自从nagios报警服务配置完善以后,潜伏在DB上的问题变得愈加凸显，这期间还经历了三番五次的机器故障，于是就更加紧绷了我们对于目前DB状态的关注度，通过cacti看每组机器资源的使用情况，通过nagios的alert提示会知道哪些异常在频繁出现，尽管没有发出报警通知(报警策略：所有服务检测每个5分钟扫描一次，发现故障第一次提示开始，每隔1分钟再去尝试，一共4次，当确认该服务失败或者超过阀值后，将状态从之前的 Soft更新为Hard，然后便会发出邮件触发139邮箱短信报警，报警邮件的周期为每30钟一次)。观察每个时段nagois的alert提示，同时比对该事件点在cacti上的资源使用情况，给我们一步步排查异常提供了线索。

先说下这次优化过程中的体会，对于自己很不满意，一个字“差”！调整的参数都知道，为什么没有去具体调整看看效果；也同样发现是sql 语句的问题，也同样去expain过，但为什么没有做出任何建设性的改进，每天喊着合理有效的去加索引，无奈的是自己找不到那个很需要索引的语句，在 slow-log里面抛呀抛的，人家田老师show processlist，一眼就将那个需要索引字段的语句抓了出来，差距呀！田老师还通过show profile让我们清楚的知道一条语句消耗时间的地方在哪个过程上。什么是学以致用呀，这就是，突然又让我想起考大学，同样是各种公式和技巧方法，别人学了就能考上大学，而自己呢。那会跟Hiro在一起学习mysql的时候，我就曾经感叹道，什么是一好百好，真是这样的，一个人之前学习好，那么他工作后如果上心的话也一样是优秀的，为什么google喜欢要清华的，真的就是这样。下面就说说这次优化的诸多尝试。

这边一个业务的其中一组服务器，一个6台机器，主库为drbd的两台，一台对外提供写，从库有四台，均对外提供读的服务。最近一段时间频繁出现某一刻CPU使用率突然彪生80%–>90%–>100%，以致前端程序无法响应不断抛出异常，iowait在这期间也是从之前20%-30%，到最后突破60%，随着数据量的增加还有增长的趋势。田老师告诉我，这次优化的大致思路就是先将CPU将下来，这样db可以有时间去处理其他请求，再将iowait将下来，i/o等待过长，自然会降低sql执行的效率。

CPU: 通过调整tmp_table_size、max_heap_table_size、sort_buffer_size、 thread_cache_size。为什么要调整这些参数呢？当然是事出有因，通过show processlist可以看到thread的当前状态，我们发现会有频繁的copying tmp table，比较理想说明还在使用内存表，尽管没有出现copying tmp table on disk，但是适当调整tmp_table_size还是能起到未雨绸缪的作用；还发现有converting HEAP to MyISAM，很有理由需要调整max_heap_table_size(其上线为tmp_table_size的大小)。

我们的大部分sql都是含有 order by和group by，sort_buffer_size可以提供这种语句的执行效率，但是它的设置要格外小心，它并不是全局的参数，而是每个线程所有能获得，怎么能够适可而止呢，通过sort_merge_passes这个状态参数每秒钟变化的幅度来评估你sort_buffer_size设置是否已经够用了。 thread_cache_size这个参数也是为了减小cpu的消耗，我们都知道new一个线程是重操作。其实，更加理想的状态是，避免创建临时表，而是完全通过索引就能实现排序分组的目的，这样就大大减轻了cpu的负载。filesort有两中算法：低效率算法需要读两次表，升级版的算法一次就能将最后的结果取出，而它的代价是要缓存跟多点的信息，一般情况下 MySQL都会选择效率高的升级版的算法，除非你的筛选字段中有blob或text这样的大字段，同时还可以通过调整 max_length_for_sort_data的大小提高升级算法的效率。

iowait: 通过在高峰期抓到的sql语句，将其加上必要的索引(单列索引、复合索引)，减少扫描表中记录的数量，由于视图建立的过于庞大，几乎调整的可能性很小牵扯的改动很大，这次的优化主要还是单表语句的优化。在通过explain分析语句的时候，惊奇的发现了一个颠覆复合索引理论的结果，我们都知道复合索引的 leftmost原则，我的测试语句where后面的条件顺序并不是之前田老师定义的复合索引中的顺序，但是它依然使用了那个复合索引o_o!….通过观察田老师show profile的执行结果，可以清楚看到语句消耗主要集中在sending data和removing tmp table这两项操作上。

slave lag: 主从落后也是时有发生，通过前两个指标的调整，落后的秒数会有所改善，发生落后的频率也会降低，同时在田老师的建议下，所有的slave db计划全部改用innodb puglin的引擎，希望通过利用MySQL自身性能的改进也能对目前问题的缓解有一定帮助。

此外，最近的几次故障中，也充分暴露了MySQL Proxy的一些问题，比如：后台slave db中如果有一台机器故障宕机后，便会影响整个业务，并不像手册里面说的会将故障机标注，同时从之前的配置中将其移除，又加上MySQL Proxy请求分布不均，我们通过netstat的监控脚本可以清楚看到转发到各个后端slave db的请求数，而又碰掉某一段时间proxy将所有的请求都发到故障机上，那么业务就杯具了。MySQL Proxy还有个问题就是，当master db某地时间因为负载或是真的宕机，那么proxy便会认为all backends down，这样的提示你会在php抛出的异常日志中发现，即便你的所有slave db都是正常的。

经过一系列的调整之后，目前这组DB趋于稳定，大家可以暂时消停一段时间，但随着业务量的增加问题还会逐渐出现，因为关于视图语句的改善，索引的调整，表结的合理性，这些都没有根本性的解决，所以问题仍在，只是暂时弱化，PK的道路还很漫长。