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

The editor will share with you the example analysis of the whole process of fault diagnosis assisted by SHOW PROCESSLIST in MySQL. I hope you will get something after reading this article. Let's discuss it together.

1. SHOW PROCESSLIST command

SHOW PROCESSLIST shows the threads that are running (the user is running threads) (or SHOW FULL PROCESSLIST displays more information). You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have this PROCESS privilege, you can see all the threads. Otherwise, you can only see your own thread (that is, the thread 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 mutexes and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences due to the need for mutexes. Performance_schema.threads also shows what information about background threads is not available in INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST, which means that performance_schema.threads can be used to monitor other thread information sources of activity.

SHOW PROCESSLIST this statement is useful if you receive a "too many connections" error message and want to know what happened. MySQL reserves an additional connection for authorized accounts to use SUPER to ensure that administrators can always connect and check the system (assuming you don't grant this permission to all users).

A thread can be killed with a KILL statement.

The following is an example of SHOW PROCESSLIST output:

Mysql > show full processlist\ gateway * 1. Row * * Id: 123 User: root Host: localhost db: performance_schemaCommand: Query Time: 0 State: starting Info: show full processlist1 row in set (0.00 sec)

The resulting column SHOW PROCESSLIST has the following meanings:

Id

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

User

The declared MySQL user, in the case of system user, refers to non-client threads generated by the server to process tasks internally. This could be a replication slave or an I / O or SQL thread used by a deferred line processor. Unauthenticated user refers to a thread that has been associated with a client connection but has not yet completed the authentication of the client user. Event_scheduler refers to the thread that monitors scheduled events. If it is system user, then the host is not specified in the Host column.

Host

The hostname of the client that issued the statement (except that system user does not have a host) to make it easier to determine which client is doing what, displayed as host_name:client_port.

Db

The default database for the current execution statement, if selected; otherwise, NULL.

Command

Displays the commands that this thread is executing at the moment, usually corresponding to DDL or DML statements.

Time

Indicates how long the thread is in its current state, and the concept of the thread's current time may change in some cases: the thread can change the time. For threads running on the slave station that processes events from the host, the thread time is set to the time found in the event, thus reflecting the current time of the master rather than the slave. SET TIMESTAMP = value.

State

Most states correspond to very fast operations corresponding to Command instructions. If the thread stays in a given state for multiple seconds, there may be problems that need to be investigated.

Info

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

Second, thread command (Command) value

A thread can have any of the following Command values:

Binlog Dump: this is the thread on the master server that sends 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 the prepared statement.

Connect: in replication, the 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 performing a create-database operation.

Daemon: this thread is inside the server, not the thread that serves the client connection.

Debug: the thread is generating debugging information.

Delayed insert: a thread is a deferred 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 the precompiled statement, and JDBC supports subtype execution of SQL).

Fetch: the thread is executing the result of a prepare statement.

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 another thread.

Long Data: this thread retrieves long data in the result of executing a prepare statement.

Ping: the thread is processing the server ping request.

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

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

Query: this thread is executing a statement.

Quit: thread is terminating.

Refresh: threads refresh tables, logs, or caches, or reset state variables or copy server information.

Register Slave: the thread is registering the slave server.

Reset stmt: the thread is resetting a prepared statement.

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

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 it.

Thread status (State) value

General thread state (State) value

The following list describes the thread values associated with regular query processing rather than more complex activities such as replication. Many of them are used only to find errors 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 created the table. This state 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 to see if the server has the necessary permissions to execute the statement.

Checking table: the thread is performing a table check operation.

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

Closing tables: the thread refreshes the changed table data to disk and closes the used table. This should be a quick operation. If not, verify that you do not have a complete disk and that the disk is not very heavily used.

Copy to tmp table: the thread is processing the ALTER TABLE statement. This state occurs after a table with a new structure has been created, but before the rows are copied to the table. For threads in this state, you can use performance mode to get the progress of the replication operation.

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

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

Creating sort index: a thread is processing a thread that SELECT parses using an internal temporary table.

Creating table: the thread is creating a table, which includes creating a temporary table.

Committing alter table to storage engine: the server has completed the ALTER TABLE in place and submitted the results.

Deleting from main table: the server is performing the first part of a multi-table deletion, which only deletes from the first table and saves the columns and offsets to be used for deletion from other (referenced) tables.

Deleting from reference tables: the server is performing the second part of a multi-table deletion and deleting matching rows from other tables.

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

End: this occurs before the ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statement is finished but cleaned up.

Executing: this thread has already started executing a statement.

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

Freeing items: the thread has executed a command, and some releases of items completed in this state involve query caching, which is usually followed by cleaning up.

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

Init: this occurs before initializing ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE, and the actions taken by the server in this state include refreshing binary logs, Innodb logs, and some query cache cleanup operations. For the final state, the following actions may occur: delete query cache entries after changing the data in the table, write events to the binary log, and free memory buffers, including blob.

Killed: executes the KILL statement and sends a declaration to the thread that should be interrupted the next time the kill flag is checked. Check this flag in each main loop of the MySQL, but in some cases it may take a short time for the thread to die. If a thread is locked by another thread, the kill takes effect as soon as the lock is released by another thread.

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

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

Manage keys: the server enables or disables table indexing.

NULL: this state is used for the SHOW PROCESSLIST state.

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

Opening tables: the thread is trying to open a table, which should be a very fast program unless something prevents it. For example, an ALTER TABLE or a LOCK TABLE statement can prevent a table from being opened until the statement is complete. You may also need to be concerned about whether the value of the table_open_cache parameter is large enough. For system tables, use Opening system tables status.

Optimizing: the server is performing the initial optimization of the query.

Preparing: this state occurs during query optimization.

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

Query end: after processing the query, this state occurs before the freeing items state.

Removing duplicates: the use of SELECT DISTINCT in this query prevents MySQL from optimizing different operations 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: after processing the statement, the thread is deleting an internal temporary table SELECT. If no temporary table is created, this state is not used.

Rename: the thread is renaming a table.

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

Reopen tables: the thread acquires the lock on the table, but notices it after acquiring the lock on the underlying table structure change. 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 multithreaded repair of the MyISAM table.

Repair with keycache: the repair code creates keys 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 the number of rows, AUTO_INCREMENT counters, and key distribution.

Searching rows for update: the thread is in the first phase to find all matching rows before updating. You must do this if UPDATE wants 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 one to satisfy a GROUP BY.

Sorting for order: the thread is doing one to satisfy an ORDER BY.

Sorting index: threads are sorted 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 may be disk-bound and perform 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, updating only the first table and saving the columns and offsets used to update other (referenced) tables.

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

User lock: the thread will require or is waiting for a consultative lock requested through a GET_LOCK () call. Because of SHOW PROFILE, this state means that the thread is requesting a lock (not waiting for it).

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

Troubleshooting status (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 temporary tables from disk to memory, which are temporary tables created directly on disk rather than transferred from memory to disk.

Copying to tmp table on disk: the state when a thread changes a temporary table from memory to disk-based format to save memory and then copies the temporary table from disk to memory.

Creating tmp table: the 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 in the operation will be Copying to tmp table on disk.

Sending data: the thread is reading and processing rows of SELECT statements and sending data to the client. Because operations that occur during this state tend to perform a large amount of disk access (read), it is usually the longest running state for the entire life cycle of a given query.

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

Waiting for commit lock:FLUSH TABLES WITH READ LOCK is waiting for the lock to be committed.

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

Waiting for tables: the thread is notified that the underlying structure of the table has changed and needs to reopen the table to get the new structure. However, to reopen the table, you must wait until all other threads close 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: threads are executing FLUSH TABLES and are waiting for all threads to close their tables, or threads are notified that the underlying structure in the table has changed and that the table needs to be reopened to get a new structure. However, to reopen the table, you must wait until all other threads close 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 for THR_LOCK to acquire a lock or lock from the metadata locking subsystem, where lock_type indicates the type of lock. THR_LOCK states: Waiting for table level lock; these states mean 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 packets to the network. If a thread has been executing for a long time and has been in a Writing to net state, then it has been sending packets to the network, so you can try to resize the max_allowed_packet. In addition, this may cause a large number of other threads to block.

Waiting on cond: the thread wait condition becomes the general state of true, and there is no specific state information available.

System lock: the thread has called mysql_lock_tables () and the thread state has never been 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 waits for a table-level lock while executing a table lock. If this state is caused by requesting an external lock and you are not using multiple mysqld server MyISAM that are accessing the same table, you can use this-skip-external-locking option to disable external system locks. However, external locking is disabled by default, so this option is likely to not work. Because of SHOW PROFILE, this state means that the thread is requesting a lock (not waiting for it). For system tables, use Locking system tables status.

Query cache status (State) value

Checking privileges on cached query: the server is checking to see if the user has access to cached query results.

Checking query cache for query: the server is checking to see if the current query exists in the query cache.

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

Sending cached result to client: the server is getting the results 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 for a query cache lock. This situation may require some query caching operations, such as INSERT or DELETE statements that invalidate the query cache, RESET QUERY CACHE, and so on.

Event Scheduler thread state (State) value

These states apply to event scheduler threads, creating threads that execute scheduled events or threads that terminate the scheduler.

Clearing

The scheduler thread or the thread executing the event is terminating and is about to end.

Initialized

The scheduler thread or the thread that will execute the event has been 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 issues 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 such as copying master thread state (State) values, copying slave library IO thread state (State) values, copying slave library SQL thread (State) values, copying slave library Connect thread (State) values, details can be seen: MySQL master slave replication thread state transition

After reading this article, I believe you have a certain understanding of "sample analysis of the whole process of SHOW PROCESSLIST-assisted fault diagnosis in MySQL". If you want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.