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

This article mainly explains the "detailed description of the processlist command in mysql". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the detailed description of processlist command in mysql".

The output of the processlist command shows which threads are running and can help identify problematic query statements. Use this command in two ways. If you have SUPER permission, you can see all the threads; otherwise, you can only see the threads that you initiated (that is, the threads running under the corresponding MySQL account).

You can use the show processlist command for output, or you can use the following statement for operation

Mysql > SELECT * FROM information_schema.processlist limit 10

+-+ +

| | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

+-+ +

| | 45730 | kkuser | 192.168.0.2 Sleep 50753 | landray_kk_db | Sleep | 2 | | NULL |

| | 45732 | kkuser | 192.168.0.2 Sleep 50755 | landray_kk_db | Sleep | 3 | | NULL |

| | 45487 | kkuser | 192.168.0.3 NULL 62093 | landray_kk_db | Sleep | 11 | | NULL |

| | 45739 | kkuser | 192.168.0.2 Sleep 50762 | landray_kk_db | Sleep | 5 | | NULL |

| | 46013 | kkuser | 192.168.0.4 Sleep 50997 | landray_kk_db | Sleep | 2 | | NULL |

| | 45763 | kkuser | 192.168.0.2 Sleep 50787 | landray_kk_db | Sleep | 2 | | NULL |

| | 46086 | kkuser | 192.168.0.4 Sleep 51071 | landray_kk_db | Sleep | 10 | | NULL |

| | 46298 | kkuser | 192.168.0.5 kkuser | landray_kk_db | Sleep | 5 | | NULL |

| | 46300 | kkuser | 192.168.0.5 Sleep 50768 | landray_kk_db | Sleep | 5 | NULL |

| 46297 | kkuser | 192.168.0.5 connect 50765 | landray_kk_db | connect | 150 | Send data | select a.deptid.deptname. | |

+-+ +

10 rows in set (0.01 sec)

As you can see, there is Send data in the last column. After analysis, it is found that the original statement is as follows:

Select a.deptid,a.deptname,b.person_name,b.person_id from deptment a,person b

Where a.deptid = b.deptid

Through explain, it is found that the dept_id of the person table lacks an index, resulting in scanning the entire table, accessing more than 100000 records at a time.

Finally, the problem is solved by adding an index.

Let's briefly talk about each column description of processlist:

First, let's briefly talk about the meaning and purpose of each column.

The first column, session id, is used at the mysql level

The second column, the user column, the accessed user, this command only displays the sql statements within your permissions.

The third column, the host column, shows which port the statement is issued from which ip.

The fourth column, the db column, shows which database the process is currently connected to.

The fifth column, the command column, shows the commands executed for the current connection.

The sixth column, the time column, the duration of this state, in seconds, if there is a statement behind it, be careful, indicating that there is something wrong with the statement.

The seventh column, the state column, shows the status of the sql statement using the current connection. For important columns, there will be descriptions of all the states later. Please note that state is only a certain state in the execution of the statement. A sql statement has been queried as an example, and it may need to be completed through states such as copying to tmp table,Sorting result,Sending data.

The eighth column, the info column, shows this sql statement, because the length is limited, so the long sql statement is not complete, but it is an important basis for judging the problem statement.

The following focuses on the values of each item in the state column

The most important thing in this command is the state column. Mysql lists the following states:

Checking table

Checking the datasheet (this is automatic).

Closing tables

The modified data in the table is being flushed to disk and the table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

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

Creating tmp table

Creating a temporary table to hold some of the query results.

Deleting from main table

The server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables

The server is performing the second part of the multi-table deletion and is deleting records for 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 bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time 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

The record of the Select query is being processed and the results are being sent to the client. The meaning of "Sending data" state, it turns out that the name of this state is very misleading, the so-called "Sending data" does not simply send data, but includes "collect + send data".

Sorting for group

Sorting for GROUP BY.

Sorting for order

Sorting for ORDER BY.

Opening tables

This process should be very fast unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an Alter TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates

An Select DISTINCT query is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client.

Reopen table

A lock on a table is acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting

The repair directive is sorting to create an index.

Repair with keycache

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

Searching rows for update

We are talking about finding qualified records for updating. It must be done before Update modifies the relevant records.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting to acquire an external system lock. If you are not currently 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 get 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 informed that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs 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 a new request.

Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked.

There are other states not listed above, but most of them are only useful to see if there are any errors on the server.

Thank you for your reading, the above is the "detailed description of the processlist command in mysql", after the study of this article, I believe you have a deeper understanding of the detailed description of the processlist command in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.