Troubleshoot MySQL processes in Linux systems that consume CPU 300%

Today received a colleague's call, said the public account of the game can not login, users can not play, trouble to solve as soon as possible.

1, visit IDC computer room to view the server traffic map, traffic is normal, troubleshooting and traffic independent.

2, the login public account of the game server found that the system is too high, the MySQL process CPU utilization of more than 300%.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6F/94/wKiom1WhD27DfMuOAACSmwBjW2A094.jpg "title=" QQ picture 20150711204218.png "alt=" Wkiom1whd27dfmuoaacsmwbjw2a094.jpg "/>

In order to quickly solve the problem directly restart the MySQL service, then check the Sysql process CPU usage is still high. The failure has not been resolved, continue to look for reasons.

3. Log in to the MySQL database, execute show processlist, view the currently executing SQL statement, and find several tables with "sending data" status and SQL execution time too long.

4, notify the developer to check whether these tables have increased the index, developers incredibly unable to handle, no language ...

5, waiting for developers to encore time to continue to query the solution. Add the following parameters to the MySQL configuration file my.cnf:

Tmp_table_size = 300M

Query_cache_size = 1024M

Hope that by increasing the temporal table and query cache size to solve the problem, the increase of these two parameters, found that the CPU usage has decreased, has a certain effect, but still more than 100%. The game is still slow to open.

6, the spirit of the failure of unequal, early to solve the problem, users can continue to play games, users will not have the idea of complaints. Continue to log in to MySQL, execute Show index from tablename, query the "sending data" of several tables, found that did not increase the index. At this time this kind of want to split the feeling of people.

7, the implementation of the statement to increase the index, the appearance of "sending data" of several tables indexed.

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

After restarting the MySQL service, the CPU utilization of the MySQL process returns to normal, and the game in the public account can log in normally, and the fault is resolved. Notify colleagues to verify.

This incident took 2 hours from the beginning of the problem to the end of the problem, and every day with the developer, the table that needs a large number of queries must be indexed, must be indexed, but still appear because no index caused the failure of the event. This shows how important it is to standardize the developer's work habits and standardize the development process.

Take this record of the whole process of failure, but also hope to give friends a solution to the problem of ideas for reference.

This article is from "Happy Fish" blog, please make sure to keep this source http://01000.blog.51cto.com/2410614/1673296

Troubleshoot MySQL processes in Linux systems that consume CPU 300%