MySQL artifact show full processlist

When synchronizing test data today, the network was suddenly disconnected. After reconnecting, I found that the table could not be opened.

You can see that the data length of the table is 112192kb, but unfortunately it cannot be opened.

If you can’t open it, prepare to delete it and try again.

Things are often not that simple. Sure enough, it couldn't be deleted, nor could truncate. Then navicat got stuck, so I logged into the database and performed the dorp operation, but it still didn't work.

It is probably a network error that caused some strange things to happen.

Then let’s take a look and see what happened.

The artifact appears.

show full processlist;

show full processlist The result returned changes in real time and is a live snapshot of the mysql link execution, so it is very useful for handling emergencies. it works.

This sql usually acts as a firefighter to solve some sudden problems.

It can check some of the current running conditions of mysql, whether there is pressure, what SQL is being executed, how much time the statement takes, whether there is any slow SQL being executed, etc.

When you find some sql that takes a long time to execute, you need to pay more attention. Kill it if necessary and solve the problem first.

The command has three execution methods:

1. This is to query directly on the command line. The \G at the end means that the query results will be printed in columns, so that each field can be printed to separate rows.

mysql> show full processlist;
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |
| 449001 | root | 127.123.213.11:59900 | stark | Sleep   | 1241 |          | NULL                  |
| 449002 | root | 127.123.213.11:59958 | stark | Sleep   | 1216 |          | NULL                  |
| 449003 | root | 127.123.213.11:60088 | stark | Sleep   | 1159 |          | NULL                  |
| 449004 | root | 127.123.213.11:60108 | stark | Sleep   | 1151 |          | NULL                  |
| 449005 | root | 127.123.213.11:60280 | stark | Sleep   | 1076 |          | NULL                  |
| 449006 | root | 127.123.213.11:60286 | stark | Sleep   | 1074 |          | NULL                  |
| 449007 | root | 127.123.213.11:60344 | stark | Sleep   | 1052 |          | NULL                  |
| 449008 | root | 127.123.213.11:60450 | stark | Sleep   | 1005 |          | NULL                  |
| 449009 | root | 127.123.213.11:60498 | stark | Sleep   |  986 |          | NULL                  |
| 449013 | root | localhost            | NULL  | Query   |    0 | starting | show full processlist |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
11 rows in set (0.01 sec)
mysql> show full processlist\G;
*************************** 1. row ***************************
     Id: 449000
   User: root
   Host: 127.123.213.11:59828
     db: stark
Command: Sleep
   Time: 1283
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 449001
   User: root
   Host: 127.123.213.11:59900
     db: stark
Command: Sleep
   Time: 1254
  State: 
   Info: NULL

2. View the snapshot by querying the tables related to the link thread

SELECT id, db, USER, HOST, command, time, state, info FROM information_schema. PROCESSLIST WHERE command != ' Sleep' ORDER BY time DESC;

3. View through [Tools] => [Server Monitoring] in navicat.

This method is more convenient and can also be sorted.

A brief introduction to the meaning of each column:

Id: The unique identifier of the link mysql server thread. You can terminate the link of this thread through kill.

User: The user of the current thread connecting to the database

Host: Displays which IP and which port this statement is issued from. Can be used to track the user who issued the problematic statement

db: The database to which the thread is connected, null if not available

Command: Displays the executed command of the current connection, usually sleep or idle (sleep) , query (query), connection (connect)

Time: The time the thread is in the current state, the unit is seconds

State: Displays the status of the sql statement using the current connection, very important Column, there will be descriptions of all states later. Please note that state is just a certain state in statement execution. A sql statement, for example, has been queried. It may need to go through copying to tmp table, Sorting result, Sending data and other states. Completion

Info: The sql statement executed by the thread, or null if no statement is executed. This statement can be an execution statement sent by the client or an internal execution statement.

How to solve the problem after discovering it?

1. You can kill the problematic lines above individually

kill 449000

2. You can also batch terminate threads that take more than 3 minutes

- - Query the threads whose execution time exceeds 3 minutes, and then splice them into a kill statement

select concat('kill ', id, ';')

from information_schema.processlist

where command != 'Sleep'

and time > 3*60

order by time desc;

Of course the problem can usually be solved at this point, but this During the show processlist process, I only saw the previous truncate and drop operations, and killed these two threads, which was of no use. . . .

Of course the above is not nonsense, this is something similar to methodology, just like in [Chinese Captain], when encountering a flight accident, first check it according to the manual, investigate the cause, and solve the problem.

Continue

Immediately afterwards, I used navicat to perform the table repair operation, and the result was Waiting for table metadata lock

When MySQL was performing some DDL operations such as alter table , if there are uncommitted transactions on the table, Waiting for table metadata lock will occur. Once metadata lock occurs, subsequent operations on the table will be blocked.

Solution:

1. View the currently uncommitted transactions from the information_schema.innodb_trx table

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G

Field meaning:

trx_state: transaction status, usually RUNNING

trx_started: starting time of transaction execution, if the time is long, analyze whether the transaction is reasonable

trx_mysql_thread_id: MySQL thread ID, used for kill

trx_query: sql in the transaction

Generally, as long as these threads are killed, the DDL operation will not wait for table metadata lock.

2. Adjust the lock timeout threshold

lock_wait_timeout represents the timeout (in seconds) for obtaining the metadata lock. The allowed value range is 1 to 31536000 (1 year). The default value is 31536000.

See https://dev.mysql.com/doc/refman/5.6/en/se...

The default value is one year. . . .

Adjust it to 30 minutes

set session lock_wait_timeout = 1800;

set global lock_wait_timeout = 1800;

so that it will fail quickly when this problem occurs (failfast).

Recommended tutorials: "MySQL Tutorial" "Navicat"

The above is the detailed content of MySQL artifact show full processlist. For more information, please follow other related articles on the PHP Chinese website!