MySQL-Show Processlist

Mysql> Show full processlist or mysql> show processlist;+---------+-----------+-------------------+----------+- ------------+---------+------------------------+---------------------------------------+| Id      | User      | Host              | db       | Command     | Time    | State                  | Info                                  |+---------+-----------+-------------------+----------+-------------+---------+----------------------- -+---------------------------------------+| 3672856 | ABCE |      192.168.5.1:38311 | ABCE     | Query       |       0 | Sending Data           | Select COUNT (1) from (select          +---------+-----------+-------------------+----------+------- ------+---------+------------------------+---------------------------------------+

Description

Id:id identification. Kill the user is useful user: The current user host: The current connection is from which IP and port connected to the DB: Database name command: Connection status, usually sleep (sleep), query, Connect (Connect) Time: The duration of the connection, in seconds State: status of the current SQL statement Info:sql statement contents

　

Where state is critical, the following table lists state key states and descriptions:

State	Description	Suggestions
After create	When a thread creates a table (including a temporary table), the state at the end of the function is created. Even if the creation fails for some reason, it is also displayed.
Analyzing	Thread is estimating the distribution of keys for the MyISAM table
Checking permissions	Permission check
Checking table	Check table
Cleaning up	Command end, ready to free memory and reset state variables
Closing tables	Refresh the table data to disk and close the table. This state is generally faster, otherwise you should look at the use of the disk
Converting HEAP to MyISAM	Convert internal memory staging table to MyISAM table on disk
Copy to TMP table	The thread executes the ALTER TABLE command. Occurs when the table structure has been modified, but the data has not been copied back	Recommendation: Early morning execution
Altering table	Execute ALTER TABLE command in situ
Copying to TMP table	Copying data from memory to a staging table	Recommendation: Create an index
Copying to Group table	If the statement's order by and group by columns are different, the rows are sorted in groups and copied to the staging table
Copying to TMP table on disk	Copy to a temporary table on disk. The temporary result set is too large because the thread is copying the in-memory data to disk to save memory.	Recommendation: Create indexes and increase tmp_table_size/max_heap_table_size
Creating Index	Executing ALTER TABLE on MYISAM table ... enable keys
Creating Sort Index	To perform a select operation with an internal temporary table	Recommendation: Create an index
Creating table	Create a table (with temporary tables)
Creating tmp table	Now create a temporary table on memory or on disk. If you first create a temporary table in memory and then convert to disk, the state that is displayed is copying to TMP table on disk	Recommendation: Create an index
Committing alter TABLE to storage engine	Principle ALTER TABLE end, submit result
deleting from Main Table	The first part of a multi-table deletion is performed. Just remove from the first table, save columns and offsets to delete other tables
deleting from reference tables	Second part of multiple table deletions, deleting matching rows from other tables
Discard_or_import_tablespace	Thread is handling ALTER TABLE ... discard tablespace or ALTER TABLE ... import tablespace
Freeing items	The thread has finished executing a command. Release some items
Fulltext initialization	Ready to perform full search
removing duplicates	The query uses distinct
removing TMP table	Remove internal temporary tables
Reading from net	Server correct request sent over network read client	Recommendation: Reduce client-sent packet size to improve network bandwidth/quality
Sending data	Sending data from the server to the client is also likely to receive the data returned by the storage engine layer and send it to the client, especially when the volume of data is large.	Recommendation: Reduce the amount of data that needs to be scanned by index or limit
sorting result	Sorting results, similar to creating sort index, is just a normal table, not a sort of in-memory table	Recommendation: Create an index
Statistics	Data statistics to parse the execution plan, which may be poor disk IO performance if the state is more often present	Recommendation: View the current IO performance status, such as Iowait
System Lock	Waiting for system level lock	Recommendation: Turn off external lock skip-external-locking (it has been turned off by default)
Waiting for global read lock	Normally flush TABLES with read lock full wait global read lock
Waiting for tables, or waiting for table flush	Usually because of flush TABLES, ALTER table, RENAME table, REPAIR table, ANALYZE table, OPTIMIZE table, etc., you need to refresh and reopen the tables structure

MySQL-Show Processlist