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

How to use Mysql show processlist to troubleshoot problems, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mysql show full processlist to view current thread processing

Scene of the accident

The result of each execution should change, because it is real-time, so I define it as "the scene of the incident". Each execution is equivalent to a snapshot of the scene.

Generally speaking, show processlist or show full processlist are used to check whether the current mysql is under pressure, what statements are running, how long the current statements take, and whether there is any slow SQL being executed.

You can see the total number of links and which threads have problems (time is the number of seconds of execution, so you should pay more attention to those that take a long time), and then you can kill the problematic threads, which can temporarily solve some sudden problems.

Sometimes a snapshot may not see anything wrong, so you can try to refresh it frequently.

Problem troubleshooting

Show full processlist can see all the links, but most of the links' state is actually Sleep, which is idle and does not have much viewing value.

What we want to observe is problematic, so we can filter:

-- query links with non-Sleep status, display them in reverse order of elapsed time, and filter select id, db, user, host, command, time, state, infofrom information_schema.processlistwhere command! = 'Sleep'order by time desc! by yourself.

In this way, filter out which are working, and then according to the time-consuming flashback display, the top of the list is most likely to be the problematic link, and then look at the info column, you can see the specific execution of what SQL statements, for analysis

The display column explains:

Id-Thread ID, you can use: kill id; to kill a thread, very useful

Db-Database

User-user

Host-IP of the host connecting the library

Command-currently executed commands, such as the most common: Sleep,Query,Connect, etc.

Time-elapsed time, in seconds, very useful

State-execution status, such as Sending data,Sorting for group,Creating tmp table,Locked, etc., is useful. For other states, see the reference article at the end of this article.

Info-SQL statement executed, useful

Kill usage

The thread ID mentioned above can be killed through kill; so basically the above can find out the problematic execution statements, and then you can kill them, so can you kill them one by one?

-- query threads that take more than 2 minutes to execute, and then splice into kill statements select concat ('kill', id,';') from information_schema.processlistwhere command! = 'Sleep'and time > 2*60order by time desc

I don't have to say it in the next step. I'll run the execution result of the splicing kill again and get it done.

This is sometimes very easy to use, who knows who uses it

common problem

Some problems will lead to a chain reaction, and it is not easy to locate, sometimes it is thought that it is a slow query, but most of the time is probably waiting for the release of CPU and memory resources, so sometimes the time consumed by the same query sometimes varies greatly.

Summarizes some common questions:

CPU alarm: it is most likely caused by more calculations in SQL

Ultra-high number of connections: it is very likely that there are slow queries, and then many queries are queued up. When troubleshooting problems, you can see a large number of SQL statements similar to the "incident scene". Then there may be no index or the index is not working well. You can use: explain to analyze the SQL statement to see whether the above content is helpful to you? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.