Dry share: MySQL's "diamond" snapping storm

Snapping up diamonds is not uncommon, the rare thing is that money is not earned, things happen on May 20, 2015, good days naturally without the participation of businessmen. You can restore the scene, to solve the ideas dedicated to you, please enjoy show time,everybody~

1, the cause of optimization and work preparation

May 20, 2014 3:40 P.M. received the other party does not want to reveal the name of the "king Sledgehammer" leader of the phone, the other too hectic only provide a slow Web site access information, at that time Bo master that Heart 10,000 xx Pentium and past, as the saying goes, wine meat through the intestines, the money will be for people to eliminate disaster.

For bloggers to visit the site slow, first can not disorderly, first think of is to see the web, first look at the static, if the static OK to see the dynamic, if not OK to look at the storage, and then do not see whether the length of access to the DB is normal. This is where the reason can be positioned. There will be no other reason. If you are too food, then you can put my ideas back, I believe that you are a good help, at this time and the other side of the communication is more likely to obtain information, but the other side do not understand, had nothing to do, and the other party to negotiate the relevant responsibility immediately after the server (I part-time XX Drilling World Group Technical adviser).

With personal experience to view the Web load is not high, static access is normal, because the online activity is in progress, a minute late to the merchant is a loss, at this time can not do many system troubleshooting, directly to determine whether it is the problem of the backend db? View the load with the login db. found that the DB load is not normal, there is no other judgment (what IO look ah, memory look ah, Nic look ah, then see the company has closed. ), the Emergency recovery problem is to maximize the recovery problem , find the problem immediately solve the problem. At this point the database has a slow query.

1================2015 Year May 20 -: -: 08th load is as follows: ================2[[Email protected] ~]$ Uptime3  -: -: $Up122Days +:Wuyi,1User, load Average:6.44,5.76,5.384 5[[Email protected] ~]$ Uptime6  -:Wuyi: -Up122Days +: A,1User, load Average:8.01,6.30,5.58

2, judge the problem

Show full processlistwith login database; This tool is a must-have for several years of operation. Don't say you won't. If not, I should have read my blog .

Even after grasping two times found that this pile of things do not move ah, the front row of the update is locked, want to write not to go in. The select is too much to read.

1 mysql> show processlist; 2 +----+-------------+-----------+------+---------+------+--------------------------------------------- --------------------------------+------------------+

3, positioning to optimize the statement

Return to look at the following query statements are queried by three criteria. The statement to be optimized is then the most frequently occurring statement under Select

↑↑↑ query statements as above ↑↑↑

Then grabbed a command explain, repeatedly confirmed after adding sql_no_cache do not let it go cache and repeatedly confirmed, the final judgment of the statement did not index or go to index, a total of more than 73,000 data viewing time is amazing.

1 Select  from where ader=' **_**-jazz_flash' and dateline='**_**' and pos='**_**';

At this point, you see that the indexes and indexes that are likely to go are nonexistent. Run independently in more than 70,000 statements

1 Possible_keys:null 2 3     key:null　　　　45  rows:71328  #接近全盘扫描 　　　　

I remember this machine is a Dell server 2850 is a very old server, but this is obviously not a hardware problem, with the other side of the supervisor, there is no one to optimize the machine, while the phone asked to see, to confirm their ideas, use Show view table structure show CREATE TABLE * * _**_detai\g, sure enough, in addition to the primary key index, an index is not established (for this old dilapidated server proud, it unexpectedly carried so long to give younger brother to worship).

4. Solutions

It's a rip-off. We continue, this time has taken 3 minutes, the rules to establish the index I believe we all know, here but more explanation, a look at the summary. When you get the above conclusion, see which field column has a large number of unique values. Use SELECT COUNT (Distinct XX) from **_**_detai, the above three statements are viewed with a second-order command, and finally found that the values of the three columns are not high for 766/531/154, because one column is the date, its unique value is the least, and the secondary column does not understand.

Then use SELECT COUNT (*) from **_**_detail; command to see the total amount reached more than 70,000.

According to the above situation, and the query statement inside is also very special is the equal sign. In this case, indexing is easy to follow. In this case, consider going to the Federated index. Based on the above information and consulting the development manager other statements, create the following index:

1 mysql> CREATE index d_a_p on **_**_detail (Dateline,daer), Azz (ten), POS ( ））；

The query order of the statements is the development manager of the inquiry, because the federated index has the attribute of the prefix, so when the index is determined it is not created directly, but is negotiated with the development manager, and a few read requests need to be killed at this time. Select several in front. Show Proacesslist;update is considered according to business requirements. use caution as to how to kill. Kill+id believe that few will not.

The index is established and then used explain to see if the index is in effect, and then also use the Select+sql_no_cache parameter to not go through the cache query statement. It was found that only 12 statements were scanned at this point, and the query time was few and far between.

Use show proacesslist again; view MySQL threads, almost invisible. The effect is obvious.

　　

5. Solve the effect

After the optimization of the load, has been from the previous 6.x, 8.x slowly down to 2.x,1.72, five minutes later to 0.07, 0.21 of the normal

1[[Email protected] ~]$ Uptime2   -: -: theUp -Days +: in,2Users, load average:2.40,4.62,5.093[[Email protected] ~]$ Uptime4   -: -: inUp -Days +: in,2Users, load average:1.72,4.32,4.985[[Email protected] ~]$ Uptime6   -: -: -Up -Days +: in,1Users, load average:1.66,4.26,4.957[[Email protected] ~]$ Uptime8   -: to: -Up -Days +: *,1Users, load average:0.07,1.39,3.429[[Email protected] ~]$ UptimeTen   -: to: *Up -Days +: $,1Users, load average:0.21,1.38,3.40

6. Summary

Problem determination + resolution 10 minutes or less

Optimization judgment + post-observation 15 minutes or so

The total time to solve this problem is about 25 minutes.

This problem due to the other side of the MySQL database optimization is not in place, the company does not have the relevant technical personnel, routine maintenance work can not be carried out normally, resulting in unexpected state access anomalies. In order to ensure that the server will work properly, after the optimization is completed, add the following parameters to record the slow query statement under the configuration file (MY.CNF).

1 long_query_time =2 #<== more than 2 seconds, log in log.  23 log_queries_not_using_indexes #<== does not walk the index of the statement, recorded in the log.  45 log-slow-queries =/data/3306/slow.log #<==log file

However, indexing is based on the fact that the production scenario, the number of data in the table and the peak period cannot be indexed, for example: 3 million records. Because the federated index is used in this problem resolution, the attribute of the Federated index is the prefix, which is different from the other indexes, so it is more prudent to create and you need to negotiate with development to create the rules. Otherwise, the index is invalid.

The optimization of MySQL has drawn the following conclusions from this problem solving process:

1, emergency catch slow query SQL statement:

Log in to Database

Show full prcesslist;

2, Rainy day: Important not urgent: analysis slow query log. (The generated log method has specific parameters in the above summary)

Analyze slow query SQL statements, send emails to relevant workers on a daily basis, core development, Advanced OPS, or DBA

Daily cutting slow query log, to re-analyze and send to everyone.

Cutting method:

1) MV, relaod process. 2) cp,> Empty

2) Using Timed tasks

The above share content to this end, if have the question welcome to send the mail to [email protected] Discuss the exchange, hoped to be helpful to everybody.

Dry share: MySQL's "diamond" snapping storm