MySQL execution Status Analysis

MySQL execution Status Analysis

When you feel that MySQL performance is faulty, you will usually first check the current MySQL execution status and use show processlist to view it. For example:

The state column information is very important. First, let's look at the meaning of each column, and then look at the common state of state.

Meaning of each column

1. id

An identifier used when you want to kill a statement, such as mysql> kill 207;

2. user

Displays the current user. If it is not root, this command only displays SQL statements within your permission range.

3. host

Displays the port from which the statement is sent and can be used to track the problematic statement.

4. db

Displays the database to which the process is currently connected.

5. command

Displays the commands executed by the current connection, which are generally sleep, query, and connect)

6. time

Duration of this state, in seconds

7. state

Displays the status of the SQL statement using the current connection. The state is only a state in the execution of the statement, for example, the query must go through copying to tmp table, Sorting result, sending data and other statuses can be completed

8. info

This SQL statement is displayed. Due to the limited length, the long SQL statement is not displayed completely, but an important basis for determining the problematic statement

Common state Analysis

1. Sleep

It usually indicates that the resource is not released. If it is through the connection pool, the sleep status should be constant within a certain range, for example:

The Data Query time is 0.1 seconds, and the network output takes about 1 second. The original data connection can be released in 0.1 seconds. However, because the front-end program does not perform the close operation, the result is directly output, the database connection remains in sleep state until the result is displayed on the user's desktop.

2. Locked

The operation is locked. innodb is usually used to reduce the number of locked states.

3. Copy to tmp table

When indexes and existing structures cannot cover query conditions, a temporary table is created to meet query requirements, resulting in huge I/o pressure. Copy to tmp table is usually related to table connection queries, we recommend that you reduce associated queries or Optimize Query statements in depth. If the execution time of statements in this status is too long, other operations will be seriously affected. You can kill the operation.

4. Sending data

Sending data does not send data. It is a process for obtaining data from a physical disk. If you have a large number of results, you need to extract data from different disk fragments, if there are too many sending data connections, it is usually because the result set affected by a query is too large, that is, the query index is not optimized enough.

5. Storing result to query cache

If this status occurs frequently, use set profiling for analysis. If there is a large proportion of resource overhead in SQL (even if it is a very small overhead, check the proportion ), this indicates that the query cache contains a large number of fragments. You can use flush query cache to immediately clear the fragments. You can set the Query cache parameters as appropriate.

This article permanently updates the link address: