MySQL SHOW PROCESSLIST

MySQL Show Processes

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

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.

Important Points to Understand MySQL show processes

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 process to Check

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
  1. Row

Id: 1 User: system user Host: db: NULL Command: Connect Time: 1030455 State: Waiting for master to send event Info: NULL
  1. 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
  1. 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
  1. 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
  1. Row

Id: 3123 User: stefan Host: localhost db: apollon Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST

Example of show processlist command

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)

MySQL SHOW FULL PROCESSLIST

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
The author
Bilal Mohammed

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.