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

This article introduces you how to use the show processlist command to view performance in MySQL. The content is very detailed. Interested friends can refer to it for reference. I hope it can help you.

The user column displays a single previous user, if not root, this command displays only sql statements within your permission range.

The host column shows which port on which ip the statement originated. Oh, can be used to track down the problem sentence users.

The db column shows which database this process is currently connected to.

Command column, showing the current connection to execute the command, generally sleep (sleep), query (query), connect (connect).

Time column, the duration of this state, in seconds.

state column, showing the status of sql statements using the current connection, a very important column, followed by the description of all the states, please note that state is only a state in the execution of statements, a sql statement, queried for example, may need to go through copying to tmp table, Sorting result, Sending data and other states to complete

info column, showing this sql statement, because the length is limited, so long sql statements are not displayed completely, but this is an important basis for judging the problem statement.

The most important thing in this command is the state column. The states listed in mysql mainly include the following:

Checking table

Checking data tables (this is automatic).

Closing tables

Flushing modified data from tables to disk while closing exhausted tables. This is a quick operation, if not, you should make sure that disk space is full or that the disk is under pressure.

Connect Out

Replication slave is connecting to master.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, converting temporary tables from memory storage to disk storage to save memory.

Creating tmp table

Creating temporary tables to hold partial query results.

deleting from main table

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

deleting from reference tables

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

Flushing tables

Executing FLUSH TABLES, waiting for another thread to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag and discard the next kill request. MySQL checks the kill flag in each main loop, although in some cases the thread may take a short while to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

Locked

Locked by other queries.

Sending data

Processing records for Select queries while sending results to clients.

Sorting for group

Sorting for GROUP BY.

Sorting for order

Sorting for ORDER BY.

Opening tables

This process should be quick unless it is interrupted by other factors. For example, a data table cannot be opened by another thread until an Alter TABLE or LOCK TABLE statement has been executed. Trying to open a table.

Removing duplicates

Executing a Select DISTINCT query, but MySQL was unable to optimize duplicate records in the previous phase. Therefore, MySQL needs to remove duplicate records again before sending the results to the client.

Reopen table

A lock is acquired on a table, but the lock cannot be acquired until the table structure is modified. Lock released, closed datasheet, attempting to reopen datasheet.

Repair by sorting

Repair instructions are being sorted to create an index.

Repair with keycache

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

Searching rows for update

We're trying to find records that match the criteria for updating. It must be completed before Update can modify the related records.

Sleeping

Waiting for client to send new request.

System lock

Waiting to acquire an external system lock. if there are not currently multiple mysqld server running request that same table at the same time, you can disable external system locking by adding the--skip-external-locking parameter.

Upgrading lock

Insert DELAYED is attempting to acquire a lock table 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 retrieve the new structure. Then, in order to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated when 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 a new request.

Most states correspond to very fast operations, and if a thread stays in the same state for a few seconds, something may be wrong and needs to be checked. There are other states not listed above, but most of them are only useful to check for errors on the server.

common counter

About how to use the show processlist command to view performance in MySQL to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.