MySQL show full processlist command

If the database is slow and you want to see what MySQL is doing, you can execute this statement to list the SQL statements currently being executed by the MySQL connection.

Id user
Host dB Command time state info

2599
OA SVCTAG-91FFF2X: 3726 penguin
Sleep 7321 <null>

2956
Rw_penguin
SVCTAG-6HKDF2X: 5481
Penguin sleep 2293 <null>

2957
Rw_penguin
CYSY-DL580-2: 3925 penguin sleep.
2162 <null>

2963
Rw_penguin
CYSY-DL580-2: 4122 penguin sleep.
2162 <null>

2965
Rw_penguin
CYSY-DL580-2: 4270 penguin sleep.
2162 <null>

........................................................................

The following content is taken from the manual

Show processlist shows which threads are running. You can also use mysqladmin
The processlist statement obtains this information. If you have super permission, you can see all the threads. Otherwise, you can only view 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.

This statement reports the name of the host connected by TCP/IP (in the host_name: client_port format) to determine which client is doing what conveniently.

This statement is useful if you get the "too many ons" error message and want to know what is happening. MySQL retains an additional connection for the account with the super permission to ensure that the administrator can connect to and check the system at any time (assuming that you have not granted this permission to all users ).

Common statuses in output from show processlist:

· Checking table

The thread is performing an (automatic) Table check.

· Closing tables

This means that the thread is refreshing the changed table data and shutting down the used table. This should be a quick operation. If not, verify that your disk is not full and that the disk is not overloaded.

· Connect out

Connect to the slave server on the master server.

· Copying to TMP table on disk

The temporary result set is larger than tmp_table_size. The thread changes the temporary table format from the memory to the disk mode to save memory.

· Creating TMP table

The thread is creating a temporary table to maintain partial results.

· Deleting from main table

The server is executing the first part of multi-Table deletion and only deletes from the first table.

· Deleting from reference tables

The server is executing the second part of multi-Table deletion to delete matched rows from other tables.

· Flushing tables

The thread is executing flush tables and is waiting for all threads to close the table.

· Fulltext Initialization

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

· Killed

Someone has sent a kill command to the thread. Should be abandoned when marking the next check termination. This flag is checked in every major loop of MySQL, but in some cases, thread termination takes a short time. If the thread is locked by another thread, the stop operation takes effect as long as other threads are locked.

· Locked

The query is locked by other queries.

· Sending data

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

· Sorting for group

The thread is being classified to meet the requirements of group.

· Sorting for order

The thread is being classified to meet the order by requirement.

· Opening tables

The thread is trying to open a table. This should be a very fast process, unless the open operation is blocked. For example, an alter
A table or a lock TABLE statement can prevent opening a table until the statement is complete.

· Removing duplicates

The query is using select distinct. During use, MySQL cannot optimize different operations in the early stages. Therefore, MySQL requires an additional stage to cancel all the duplicate rows before sending the results to the client.

· Reopen table

The thread gets a table locked. However, after the table is locked, it is notified that the table structure below it has changed. It has released the lock, closed the table, and tried to open it again.

· Repair by sorting

The fix code is using a category to create an index.

· Repair with keycache

The fix code is using the key cache to create keywords one by one. This is much slower than classification.

· Searching rows for update

The thread is in the first stage to find all matched rows before updating. This is required if update is modifying the index used to find the relevant row.

· Sleeping

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

· System lock

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

· Upgrading lock

The insert delayed hypervisor is trying to get a table lock to insert rows.

· Updating

The thread is searching for rows and updating these rows.

· User lock

The thread is waiting for get_lock ().

· Waiting for tables

The thread gets a notification that the underlying structure of the table has changed. You need to re-open the table to obtain the new structure. However, in order to re-open the table, you must wait until all other threads have disabled the table being queried.

If other threads have used flush tables or one of the following statements for the table being queried: flush
Tables tbl_name, alter table, rename table, repair table, analyze table or optimize
Table; a notification will appear.

· Waiting for Handler insert

The insert delayed hypervisor has processed all inserts in the waiting state and is waiting for new inserts.

Most statuses correspond to very fast operations. If a thread stays in these States for several seconds, it may be a problem and needs to be investigated.

Some other statuses are not mentioned in the previous list, but many of them are useful for finding program errors on the server.

Here we can see whether the table is locked, whether some statements have been executed for a long time, or even through the kill ID statement to terminate the messy connection.

 

Reference: http://tech.it168.com/a2009/0526/577/000000577940.shtml