MySQL Execution state analysis

When you feel a problem with MySQL performance, you will usually look at the current execution state of MySQL and use show processlist to view it, for example:

Where the State column information is very important, first look at the meaning of the columns, and then look at State common

The meaning of each column

1. Id

An identity that you want to kill by using a statement, for example mysql> kill 207 ;

2. User

Show current user, if not root, this command displays only the SQL statements within the scope of your permission

3. Host

Shows the port from which IP the statement was issued, and the user that can be used to track the problem statement

4, DB

Shows which database this process is currently connected to

5. Command

Displays commands for the execution of the current connection, typically hibernation (sleep), query, connection (connect)

6. Time

The time that this state lasts, the unit is seconds

7. State

Displays the state of the SQL statement using the current connection, a very important column, State is only one of the statement execution status, such as query, need to pass copying to TMP table,sorting result,sending data and other states to complete

8. Info

Display this SQL statement, because the length is limited, so the long SQL statement is not complete, but an important basis for judging the problem statement

State analysis of Common states

1. Sleep

Typically the resource is not released, and if it is through a connection pool, the sleep state should be constant within a certain number of ranges, for example:

Data query time is 0.1 seconds, and the network output needs about 1 seconds, the original data connection in 0.1 seconds can be released, but because the front-end program did not perform close operation, direct output results, then the results are not displayed in front of the user table, the database connection has been maintained in the sleep state

2, Locked

The operation is locked, usually using InnoDB can reduce the locked state of the production very well

3. Copy to TMP table

When the index and the existing structure cannot cover the query criteria, a temporary table is established to meet the query requirements, resulting in large I/O pressure the copy to TMP table is usually related to a query with a table, it is recommended to reduce the associated query or drill down to refine the query statement, if the statement that appears in this state takes too long, Will seriously affect other operations, you can kill the operation at this time

4. Sending data

Sending data is not a process to get data from a physical disk, if you have more impact result sets, you need to extract data from different disk fragments, if the sending data connection is too large, it is often the result set of a query is too big, that is, the index of the query is not optimized

5. Storing result to query cache

If this state occurs frequently, using set profiling analytics, if there is an excessive amount of resource overhead in the SQL overall overhead (that is, very small overhead, see scale), then the query cache fragment is more fragmented, using flush query cache instant cleanup, and the query cache parameter can be set appropriately as appropriate

MySQL Execution state analysis