Record the downtime caused by a subquery

At this morning (July 22, May 10), I received a text message and an alarm. The XXX Business Database went down. As I have been busy with various problems today, I was able to troubleshoot it at night. Let's take a look at the alarm information, starting from, the Service

At this morning (July 22, May 10), I received a text message and an alarm. The XXX Business Database went down. As I have been busy with various problems today, I was able to troubleshoot it at night. Let's take a look at the alarm information, starting from, the Service

At this morning (July 22, May 10), I received a text message and an alarm. My website space and XXX Business Database went down. I was busy with various problems today, so I was able to troubleshoot it at night.

Let's take a look at the alarm information first,

From onwards, the SWAP partition of the server will trigger an alarm, and then the insufficient memory will trigger an alarm, and then the memory will be exhausted and HANG will die, causing the machine to crash.

I analyzed the slow log. As a result, a statistical SQL statement directly killed the server.

This must be performed manually. The VM logs on to SQLYOG from the stepping stone host 98.149 and runs the VM in the U.S. space,

It takes 170 seconds, and it is still running on the master database and a machine with services, resulting in Table lock. Other processes keep waiting, and the accumulation increases, directly run the server.

In the evening, I executed this SQL statement again on the slave database, which took 11 minutes and 26 seconds (as the table grows, it slows down ),........................

The performance of MySQL5.1 and MySQL5.5 sub-queries is quite poor. As a spam fighter, you must use the table connection method to optimize this SQL statement unless MySQL5.6 is just launched this year.

It takes only 0.01 seconds to get the result.

Therefore, I would like to remind you that such statistical SQL statements are complex and never run on the master database. The current performance of subqueries is poor.

(Note: The image size after 51CTO's new version is very small. If you want to see a large image, download the compressed file in the attachment .)

This article is from the "hechun's technical column" blog. Be sure to keep this source