MySQL SHOW PROCESSLIST
Show [FULL] PROCESSLIST Command
The MySQL process list displays the operations performed by the set of processlist full keywords which is executed in the MySQL server. THE SHOW PROCESSLIST statement or show processlist full keyword is a single source of process information.
![Show [FULL] PROCESSLIST Command](/static_content/vpsserver_com_frontend/img/803a217a-b0ac-44b1-bf78-dc0879db40bd.png)
Important Points to Understand MySQL show processes
An alternative execution of MySQL for the show processlist command is based on the "Performance Scehema Processlist Table" which does not require a mutex unlike the default show processlist execution.
If you have process privilege, you can see all the threads belongs to other users. Alternatively, a nonanonymous person can access information without process privilege related to threads but not the threads related to the other person.
The process to Check
The show processlist command shows the foremost 100 characters of every statement in the info field without the FULL keyword. A user needs to track down the query that is slowing done the query. To cater for this issue, it is required to look at the table, as it will show running MySQL queries
The show processlist statement or show processlist command is useful when you get an error message "too many connections".
Threads can be killed with the kill statement.
Example of show processlist command:
mysql> SHOW FULL PROCESSLIST\G
-
Row
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
-
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
-
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
-
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
-
Row
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
SHOW PROCESSLIST output has the following columns
1. Id
The connection identifier. This is the exact value shown in the ID column of the INFORMATION_SCHEMA processlist table, shown in the PROCESSLIST_ID queue of the Performance Schema threads table, and replaced by the CONNECTION_ID() command in the thread.
2. User
The MySQL user who allocated the statement. A significance of the system user directs to a nonclient thread generated by the server to manage tasks internally.
3. Note
A User value of the system user is distinguishable from the SYSTEM_USER privilege.
4. Host
The hostname of the client giving the statement. The hostname for TCP/IP links is registered in host_name:client_port format to make it more manageable to decide what is going on.
5. DB
The default database for the thread if none has been selected.
6. Command
The type of command the thread is conducting on behalf of the client, or Sleep if the session is inactive.
7. Time
The time is depicted as seconds that the thread has been in its current state. For a replica SQL thread, the value is the digits of seconds between the timestamp of the previous replicated occurrence and the real-time of the replica host.
8. State
An activity, occurrence, or state that means what the thread is doing. If a thread stays in a provided state for many seconds, there might be trouble that ought to be analysed.
9. Info
The statement the thread is achieving, or NULL if it is running no statement.
What does the 'Show Processlist' Command Accomplish in MySQL?
The ‘SHOW processlist’ command can be employed to depict the running processes of running threads correlated to only your own threads and MySQL account. A user can see all running threads if the user has process privileges. It displays which threads are operating.
The following is the command.
mysql> SHOW processlist;
It will display the id user host db as a result.
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 968 | Waiting on empty queue | NULL |
| 9 | root | localhost:50255 | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
The output will be different if we change the default database. Now, let us try the MySQL command.
mysql> SHOW processlist;
The output of the command will be:
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 1148 | Waiting on empty queue | NULL |
| 9 | root | localhost:50255 | business | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
Frequently Asked Questions
What is the MySQL SHOW PROCESSLIST command?
The MySQL SHOW PROCESSLIST command is a MySQL command used to display a list of active processes in the MySQL database. It provides information about each running process, including the query being executed and the amount of time the process has been running.
How do I execute the MySQL SHOW PROCESSLIST command?
To execute the MySQL SHOW PROCESSLIST command, open the MySQL command-line client and enter the following command: SHOW PROCESSLIST;. This will display a list of active processes in the MySQL database.
What information does the MySQL SHOW PROCESSLIST command provide?
The MySQL SHOW PROCESSLIST command provides information about each running process, including the process ID, user, host, database, command, state, and query.
How do I filter the output of the MySQL SHOW PROCESSLIST command?
To filter the output of the MySQL SHOW PROCESSLIST command, you can use the WHERE clause to specify a condition. For example, to only show processes that are running a particular query, you could use the following command: SHOW PROCESSLIST WHERE Info like '%<query>%';. Replace <query> with the text of the query you want to filter on.
How do I terminate a running process with the MySQL SHOW PROCESSLIST command?
To terminate a running process with the MySQL SHOW PROCESSLIST command, you can use the KILL command followed by the process ID. For example, to terminate a process with ID 1234, you could use the following command: KILL 1234;.
Can the MySQL SHOW PROCESSLIST command be used to troubleshoot slow queries?
Yes, the MySQL SHOW PROCESSLIST command can be a useful tool for troubleshooting slow queries. By identifying queries that are taking a long time to execute, you can take steps to optimize your database performance and improve query response times.
Bilal Mohammed is a SOC analyst and cybersecurity expert who is passionate about making the internet safer. He has expertise in penetration testing, networking, network security, web development, and security operations center (SOC) services. In his spare time, he looks for security and design vulnerabilities to protect against attacks from hackers.
