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.4, “Server Status Variables”

The Command value may be one of the following: Binlog Dump, Change user, Close stmt, Connect, Connect Out, Create DB, 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 between the start of the statement or command and now.

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, 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

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, “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.

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

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