Application crash caused by MySQL optimization script (analyze)

When I first walked into the door of the company in the morning and walked to my desk, my development colleagues worried and said, "You can come!

Me: What happened?

Developer: XX database is dead!

Me: Very surprised! This database has been running very well. How can it die? What's more, I have not received any monitoring alarms?

Don't worry, wait for me to connect up remotely.

Log on to MySQL and check the status: show processlist;

Then, we can see that at least one thousand queries have been being executed. Based on previous experience, there was a lock, which caused the query to be blocked. Therefore, the application crashed, a timeout error is returned!

Take a closer look at the status of a single SQL query, most of which are:... Query26037Waiting for table flushSELECT .......

Pay attention to the keywords in the red font. The explanation on the official website is:

Flushing tables

The thread is executing flush tables and is waiting for all threads to close their tables.

That is to say, the thread executes the refresh table operation and waits for all threads to close the tables they occupy.

An SQL statement that has been executed for a long period of time has been found. It has been executed for about 10 hours and no results have been found yet.

However, this should not cause the problem of flush tables.

After kill the SQL thread, the system slowly restores the normal query status.

After the brute force processing is complete, you can view the system logs and start to find the cause. Suddenly, it is the weekend.

Yeah, weekend !!

What will happen on weekends?

There was an optimization script task executed over the weekend!

This optimization script uses analyze to analyze each table. analyze collects statistical information of the table.

Mysql detects that the corresponding table has been modified. The flush operation, close, and reopen tables must be performed.

It can be inferred that the execution time of the SQL statement is too long and the optimization script task is also started, if you perform analyze on the tables occupied by SQL, the tables must be closed and reopen. However, the SQL statement is too bad to be executed and cannot be used to release the table. So that the subsequent SQL statements will have to wait in queue.

As long as the SQL statement is killed, the table is closed, and the continued SQL statement re-opens the table, which is normal!

I made a test according to the inference. First, I manually executed the bad SQL and waited for a while without looking for any signs of results. At this time, I used analyze table table_name;

Show processlit check the status! In red box

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/140207/22415225P-0.jpg "title =" 1.jpg" alt = "wKioL1LkuZqzmw-5AAPWQXlp6PY602.jpg"/>

The experiment proves that it is a problem caused by analyze, but it is not the main problem.

Therefore, the number of developers and SQL needs to be improved for optimization. Done!

This article is from the "Shadow Knight" blog, please be sure to keep this source http://andylhz2009.blog.51cto.com/728703/1354890