Mysql analysis-show PROCESSLIST details

Last time I made a voting system, it was stuck for a long time during the peak period of the activity, and the effect of temporarily increasing the bandwidth was not obvious.

Go to the server for analysis. A large amount of CPU in top is consumed on IO wait. All signs point to mysql, including slow log queries and checking the index structure, but I feel that the fundamental problem has not been found. Finally, in show During processlist, a large number of lock processes were found. Haha, the problem is obvious.

The voting table uses the mysiam engine. After the votes are counted, update locks the table. In addition, the server performance is generally slow, and the program does not degrade asynchronously when processing the voting page. The combination of factors leads to a very slow process. But the key factor was still found, it was mysql lock.

So, when problems with mysql performance are discovered, SHOW PROCESSLIST is still very effective in showing which threads are running, for example:

# Among them, the state column information is very important. First, look at the meaning of each column, and then look at the common status of state

The meaning of each column

1. id: An identifier, used when you want to kill a statement, for example, mysql> kill 207;

2. User: Display the current user. If it is not root, this command will only display the sql statements within your authority.

3. Host: Displays which IP and port this statement was sent from, which can be used to track the user who issued the problematic statement

4. db: Display which one this process is currently connected to

5. command: Display the executed command of the current connection, Generally it is sleep, query, connect

6, time: The duration of this state , the unit is seconds

7, state: Displays the status of the sql statement using the current connection, a very important column, state is just a statement A certain state during execution, such as a query, needs to be copied to tmp table, Sorting result, Sending data and other states can be completed

8. info: Display this sql statement. Because the length is limited, long sql statements are not fully displayed, but it is an important basis for judging problem statements

state Common state analysis

1, Sleep

## usually means that the resource has not been released. If it is through the connection pool, the sleep state 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, but because the front-end program does not perform the close operation and directly outputs the results, the results are not displayed on the user's desktop. Before, the database connection has been maintained in the sleep state

The operation is locked, usually using innodb can reduce the occurrence of locked state

When the index and existing structure cannot cover the query conditions, it will be created A temporary table is used to meet query requirements, resulting in huge I/O pressure. Copy to tmp table is usually related to join table queries. It is recommended to reduce related queries or optimize query statements in depth. If the execution time of statements in this state is too long, it will be seriously affected. For other operations, you can kill the operation at this time

##4. Sending data

Sending data is not sending data, it is the process of obtaining data from the physical disk. If you have a large impact result set, you need to extract it from different disk fragments. data, if sending There are too many data connections. Usually the impact result set of a certain query is too large, that is, the index items of the query are not optimized enough

5. Storing result to query cache

If this state occurs frequently, use set profiling analysis. If there is resource overhead in the overall SQL If the proportion of overhead is too large (even if it is a very small overhead, look at the proportion), it means that query There are many cache fragments. Use flush query cache to clean it up immediately. Query cache parameters can be set appropriately as appropriate

The above are the details of Mysql analysis-show PROCESSLIST For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!