Analysis of the use of the MySQL database show processlist command

In actual project development, if we have a lot of pressure on the database, such as a large number of queries or inserts, especially multi-threaded inserts, we can kill some of the slow-executing SQLs. A commonly used command is show processlist

1. What is show processlist

show processlist: By checking the official website of MySQL, we can find that it mainly queries which threads are executing in the database. We can kill the slower threads (threads with larger time values). 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: command line, SQL statement, Navicat client, etc.

1) Command line: SHOW FULL PROCESSLIST\G

The execution 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 logs; 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) You can query the relevant information table in the database through SQL statements

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

3) You can view it through the Navicat tool. The following figure is a screenshot of the query using Navicat.

3. How to interpret show processlist

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

Id: The unique identifier of the thread connected to the MySQL server. You can kill the thread to terminate the connection.

User: The user of the current thread connecting to the database

Host: Shows which IP and port this statement is sent from. Can be used to track down the user who made the problematic statement

db: the database to which the thread is connected, or null if none

Command: Displays the command executed by the current connection, usually sleep, query, or connect.

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

State: Displays the status of the SQL statement using the current connection. This is a very important column. All the statuses will be described later. Please note that state is only a certain status in the execution of a statement. For example, a SQL statement, such as query, may need to go through the states of copying to tmp table, sorting result, and sending data before it can be completed.

Info: The SQL statement executed by the thread, or null if no statement is executed. This statement can be used to execute statements sent by the client or internally executed statements.

4. How to process the show processlist results

In the above steps, we can check the execution time of each thread and other information. Therefore, for threads with longer execution time, we can directly kill them by directly executing the kill ID number.

If you want to check the time longer 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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.