After create: This occurs when a thread creates a table (including an internal temporary table), at the end of the function that creates the table. This state is used even if the table cannot be created due to some errors.
altering table: The server is executing an in-place ALTER TABLE.
Analyzing: The thread is calculating the MyISAM table key distribution (for example, for ANALYZE TABLE).
checking permissions: The thread is checking whether the server has the permissions required to execute the statement.
Checking table: The thread is performing a table check operation.
cleaning up: The thread has processed a command and is ready to free memory and reset some state variables.
closing tables: The thread is flushing the changed table data to disk and closing the used table. This should be a quick operation. If not, verify that you are not full of disks and that the disk is not in very heavy use.
committing alter table to storage engine: The server has completed the in-place ALTER TABLE and is submitting the result.
converting HEAP to ondisk: The thread is converting an internal temporary table from a MEMORY table to a table on disk.
copy to tmp table: The thread is processing the ALTER TABLE statement. This state occurs after a new structured table has been created but before the rows are copied into it.
For threads in this state, performance mode can be used to get the progress of the copy operation.
Copying to group table: If the statement has different ORDER BY and GROUP BY conditions, the rows are sorted by group and copied to the temporary table.
Copying to tmp table: The server is copying data to a temporary table in memory.
Copying to tmp table on disk: The server is copying data to a temporary table on disk. The temporary result set has become too large. Therefore, threads are changing temporary tables from memory format to disk-based format to save memory.
Creating index: The thread is processing the ALTER TABLE of the MyISAM table... ENABLE KEYS.
Creating sort index: The thread is processing SELECT parsing using internal temporary tables.
creating table: The thread is creating the table. This includes creating temporary tables.
Creating tmp table: The thread is creating a temporary table on memory or disk. If a table is created in memory but is later converted to a table on disk, the status during that operation is Copying to tmp table on disk.
deleting from main table: The server is performing the first part of multi-table deletion. It only deletes from the first table and saves the columns and offsets for use in deleting from other (referenced) tables.
deleting from reference tables: The server is performing the second part of the multi-table deletion and deleting the matching rows from other tables.
discard_or_import_tablespace: The thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.
end: At the end of the ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT or UPDATE statement but occurs before cleaning.
For the end state, the following actions may be occurring:
Write events to binary log
Free the memory buffer, including blob:executing: The thread has started executing the statement.
Execution of init_command: The thread is executing a statement in the value of the init_command system variable.
freeing items: The thread has executed a command. This state usually occurs before cleaning up.
FULLTEXT initialization: The server is preparing for a natural language full text search.
init: Occurs before initializing the ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statement. In this state, the actions taken by the server include refreshing the binary log and the InnoDB log.
Killed: Someone sent a KILL statement to the thread, which should abort the next time you check the kill flag. This flag is checked in every major loop of MySQL, but in some cases it may take a short time for the thread to die. If a thread is locked by another thread, it takes effect immediately after the other thread releases its lock.
Locking system tables: The thread is trying to lock a system table (for example, a time zone or log table).
logging slow query: The thread is writing a statement to the slow query log.
login: The initial state of the connection thread until the client successfully authenticates.
manage keys: The server is enabling or disabling table indexing.
Opening system tables: The thread is trying to open a system table (for example, a time zone or log table).
Opening tables: The thread is trying to open the table. This should be a very fast process unless there is anything to block it from opening. For example, an ALTER TABLE or LOCK TABLE statement can prevent the opening of the table until the statement is complete. It's also worth checking if your table_open_cache value is large enough.
For system tables, use the Opening system tables status instead.
optimization: The server is performing initial optimization for the query.
preparing: This state occurs during query optimization.
preparing for alter table: The server is preparing to execute in-place ALTER TABLE.
Purging old relay logs: The thread is deleting unwanted relay log files.
query end: This state occurs after processing the query but before the freeing items state.
Receiving from client: The server is reading packets from the client.
Removing duplicates: Query is being used in SELECT DISTINCT mode, so that MySQL cannot optimize DISTINCT operations in early stages. For this reason, MySQL requires an extra phase to remove all duplicate rows before sending the result to the client.
removing tmp table: The thread is removing the internal temporary table after processing the SELECT statement. If no temporary table is created, this state is not used.
rename: The thread is renaming the table.
rename result table: The thread is processing an ALTER TABLE statement, has created a new table, and is renamed to replace the original table.
Reopen tables: The thread acquires the lock of the table, but after acquiring the lock, it finds that the underlying structure of the table has been changed. It has released the lock, closed the table, and is trying to reopen it.
Repair by sorting: Fix the code to create indexes using sorting.
Repair done: The thread has completed multi-threading repair of the MyISAM table.
Repair with keycache: Fix the code to create one key using key cache at a time. This is much slower than Repair by sorting.
Rolling back: The thread is rolling back the transaction.
Saving state: For MyISAM table operations, such as repair or analysis, the thread is saving the new table state to the .MYI file header. The status includes information such as row count, AUTO_INCREMENT counter and key distribution.
Searching rows for update: The thread is in the first phase, looking for all matching rows, and then updating them. This must be done if UPDATE changes the index used to find the rows involved.
Sending data: Before MySQL 8.0.17: The thread is reading and processing the lines of the SELECT statement and sending the data to the client. Because operations that occur during this state tend to perform a large number of disk accesses (reads), it is usually the state where a given query runs the longest time in its lifetime. MySQL 8.0.17 and later: This status is no longer indicated separately, but is included in the Executing state.
Sending to client: The server is writing packets to the client.
setup: The thread starts the ALTER TABLE operation.
Sorting for group: The thread is performing sorting to satisfy the GROUP BY.
Sorting for order: The thread is performing the sort to satisfy the ORDER BY.
Sorting index: The thread is sorting the index pages for more efficient access during MyISAM table optimization operations.
Sorting result: For SELECT statements, this is similar to the Creating sort index, but is suitable for non-temporary tables.
starting: The first stage of the statement execution begins.
statistics: The server is calculating statistics to develop a query execution plan. If the thread stays in this state for a long time, the server may perform other work in the disk binding.
System lock: The thread has called mysql_lock_tables() and the thread state has not been updated since then. This is a very general state, probably for many reasons.
For example, a thread will request or is waiting for an internal or external system lock on the table. This may occur when InnoDB waits for a table-level lock during execution of LOCK TABLES. If this state is caused by requesting an external lock, and you are not using multiple mysqld servers that access the same MyISAM table, you can use the --skip-external-locking option to disable the external system lock. However, external locking is disabled by default, so it is possible that this option has no effect. For SHOW PROFILE, this state means that the thread is requesting a lock (not waiting for it).
For system tables, use the Locking system tables status instead.
update: The thread is ready to start updating the table.
Update: The thread is searching for rows to be updated and updating them.
updating main table: The server is performing the first part of the multi-table update. It only updates the first table and saves the columns and offsets for updating other (referenced) tables.
updating reference tables: The server is performing the second part of the multi-table update and updating matching rows in other tables.
User lock: The thread will request or is waiting for a requested consultation lock using GET_LOCK() to call the request. For SHOW PROFILE, this state means that the thread is requesting the lock (rather than waiting for it).
User sleep: The thread has called SLEEP().
Waiting for commit lock: FLUSH TABLES WITH READ LOCK is waiting to submit the lock.
waiting for handler commit: The thread is waiting for transaction commit, compared to other parts of query processing.
Waiting for tables: The thread is notified that the underlying structure of the table has changed, and it needs to reopen the table to get a new structure. However, to reopen the table, it must wait for all other threads to close the table.
This notification occurs if another thread uses FLUSH TABLES or one of the following statements on the table: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, this notification occurs.
Waiting for table flush: The thread is executing FLUSH TABLES and waiting for all threads to close its table, or the thread receives a notification that the underlying structure of the table has changed and it needs to reopen the table to get a new structure. However, to reopen the table, it must wait for all other threads to close the table.
This notification occurs if another thread uses FLUSH TABLES or one of the following statements on the table: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, this notification occurs.
Waiting for lock_type lock: The server is waiting to obtain a lock of the THR_LOCK lock or metadata lock subsystem, where lock_type indicates the type of lock.
This state means waiting for a THR_LOCK:
Waiting for table level lock: These states indicate waiting for metadata lock:
Waiting for event metadata lock: Waiting for global read lock: Waiting for schema metadata lock: Waiting for stored function metadata lock: Waiting for stored procedure metadata lock: Waiting for table metadata lock: Waiting for trigger metadata lock: Information about table lock metrics. Information about metadata locking. To see which locks block lock requests.
Waiting on cond: The thread is waiting for a condition to become true. No specific status information is available.
Writing to net: The server is writing packets to the network.
