[MySQLCPU] solution for online climbing of 800% and load of 12

Source: Internet
Author: User
[MySQLCPU] the online traffic soared by 800%. An alarm was triggered when the load reached 12. The load reached 800%, and the load was too high. 11 times. After MySQL is 5.6.12-log1top, The mysqld process occupies all resources. 2. view the error log without any exceptions. 3. showeningeinnodbstatusG. No Deadlock information.

[MySQL CPU] the online traffic soared by 800%. An alarm was triggered when the load reached 12. The load was too high, it reached 800%, and the load was too high. 11 times. After MySQL is 5.6.12-log 1 top, the mysqld process occupies all resources. 2. Check the error log. No exceptions. 3. show eninge innodb status \ G. No Deadlock information.

[MySQL CPU] online surge of 800%, and the load reaches 12.

After receiving an alarm, the load was too high, reached 800%, and the load was too high, with 11 errors.

MySQL version 5.6.12-log


After 1, mysqld processes occupy all resources.


2. View error logs without any exceptions.


3 show eninge innodb status \ G, no deadlock information.


4 show full processlist;

There is no time-consuming slow SQL statement to run again. Depending on concurrency, the current total number of threads is about 30.


5. Check iostat. The read and write operations are normal.


What is the problem? View the slow log and find that the following SQL statements are executed frequently, which takes 5 seconds. The explain statements include the Using join buffer (Block Nested Loop)

mysql> explain select web_page_object.web_page_object_id,    ->     web_page_object.object_id,    ->     web_p_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_p,web_page_object,object_media as om1,object_media as om2    ->     where web_page_p.id=web_page_object.web_page_p_id    ->     and web_page_object.object_media_id=om1.object_media_id    ->     and web_page_p.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_p.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_p    | eq_ref | PRIMARY,idx           | PRIMARY | 4       | db_jiapin.web_page_object.web_page_p_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)


Check that the SQL statement is caused by the if statement after where. After the SQL statement is split into if statements, the SQL statement takes less than 0.1 seconds. The database load is also lowered.


It also records the previously encountered

(Block Nested Loop) the case is that there is or judgment in the on condition after join.
It will also cause Block Nested Loop, resulting in high database load.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.