Viewing and analyzing MySQL execution status

When you feel that there is a problem with MySQL performance, you usually check the current MySQL execution status first, using show processlist to view it, for example:

The state column information is very important. First, look at the meaning of each column, and then look at the common state status

What the columns mean

1. id

A flag that you use when you want to kill a statement, for example, mysql> kill 207;

2. user

Display the current user. If you are not root, this command will only display the SQL statements within your authority range.

3. Host

Shows which IP and port the statement was sent from, which can be used to track down the user who issued the problematic statement

4.db

Displays which database this process is currently connected to

5. Command

Displays the commands executed by the current connection, generally sleep, query, and connect

6. time

The duration of this state, in seconds

7. state

Displays the status of the SQL statement using the current connection. This is a very important column. The state is just a certain state in the statement execution. For example, a query needs to go through the states of copying to tmp table, sorting result, and sending data before it can be completed.

8.info

Display this SQL statement. Because of the limited length, long SQL statements are not fully displayed, but it is an important basis for judging problem statements.

state Common state analysis

1. Sleep

Usually means that resources are not released. If it is through a connection pool, the sleep state should be constant within a certain range, for example:

The data query time is 0.1 seconds, while the network output takes about 1 second. The original data connection can be released in 0.1 seconds, 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 before the result is displayed on the user's desktop.

2. Locked

The operation is locked. Usually, using innodb can effectively reduce the occurrence of locked status.

3. Copy to tmp table

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

4. Sending data

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

5. Storing results to query cache

If this status occurs frequently, use set profiling to analyze. If the resource overhead accounts for a large proportion of the overall SQL overhead (even if the overhead is very small, look at the proportion), it means that the query cache is fragmented. Use flush query cache to clear it immediately. The query cache parameters can be set appropriately.

The two common bottlenecks for MySQL databases are CPU and I/O bottlenecks. CPU saturation usually occurs when data is loaded into memory or read from disk. The disk I/O bottleneck occurs when the loaded data is much larger than the memory capacity. If the application is distributed on the network, the bottleneck will appear on the network when the query volume is very large. We can use mpstat, iostat, sar and vmstat to view the performance status of the system.

In addition to the performance bottleneck of the server hardware, for the MySQL system itself, we can use tools to optimize the performance of the database. There are usually three types: using indexes, using EXPLAIN to analyze queries, and adjusting MySQL's internal configuration.

When optimizing MySQL, you usually need to analyze the database. Common analysis methods include slow query logs, EXPLAIN analysis queries, profiling analysis, and show command query system status and system variables. Only by locating and analyzing performance bottlenecks can the performance of the database system be better optimized.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links