This article documents the major general thread states. More specific lists related to delayed inserts, replication, the query cache and the event scheduler are listed in:
These correspond to the STATE values listed by the statement or in the as well as the PROCESSLIST_STATE value listed in the
This page is licensed: CC BY-SA / Gnu FDL
converting HEAP to Aria
Converting an internal temporary table into an on-disk temporary table.
converting HEAP to MyISAM
Converting an internal temporary table into an on-disk temporary table.
copy to tmp table
A new table has been created as part of an statement, and rows are about to be copied into it.
Copying to group table
Sorting the rows by group and copying to a temporary table, which occurs when a statement has different and criteria.
Copying to tmp table
Copying to a temporary table in memory.
Copying to tmp table on disk
Copying to a temporary table on disk, as the resultset is too large to fit into memory.
Creating index
Processing an for an or table.
Creating sort index
Processing a statement resolved using an internal temporary table.
creating table
Creating a table (temporary or non-temporary).
Creating tmp table
Creating a temporary table (in memory or on-disk).
deleting from main table
Deleting from the first table in a multi-table , saving columns and offsets for use in deleting from the other tables.
deleting from reference tables
Deleting matched rows from secondary reference tables as part of a multi-table .
discard_or_import_tablespace
Processing an or statement.
end
State before the final cleanup of an , , , , , or statement.
executing
Executing a statement.
Execution of init_command
Executing statements specified by the --init_command option.
filling schema table
A table in the database is being built.
freeing items
Freeing items from the after executing a command. Usually followed by the cleaning up state.
Flushing tables
Executing a statement and waiting for other threads to close their tables.
FULLTEXT initialization
Preparing to run a search. This includes running the fulltext search (MATCH ... AGAINST) and creating a list of the result in memory
init
About to initialize an , , , , or statement. Could be performaing cleanup, or flushing the or InnoDB log.
Killed
Thread will abort next time it checks the kill flag. Requires waiting for any locks to be released.
Locked
Query has been locked by another query.
logging slow query
Writing statement to the .
NULL
State used for .
login
Connection thread has not yet been authenticated.
manage keys
Enabling or disabling a table index.
Opening table[s]
Trying to open a table. Usually very quick unless the limit set by has been reached, or an or is in progress.
optimizing
Server is performing initial optimizations in for a query.
preparing
State occurring during query optimization.
Purging old relay logs
Relay logs that are no longer needed are being removed.
query end
Query has finished being processed, but items have not yet been freed (the freeing items state.
Reading file
Server is reading the file (for example during ).
Reading from net
Server is reading a network packet.
Removing duplicates
Duplicated rows being removed before sending to the client. This happens when SELECT DISTINCT is used in a way that the distinct operation could not be optimized at an earlier point.
removing tmp table
Removing an internal temporary table after processing a statement.
rename
Renaming a table.
rename result table
Renaming a table that results from an statement having created a new table.
Reopen tables
Table is being re-opened after thread obtained a lock but the underlying table structure had changed, so the lock was released.
Repair by sorting
Indexes are being created with the use of a sort. Much faster than the related Repair with keycache.
Repair done
Multi-threaded repair has been completed.
Repair with keycache
Indexes are being created through the key cache, one-by-one. Much slower than the related Repair by sorting.
Rolling back
A transaction is being rolled back.
Saving state
New table state is being saved. For example, after, analyzing a table, the key distributions, rowcount etc. are saved to the .MYI file.
Searching rows for update
Finding matching rows before performing an , which is needed when the UPDATE would change the index used for the UPDATE
Sending data
Sending data to the client as part of processing a statement or other statements that returns data like . Often the longest-occurring state as it also include all reading from tables and disk read activities. Where an aggregation or un-indexed filtering occurs there is significantly more rows read than what is sent to the client.
setup
Setting up an operation.
Sorting for group
Sorting as part of a
Sorting for order
Sorting as part of an
Sorting index
Sorting index pages as part of a table optimization operation.
Sorting result
Processing a statement using a non-temporary table.
statistics
Calculating statistics as part of deciding on a query execution plan. Usually a brief state unless the server is disk-bound.
System lock
Requesting or waiting for an external lock for a specific table. The determines what kind of external lock to use. For example, the storage engine uses file-based locks. However, MyISAM's external locks are disabled by default, due to the default value of the system variable. Transactional storage engines such as also register the transaction or statement with MariaDB's while in this thread state. See for more information about that.
Table lock
About to request a table's internal lock after acquiring the table's external lock. This thread state occurs after the System lock thread state.
update
About to start updating table.
Updating
Searching for and updating rows in a table.
updating main table
Updating the first table in a multi-table update, and saving columns and offsets for use in the other tables.
updating reference tables
Updating the secondary (reference) tables in a multi-table update
updating status
This state occurs after a query's execution is complete. If the query's execution time exceeds , then is incremented, and if the is enabled, then the query is logged. If the plugin is enabled, then the query is also logged into the audit log at this stage. If the plugin is enabled, then CPU statistics are also updated at this stage.
User lock
About to request or waiting for an advisory lock from a call. For , means requesting a lock only.
User sleep
A call has been invoked.
Waiting for commit lock
is waiting for a commit lock, or a statement resulting in an explicit or implicit commit is waiting for a read lock to be released. This state was called Waiting for all running commits to finish in earlier versions.
Waiting for global read lock
Waiting for a global read lock.
Waiting for table level lock
External lock acquired,and internal lock about to be requested. Occurs after the System lock state. In earlier versions, this was called Table lock.
Waiting for xx lock
Waiting to obtain a lock of type xx.
Waiting on cond
Waiting for an unspecified condition to occur.
Writing to net
Writing a packet to the network.
After create
The function that created (or tried to create) a table (temporary or non-temporary) has just ended.
Analyzing
Calculating table key distributions, such as when running an ANALYZE TABLE statement.
checking permissions
Checking to see whether the permissions are adequate to perform the statement.
Checking table
Checking the table.
cleaning up
Preparing to reset state variables and free memory after executing a command.
closing tables
Flushing the changes to disk and closing the table. This state will only persist if the disk is full or under extremely high load.