MySQL SHOW PROCESSLIST assists in the entire process of troubleshooting

1. SHOW PROCESSLIST command

SHOW PROCESSLIST shows the threads that are running (user-run threads) (or SHOW FULL PROCESSLIST shows more information). You can also obtain this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can only see your own threads (that is, the threads associated with the MySQL account you are using). If you do not use the FULL keyword, the first 100 characters of each statement are displayed in the Info field.

Process information is also available from the performance_schema.threads table. However, access to threads does not require a mutex lock and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences due to the need for a mutex. performance_schema.threads also displays information about background threads, which is not available from INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST , meaning that performance_schema.threads can be used to monitor an additional source of thread information for activity.

The SHOW PROCESSLIST statement is useful if you get a "Too many connections" error message and want to understand what is going on. MySQL reserves one extra connection for accounts with the SUPER privilege to ensure that administrators can always connect and examine the system (assuming you do not grant this privilege to all users).

Threads can be killed using the KILL statement.

The following is example SHOW PROCESSLIST output:

mysql> show full processlist\G
*************************** 1. row ***************************
  Id: 123
 User: root
 Host: localhost
  db: performance_schema
Command: Query
 Time: 0
 State: starting
 Info: show full processlist
1 row in set (0.00 sec)

The columns produced by SHOW PROCESSLIST have the following meanings:

The connection identifier, which is the same type of value that appears in the ID column of the INFORMATION_SCHEMA.PROCESSLIST table, in the PROCESSLIST_ID column of the threads table of the performance view, and in the values returned by the CONNECTION_ID() function.

User

The MySQL user issuing the statement; if it is the system user, it refers to the nonclient thread spawned by the server to handle tasks internally. This might be the I/O or SQL thread used by a replication slave or a delayed row handler. An unauthenticated user is a thread that has been associated with a client connection but has not yet completed authentication of the client user. event_scheduler refers to the thread that monitors scheduled events. If it is a system user, no host will be specified in the Host column.

Host

The host name of the client that issued the statement (except for system user, which has no host name), to make it easier to determine which client is doing what, displayed as host_name:client_port.

The default database for the currently executing statement, if one is selected; otherwise NULL.

Command

Displays the command that this thread is currently executing, which usually corresponds to a DDL or DML statement.

Time

Indicates how long the thread has been in the current state. The thread's concept of current time may change in some cases: the thread can change time. For a thread running on a slave that is processing an event from a master, the thread time is set to the time found in the event, and thus reflects the current time on the master rather than the slave. SET TIMESTAMP = value.

State

Corresponding to the Command instruction, most states correspond to very fast operations. If a thread remains in a given state for multiple seconds, there may be a problem that needs to be investigated.

Info

Contains the text of the statement executed by the thread, or NULL if it is not executing. By default, this value contains only the first 100 characters of the statement. To view the complete statement, use SHOW FULL PROCESSLIST.

2. Thread Command Value

A thread can have any of the following Command values:

Binlog Dump: This is the thread on the master server that is used to send the binary log contents to the slave server.

Table Dump: The thread sends the table contents to the slave server.

Change user: The thread is performing a change user operation.

Close stmt: The thread is closing a prepared statement.

Connect: During replication, a slave server connects to its master server.

Connect Out: In replication, the slave server is connecting to its master server.

Create DB: The thread is executing a create-database operation.

Daemon: This thread is internal to the server, not the thread that services client connections.

Debug: The thread is generating debug information.

Delayed insert: The thread is a delayed insert handler.

Drop DB: The thread is performing a drop-database operation.

Execute: The thread is executing a prepared statement (the prepare statement type is a precompiled statement, and JDBC supports this type of SQL execution).

Fetch: The thread is executing the results of a prepared statement.

Field List: The thread is retrieving information about table columns.

Init DB: The thread is selecting a default database.

Kill: The thread is killing another thread.

Long Data: This thread retrieves long data as a result of executing a prepared statement.

Ping: The thread is processing a server ping request.

Prepare: The thread is generating an execution plan for the statement.

Processlist: The thread is generating information about the server threads.

Query: The thread is executing a statement.

Quit: The thread is terminating.

Refresh: The thread is refreshing a table, log, or cache, or resetting status variables or replicating server information.

Register Slave: The thread is registering a slave server.

Reset stmt: The thread is resetting a prepared statement.

Set option: The thread is setting or resetting a 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.

Statistics: The thread is generating server status information.

Time: Never used.

3. Thread State Value

General thread state value

The following list describes the State thread values associated with general query processing, as opposed to more complex activities such as replication. Many of these are only used to find bugs in the server.

After create: When a thread creates a table (including an internal temporary table), it is created at the end of the function that creates the table. This status is used even if the table cannot be created due to some error.

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 checking operation.

cleaning up: The thread has processed a command and is preparing to free up memory and reset some state variables.

closing tables: The thread flushes changed table data to disk and closes used tables. This should be a quick operation. If not, verify that you don't have a full disk, and that the disk is not extremely heavily used.

copy to tmp table: The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created, but before the rows have been copied to the table. For threads in this state, you can use the Performance Schema to get information about the progress of the copy operation.

Copying to group table: If the statement has different ORDER BY and GROUP BY criteria, the rows are arranged in groups and copied to a temporary table.

Creating index: The thread is processing ALTER TABLE ... ENABLE KEYS of a MyISAM table.

Creating sort index: The thread is processing a SELECT parsed using an internal temporary table.

creating table: The thread is creating a table, which includes creating temporary tables.

committing alter table to storage engine: The server has completed the 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 is deleting only from the first table and saving the columns and offsets to be used for the delete from the other (reference) tables.

deleting from reference tables: The server is performing the second part of a multiple-table delete and deleting matching rows from the other tables.

discard_or_import_tablespace : The thread is processing an 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 a statement.

Execution of init_command: The thread is executing statements in the value of the init_command system variable.

freeing items: The thread has executed a command. Some of the freeing of items done in this state involves querying the cache. This state is usually followed by cleaning up.

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

init: This operation occurs before initializing ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE. The actions taken by the server in this state include flushing binary logs, Innodb logs, and some query cache cleanup operations. For the final state, the following actions may occur: query cache entries are deleted after changing data in the table, events are written to the binary log, and memory buffers, including blobs, are released.

Killed: The KILL statement is executed, sending a statement to the thread that it should be interrupted the next time the kill flag is checked. The flag is checked in every main loop of MySQL, but in some cases it may take a short time for a thread to die. If the thread is locked by some other thread, the kill will take effect as soon as the other thread releases the lock.

Locking system tables: The thread is trying to lock a system table (for example, the time zone or log table).

manage keys: The server enables or disables table indexes.

NULL: This status is used for SHOW PROCESSLIST status.

Opening system tables: The thread attempted to open a system table (for example, the time zone or log table).

Opening tables: The thread is trying to open a table, this should be a very fast procedure unless something is preventing the open. For example, an ALTER TABLE or a LOCK TABLE statement can prevent a table from being opened until the statement completes. You may also need to pay attention to whether the value of the table_open_cache parameter is large enough. For system tables, use the Opening system tables state.

optimizing: The server is performing initial optimization of the query.

preparing: This state occurs during query optimization.

Purging old relay logs: The thread is deleting unnecessary relay log files.

query end: This state occurs after processing the query and before the freeing items state.

Removing duplicates: The use of SELECT DISTINCT in this query prevents MySQL from optimizing distinct operations at an early stage. Therefore, MySQL needs an extra stage to remove all duplicate rows before sending the result to the client.

removing tmp table: The thread is removing an internal temporary table after processing a SELECT statement. If no temporary table is created, this status is not used.

rename: The thread is renaming a table.

rename result table: The thread is processing an ALTER TABLE statement, has created the new table, and renamed it to replace the original table.

Reopen tables: The thread obtained a lock on a table, but noticed after obtaining the lock that the underlying table structure changed. It releases the lock, closes the table, and tries to reopen it.

Repair by sorting: The repair code is using sorting to create the index.

preparing for alter table: The server is preparing to execute ALTER TABLE in place.

Repair done: The thread has completed a multi-threaded repair of a MyISAM table.

Repair with keycache: The repair code creates keys using one by one through the key cache, which 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 saves 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 doing the first phase to find all matching rows before updating. This is necessary if the UPDATE is to change the index used to find the rows involved.

setup: The thread is starting an ALTER TABLE operation.

Sorting for group: The thread is doing a query that satisfies a GROUP BY.

Sorting for order: The thread is doing a query that satisfies an ORDER BY.

Sorting index: The thread is sorting index pages for more efficient access during MyISAM table optimization operations.

Sorting result: For a SELECT statement, this is similar to Creating sort index, but for non-temporary tables.

statistics: The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk bound, performing other work.

update: The thread is preparing to start updating the table.

Updating: The thread is searching for rows to update and is 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 the columns and offsets used to update the other (referenced) tables.

Updating reference tables: The server is performing the second part of a multiple-table update and updating matching rows from the other tables.

User lock: The thread is about to request or is waiting for an advisory lock requested via a GET_LOCK() call. Because of SHOW PROFILE, this state means that the thread is requesting the lock (not waiting for it).

User sleep: The thread calls a SLEEP() call.

Fault diagnosis state value (personal extraction)

logging slow query: The thread is writing statements to the slow query log.

altering table: The server is performing an in-place ALTER TABLE.

Receiving from client: The server is reading packets from the client.

Copying to tmp table: The server is copying a temporary table from disk to memory. This is a temporary table created directly on disk rather than transferred from memory to disk.

Copying to tmp table on disk: The state when the thread copies the temporary table from disk to memory after changing it from memory to disk-based storage to save memory.

Creating tmp table: The thread is creating a temporary table in memory or on disk. If the table is created in memory but later converted to a disk table, the status in the operation will be Copying to tmp table on disk.

Sending data: The thread is reading and processing rows from a SELECT statement and sending data to the client. Because operations occurring during this state tend to perform a large number of disk accesses (reads), it is typically the longest running state in the entire lifetime of a given query.

Sending to client: The server is writing a data packet to the client.

Waiting for commit lock: FLUSH TABLES WITH READ LOCK is waiting for a commit lock.

Waiting for global read lock: FLUSH TABLES WITH READ LOCK is waiting for a global read lock or the read_only global system variable is being set.

Waiting for tables: The thread gets a notification that the underlying structure of the table has changed and needs to reopen the table to obtain the new structure. However, to reopen the table, you must wait until all other threads have closed the table. Notification occurs if another thread has used FLUSH TABLES or one of the following statements: FLUSH TABLES tbl_name , ALTER TABLE , RENAME TABLE , REPAIR TABLE , ANALYZE TABLE , or OPTIMIZE TABLE .

Waiting for table flush: The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread gets a notification that the underlying structure in the table has changed and needs to reopen the table to obtain the new structure. However, to reopen the table, you must wait until all other threads have closed the table. This notification is issued if another thread has used FLUSH TABLES or one of the following statements: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

Waiting for lock_type lock: The server is waiting to acquire a THR_LOCK lock or a lock from the metadata locking subsystem, where lock_type indicates the type of lock. The THR_LOCK state means: 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.

Writing to net: The server is writing data packets to the network. If a thread is executing for a long time and is always in the Writing to net state, it is sending data packets to the network all the time. You can try to adjust the max_allowed_packet size. Additionally, this may cause significant blocking of other threads.

Waiting on cond: A general state in which the thread is waiting for a condition to become true. No specific state information is available.

System lock: The thread has called mysql_lock_tables() and the thread state has never been updated. This is a very common condition 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 a table-level lock while executing LOCK TABLES. If this state is caused by requesting external locks, and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks by using the --skip-external-locking option. However, external locking is disabled by default, so there is a high chance that this option will not work. Because of SHOW PROFILE, this state means that the thread is requesting the lock (not waiting for it). For system tables, use the Locking system tables state.

Query cache state value

checking privileges on cached query: The server is checking whether the user has privileges to access cached query results.

checking query cache for query: The server is checking whether the current query exists in the query cache.

invalidating query cache entries: A query cache entry is marked as invalid because the underlying table has changed.

sending cached result to client: The server is getting the result of the query from the query cache and sending it to the client.

storing result in query cache: The server stores the query results in the query cache.

Waiting for query cache lock: This state occurs when the session is waiting to take the query cache lock. This situation may require some query cache operations to be performed, such as INSERT or DELETE statements that invalidate the query cache, RESET QUERY CACHE, and so on.

Event Scheduler Thread State Value

These states apply to the event dispatcher thread, the thread created to execute dispatched events, or the thread that terminates the dispatcher.

Clearing

The scheduler thread or the thread executing the event is terminating and will soon end.

Initialized

The scheduler thread, or the thread that will execute events, is initialized.

Waiting for next activation

The scheduler has a non-empty event queue, but the next activation is in the future.

Waiting for scheduler to stop

The thread issued SET GLOBAL event_scheduler=OFF and is waiting for the scheduler to stop.

Waiting on empty queue

The scheduler's event queue is empty and it is sleeping.

other

In addition to the above categories, there are also replication master thread state (State) value, replication slave IO thread state (State) value, replication slave SQL thread (State) value, replication slave Connect thread (State) value, details can be seen: MySQL master-slave replication thread state transition

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in: