[MySQL CPU]線上飆升800%,load達到12的解決過程

mchdba發表於2014-05-01

接到報警通知,負載過高,達到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,導致資料庫負載過高。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26230597/viewspace-1152554/,如需轉載,請註明出處,否則將追究法律責任。

相關文章