How to check MySQL lock via MySQL show processlist command

Font: [Increase decrease] Type: Reprint time: 2010-03-07show processlist command is very practical, sometimes MySQL often run to more than 50% or more, you need to use this command to see which SQL statements occupy more resources, you know which website program problems.

The output of the Processlist command shows which threads are running and can help identify problematic query statements, using this command in two ways.

1. Enter Mysqladmin processlist into the Mysql/bin directory;

2. Start MySQL and enter show Processlist;

If you have super privileges, you can see all the threads, otherwise you will only see the thread that you initiated (this is the thread that the current MySQL account is running on).

The data is obtained in the form of the following (only three interception):

Mysql> show Processlist;

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+-------- --

| Id | User | Host | db | Command | time| State | Info

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+-------- --

|207|root |192.168.0.20:51718 |mytest | Sleep |        5 | | Null

|208|root |192.168.0.20:51719 |mytest | Sleep |        5 | | Null

|220|root |192.168.0.20:51731 |mytest | Query | 84 | Locked |

Select Bookname,culture,value,type from book where id=001

First of all, the meaning and purpose of each column, the first column, ID, needless to say, an identity, you want to kill a statement when it is useful. The user column shows the single-user, if not root, this command displays only the SQL statements within the scope of your permission. The host column that shows which IP port this statement was issued from. Oh, can be used to track the problem of the user statement. The DB column that shows which database the process is currently connected to. Command column, which displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect). Time column, which is the duration of this state, in seconds. State column, which shows the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement that has been queried as an example, may need to go through copying to TMP table, Sorting result,sending data and other states can be completed, the info column, display this SQL statement, because the length is limited, so the long SQL statement is not complete, but a judgment question statement important basis.

The most critical of this command is the State column, which is listed in the following categories:

Checking table
Checking the data table (this is automatic).
Closing tables
The modified data in the table is being flushed to disk, and the tables that have been exhausted are being closed. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load.
Connect out
Replication from the server is connecting to the primary server.
Copying to TMP table on disk
Because 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 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 and is deleting records from other tables.
Flushing tables
Executing flush TABLES, waiting for other threads to close the data table.
Killed
Sends a KILL request to a thread, the thread checks the kill flag bit and discards the next kill request. MySQL checks the kill flag bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released.
Locked
Locked by another query.
Sending data
The record for the select query is being processed, and the results are being sent to the client.
Sorting for group
Sorting is being done for group by.
Sorting for order
The order by is being sorted.
Opening tables
The process should be quick, unless other factors interfere with it. For example, a data table cannot be opened by another thread until the row of the ALTER TABLE or LOCK TABLE statement is complete. Attempting to open a table.
removing duplicates
A query that is executing a SELECT distinct method 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 obtained, but it must be changed after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again.
Repair by sorting
Repair instructions are being sorted to create an index.
Repair with Keycache
The repair instructions are using the index cache to create a new index one by one. It will be slower than repair by sorting.
Searching rows for update
The qualifying records are being told to find out to prepare for the update. It must be completed before the update is about to modify the related records.
Sleeping
Waiting for the client to send a new request.
System Lock
Is waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing 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 notified that the data table structure has been modified and the data table needs to be reopened to obtain a new structure. Then, to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following cases: 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 insertions and is waiting for a new request.
Most of the state corresponds to a fast operation, so long as one thread remains in the same state for several seconds, a problem may occur and need to be checked.
There are other states that are not listed above, but most of them are only useful to see if there is an error in the server.

The MySQL manual has a description of all the statuses and links as follows:http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Performing State analysis

Sleep status

Usually indicates that the resource is not released, and if it is through a connection pool, the sleep state should be constant within a certain number of ranges

Actual combat example: due to the front-end data output (especially the output to the user terminal) did not close the database connection, resulting in a large number of sleep connections due to network connection speed, in the event of abnormal speed, the database too many connections hang dead.

Simple Interpretation, Data query and execution usually takes less than 0.01 seconds, and the network output usually takes 1 seconds or more, the original data connection can be released in 0.01 seconds, but because the front-end program does not perform close operation, direct output results, then the results are not displayed in front of the user table, the database connection has been maintained in the sleep state!

Waiting for net, reading from net, writing to net

Sometimes it doesn't hurt.

Quickly check the database to the front-end network connection status and traffic, as seen in large numbers

Case: Because of the plug-in program, the intranet database read a lot, the intranet use of the hundred trillion switch quickly full, resulting in a large number of connections blocked in waiting for net, the database connection too many crashes

Locked status

There is an update action lock

Typically, InnoDB can be used to reduce the locked state, but it is important to remember that the update operation uses the index correctly, even if the low-frequency update operation is not negligent. As shown in the example above affecting the result set.

In the age of MyISAM, Locked is a nightmare for many high-concurrency applications. So MySQL officials are starting to tend to recommend InnoDB.

Copy to TMP table

Indexes and existing structures cannot cover query criteria before a temporary table is established to meet the query requirements, resulting in a huge amount of horrible I/O pressure.

Very scary search statements can lead to such cases, if it is data analysis, or a midnight cycle of data cleanup tasks, occasionally appearing, may be allowed. The frequent occurrence must be optimized.

The Copy to TMP table is usually related to a linked table query, and it is recommended that you become accustomed to not using a table query.

Practical Examples:

U a Community database blocking, distress, check, its server exists multiple database applications and websites, one of the infrequently used small site database produced a horrible copy to TMP table operation, resulting in the entire hard disk I/O and CPU pressure overload. Kill the operation to recover everything.

Sending data

Sending data is not to be sent, not deceived by the name, this is the process of getting data from the physical disk, if you have more impact results set, then you need to extract data from different disk fragments,

Occasionally the state is connected without hindrance.

Back to the problem that affected the result set, in general, if the sending data connection is too large, the result set of the query is usually too big, that is, the index entry of the query is not optimized.

If a large number of similar SQL statements appear in the Show Proesslist list, and are all in the sending data state, optimize the query index, and remember to think about the ideas that affect the result set.

Storing result to query cache

In this state, if it occurs frequently, using set profiling analysis, if there is too much resource overhead in SQL overall overhead (that is, very small overhead, see scale), then query cache fragmentation is more

Use flush query cache to instantly clean up or make scheduled tasks

The Query cache parameter can be set appropriately, as appropriate.

Freeing items

Theoretically, this thing doesn't come in a lot. Occasionally, there is no obstruction.

If there is a large amount of memory, the hard disk may have been having problems. such as hard disk full or damaged.

When I/O pressure is too large, there may be a longer time for free items to execute.

Sorting for ...

Similar to sending data, the result set is too large, the sorting criteria are not indexed, they need to be sorted in memory, and even a temporary structure order needs to be created.

Other

There are many States, encountered, to check the information. Basically we encounter other states with less blocking, so don't care

How to check MySQL lock via MySQL show processlist command