Remember that MySQL causes high CPU usage issues

The last few days to update the program, testing the function, found that the application login is very slow, thought to be a problem with the application, but wait for a long time after the discovery of the application login is OK. Check the application log and discover that the data pool is full and cannot create more connections. Then quickly log in to the database check,

1, top view found that the MySQL process CPU utilization is as high as 1300%, I le go, what a ghost!

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/89/3E/wKiom1gNsLLwh6qlAAAef-bHiU4191.png-wh_500x0-wm_3 -wmp_4-s_3727420077.png "title=" qq picture 20161024145725.png "alt=" Wkiom1gnsllwh6qlaaaef-bhiu4191.png-wh_50 "/>

2. Log in to MySQL view thread

# show Processlist

Found a large number of connections from the application server, and all are query state. Consider the slow query log which SQL runs for a long time, causing the connection to remain in query state.

3. Turn on slow query log

# Show variables like "%slow%"; --see if the slow query log is turned on

# set global slow_query_log=on; --Turn on slow query log

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/89/3F/wKiom1gNtamwRckWAAArle1Uruc115.png-wh_500x0-wm_3 -wmp_4-s_1641193388.png "title=" qq picture 20161024151923.png "alt=" Wkiom1gntamwrckwaaarle1uruc115.png-wh_50 "/>

4. Open the slow query log for a period of time to view the slow query log

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/89/3F/wKiom1gNtr2iBlyZAACir4sPfwc632.png-wh_500x0-wm_3 -wmp_4-s_1738284705.png "title=" qq picture 20161024152405.png "alt=" Wkiom1gntr2iblyzaacir4spfwc632.png-wh_50 "/>

Mom, the SQL run time is basically around 150s, this application can get up just really god. This SQL is also no one ~

So what now? Build an index? Optimize SQL statements?

Think of the optimization SQL statement is also coercion, follow up on the SQL statement optimization bar. First try creating an index look, after creating the index, we find that the CPU usage is down and the app login is OK.

5. Use MySQL to bring the slow query log analysis tool Mysqldumpslow to see which statements are more serious

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/89/40/wKiom1gNuSiA_V2HAACir4sPfwc614.png-wh_500x0-wm_3 -wmp_4-s_1769000994.png "title=" qq picture 20161024152405.png "alt=" Wkiom1gnusia_v2haacir4spfwc614.png-wh_50 "/>

Remember that MySQL causes high CPU usage issues