Additional languages
« 13.5.4.23 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax
13.5.4.25 SHOW SCHEDULER STATUS Syntax »
Section Navigation      [Toggle]

13.5.4.24. 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). See Section 13.5.5.3, “KILL Syntax”. 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 Network Monitoring and Advisory Service 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).

The output of SHOW PROCESSLIST may look like this: 

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 non-client 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. event_scheduler refers to the thread that monitors scheduled events. For system user or event_scheduler, there is no host specified in the Host column.

  • Host

    The hostname of the client issuing the statement (except for system user where there is no host). SHOW PROCESSLIST reports the hostname 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 value of that column corresponds to the COM_xxx commands of the client/server protocol. See Section 5.2.5, “Status Variables”

    The Command value may be one of the following: Binlog Dump, Change user, Close stmt, Connect, Connect Out, Create DB, Daemon, Debug, Delayed insert, Drop DB, Error, Execute, Fetch, Field List, Init DB, Kill, Long Data, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time

  • Time

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

  • State

    An action, event, or state, which can be one of the following: After create, Analyzing, Changing master, Checking master version, Checking table, Connecting to master, Copying to group table, Copying to tmp table, Creating delayed handler, Creating index, Creating sort index, Creating table from master dump, Creating tmp table, Execution of init_command, FULLTEXT initialization, Finished reading one binlog; switching to next binlog, Flushing tables, Killed, Killing slave, Locked, Making temp file , Opening master dump table, Opening table, Opening tables, Processing request, Purging old relay logs, Queueing master event to the relay log, Reading event from the relay log, Reading from net, Reading master dump table data, Rebuilding the index on master dump table, Reconnecting after a failed binlog dump request, Reconnecting after a failed master event read, Registering slave on master, Removing duplicates, Reopen tables, Repair by sorting, Repair done, Repair with keycache, Requesting binlog dump, Rolling back, Saving state, Searching rows for update, Sending binlog event to slave, Sending data, Sorting for group, Sorting for order, Sorting index, Sorting result, System lock, Table lock, Thread initialized, Updating, User lock, Waiting for INSERT, Waiting for master to send event, Waiting for master update, Waiting for slave mutex on exit, Waiting for table, Waiting for tables, Waiting for the next event in relay log, Waiting on cond, Waiting to finalize termination, Waiting to reconnect after a failed binlog dump request, Waiting to reconnect after a failed master event read, Writing to net, allocating local table, cleaning up, closing tables, converting HEAP to MyISAM, copy to tmp table, creating table, deleting from main table, deleting from reference tables, discard_or_import_tablespace, end, freeing items, got handler lock, got old table, info, init, insert, logging slow query, login, preparing, purging old relay logs, query end, removing tmp table, rename, rename result table, reschedule, setup, starting slave, statistics, storing row into queue, unauthenticated user, update, updating, updating main table, updating reference tables, upgrading lock, waiting for delay_list, waiting for handler insert, waiting for handler lock, waiting for handler open, Waiting for event from ndbcluster

    The most common State values are described in the rest of this section. Most of the other State values are useful only for finding bugs in the server. See also Section 6.4.1, “Replication Implementation Details”, for additional information about process states for replication servers.

    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.

Some State values commonly seen in the output from SHOW PROCESSLIST:

  • Checking table

    The thread is performing a table check operation.

  • Closing tables

    Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.

  • Connect Out

    A replication slave is connecting to its master.

  • Copying to group table

    If a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.

  • Copying to tmp table

    The server is copying to a temporary table in memory.

  • Copying to tmp table on disk

    The server is copying to a temporary table on disk. The temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory.

  • Creating tmp table

    The thread is creating a temporary table to hold a part of the result for the query.

  • deleting from main table

    The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving fields and offsets to be used for deleting from the other (reference) tables.

  • deleting from reference tables

    The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

  • Flushing tables

    The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.

  • FULLTEXT initialization

    The server is preparing to perform a natural-language full-text search.

  • Killed

    Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

  • Locked

    The query is locked by another query.

  • Sending data

    The thread is processing rows for a SELECT statement and also is sending data to the client.

  • Sorting for group

    The thread is doing a sort to satisfy a GROUP BY.

  • Sorting for order

    The thread is doing a sort to satisfy a ORDER BY.

  • Opening tables

    The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished.

  • Reading from net

    The server is reading a packet from the network.

  • Removing duplicates

    The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

  • Reopen table

    The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.

  • Repair by sorting

    The repair code is using a sort to create indexes.

  • Repair with keycache

    The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

  • Searching rows for update

    The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.

  • Sleeping

    The thread is waiting for the client to send a new statement to it.

  • statistics

    The server is calculating statistics to develop a query execution plan.

  • System lock

    The thread is waiting to get an external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option.

  • unauthenticated user

    The state of a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.

  • Upgrading lock

    The INSERT DELAYED handler is trying to get a lock for the table to insert rows.

  • Updating

    The thread is searching for rows to update and is updating them.

  • updating main table

    The server is executing the first part of a multiple-table update. It is updating only the first table, and saving fields and offsets to be used for updating the other (reference) tables.

  • updating reference tables

    The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.

  • User Lock

    The thread is waiting on a GET_LOCK().

  • Waiting for event from ndbcluster

    The server is acting as an SQL node in a MySQL Cluster, and is connected to a cluster management node.

  • Waiting for tables

    The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

    This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

  • waiting for handler insert

    The INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.

  • Writing to net

    The server is writing a packet to the network.

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


User Comments

Posted by Artem Naluzhny on February 17 2006 8:28pm[Delete] [Edit]

Put SQL comment before each SQL request in your code:

"/* REMOTE_ADDR: '192.168.1.1'; REMOTE_USER: 'John Doe'; REQUEST_URI: '/admin/update_info.php'; CALLED_FROM: 'MyFunction()'; */ update Items set Param1='Value1' where Param2='Value2' "

It helps to determine the requestor of each SQL statement in "show full processlist" output.

Do not forget to check the syntax of values in the comments (e.g. "*/").


By the way SQL statements with the comments will be stored in binlogs. So it is also a nice logging tool.


NB:
The feature does not work for MySQL command line client >=4.1 - all SQL comments are stripped before sending to the server.

Posted by [name withheld] on February 5 2005 3:58pm[Delete] [Edit]

You have

"Copying to tmp table on disk"

and

"Creating tmp table"

but there is a status:

"Copying to tmp table"

It is unclear if the "on disk" is implied or if it is a tmp table in Heap.

Posted by Chris Wilson on November 24 2006 3:04pm[Delete] [Edit]

I think there is a problem with this description:

"waiting for handler insert: The INSERT DELAYED handler has processed all pending inserts and is waiting for new ones."

That's not what I see. The thread which issued INSERT DELAYED hangs around in this state for a while, with the "Command" column being "Query". I'm not sure exactly what it's doing, probably running the INSERT DELAYED command, but I'm pretty sure it's not "waiting for new ones", as I can get several threads in this state, each apparently associated with a client.

Posted by Sujith Kumar on January 12 2007 12:17pm[Delete] [Edit]

i got one good example :

mysql_connect("localhost","root",""); // change here!

$q = mysql_query("SHOW FULL PROCESSLIST");

echo "<p>MySQL-Processes:</p>\n";
echo "<table width='*' border='1' cellspacing='1' cellpadding='3'>\n";

while($l = mysql_fetch_row($q) ) {

echo "<tr>\n";
foreach($l as $val) echo "<td>$val&nbsp;</td>\n";
echo "</tr>\n";

}
echo "</table>\n";

// works only with MySql 4!

echo "<p>Querycache Status:</p>\n";
echo "<table width='*' border='1' cellspacing='1' cellpadding='3'>\n";
$q = mysql_query("SHOW STATUS LIKE 'Qcache%'");
while($l = mysql_fetch_row($q) ) {

echo "<tr>\n";
foreach($l as $val) echo "<td>$val&nbsp;</td>\n";
echo "</tr>\n";

}
echo "</table>\n";

mysql_close();



Add your own comment.