How to use show processlist instructions in MySQL database

How to use the show processlist instruction in the MySQL database? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

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 kill them out for slower threads (threads with large 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, through the command line, SQL statements, Navicat clients, and so on.

1) Command line: SHOW FULL PROCESSLIST\ G

The implementation results are as follows:

Mysql > SHOW FULL PROCESSLIST\ gateway * 1. Row * * Id: 1User: system userHost:db: NULLCommand: ConnectTime: 1030455State: Waiting for master to send eventInfo: NULL** 2. Row * * Id: 2User: system userHost:db: NULLCommand: ConnectTime: 1004State: Has read all relay log Waiting for the slave thread to update itInfo: NULL** 3. Row * * Id: 3112User: replikatorHost: artemis:2204db: NULLCommand: Binlog DumpTime: 2144State: Has sent all binlog to slave Waiting for binlog to be updatedInfo: NULL** 4. Row * * Id: 3113User: replikatorHost: iconnect2:45781db: NULLCommand: Binlog DumpTime: 2086State: Has sent all binlog to slave Waiting for binlog to be updatedInfo: NULL** 5.row * * Id: 3123User: stefanHost: localhostdb: apollonCommand: QueryTime: 0State: NULLInfo: SHOW FULL PROCESSLISTrows in set (0.00 sec)

2) tables with relevant information in the database can be queried 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. For example, the following image is a screenshot queried using Navicat.

3. How to interpret show processlist

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

Id: unique identification of the link mysql server thread, which can be terminated through kill.

User: the user that the current thread links to the database

Host: shows which port of the ip this statement is issued from. Can be used to track the user with the problem statement

Db: the database linked by the thread, or null if it does not exist

Command: the command that displays the execution of the current connection, which is generally dormant or idle (sleep), query (query), connection (connect)

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

State: displays the status of the sql statement using the current connection. Important columns will have descriptions of all the states later. Please note that state is only a certain state in the execution of the statement. A sql statement has been queried as an example, and it may need to be completed through states such as copying to tmp table,Sorting result,Sending data.

Info: a sql statement executed by a thread, or null if no statement is executed. This statement enables the execution statement sent by the client to be internally executed.

4. What to do with the show processlist result

In the above steps, we can find information such as the execution time of each thread, so for threads with a long execution time, we can kill them directly and execute the kill Id number directly.

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's answer to the question on how to use the show processlist instruction in the MySQL database is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.