SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which
threads are running. You can also get this information from the
INFORMATION_SCHEMA
PROCESSLIST table or 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.
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.4, “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:
IdThe connection identifier.
UserThe 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 userrefers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.event_schedulerrefers to the thread that monitors scheduled events. Forsystem user, there is no host specified in theHostcolumn.HostThe host name of the client issuing the statement (except for
system userwhere there is no host).SHOW PROCESSLISTreports the host name for TCP/IP connections inhost_name:client_portdbThe default database, if one is selected, otherwise
NULL.CommandThe type of command the thread is executing. For descriptions for thread commands, see Section 7.12.5, “Examining Thread Information”. The value of this column corresponds to the
COM_commands of the client/server protocol andxxxCom_status variables. See Section 5.1.6, “Server Status Variables”xxxTimeThe time in seconds that the thread has been in its current state.
StateAn action, event, or state that indicates what the thread is doing. Descriptions for
Statevalues can be found at Section 7.12.5, “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 PROCESSLISTstatement, the value ofStateisNULL.InfoThe statement that the thread is executing, or
NULLif 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 aCALL p1()statement executes a stored procedurep1(), and the procedure is executing aSELECTstatement, theInfovalue shows theSELECTstatement.