Explanation of MySQL performance inspection through show processlist command

The show processlist command is very useful. Sometimes MySQL often runs to more than 50% or more, so you need to use this command to see which SQL statement occupies more resources, so you can know which website has a program problem.

The output of the show processlist command shows which threads are running, which can help identify problematic queries. If you have the SUPER privilege, you can see all threads. Otherwise, you can only see the threads you initiated (the threads running under the current MySQL account).

Let me first briefly talk about the meaning and purpose of each column

The first column, id, is a flag, which is very useful when you want to kill a statement.

• The user column shows the current user. If you are not the root user, this command will only show the SQL statements within your permission range.
• The host column shows the IP and port from which the statement was issued. Haha, it can be used to track down the user who made the problematic statement.
• The db column shows which database the process is currently connected to.
• The command column displays the command executed by the current connection, which is usually sleep, query, or connect.
• The time column is the duration of this state in seconds.
• The state column shows the status of the SQL statement using the current connection. It is a very important column. All the statuses will be described later. Please note that state is only a certain status in the execution of a statement. For example, a SQL statement may need to go through the states of copying to tmp table, sorting result, and sending data before it can be completed.
• The info column displays the SQL statement. Due to its limited length, long SQL statements are not fully displayed, but this is an important basis for judging problematic statements.

The most critical part of this command is the state column. The states listed by MySQL are mainly the following:

Checking table

Checking data tables (this is automatic).

Closing tables

The modified data in the table is being flushed to disk, and the table that has been used is being closed. This is a very quick operation, but if it is not, you should check to see if the disk is full or if the disk is under heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Since the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to hold partial query results.

deleting from main table

The server is executing the first part of a multiple-table delete and has just deleted the first table.

deleting from reference tables

The server is executing the second part of a multi-table delete and is deleting records from other tables.

Flushing tables

FLUSH TABLES is being executed, waiting for other threads to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag and abandon the next kill request. MySQL checks the kill flag in each main loop, but in some cases it may take a while for the thread to die. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.

Locked

Locked by another query.

Sending data

The records of the Select query are being processed and the results are being sent to the client.

Sorting for group

Sorting for GROUP BY.

Sorting for order

Sorting for ORDER BY.

Opening tables

This process should be quick unless interfered with by other factors. For example, the data table cannot be opened by other threads until the Alter TABLE or LOCK TABLE statement is completed. Trying to open a table.

Removing duplicates

A Select DISTINCT query is being executed, but MySQL was unable to optimize away the duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records again and then send the results to the client.

Reopen table

A lock on a table is obtained, but the lock can only be obtained after the table structure is modified. The lock has been released, the table has been closed, and an attempt is being made to reopen the table.

Repair by sorting

Fix instructions being sorted to create index.

Repair with keycache

The repair instruction is creating new indexes one by one using the index cache. It will be slower than Repair by sorting.

Searching rows for update

The records that meet the conditions are being found for update. It must be completed before Update can modify related records.

Sleeping

Waiting for new client request.

System lock

Waiting to acquire an external system lock. If you are not running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the --skip-external-locking parameter.

Upgrading lock

Insert DELAYED is trying to acquire a table lock to insert a new record.

Updating

Searching for matching records and modifying them.

User Lock

Waiting for GET_LOCK().

Waiting for tables

The thread is notified that the data table structure has been modified and needs to be reopened to obtain the new structure. Then, in order to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following situations: FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

waiting for handler insert

Insert DELAYED has processed all pending insert operations and is waiting for new requests.

Most states correspond to very fast operations. If a thread remains in the same state for several seconds, there may be a problem and it needs to be checked. There are other statuses not listed above, but most of them are only useful to see if there are any errors with the server.

Common counters

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. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links