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的道路还很漫长。

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

3 thoughts on “mysqld服务器CPU/IOWAIT瞬间出现峰值的问题

  1. where 后的字段不一定按照顺序最左匹配,mysql的优化引擎会自动调整顺序以使用符合索引。

  2. Pingback: 用MySQL Slow Log解决MySQL CPU占用高的问题 | 极客521

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>