Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to look at MySQL thread status, with certain reference value, interested friends can refer to, I hope you have a lot of gains after reading this article, let Xiaobian take you to understand.

Simple translation:

After create

This occurs when a thread creates tables (including internal temporary tables) at the end of a function that creates tables. This state is used even if the table cannot be created due to some error.

Analyzing

Thread is calculating MyISAM table key distribution (for example, for ANALYZE TABLE).

checking permissions

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 changed table data to disk and closing used tables. This should be a quick operation. If not, verify that you have disk space left.

converting HEAP to ondisk

The thread is converting internal temporary tables from MEMORY tables to disk tables.

copy to tmp table

Thread is processing an ALTER TABLE statement. This state occurs when a table with a new structure is created but before rows are copied into it.

For threads in this state, you can use performance mode to get progress on replication operations.

Copying to group table

If the statement has different conditional ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to the temporary table.

Copying to tmp table

The server is copying temporary tables into memory.

altering table

Server is executing in-place ALTER TABLE.

Copying to tmp table on disk

Server is copying temporary tables to disk.

Creating index

Thread processing ALTER TABLE... ENABLE KEYS A MyISAM table.

Creating sort index

Thread is processing SELECT threads that use internal temporary table resolution.

creating table

Thread is creating a table. This includes creating temporary tables.

Creating tmp table

This thread is creating temporary tables in memory or on disk. If the table is created in memory but later converted to a disk table, the state during that operation will be Copying to tmp table on disk.

committing alter table to storage engine

The server has completed ALTER TABLE in-place and committed the results.

deleting from main table

The server is performing the first part of a multi-table delete. It deletes only from the first table and saves columns and offsets for deletion from other (reference) tables.

deleting from reference tables

The server is performing the second part of the multi-table deletion and deleting matching rows from other tables.

discard_or_import_tablespace

Thread processing ALTER TABLE... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.

end

This occurs at the end, but before the cleanup of an ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statement.

executing

The thread has started executing statements.

Execution of init_command

Thread is executing statements in the init_command system variable value.

freeing items

The thread executed a command. Some releases of items completed during this state involve query caching. This is usually followed by cleaning up.

FULLTEXT initialization

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

init

This occurs before initialization with ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing binary logs, InnoDB logs, and some query cache cleanup operations.

For the end state, the following actions may occur:

Delete query cache entries after deleting data from tables

Write events to binary log

Free memory buffers, including blobs

Killed

Someone KILL sends a statement to the thread that should abort the next time the kill flag is checked. This flag is checked in every MySQL main loop, but in some cases it may still take a short time for the thread to die. If a thread is locked by another thread, kill takes effect once the other thread releases its lock.

logging slow query

This thread is writing a statement to the slow query log.

login

Connect the initial state of the thread until the client successfully authenticates.

manage keys

The server is enabling or disabling table indexing.

NULL

This state is used for the SHOW PROCESSLIST state.

Opening tables

Thread is trying to open a table. This should be a very fast program unless something stops it from opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent a table from opening until the statement ends.

optimizing

The server is performing initial optimization on the query.

preparing

This state occurs during query optimization.

Purging old relay logs

This thread is deleting unwanted relay log files.

query end

This state occurs after processing the query but before the free items state.

Receiving from client

The server is reading packets from the client. Reading from net called this state prior to MySQL 5.7.8.

Removing duplicates

The query uses SELECT DISTINCT in such a way that MySQL cannot optimize different operations at an early stage. Therefore, MySQL requires an extra phase to remove all duplicate rows before sending the results to the client.

removing tmp table

This thread deletes internal temporary tables after processing SELECT statements. This state is not used if no temporary tables are created.

rename

The thread is renaming a table.

rename result table

The thread is processing an ALTER TABLE statement, creating a new table and renaming it to replace the original table.

Reopen tables

The thread acquires a lock on the table, but after acquiring the lock notices that the underlying table structure has changed. It released the lock, closed the table, and tried to reopen it.

Repair by sorting

Fix code uses sorting to create indexes.

preparing for alter table

The server is preparing to execute an in-place ALTER TABLE.

Repair done

This thread has completed a multithreaded repair of MyISAM tables.

Repair with keycache

The fix code creates keys individually through the key cache. Repair by sorting.

Rolling back

The thread is rolling back a transaction.

Saving state

For MyISAM table operations such as repair or analysis, the thread saves the new table state to the.MYI file header. The state includes information such as the number of rows, AUTO_INCREMENT counter, and key distribution.

Searching rows for update

The thread is in the first phase to find all matching rows before updating. UPDATE must do this if you want to change the index used to find the row in question.

Sending data

The thread is reading and processing the rows 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 typically the longest running state in a given query lifecycle.

Sending to client

The server is writing packets to the client. Writing to net called this state prior to MySQL 5.7.8.

setup

Thread is starting an ALTER TABLE operation.

Sorting for group

Threads are sorting to satisfy GROUP BY.

Sorting for order

Threads are sorting to satisfy ORDER BY.

Sorting index

This thread is sorting index pages for more efficient access during MyISAM table optimization operations

Sorting result

For SELECT declarations, this is similar to Creating sort index non-temporary tables.

statistics

The server is calculating statistics to develop query execution plans. If the thread remains in this state for a long time, the server may be disk-bound to perform other work.

System lock

The thread has called mysql_lock_tables() and the thread state has not been updated. This is a very common condition and can occur for a variety of reasons.

For example, a thread will request or be waiting for an internal or external system lock on a table. This happens when InnoDB waits for a table-level lock during execution. LOCK TABLES If this state is caused by a request for an external lock and you are not using multiple mysqld servers accessing the same table, MyISAM can disable external system locks using the--skip-external-locking option. However, external locking is disabled by default, so this option is most likely not valid. For SHOW PROFILE, this state means that the thread is requesting a lock (without waiting for it).

update

Thread is ready to start updating tables.

Updating

Threads are searching for rows to update and updating them.

updating main table

The server is performing the first part of a multi-table update. It updates only the first table and saves columns and offsets used to update 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 demand or be waiting for an advisory lock requested via the GET_LOCK() call. For SHOW PROFILE, this state indicates that the thread is requesting a lock (without waiting for it).

User sleep

The thread has called a SLEEP() call.

Waiting for commit lock

FLUSH TABLES WITH READ LOCK Waiting for commit lock.

Waiting for global read lock

FLUSH TABLES WITH READ LOCK Waiting for global read lock or read_only Setting global system variables.

Waiting for tables

The thread gets a notification that the underlying structure of the table has changed and that it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the related table.

Waiting for table flush

Threads are executing FLUSH TABLES and are waiting for all threads to close their tables, or threads are notified that a table's infrastructure has changed and it needs to reopen the table to get the new structure. However, to reopen a table, it must wait until all other threads have closed the related table.

Waiting for *lock_type* lock

The server is waiting for THR_LOCK to acquire a lock or lock from the metadata lock subsystem, where lock_type indicates the type of lock.

This state indicates waiting for THR_LOCK:

These states indicate pending 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

Waiting for table level lock

Waiting on cond

Threads are waiting for the condition to become true in general state. There is no specific state information.

Writing to net

The server is writing packets to the network. Sending to client This state has been invoked since MySQL 5.7.8.

Thank you for reading this article carefully. I hope that the article "How to Look at MySQL Thread Status" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.