[MySQL CPU]线上飙升800%,load达到12的解决过程
接到报警通知,负载过高,达到800%,load也过高,有11了。
MySQL版本号为5.6.12-log
1 top 之后,确实是mysqld进程占领了全部资源。
2 查看error日志,无不论什么异常
3 show eninge innodb status\G,没有死锁信息。
4 show full processlist;
没有耗时很大的慢sql再跑。看并发,当前的线程总数量也才30个左右。
5 查看iostat,读写正常。
究竟是什么问题呢?查看slow log,发现例如以下SQL,频繁运行,耗时在5秒之间,explain有Using join buffer (Block Nested Loop)
mysql> explain select web_page_object.web_page_object_id, -> web_page_object.object_id, -> web_div_name,web_page_object.position_sort,web_page_object.end_time,om1.label,om1.file,jump_url,om2.label as label1,om2.file as file1 -> from web_page_div,web_page_object,object_media as om1,object_media as om2 -> where web_page_div.id=web_page_object.web_page_div_id -> and web_page_object.object_media_id=om1.object_media_id -> and web_page_div.web_page_id=1200 -> and if(web_page_object.object_media_id1=0, -> web_page_object.object_media_id=om2.object_media_id, -> web_page_object.object_media_id1=om2.object_media_id) -> -> and ‘2014-05-01 15:09:49‘>=start_time -> and ‘2014-05-01 15:09:49‘<= end_time -> -> and object_status=0 -> order by web_page_div.id,web_page_object.position_sort; +----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+ | 1 | SIMPLE | web_page_object | ALL | object_media_id_index | NULL | NULL | NULL | 51165 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | web_page_div | eq_ref | PRIMARY,idx | PRIMARY | 4 | db_jiapin.web_page_object.web_page_div_id | 1 | Using where | | 1 | SIMPLE | om1 | eq_ref | PRIMARY | PRIMARY | 4 | db_jiapin.web_page_object.object_media_id | 1 | Using where | | 1 | SIMPLE | om2 | ALL | NULL | NULL | NULL | NULL | 74759 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+ Using join buffer (Block Nested Loop)
看SQL是where后面的if推断引起的,拆分if之后,就正常了,SQL耗时不到0.1秒。数据库load也降下来了。
还记录曾经碰到的
(Block Nested Loop)的案例是 join后面的on条件里面有or推断。
也会引起Block Nested Loop,导致数据库负载过高。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。