MySQL database show processlist instruction usage parsing

In the actual project development, if we have great pressure on the database, such as a large number of queries or inserts, etc. sql, especially multi-thread inserts, etc., for some slow sql, we can drop its kill, and the commonly used command is show processlist

1. What is show processlist

show processlist: By looking at the official website of mysql, we can find that it mainly queries which threads in the database are executing, and we can drop kill for slower threads (threads with large value of time). In addition, the results returned by show full processlist change in real time.

2. How to use show processlist

There are three ways to execute show processlist, through the command line, SQL statement, Navicat client, and so on.

1) Command line: SHOW FULL PROCESSLIST\ G

The implementation results are as follows:

mysql> SHOW FULL PROCESSLIST\G

*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL

*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
    I/O thread to update it
Info: NULL

*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
rows in set (0.00 sec)

2) Tables of related information in the database can be queried through the sql statement

select id, db, user, host, command, time, state, info from information_schema. processlist order by time desc

3) It can be viewed through Navicat tool. The following figure is a screenshot queried by Navicat.

3. How to interpret show processlist

The following is an interpretation of the results queried by using this command.

Id: The only identity of a thread linking to an mysql server, which can be terminated by kill.

User: The user of the current thread linking database

Host: Shows which port on which ip this statement was issued from. Users that can be used to track out problem statements

db: Thread linked database, null if not

Command: Displays the current connection of the execution of the command, 1 is hibernate or idle (sleep), query (query), connect (connect)

Time: The time, in seconds, that the thread is in its current state

State: Displays the status of the sql statement using the current connection. It is an important column. All the states will be described later. Please note that state is only one state in the execution of the statement, and one sql statement. For example, queried copying to tmp table, Sorting E110EN, Sending data may be completed

Info: An sql statement executed by a thread, or null if no statement is executed. This statement can make the execution statement sent by the client also be an internal execution statement

4. How to deal with show processlist results

In the above steps, we can find the execution time of each thread and other information, so for threads with long execution time, we can directly drop their kill and directly execute kill Id number.

If you want to check for more than 5 minutes, you can splice and execute the following sql

select concat('kill ', id, ';') from information_schema.processlist where command != 'Sleep' and time > 5*60 order by time desc