Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to check the implementation status in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

When you feel that there is a problem with the performance of mysql, you will usually take a look at the current execution status of mysql and use show processlist to view it, for example:

Among them, the state status column information is very important. First, take a look at the meaning of each column, and then take a look at the common state status.

The meaning of each column

1 、 id

A logo that you use when you want to kill a statement, such as mysql > kill

2 、 user

Displays the current user. If it is not root, this command only displays sql statements within your permission.

3 、 host

Shows which ip and which port the statement is issued from, and can be used to track the user who found the problem statement.

4 、 db

Shows which database this process is currently connected to

5 、 command

Commands that show the execution of the current connection, such as sleep, query, connect

6 、 time

The duration of this state, in seconds

7 、 state

Displays the status of the sql statement using the current connection, a very important column. State is only a certain state in the execution of the statement, such as a query, which needs to be completed through states such as copying to tmp table,Sorting result,Sending data.

8 、 info

Display this sql statement, because the length is limited, so the long sql statement is not fully displayed, but it is an important basis for judging the problem statement.

Analysis of Common State of state

1 、 Sleep

It usually means that the resource is not released. If it is through connection pooling, the sleep status should always be within a certain range, for example:

The data query time is 0.1 second, while the network output takes about 1 second. The original data connection can be released in 0.1 second, but because the front-end program does not perform the close operation and directly outputs the result, the database connection remains in the sleep state until the result is displayed on the user's desktop.

2 、 Locked

The operation is locked, and innodb is usually used to reduce the generation of locked state.

3 、 Copy to tmp table

When the index and the existing structure cannot cover the query conditions, a temporary table will be created to meet the query requirements. The huge pressure Copy to tmp table is usually related to the join table query. It is recommended to reduce the associated query or further optimize the query statement. If the execution time of the statement in this state is too long, it will seriously affect other operations. At this time, you can kill this operation.

4 、 Sending data

Sending data is not a process of sending data, but a process of getting data from a physical disk. If you have more influence result sets, you need to extract data from different disk fragments. If there are too many sending data connections, it is usually because the impact result set of a query is too large, that is, the index items of the query are not optimized enough.

5 、 Storing result to query cache

If this state occurs frequently, using set profiling analysis, if the proportion of resource overhead in the overall cost of SQL is too large (even if it is very small, depending on the proportion), it means that there are more query cache fragments, and flush query cache can be used to clean up immediately, and the Query cache parameter can be set as appropriate.

MySQL database is the two common bottlenecks of CPU and Imax O. When CPU is saturated, it usually occurs when the data is loaded into memory or read from disk. Disk I bottleneck O occurs when the loaded data is much larger than the memory capacity. If the application is distributed on the network, then the flat bottleneck will appear on the network when the query volume is quite large. We can use mpstat, iostat, sar and vmstat to check the performance status of the system.

In addition to the performance bottlenecks of the server hardware, for the MySQL system itself, we can use tools to optimize database performance, usually in three ways: using indexes, using EXPLAIN to analyze queries, and adjusting the internal configuration of MySQL.

When optimizing MySQL, we usually need to analyze the database. The common analysis methods are slow query log, EXPLAIN analysis query, profiling analysis and show command query system status and system variables. By locating the bottleneck of analysis performance, we can better optimize the performance of the database system.

After reading the above, do you have any further understanding of how to check the execution status in MySQL? If you want to know more knowledge or related content, 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.