Skip navigation links
12.4.5.28 SHOW PROFILE Syntax »
Section Navigation      [Toggle]

12.4.5.27. SHOW PROCESSLIST Syntax

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

MySQL Enterprise.  Subscribers to MySQL Enterprise Monitor receive instant notification and expert advice on resolution when there are too many concurrent processes. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

This statement is very useful if you get the “too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

Threads can be killed with the KILL statement. See Section 12.4.6.3, “KILL Syntax”.

Here is an example of what SHOW PROCESSLIST output looks like: 

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
5 rows in set (0.00 sec)

The columns have the following meaning:

  • Id

    The connection identifier.

  • User

    The MySQL user who issued the statement. If this is system user, it refers to a nonclient thread spawned by the server to handle tasks internally. This could be the I/O or SQL thread used on replication slaves or a delayed-row handler. unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done. For system user, there is no host specified in the Host column.

  • Host

    The host name of the client issuing the statement (except for system user where there is no host). SHOW PROCESSLIST reports the host name for TCP/IP connections in host_name:client_port format to make it easier to determine which client is doing what.

  • db

    The default database, if one is selected, otherwise NULL.

  • Command

    The type of command the thread is executing. Descriptions for thread commands can be found at Section 7.5.6, “Examining Thread Information”. The value of this column corresponds to the COM_xxx commands of the client/server protocol. See Section 5.1.6, “Server Status Variables”

  • Time

    The time in seconds that the thread has been in its current state.

  • State

    An action, event, or state that indicates what the thread is doing. Descriptions for State values can be found at Section 7.5.6, “Examining Thread Information”.

    Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.

    For the SHOW PROCESSLIST statement, the value of State is NULL.

  • Info

    The statement that the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL p1() statement executes a stored procedure p1(), and the procedure is executing a SELECT statement, the Info value shows the SELECT statement.


User Comments

Posted by Daniel Schneller on August 6 2007 8:06am[Delete] [Edit]

The "Time" column will show almost the same value as "show slave status" for seconds_behind_master in replication scenarios (probably depending on replication speed) as long as there is still work to catch up (Status "Reading event from the relay log") - therefore it is usually counting backwards.
Once it "Has read all relay log" it will start counting upwards again to indicate the time it has been waiting.

...
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5020
1 row in set (0,00 sec)

+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host            | db   | Command | Time | State                            | Info             |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
|  1 | root        | localhost:32893 | NULL | Sleep   |    0 |                                  | NULL             |
|  5 | system user |                 | NULL | Connect |   98 | Waiting for master to send event | NULL             |
|  6 | system user |                 | NULL | Connect | 5018 | Reading event from the relay log | NULL             |

Posted by Baron Schwartz on October 12 2008 4:03pm[Delete] [Edit]

If you get the error

ERROR 1227 (42000): Access denied; you need the PROCESS privilege for this operation

then you are probably connected as the anonymous user. Try running "select current_user" to see.

Add your own comment.