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

This article mainly introduces the example analysis of MySQL thread state, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Article catalogue

1. Show processlist

II. Command command type

III. User thread status

IV. Dump thread status

5. IO thread status

VI. SQL thread status

7. Master-slave connection thread status

8. Event scheduling thread status

1. Show processlist

Id: the 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 means that a non-client thread generated by MySQL is performing an internal task. For example, slave in active and standby replication

The unauthenticated user O or SQL thread or the thread of the deferred line handler used on the library. "unauthenticated user" refers to the thread that the client has established an TCP/IP connection with the server but has not authenticated the user of the client with the user password. "event_scheduler" refers to the thread that monitors scheduled task scheduling events.

Host: the hostname of the client executing the statement, shown in host_name:client_port (or in ip:client_port format if the skip_name_resolve parameter is enabled)

Db: the default database for the client connection (if the library name is specified when connecting), otherwise it is displayed as NULL.

Command: the type of command that the thread is executing.

Time: the number of times, in seconds, that a thread has been in its current state. For the SQL thread from the library, this value is the number of seconds between the time the event was last copied and the actual time from the library.

State: prompts the thread what action, event, or state it is doing.

Info: the statement that the thread is executing.

II. Command command type

Binlog Dump: the master library thread is used to send binary log contents 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 library thread has connected to the master library

Connect Out: the slave library is connecting to the master library

Create DB: the thread is performing a database building operation

Daemon: this is the server internal thread, not the client connection thread

Debug: the thread is generating debugging information

Delayed insert: is a thread that delays inserting handlers

Drop DB: the thread is performing a drop database operation

Execute: the thread is executing a precompiled statement

Fetch: the thread is executing the statement and getting the result set from it

Field List: the thread is retrieving information about the table column

Init DB: the thread is selecting the default database

Kill: the thread is killing other threads

Long Data: the thread is executing the statement, retrieving and returning a data result set of the type of long field (large field)

Ping: the thread is processing the server ping request

Prepare: the thread is executing a precompiled statement

Processlist: threads are generating information about server threads

Query: thread is executing query statement

Quit: thread is terminating

Refresh: threads are refreshing tables, logs, or caches, or resetting state variables or copying server information

Register Slave: the thread is registering the slave library on the master library

Reset stmt: the thread is resetting precompiled statements

Set option: the thread is setting or resetting client statement execution options

Shutdown: the thread is performing a shutdown server

Sleep: the thread is waiting for the client to send it a new statement request

Statistics: thread is generating server status information

Table Dump: the thread is sending table contents to the slave library

III. User thread status

After create: this state occurs when a thread finishes creating a table (including internal temporary tables).

This state occurs even if there is a final error in creating the table due to some error

Analyzing: the thread is ANALYZE TABLE

Checking permissions: checking in server to see if the thread has the necessary permissions to execute the statement

Checking table: thread is performing a table check operation

Cleaning up: the thread has finished executing a command and is ready to free up memory and reset some state variables

Closing tables: the thread is flushing the data that the table has changed to disk and closing the table.

Converting HEAP to MyISAM: threads are converting internal temporary tables from MEMORY engine tables to disk MyISAM engine temporary tables

Copy to tmp table: the thread is executing the ALTER TABLE statement. This state occurs after the table of the new structure has been created, and before the execution of copy old table data into the new table.

Copying to group table: if the statement uses different ORDER BY and GROUP BY conditional columns, sort the row data by group by and copy the sort results to the temporary table

Copying to tmp table:server is copying data to a temporary table in memory

Altering table:server is performing the ALTER TABLE process of in-place

Copying to tmp table on disk:server is copying data to a disk temporary table. Because the temporary result set is too large, the thread is converting memory temporary tables to disk-based temporary tables to save memory

Creating index: the thread is executing an ALTER TABLE. ENABLE KEYS statement

Creating sort index: the thread is executing SELECT and using an internal temporary table

Creating table: the thread is creating the table. This state is also used when creating temporary tables, including

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 state during the operation will be "Copying to tmp table on disk"

Committing alter table to storage engine:server has executed the ALTER TABLE statement that completes the in-place algorithm and is submitting

Deleting from main table:server is executing the first part of the multiple table delete statement. See this.

The status indicates that it is being deleted from the first table and saves the column data and offsets that are later used to delete other tables

Deleting from reference tables:server is executing the second part of the multi-table delete statement to delete matching rows from other tables

Discard_or_import_tablespace: the thread is executing ALTER TABLE... DISCARD TABLESPACE or ALTER TABLE... IMPORT TABLESPACE statement

End: this occurs at the end of statement execution, but this state occurs before clearing the ALTER TABLE,CREATE VIEW,DELETE,INSERT,SELECT or UPDATE statement

Executing: the thread is executing a statement

Execution of init_command: the thread is executing a statement that initializes the system variable

Freeing items: the thread has executed a command. Release something that involves query cache state

Items. This state usually follows the cleaning up state.

FULLTEXT initialization:server is preparing to perform a natural language full-text search

Init: the state that occurs before the ALTER TABLE,DELETE,INSERT,SELECT or UPDATE statement is initialized. The actions performed by server in this state include refreshing binary logs, InnoDB logs, and some query cache cleanup operations. At the end of this state, there may be the following actions:

Delete query cache entries when the data in the table changes

Write events to the binary log

Free memory buffers, including blob

Killed: initiates a kill operation to the thread, and the thread should perform the termination operation. Check the thread's kill flag in each main loop of the MySQL, but in some cases, it may only take a short time to kill the thread. However, if the thread being kill is locked by another thread, you need to wait for the other thread to release the lock before the kill command takes effect and executes.

Logging slow query: the thread is writing a statement to the slow log

Login: the initial state of the connection thread until the client is successfully authenticated

Manage keys:server is enabling or disabling table indexes

NULL: this state is used for SHOW PROCESSLIST statements

Opening tables: the thread is trying to open a table. The open table operation should be very fast unless the open operation is blocked. For example, an ALTER TABLE or LOCK TABLE statement can prevent a table from being opened until the statement is complete. It is also possible that the table_open_cache is not large enough to open the table.

Optimizing:server is performing initial optimization on the query

Preparing: this state occurs during query optimization

Purging old relay logs: the thread is deleting unwanted relay log files

Query end: this state occurs after the query statement is executed but before the related state items of the query statement is released

Reading from net:server is reading packets from the network. After MySQL 5.7.8, this state is called "Receiving from client"-Receiving from client: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, it prevents MySQL from optimizing the distinct operation at an early stage. Therefore, MySQL needs an additional phase to delete all duplicate rows and then send the results to the client

Removing tmp table: the thread is deleting the internal temporary table after the execution of the SELECT statement is complete. This state does not occur if the SELECT statement does not create a temporary table

Rename: the thread is executing a rename statement to rename the table

Rename result table: the thread is executing the ALTER TABLE statement to rename the table, has created the new table, and is replacing the old table name with the new table

Reopen tables: the thread acquires the table lock, but after acquiring the lock, it finds that the underlying table structure has 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:server is preparing to execute the ALTER TABLE statement of the in-place algorithm-Repair done: the thread has completed multithreaded repair of the MyISAM table

Repair with keycache: the repair code is using the method of creating key one by one through keycache to repair the index. This is much slower than repairing through a sort index.

Rolling back: thread is rolling back transaction

Saving state: for MyISAM table operations, such as repair or analysis, the thread is saving the new table state to the .MYI header. Status includes: number of rows of table data, AUTO_INCREMENT counters and key

Information like distribution.

Searching rows for update: the thread is in the first phase to find all matching rows and then update them. If UPDATE is changing the index used to find the rows involved, you must first find the rows where the update satisfies the match

Sending data: the thread is reading and processing rows of data generated by the SELECT statement and sending the data to the client. Because an operation that occurs during this state can result in a large amount of disk access (read), it is usually the longest running state during the lifetime of a given query

Sending to client:server is writing a packet to the client. 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 sort operation

Sorting for order: the thread is performing an ORDER BY sort operation

Sorting index: threads are sorting index pages for more efficient access during MyISAM table optimization operations

Sorting result: for SELECT statements, this is similar to the "Creating sort index" state, but for non-temporary tables

Statistics:server is calculating statistics to optimize the query execution plan. If a thread has been in this state for a long time, the server may be doing something else on the disk that blocks the statistics operation, or a lock wait may occur.

System lock: the thread calls mysql_lock_tables (), and the thread state is never updated. This is a very common state, and there are many reasons for it. For example, a thread will request or wait for an internal or external system lock of the table. This can happen when InnoDB waits for table-level locks during LOCK TABLES execution. If this state is caused by an external lock request, if you do not use multiple mysqld servers to access the same MyISAM table, you can use the-skip-external-locking option to disable external system locks. However, external locking is disabled by default, so this option may not be valid.

For SHOW PROFILE, this state indicates that the thread is requesting a lock

Update: the thread is ready to update the table

Updating: threads search for and update rows of data

Updating main table:server is executing the first part of a multi-table update statement. This status indicates that you are

Update the first table and save column values and offsets for updating other (reference) tables

Updating reference tables:server is executing the second part of the multi-table update statement to update other tables

Matching rows of

User lock: the thread will request or is waiting for the recommended lock to be called through GET_LOCK (). For SHOW PROFILE, this state indicates that the thread is requesting a lock (without waiting)

User sleep: the thread has called the SLEEP () call

The Waiting for commit lock:FLUSH TABLES WITH READ LOCK statement is acquiring the commit lock

Waiting for global read lock:FLUSH TABLES WITH READ LOCK is waiting to acquire a global read lock or global read_only system variable setting

Waiting for tables: the thread gets a notification that the underlying structure of the table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other lines

Programs turn off access to tables of the old data structure. This notification appears if another thread has used FLUSH TABLES or one of the following statements in 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 for all threads to close the accessed table, or if the thread is notified that the underlying structure of the table has changed, it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have turned off access to the old table structure. This notification appears if another thread has used FLUSH TABLES or one of the following statements in 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 acquire a THR_LOCK lock or a MDL lock from the metadata locking subsystem, where lock_type represents the type of MDL lock that is waiting to be acquired. There is only one type of THR_LOCK (Waiting for table level lock), and there are several 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 waiting for the condition to become the common state of true. No specific status information is available

Writing to net:server is writing packets to the network. It is called "Sending to client" after MySQL 5.7.8.

IV. Dump thread status

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

And switch to the next binlog file

Master has sent all binlog to slave; waiting for more updates: the thread has read all remaining update logs from the binary log and sent them to the slave library. The thread is currently idle and is waiting for new events to update the data 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 consists of events, and an event is usually made up of data that has been updated and some other information)

Waiting to finalize termination: a very transient state that occurs when a thread stops and the thread is performing actions related to stopping the thread.

5. IO thread status

Checking master version: a very brief state after establishing a connection to 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 playback by the SQL thread

Reconnecting after a failed binlog dump request: the thread is trying to reconnect to the main library

Reconnecting after a failed master event read: the thread is trying to reconnect to the master library. When the reconnection is successful, the state will change to "Waiting for master to send event"-Registering slave on master: a very short state after the successful connection to the master library, indicating that the connection information of the slave library (such as IP and port information of the slave library, etc.) is being registered with the master library.

Requesting binlog dump: a very transient state after a successful connection to the main library, using when

Sends a request to the main library for the contents of the binary log starting from the current location

Waiting for its turn to commit: if the slave_preserve_commit_order parameter is enabled, the

Indicates that the Imax O thread from the library is waiting for the older worker thread to submit data

Waiting for master to send event: the thread is connected to the main library and is waiting for a new binary

Log events, which may last for a long time if the main library is idle. If the wait time lasts longer than slave_net_timeout seconds, the timeout occurs from the library IDB O thread. At this point, the slave library Ibind 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 main library

Waiting for slave mutex on exit: the state that occurs briefly when a thread is stopped, indicating that the relevant mutex resources of the Igamot O thread are being reclaimed

Waiting for the slave SQL thread to free enough relay log space: if the relay_log_space_limit variable is set to a value other than 0, the total size of the relay log grows to more than this value. The relay_log_space_limit O thread waits until the SQL thread replays the contents of the relay log and deletes the replayed completed relay log to free up the space occupied by the relay log so that it satisfies the value in the relay log that the size is not greater than the value of the relay variable.

Waiting to reconnect after a failed binlog dump request: if the binary log dump request fails (due to disconnection), the thread enters the sleep state, which occurs, and then the Ibino thread periodically attempts to reconnect 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 there is a heartbeat mechanism in connection with the master database by the Istroke O thread of the slave database. When the master database exceeds this heartbeat time and does not send a new event to the slave, it will issue a heartbeat request to the master database. If the request is successful, it will reset the heartbeat time. When the master database has a new event sent to slave, the heartbeat will also be reset. Heartbeat time is set by the MASTER_HEARTBEAT_PERIOD option (in seconds) of the change master statement, ranging from 0 to 4294967 seconds, with a resolution (millisecond) minimum non-zero value of 0.001, representing 1 millisecond. Setting the interval to 0 disables heartbeat. The default value is 1/2 of the slave_net_timeout configuration parameter. So, in theory, there will not be a situation in which the master and slave database is normally disconnected because the master database does not write data, which leads to the disconnection of the slave library IWeiO thread.

Waiting to reconnect after a failed master event read: an error occurred while reading the main library binlog (due to disconnection). Before trying to reconnect to the main library, the thread is sleep with the number of seconds set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default is 60) (this is the interval between retries after the reconnection fails)

VI. 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 adding the data to be read from the library to the 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 that contains the row data to be read from the library. Note: this state can only be encountered in versions prior to MySQL 5.0.3 when the main library recorded the original LOAD DATA INFILE statement.

Reading event from the relay log: the thread is reading events from the relay log for playback

Slave has read all relay log; waiting for more updates: the thread has redone all the events in all the relay log files and is waiting for the Iram O thread to write new events to the relay log

Waiting for an event from Coordinator: when multithreaded replication is used from the library (slave_parallel_workers is greater than 1), this state indicates that a slave works thread is waiting for the coordinator thread (Coordinator thread) to assign log events

Waiting for slave mutex on exit: a very transient state that occurs when a thread stops

Waiting for Slave Workers to free pending events: a wait operation occurs when the total number of events handled by the Workers thread exceeds the size of the slave_pending_jobs_size_max system variable (the coordinator thread does not assign events to the worker thread). The coordinator resumes scheduling when the total number of events handled by the Workers thread falls below the slave_pending_jobs_size_max limit. This state occurs only 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

The Waiting until MASTER_DELAY seconds after master executed event:SQL thread has read the event, but is not applying it, but is waiting for the delayed replication time set from the 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 and extracted the SQL statement, which may currently be executing the event corresponding to that 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 appears after the "Opening master dump table" status, indicating that data is being read from the main library dump table

Rebuilding the index on master dump table: the state that occurs after the "Reading master dump table data" status, indicating that the main library dump table index is being rebuilt

8. Event scheduling thread status

Clearing: the scheduler thread is stopping execution of the event

Initialized: the scheduler thread has been initialized and is about to execute the scheduling event

Waiting for next activation: when the scheduler has a non-empty event queue, it is waiting for an event in the queue to be activated at some point in the future for scheduling and execution

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 dormant

Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL Thread status" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related 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.