1.show Full Processlist
Displays all of the MySQL processes that are executing to view the current MySQL operation, avoiding deadlocks and other anomalies.
The main columns:
- ID: Process ID
- User: Login Account
- Host: Logon User IP port
- DB: Access that library
- Time: The duration of the operation
- Command: Displays commands for the execution of the current connection, typically the daemon (Daemon) hibernation (sleep), query, connection (connect)
- Rows_sent: Number of rows returned by the command
- Rows_examined: The number of rows checked by the command (when this value is large, indicates that the command needs to check the number of rows is very large, you can use explain the command to see the actual execution, to determine whether the normal use of indexes, etc.)
- Rows_read: Number of rows read by the command
- State: Current status
- 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 is connecting from the server 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: A temporary table is being created to hold some of the 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: A kill request is sent to a thread, then the thread will check the kill flag bit and discard 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 of the select query is being processed, and the results are being sent to the client.
- Sorting for group: The group by is being sorted.
- Sorting for order: The order by is being sorted.
- Opening tables: This process should be very fast, unless otherwise disturbed by other factors. 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 is being executed for a SELECT distinct mode, 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: 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: A qualifying record is being told to find out for updates. 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: Waiting for an external lock to be made. 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 needs to reopen the data table to get 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.
2.kill
When some process deadlock is found, you can use the command kill ID to end the process to release the lock.
Reference: "MySQL show processlist command big role"
MySQL Processlist/kill
