The output of the Processlist command shows which threads are running and can check the current database's running state, using this command in two ways.
1 Enter the Mysql/bin directory under the input mysqladmin processlist;
2 start MySQL, enter show Processlist;
If you have SUPER privileges, you can see all the threads, otherwise you will see only the threads of your account.
Demonstrate:
Mysql> Show full processlist;
+-–+--–+ ————— –+ ————-+ ——— +--+ ——-+ ——————— –+
| Id | User | Host | db | Command | Time | State | Info |
+-–+--–+ ————— –+ ————-+ ——— +--+ ——-+ ——————— –+
| 66 | Dragon | localhost:47205 | Dragon_test | Sleep | 242 | | NULL |
| 95 | Dragon | localhost:47234 | Dragon_test | Sleep | 242 | | NULL |
| 96 | Dragon | localhost:57651 | Test | Sleep | 5088 | | NULL |
| 127 | Dragon | localhost:51277 | Dragon_test | Sleep | 27 | | NULL |
| 128 | Dragon | localhost:51278 | Dragon_test | Sleep | 27 | | NULL |
| 217 | Dragon | localhost | Dragon_test | Query | 0 | NULL | Show Full Processlist |
+-–+--–+ ————— –+ ————-+ ——— +--+ ——-+ ——————— –+
The meaning and purpose of each column,
ID: an identity
User: Displays the current user, if not root, this command displays only the SQL statements within the scope of your permission.
Host: Shows which IP port this statement was issued from
DB: Displays the database that the process is currently connected to.
Command: Displays commands for the execution of the current connection, typically hibernation (sleep), query, connection (connect).
Time: The duration of this state, in seconds.
State: Displays the status of the SQL statement using the current connection, just one state in the execution of the statement, an SQL statement that has been queried as an example and may need to be completed by copying to TMP table,sorting result,sending data.
Info: This SQL statement is displayed because the length is limited, so the long SQL statement is incomplete, but a critical basis for judging the problem statement.
The most critical of this command is the State column, which is listed in the following categories:
Checking table
Checking the data table (this is automatic).
Closing tables
The modified data in the table is being flushed to disk, and the tables that have been exhausted are being closed. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load.
Connect out
Replication from the server is connecting to the primary server.
Copying to TMP table on disk
Because the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.
Creating tmp table
Creating temporary tables to hold partial query results.
deleting from Main Table
The server is performing the first part of a multi-table delete and has just deleted the first table.
deleting from reference tables
The server is performing the second part of a multi-table delete and is deleting records from other tables.
Flushing tables
Executing FLUSH TABLES, waiting for other threads to close the data table.
Killed
Sends a KILL request to a thread, the thread checks the kill flag bit and discards the next kill request. MySQL checks the kill flag bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released.
Locked
Locked by another query.
Sending data
The record for the SELECT query is being processed, and the results are being sent to the client.
Sorting for group
Sorting is being done for GROUP by.
Sorting for order
The order by is being sorted.
Opening tables
The process should be quick, unless other factors interfere with it. For example, a data table cannot be opened by another thread until the row of the ALTER table or LOCK table statement is complete. Attempting to open a table.
removing duplicates
A query that is executing a SELECT DISTINCT method is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again, and then send the results to the client.
Reopen table
A lock on a table is obtained, but it must be changed after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again.
Repair by sorting
Repair instructions are being sorted to create an index.
Repair with Keycache
The repair instructions are using the index cache to create a new index one by one. It will be slower than Repair by sorting.
Searching rows for update
The qualifying records are being told to find out to prepare for the update. It must be completed before the UPDATE is about to modify the related records.
Sleeping
Waiting for the client to send a new request.
System Lock
Is waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing the –skip-external-locking parameter.
Upgrading lock
Insert DELAYED is trying to get a lock table to insert a new record.
Updating
Searching for matching records, and modifying them.
User Lock
Waiting for Get_lock ().
Waiting for tables
The thread is notified that the data table structure has been modified and the data table needs to be reopened to obtain a new structure. Then, to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following cases: FLUSH TABLES tbl_name, ALTER table, RENAME table, REPAIR table, ANALYZE table, or OPTIMIZE table.
Waiting for handler insert
Insert DELAYED has processed all pending insertions and is waiting for a new request. Most of the state corresponds to a fast operation, so long as one thread remains in the same state for several seconds, a problem may occur and need to be checked.
mysql command show full processlist
