欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

[MySQL CPU]线上飙升800%,load达到12的解决过程_MySQL

程序员文章站 2022-03-16 22:20:06
...
接到报警通知,负载过高,达到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' 	->	 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,导致数据库负载过高。