Failure case: A subquery causes service to crash

Source: Internet
Author: User

I also know before MySQL 5.6 subquery is best to rewrite the way to join, but before the understanding is not deep enough to see the implementation plan is OK, until today the main library hang ....

Phenomenon: The SQL execution frequency is not very high, about 10s once, the first two days is good, About 1.5s can produce results, before you realize the seriousness of the problem, until the end of the afternoon the entire MySQL crash, response time is very long, the performance of the SQL execution time is very long, into nearly 100s, and each of the query connection will consume about 1 cores of the cpu,20 to occupy 20 cores, seriously affect the normal execution of other SQL

The state item in show Processlist is always shown as preparing,profile This SQL is the same.

Problem SQL and resolution plan:


Look at the situation SQL has gone index, scan the number of rows is not much, a single table record is 2w line, the implementation time 100s;

After the stop slave on the idle Slave, the problem is still determined to be caused by the SQL;


Workaround: Change it to join, or upgrade to 5.6 to resolve, the MS level returns results



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.