The role of show full processlist in Mysql 01/30 Update SLTechnology News&Howtos

The role of show full processlist in Mysql

2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Today, the editor will bring you an article about show full processlist in Mysql. The editor thinks it is very practical, so I will share it for you as a reference. Let's follow the editor and have a look.

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

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

It can check some of the current mysql operation, whether there is pressure, what sql is being executed, how long the statement takes, whether there is a slow sql execution, and so on.

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

Commands can be executed in three ways:

1. This kind of query is directly on the command line, and the\ G at the end means that the query results are printed in columns, so that each field can be printed to a separate row.

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 it through * * tools * *-> * * Server Monitoring * * in navicat.

This method is more convenient and can be sorted.

A brief introduction to the meaning of each column:

Id: unique identification of the link mysql server thread, which can be terminated through kill.

User: the user that the current thread links to the database

Host: shows which port of the ip this statement is issued from. Can be used to track the user with the problem statement

Db: the database linked by the thread, or null if it does not exist

Command: the command that displays the execution of the current connection, which is generally dormant or idle (sleep), query (query), connection (connect)

Time: the time in seconds that a thread is in its current state

State: displays the status of the sql statement using the current connection. Important columns will have 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.

Info: a sql statement executed by a thread, or null if no statement is executed. This statement enables the execution statement sent by the client to be internally executed.

How do you solve the problem after you find it?

1. You can kill the problematic lines individually.

Kill 449000

2. Threads with a batch end time of more than 3 minutes can also be used.

Query threads that take more than 3 minutes to execute, and then splice them into kill statements

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

From information_schema.processlist

Where command! = 'Sleep'

And time > 300060

Order by time desc

Of course, the problem can generally be solved, but this time in the show processlist process, only saw the previous truncate and drop operations, the two threads kill, it is useless.

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

Go ahead

Then, the table repair operation is performed with navicat, and the result is Waiting for table metadata lock

When MySQL is doing some DDL operations such as alter table, Waiting for table metadata lock will appear if there are uncommitted transactions on the table, and once metadata lock occurs, subsequent operations on the table will be blocked.

Solution:

1. View 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: the start time of the transaction execution. If the time is long, analyze whether the transaction is reasonable.

Trx_mysql_thread_id: thread ID of MySQL for kill

Trx_query: sql in a transaction

Generally, as long as the kill drops these threads, the DDL operation will not Waiting for table metadata lock.

2. Adjust the lock timeout threshold

Lock_wait_timeout represents the timeout (in seconds) for getting metadata lock, with values ranging from 1 to 31536000 (1 year). The default value is 31536000.

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

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 you can fail quickly when the problem occurs (failfast).

On the role of show full processlist in Mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you like this article, you might as well share it for more people to see.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.