MySQL Show processlist Command detailed

Command format

SHOW [Full] Processlist

Show Processlist shows which threads are running. You can also use the mysqladmin processlist statement to get this information. If you have super privileges, you can see all the threads. Otherwise, you can only see your own thread (that is, the thread associated with the MySQL account you are using). If you do not use the full keyword, only the first 100 characters of each query are displayed.

MySQL Thread status description

The most critical of this command is the State column, which is listed in the following categories:

State	Meaning
After create	When a thread creates a table (including temporary tables)
Analyzing	Thread is analyzing the key distribution of MyISAM
Checking permissions	Thread is checking permission to execute statement
Checking table	The thread is performing a table check operation
Cleaning up	The thread has finished processing a command and then starts preparing to release the memory and reset state variables
Closing tables	The thread flush the changed table data to disk and then closes the table used. This operation should be very fast, if this status occurs for a long time, be careful to check the status of the disk.
converting HEAP to MyISAM	Convert an in-memory temp table to a MyISAM table on disk
Copy to TMP table	The thread executes the ALTER TABLE statement. This state occurs before the new table structure has been created but the data is still copied to the new table.
Copying to group table	If the group by and order by conditions of a statement are different, the data is sorted and then copied to the staging table by the group by
Copying to TMP table	Copy to in-memory temporal table
Copying to tmp table on disk	If the temporary table is too large, the server copies the temporary tables in memory to disk.
Creating Index	Execute on a MyISAMALTER TABLE ... ENABLE KEYS
Creating sort index	A thread executes a SELECT statement by executing a temporary table
Creating table	A thread is creating a table (including a staging table)
Creating tmp table	Create a temporary table (either on memory or disk), if the table starts in memory to the back too large, the temporary table will be converted to disk, and the state will becomeCopying to tmp table on disk
deleting from Main Table	The server is executing the first part of a multi-table DELETE statement, that is, deleting data from the first table, and preserving rows and offset data to delete data from other tables.
deleting from reference tables	The server is performing two parts of a multi-table DELETE statement
Discard_or_import_tablespace	The thread is executing ALTER TABLE ... DISCARD TABLESPACE or a ALTER TABLE ... IMPORT TABLESPACE statement.
End	This state appears in,,,, ALTER TABLE CREATE VIEW DELETE INSERT SELECT ,UPDATE语句结束之后，但在清理以前。
Executing	The thread starts executing a statement
Execution of Init_command	The thread executes the statement in the Init_command system variable
Freeing items	The thread has executed the command. Some release
Flushing tables	This thread executes theFLUSH TABLES，而且等待所有的线程关闭表
FULLTEXT initialization	The server is ready to perform a text search
Init
Killed	Sent a KILL statement to this query. This statement should be discarded the next time the kill flag is checked.
Locked	The query was locked by another query.
Logging Slow Query	Thread is writing slow SQL to slow query log file
Null
Login	Until the thread authorization was successful, it was this state before.
Manage keys	The server is crying on the card or closing the index of the table
Opening tables,Opening table	The thread is opening the table. This operation is fast, unless there are other reasons to prevent the open operation, such as ALTER TABLELOCK TABLE
Optimizing	The server is making an initial phase optimization of a query
Preparing	Query optimization in progress
Purging Old relay logs	Thread to delete unwanted relay log files
Query end	This state occurs after processing but before the item is frozen
Reading from net	Server reads packets from the network
Removing duplicates	The query uses SELECT DISTINCT. MYSQ requires an additional procedure to remove duplicate rows before sending data to the client
removing TMP table	After the SELECT statement is executed, the thread is deleting the internal temporary table
Rename	The thread is renaming the table
Rename result table	The thread is executing an ALTER TABLE statement, and a new table has been created, renaming the new table to replace the original table.
Reopen tables	The thread acquires a lock on a table. Because it has learned that the table structure it relies on has changed. The thread needs to release the lock, close the table, and then try to reopen it.
Repair by sorting	Fix the code to create an index by sorting.
Repair Done	The thread has completed a multi-threaded repair of a myisam table.
Repair with Keycache	The fix code is creating key through the cache of key.
Rolling back	The thread is rolling back things.
Saving state	In the MyISAM analysis and repair operations, the thread will take some information of the table, such as the number of rows in the table,AUTO_INCREMENT的计数器以及key的分布都保存到.MYI文件的头部
Searching rows for update	The first phase of the statement execution, which finds all the row records that meet the criteria
Sending data	The thread reads and processes the SELECT statement, sending the data to the client. Because statements require a large amount of disk access, this state occupies the longest state in the entire life cycle of a statement.
Setup	The thread begins the ALTER TABLE statement
Sorting for group	The thread is performing a sort for group by
Sorting for order	The thread is performing a sort for order by
Sorting index	In the MyISAM table optimization operation, the index page is sorted for better access performance.
sorting result	Sort the results
Statistics	The server is computing statistics to generate an execution plan. If a thread retains this state for a long time, it means that the server is performing other disk-related work.
System Lock	The thread is requesting and acquiring an internal and external lock. If you have this status
Table Lock	Another thread state after System lock. The thread has acquired an external lock and then goes to fetch an internal table lock.
Update	The thread is ready to update.
Updating	Now looking for or updating rows
updating main table	The server is executing a multi-table UPDATE statement, updating the first table, saving rows and offsets to update the other tables.
updating reference tables	The server is executing the second part of a multi-table UPDATE statement and is updating rows from another table
User Lock	Request or wait to acquire a lock
User sleep	Thread Sleep
Waiting for release of readlock	Wait for a global read lock
Waiting for tables,Waiting for table	The table structure that the thread has been prompted to rely on has changed, and the thread needs to reopen the table to get the new structure. However, reopening the table is required to wait for other threads to close the table.
Waiting on cond	Thread is waiting for a condition to become true
Waiting to get Readlock	Line ChengFLUSH TABLES WITH READ LOCK语句，所以要获得一个全局的读锁，这个状态表示正在等这个锁。
Writing to net	The server writes the packet to the network

MySQL Show processlist Command detailed