High-performance MySQL (3) Single query problem or server problem

When you find a problem, determine whether it is a single query or a server problem. If all the programs on the server are slowing down and suddenly getting better, and each query slows down, then the slow query is not necessarily the cause, and conversely, if the server is running without problems, only one query is occasionally slowed down, You need to focus on this particular query.

In order to determine the root cause of the problem, we generally use three kinds of technology to solve.

1. Show Global Status

This method actually captures the data at a higher frequency, such as a second show global status command, and when the problem arises, some counters (such as threads_running, threads_connected, Questions, Queries) of the change to discover. This has a relatively small impact on services. For example, the following example

By using awk to calculate the number of queries per second, the number of thread connections, and the number of threads executing the query, the data can be plotted as an image through a period of time, which is helpful for analyzing and locating problems.

2. Use Show Processlist

This method is used to capture the output of show processlist to see if a large number of threads are in an abnormal state.

When you use the show Processlist command, the tail plus \g can be output vertically, and you can easily combine the Sort|uniq|sort class of commands to calculate the number of times a column value appears

As in the following example

Because it is my local test environment, so there is no data, in addition, the screenshot under Ubuntu is too bad to use, can only be done.

If the MySQL version is newer you can also use the method described in the previous article to query Informationa_schema. Processlist table.

For example: The state of a large number of threads in the "freeing items" status is a lot of problematic query of the obvious characteristics

MyISAM A typical problem is that his table-level locking, when written requests are numerous, can quickly cause server-level thread build-up.