One time MySQL consumes CPU high processing process

This morning in the formal service deployment of new code, over a period of time, server load alarm, CPU usage alarm, Login server query, found that MySQL led to high CPU usage, so show processlist query a bit, see a lot of threads in sending The state of data and lock is the operation of a table in a select database.

The SQL statements for sending data were then copied and executed once, and it was found that the time to execute them was too long and then explain analyzed.

There is no primary key and index in the condition of a select, and because that field in the query condition is unique, the field is indexed with the development plan, so the SQL of select is not in the state of sending data according to this field;

There is a condition in a select that is based on the primary key query, but also in the sending data state, but this table uses a composite primary key, but the composite primary key is only the first field of the index, the second field is non-indexed, so the query speed is much slower.

The results of the test query based on the primary key field and the second field are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/71/9B/wKioL1XUtIezrmHRAACDgefRG2U000.jpg "style=" float: none; "title=" 2.png "alt=" Wkiol1xutiezrmhraacdgefrg2u000.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/71/9E/wKiom1XUsnqQboygAACDZ4qPLB8515.jpg "style=" float: none; "title=" 1.png "alt=" Wkiom1xusnqqboygaacdz4qplb8515.jpg "/>

So you need to add an additional index to the second field.

Refer to the following article about the index of the composite primary key

http://www.oschina.net/question/12_38140

The CPU usage decreased a lot after the index was increased, but there was still a problem. So we go back to the version, in the test suit and then do the concurrency test, the problem is fully resolved and then deployed to the formal service. Turn on the slow query log in the test suit and optimize all the inefficient SQL execution.

Slow query log open articles

Http://www.cnblogs.com/echo-something/archive/2012/07/25/2607771.html

The mysql5.1 version supports Method 2, which dynamically turns the log on or off.

One time MySQL consumes CPU high processing process