Record the downtime caused by a subquery

At a.m. on July 22, May 10, a text message was sent to the police. The XXX Business Database went down and was busy with various problems, so it was only possible to troubleshoot it at night.

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

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/224P5CP-0.jpg "title =" 1.jpg"/>

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 have been performed manually. It was executed by logging on to SQLYOG from stepping stone 98.149,

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/224P5O45-1.jpg "title =" 2.jpg"/>

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, and it took 11 minutes and 26 seconds to slow down as the table grows ),........................

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/224P5K43-2.jpg "title =" 3.jpg"/>

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.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/224P5N00-3.jpg "title =" subquery .jpg "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/224P5G21-4.jpg "title =" 4.jpg"/>

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 BKJIA's new version is 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, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1197663