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.

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

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

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)