Detailed explanation of MySQL thread status

Article Directory

• • 1. show processlist
  • 2. Command command type
  • 3. User thread status
  • 4. Dump thread status
  • 5. IO thread status
  • 6. SQL thread status
  • 7. Master-slave connection thread status

  • 8. Event scheduling thread status

## Related free learning recommendations: mysql video tutorial

1. show processlist

Id: connection process identifier. Is the value returned by the CONNECTION_ID() function
• User: The name of the MySQL user who executed the statement. If "system user" is displayed, it refers to a non-client thread generated by MySQL that is performing internal tasks. For example, I/O or SQL threads used on the slave
• library in primary-standby replication or threads for delayed row handlers. "unauthenticated user" refers to a thread in which the client has established a TCP/IP connection with the server but has not yet authenticated the user password of the client. "event_scheduler" refers to the thread that monitors scheduled task scheduling events.
  
Host: The host name of the client executing the statement, displayed in host_name:client_port (if the skip_name_resolve parameter is enabled, it is displayed in ip:client_port format)
• Db: The default database to which the client connects (If the library name is specified when connecting), otherwise it is displayed as NULL.
• Command: The type of command being executed by the thread.
• Time: The amount of time (in seconds) the thread has been in the current state. For slave SQL threads, this value is the number of seconds between the time of the last replication event and the slave's actual time.
• State: Prompts what kind of operation, event or state the thread is doing.
• Info: The statement being executed by the thread.

2. Command type

Binlog Dump: The main library thread is used to send the binary log content to the slave library
• Change user: The thread is performing a change user operation
• Close stmt: The thread is closing a precompiled statement
• Connect: The slave thread has been connected to the main library
• Connect Out: The slave library is connecting to the main library
• Create DB: The thread is performing a database creation operation
• Daemon: This is an internal thread of the server, not a thread for client connection
• Debug: The thread is generating debugging information
• Delayed insert: It is a thread that delays the insertion handler
• Drop DB: The thread is performing a drop database operation
• Execute: Thread Executing a precompiled statement
• Fetch: The thread is executing the statement and obtaining the result set from it
• Field List: The thread is retrieving table column information
• Init DB: The thread is selecting the default database
• Kill: The thread is killing other threads
• Long Data: The thread is executing the statement and retrieving and returning the long field (large field) type data result set from it
• Ping: The thread is processing the server ping request
• Prepare: The thread is executing a precompiled statement
• Processlist: The thread is generating information about the server thread
• Query : The thread is executing the query statement
• Quit: The thread is terminating
• Refresh: The thread is refreshing the table, log or cache, or resetting the status variable or copying the server information
• Register Slave: The thread is registering the slave library on the main library
• Reset stmt: The thread is resetting the precompiled statement
• Set option: The thread is setting or resetting the client statement execution option
Shutdown: The thread is shutting down the server
• Sleep: The thread is waiting for the client to send it a new statement request
• Statistics: The thread is generating server status information
• Table Dump: The thread is sending the table contents to the slave library

3. User thread status

• After create: This state occurs when the thread completes creating a table (including internal temporary tables).
  This state occurs even if the table creation ends up with an error due to some error
• Analyzing: Thread is ANALYZE TABLE
• checking permissions: Checking in server whether thread has execution statement Required permissions
• Checking table: The thread is performing a table checking operation
• cleaning up: The thread has completed executing a command and is preparing to release the occupied memory and reset some state variables
• Closing tables: The thread is flushing the changed data of the table to disk and closing the table.
• converting HEAP to MyISAM: Thread is converting internal temporary table from MEMORY engine table to temporary table on disk MyISAM engine
• copy to tmp table: Thread is executing ALTER TABLE statement. This state occurs after the table with the new structure has been created and before copying the old table data to the new table.
• Copying to group table: If the statement uses different ORDER BY and GROUP BY conditional columns, then Sort these rows of data according to group by, and copy the sorted results to the temporary table
• Copying to tmp table: server is copying data to the memory temporary table
• altering table: server is executing in -Place ALTER TABLE procedure
• Copying to tmp table on disk: The server is copying data to the temporary table on disk. Because the temporary result set is too large, the thread is converting the in-memory temporary table to a disk-based temporary table to save memory
• Creating index: The thread is executing an ALTER TABLE... ENABLE KEYS statement
• Creating sort index: The thread is executing SELECT and the internal temporary table is used
• creating table: The thread is creating the table. This status is also used when creating temporary tables
• Creating tmp table: The thread is creating a temporary table in memory or on disk. If the table is created in memory but is later converted to a disk table, the status during the operation will be "Copying to tmp table on disk"
• committing alter table to storage engine: server has completed execution in- The ALTER TABLE statement of the place algorithm is submitting
• deleting from main table: The server is executing the first part of the multi-table delete statement. Seeing this
  status indicates that it is being deleted from the first table, and the column data and offsets used to delete subsequent tables are saved
• deleting from reference tables: The server is executing a multi-table delete statement The second part, delete matching rows from other tables
• discard_or_import_tablespace: Thread is executing ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement
• end: This happens at the end of statement execution This state occurs when, but before clearing the ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT or UPDATE statement
• executing: The thread is executing the statement
• Execution of init_command: The thread is executing an Statement to initialize system variables
• freeing items: The thread has completed executing a command. Release some items related to query cache status
  . This state is usually followed by the cleaning up state
• FULLTEXT initialization: The server is preparing to perform natural language full-text search
• init: This is initialized in the ALTER TABLE, DELETE, INSERT, SELECT or UPDATE statement state that occurred before. The operations performed by the server in this state include refreshing the binary log, InnoDB log and some query cache cleaning operations. At the end of this state, there may be some operations as follows:
  Delete query cache entries after the data in the table changes
  Write events to the binary log
  Release memory buffers, including blob
• Killed: Initiate a kill operation to the thread, and the thread should perform the termination operation. A thread's kill flag is checked in every main loop in MySQL, but in some cases it may only take a short time to kill the thread. However, if the thread being killed is locked by other threads, you need to wait for other threads to release the lock before the kill command will take effect and be executed.
• 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: server Enabling or disabling table indexes
• NULL: This status is used for the SHOW PROCESSLIST statement
• Opening tables: The thread is trying to open a table. Open table operations should be very fast unless the open operation is blocked. For example, an ALTER TABLE or LOCK TABLE statement prevents the table from being opened until the statement completes. In addition, it may be that table_open_cache is not large enough and the table cannot be opened.
• optimizing: The server is performing initial optimization on the query
• preparing: This state occurs during query optimization
• Purging old relay logs: The thread is deleting unnecessary relay logs File
• query end: This status occurs after executing the query statement but before releasing the query statement-related status items
• Reading from net: The server is reading data packets from the network. After MySQL 5.7.8, this state is called "Receiving from client" - Receiving from client: The server is reading packets from the client. In MySQL 5.7.8, it is called "Reading from net"
• Removing duplicates: When the query uses the SELECT DISTINCT statement, MySQL cannot optimize the distinct operation in the early stage. Therefore, MySQL requires an additional stage to remove all duplicate rows and then send the results to the client
• removing tmp table: The thread is deleting the internal temporary table after the SELECT statement has completed execution. If the SELECT statement does not create a temporary table, this state will not occur
• rename: The thread is executing the rename statement to rename the table
• rename result table: The thread is executing the ALTER TABLE statement to rename the table, A new table has been created and the old table name is being replaced with the new table
• Reopen tables: The thread obtained the table lock, but after obtaining the lock, it found that the underlying table structure had been changed.
  So release the table lock, close the table, and try to reopen the table
• Repair by sorting: The repair code is using sorting to create the index
• preparing for alter table: The server is preparing to execute in ALTER TABLE statement of -place algorithm - Repair done: This thread has completed the multi-threaded repair of the MyISAM table
• Repair with keycache: The repair code is repairing the index using the method of creating keys one by one through the key cache. This is much slower than the method of repairing by sorting the index
• Rolling back: The thread is rolling back the transaction
• Saving state: For MyISAM table operations (such as repair or analysis), the thread is rolling back the new table The status is saved to the .MYI file header. The status includes: information such as the number of table data rows, AUTO_INCREMENT counter and key
  distribution
• Searching rows for update: The thread is performing the first phase to find all matching rows before updating them. If UPDATE is changing the index used to find the rows involved, you must first find the rows that match the update.
• Sending data: The thread is reading and processing the data rows generated by the SELECT statement, and sending the data to the client. Because operations occurring during this state may generate significant disk access (reads), it is typically the longest running state within the lifetime of a given query.
• Sending to client: The server is sending to the client Write packet. Before MySQL 5.7.8, it was called "Writing to net"
• setup: The thread is performing an ALTER TABLE operation
• Sorting for group: The thread is performing a GROUP BY sorting operation
• Sorting for order: The thread is performing an ORDER BY sort operation
• Sorting index: The thread is sorting index pages to achieve more efficient access during the MyISAM table optimization operation
• Sorting result: For SELECT statement, this is similar to the "Creating sort index" status, but for non-temporary tables
• statistics: The server is calculating statistics to optimize the query execution plan. If a thread is in this state for a long time, the server may be performing other work on the disk and blocking the operation of statistical information, or a lock wait may occur.
• System lock: The thread called mysql_lock_tables() and the thread status was never updated. This is a very common state and can occur for many reasons. For example, a thread will request or be waiting for an internal or external system lock on a table. This can happen when InnoDB is waiting for table-level locks during LOCK TABLES. If this state is caused by an external lock request, you can use the –skip-external-locking option to disable external system locking if you are not using multiple mysqld servers to access the same MyISAM table. However, external locking is disabled by default, so this option may have no effect.
  For SHOW PROFILE, this status indicates that the thread is requesting a lock
• update: The thread is preparing to start updating the table
• Updating: The thread is searching and updating data rows
• updating main table: server The first part of the multi-table update statement is being executed. This status indicates that the first table is being
  updated and column values ​​and offsets are saved for use in updating other (reference) tables
• updating reference tables: server is executing the second of a multi-table update statement section, updates matching rows of other tables
  
• User lock: The thread will request or is waiting for the proposed lock requested through a GET_LOCK() call. For SHOW PROFILE, this status indicates that the thread is requesting the lock (without waiting)
• User sleep: The thread has called SLEEP() calling
• Waiting for commit lock: FLUSH TABLES WITH READ LOCK statement is getting Submit lock
• Waiting for global read lock: FLUSH TABLES WITH READ LOCK Waiting to acquire global read lock or global read_only system variable setting
• Waiting for tables: The thread gets a notification, the bottom layer of the table The structure has changed and it requires reopening the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed access to the table for the old data structure. This notification occurs if another thread has used 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

##Waiting for table flush: The thread is executing FLUSH TABLES and is waiting All threads close the table being accessed, or a thread is notified that the underlying structure of a table has changed and it needs to reopen the table to obtain the new structure. However, to reopen the table, it must wait until all other threads have closed access to the old table structure. This notification occurs if another thread has used 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

Waiting for lock_type lock: server is waiting to obtain A THR_LOCK lock or an MDL lock obtained from the metadata lock subsystem, where lock_type indicates the type of MDL lock that is waiting to be obtained. There is only one type of THR_LOCK (Waiting for table level lock). There are several types of MDL locks:

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 on cond: The thread is A general state that waits for a condition to become true. No specific status information available

• Writing to net: The server is writing packets to the network. From MySQL 5.7.8 onwards it is called "Sending to client"

4. Dump thread status

Finished reading one binlog; switching to next binlog : The thread has finished reading the binlog file

and switched to the next binlog file
• 
  Master has sent all binlog to slave; waiting for more updates: The thread has read all remaining binlog files from the binary log update logs and send them to the slave library. The thread is currently idle and is waiting for new updated data events to be written to the binary log
• Sending binlog event to slave: The thread has read an event from the binary log and now sends it to the slave library (The binary log is composed of events. An event is usually composed of updated data and some other information)
• Waiting to finalize termination: A very short-lived state that occurs when the thread stops, and the thread is executing to stop the thread related Action

5. IO thread status

• Checking master version: A very short state after establishing a connection with the main library, indicating that the version number of the main library is being checked
• Connecting to master: The thread attempts to connect to the main library
• Queueing master event to the relay log: The thread has read an event and copied it to the relay log for replay by the SQL thread
• Reconnecting after a failed binlog dump request: The thread is trying Reconnecting to the master library
• Reconnecting after a failed master event read: The thread is trying to reconnect to the master library. When the reconnection is successful, the status will change to "Waiting for master to send event" - Registering slave on master: A very short state after successfully connecting to the main library, indicating that the connection information of the slave library is being registered with the master library (such as the IP and port information of the slave library, etc.)
• Requesting binlog dump: After connecting to the master library In a very short state after the library successfully establishes a connection, it uses the current
  I/O thread position to send a request to the main library for the contents of the binary log starting from the current position
• Waiting for its turn to commit : If the slave_preserve_commit_order parameter is enabled,
  indicates that the slave I/O thread is waiting for an older worker thread to submit data
• Waiting for master to send event: The thread has been connected to the master library and is waiting for a new one Binary
  log events, this may last for a long time if the main library is idle. If the wait continues for more than slave_net_timeout seconds, the slave I/O thread times out. At this time, the slave library I/O thread thinks that the connection to the master library is disconnected, and will try to reconnect to the master library
• Waiting for master update: The initial state before connecting to the master library
• Waiting for slave mutex on exit: A state that occurs briefly when the thread stops, indicating that the related mutually exclusive resources of the I/O thread are being recycled
• Waiting for the slave SQL thread to free enough relay log space: If the relay_log_space_limit variable setting value is not is 0, then when the total relay log size grows beyond this value. The I/O thread will wait until the SQL thread releases the space occupied by the relay log by replaying the relay log content and deleting the replayed relay log, so that the size of the relay log is no larger than the value of the relay_log_space_limit variable. , the I/O thread can continue writing relay log operations.
• Waiting to reconnect after a failed binlog dump request: If the binary log dump request fails (due to disconnection), then the thread enters the sleep state. This state occurs at this time, and then the I/O thread periodically tries to reconnect. Connect to the main library. The interval between retries can be specified using the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement.

• It should be noted that the I/O thread of the slave library has a heartbeat mechanism when connecting to the main library. When no new event is sent to the slave after this heartbeat time, the I/O thread initiates a heartbeat request to the main library. If the request is successful, the heartbeat time is reset. When the main library sends a new event to the slave, this heartbeat time A reset will also occur. Heartbeat time Set by the MASTER_HEARTBEAT_PERIOD option of the change master statement (in seconds), range 0 to 4294967 seconds, resolution (milliseconds) The minimum non-zero value is 0.001, representing 1 millisecond. Setting the interval to 0 disables heartbeats. The default value is one-half the slave_net_timeout configuration parameter. So, in theory, there will be no situation where the I/O thread of the slave library is disconnected because the master library does not write data when the master-slave database is normal.

• Waiting to reconnect after a failed master event read: An error occurred while reading the main library binlog (due to disconnection). Before the I/O thread attempts to reconnect to the main library, the thread is sleeping for the number of seconds set by the MASTER_CONNECT_RETRY option (default is 60) of the CHANGE MASTER TO statement (this time is the retry interval after a failed reconnection)

6. SQL thread status

• Killing slave: The thread is processing the STOP SLAVE statement
• Making temporary file (append) before replaying LOAD DATA INFILE: The thread is executing the LOAD DATA INFILE statement and will read the data from the library Add to temporary file
• Making temporary file (create) before replaying LOAD DATA INFILE: The thread is executing the LOAD DATA INFILE statement and is creating a temporary file. The temporary file contains the row data to be read from the library. Note: This state can only be encountered when the main library records the original LOAD DATA INFILE statement in versions prior to MySQL 5.0.3
• Reading event from the relay log: The thread is reading from the relay log Events for replay
• Slave has read all relay log; waiting for more updates: The thread has redone all events in all relay log files and is waiting for the I/O thread to send the relay log to the relay log. Write new events in
• Waiting for an event from Coordinator: When the slave library uses multi-thread replication (slave_parallel_workers is greater than 1), this status indicates that a slave works thread is waiting for the coordinator thread (Coordinator thread) to allocate logs Event
• Waiting for slave mutex on exit: A very short state that occurs when the thread stops
• Waiting for Slave Workers to free pending events: When the total number of events processed by the Workers thread exceeds slave_pending_jobs_size_max When the size of the system variable is exceeded, a wait operation occurs (the coordinator thread does not assign events to worker threads). The coordinator resumes scheduling when the total number of events processed by Workers threads falls below the slave_pending_jobs_size_max limit. This state will only occur when slave_parallel_workers is set to greater than 0
• Waiting for the next event in relay log: The initial state before the "Reading event from the relay log" state
• Waiting until MASTER_DELAY seconds after master executed event: The SQL thread has read the event, but has not applied it. Instead, it is waiting for the delay copy time set by the slave library to expire. This delay time is set using the MASTER_DELAY option of CHANGE MASTER TO
  ⚫ The Info column of the SQL thread can also display the text of the statement. This means that the thread has read an event from the relay log, extracted the SQL statement from it, and may currently be executing the event corresponding to this statement.

7. Master-slave connection thread status

• Changing master: The thread is processing the CHANGE MASTER TO statement
• Killing slave: The thread is processing the STOP SLAVE statement
• Opening master dump table: This state occurs after the main library creates the dump table
• Reading master dump table data: The state that occurs after the "Opening master dump table" state , indicating that data is being read from the master database dump table
• Rebuilding the index on master dump table: The state that appears after the "Reading master dump table data" status indicates that the master database dump table index is being rebuilt

8. Event Scheduling Thread Status

• Clearing: The scheduler thread is stopping to execute the event
• Initialized: The scheduler thread has been initialized. A scheduled event is about to be executed
• Waiting for next activation: When the scheduler has a non-empty event queue, it is waiting for an event in the activation queue to be activated at some point in the future in order to schedule and execute
• Waiting for scheduler to stop: The thread issues SET GLOBAL event_scheduler = OFF and waits for the scheduler to stop
• Waiting on empty queue: The scheduler's event queue is empty, so the scheduler is sleeping

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Detailed explanation of MySQL thread status. For more information, please follow other related articles on the PHP Chinese website!