Check the mysql lock method with the mysql show processlist command

  • 2020-05-10 22:59:55
  • OfStack

The output of the processlist command, which shows which threads are running and can help identify problematic query statements, is used in two ways.

1.             enter mysql/bin and enter mysqladmin processlist;

2.             start mysql and enter show processlist;

If you have SUPER permissions, you can see all the threads, otherwise you can only see the threads you started (that is, the threads currently running the corresponding MySQL account).

The data form obtained is as follows (only 3 are intercepted) :

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

So let's just say a little bit about the meaning and use of the columns 1, column 1, id, let's just say, 1 identifier, which is useful when you want kill1. The user column displays the single user, if not root, this command only shows the sql statements that are within your jurisdiction. The host column, showing which port of which ip this statement is issued from. Well, it can be used to track down users with problematic statements. The db column, which shows which database the process is currently connected to. The command column, which displays the execution command of the current connection, is normally sleep (sleep), query (query), join (connect). time column, the duration of this state in seconds. state column, displayed with the current connection state of sql statement, it is very important to the column, the subsequent will have all the description of the status, please note that state just a one state of statement execution, 1 sql statements, has query, for example, may need to pass copying to tmp table, Sorting result, Sending data state can be completed, such as info columns, display the sql statement, because the length is limited, Therefore, the long sql statement is not fully displayed, but it is an important basis for judging the problem statement.

The most critical part of this command is the state column. mysql lists the following states:

Checking table
Checking the data table (this is automatic).
Closing tables
The modified data in the table is being flushed to disk, and the exhausted table is being closed. This is a quick operation, if not, you should check to see if the disk space is full or if the disk is under load.
Connect Out
The replication slave is connecting to the master server.
Copying to tmp table on disk
Since the temporary result set is larger than tmp_table_size, temporary tables are being moved 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 part 1 of a multi-table delete and has just dropped the first table.
deleting from reference tables
The server is performing part 2 of a multi-table delete, deleting records from other tables.
Flushing tables
Executing FLUSH TABLES, waiting for another thread to close the table.
Killed
When an kill request is sent to a thread, the thread checks the kill flag bit and aborts the next kill request. MySQL checks the kill flag bit in each main loop, although in some cases it may take a short segment before the thread dies. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.
Locked
It was 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
The process should be quick, unless something else interferes. For example, a table cannot be opened by another thread until the Alter TABLE or LOCK TABLE statements are completed. Trying to open 1 table.
Removing duplicates
An Select DISTINCT query is executing, but MySQL cannot optimize out duplicate records in the first phase. Therefore, MySQL needs to get rid of duplicate records again, and then send the results to the client.
Reopen table
A lock was obtained for a table, but the lock must be acquired after the table structure has been modified. The lock has been released, the table has been closed, and an attempt is being made to reopen the table.
Repair by sorting
The repair instruction is sorting to create an index.
Repair with keycache
The fix directive 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 looking for eligible records to update. It must be done before Update modifies the relevant records.
Sleeping
Waiting for the client to send a new request.
System lock
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 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 notified that the data table structure has been modified and needs to reopen the data table to obtain the new structure. Then, in order to reopen the 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 completed all pending inserts and is waiting for a new request.
Most states correspond to quick operations, as long as one thread remains in the same state for a few seconds, then something is wrong and needs to be checked 1 time.
There are other states that are not listed above, but most of them are only useful to see if the server has any errors.

mysql manual in all states, link is as follows: http: / / dev mysql. com/doc refman / 5.0 / en/general - thread - states. html

Related articles: