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

This article mainly introduces "how to quickly locate slow query problems in MySQL". In daily operation, I believe many people have doubts about how to quickly locate slow query problems in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to quickly locate slow query problems in MySQL". Next, please follow the editor to study!

What are the consequences of a slow query? Developers who just started using MySQL, junior DBA thought that simple queries became slower and experienced a little loss, but the destructive power of slow queries was much more than that. During the peak period of business, this SQL has not yet been processed, a large number of new query requests have piled up, the utilization rate of CPU has remained high for a long time, even as high as 100%, and the system crashes directly. Black swan events such as slow query may directly affect business stability and cause huge economic losses.

Slow query literally means slow query. For example, a certain type of query consumes about 100ms under normal circumstances, but may soar to 15s in case of an exception. To locate the slow query problem, we can follow these steps:

First, open the slow log

Second, use the slow log query analysis management tool

Third, based on the existing slow log analysis, optimize the system itself (such as query statement or table structure design).

Enable slow logging and locate exceptions

Slow log is not enabled by default. If you need to optimize SQL, you can enable this feature. After logging in to MySQL, execute the following SQL statement to enable slow logs (here, take MySQL 5.7.33 as an example, other versions are basically common):

SET GLOBAL slow_query_log = 'ON';-- queries that do not use indexes are also considered to be a possible slow query set global log_queries_not_using_indexes =' ON'

In general, the MySQL slow log is located at / var/lib/mysql/-slow.log. We can simulate a slow query, and then we can see that the slow log record is generated:

Manually trigger a slow query: by default, MySQL assumes that a query greater than 10s is a slow query SELECT sleep (11).

Take a look at the slow log:

Sudo cat / var/lib/mysql/ubt-server-slow.log/usr/sbin/mysqld, Version: 5.7.33-0ubuntu0.18.04.1 ((Ubuntu)). Started with:Tcp port: 3306 Unix socket: / var/run/mysqld/mysqld.sock# Time: 2021-03-12T08:52:54.227174Z# User@Host: df-test [df-test] @ [10.100.64.118] Id: Query_time: 11.000551 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0use number1;SET timestamp=1615539174;select sleep (11)

From this log, we can see the following information (which may increase or decrease depending on the version or configuration of MySQL):

Production time: 2021-03-12T08:52:54.227174Z

Source: df-test [df-test] @ [10.100.64.118], that is, user df-test executed this query on the machine 10.100.64.118

Query statistics: such as time consumed, number of rows sent / received

Specific SQL statement

From this information, we can know the context of this slow query more clearly and locate the specific business code more accurately. However, there is a problem here. In order to ensure the security of the MySQL database, MySQL requires that you can only log in to a specific server to see the details of the slow query log, which directly affects the efficiency of handling exceptions when they occur, and drags down the progress of exception status, analysis and resolution.

Apart from opening the slow log that comes with the system, is there any effective way for developers to solve this problem quickly, directly and accurately?

Use the MySQL slow log analysis tool

The commonly used analysis tools to optimize slow SQL are: mysqldumpslow, mysqlsla, mysql-explain-slow-log, mysql-log-filter, myprofi.

Please refer to "4.6.9 mysqldumpslow- Summary slow query Log File"

Https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html

02 mysql-log-filter

An analysis tool found on google code provides two executable scripts, python and php. The basic function has more query time statistics (average, maximum, cumulative) than the official mysqldumpslow, and others are similar. In addition to statistical information, the features are typeset and formatted for the output to ensure the simplicity of the overall output. Recommend it to friends who like concise reports.

After installing DataKit, copy a copy of the MySQL log collection configuration in the / usr/local/cloudcare/dataflux/datakit/conf.d/log/ directory

Sudo cp mysqlog.conf.sample mysqlog.conf edit mysqlog.conf: [[inputs.tailf]] # fill in the file paths of various MySQL logs. Different versions may be different. # Note, only text files are supported here. The version we use here is MySQL 5.7.33 logfiles = ["/ var/lib/mysql/*.log", "/ var/log/mysql/mysql.log", "/ var/log/mysql/error.log" ] source = "mysqlog" # specify the service name service = "mysqlog" # dedicated log parsing script (DataKit has been built in) pipeline = "mysql.p" [inputs.tailf.tags] # you can add some tags here For example: biz = "order system" # omit other default configurations.

At this point, the log collection of MySQL is configured, and you can restart DataKit (the data will need to wait for a while to be seen on Dataflux platform)

Reference link: "restart methods for different DataKit systems"

Https://help.dataflux.cn/doc/0c6ebce225784bd2ad994d5f89c5dbc89e025792#toc27

Next, we can see the corresponding log on the DataFlux platform:

From the log details diagram, we can see the slow query SQL statement marked with a red box, as well as other extracted log information, such as query time, source, server hostname, number of data rows requested to be sent, and so on.

In addition, in the log details pulled out, we can also see the resource usage (such as CPU, memory, disk, network, etc.) of this host near the time point where the slow log is generated (red dashed vertical line). To a certain extent, it can help developers solve the problem better.

So far, we have solved the problems of collecting, parsing and displaying MySQL slow logs. Now that the data is available, developers can easily find the corresponding slow query log on the web page, and synthesize the overall resource occupation of the MySQL server to give a more reasonable solution.

These are the solutions we provide today to solve the problem of MySQL slow log query. In the process of practical application, we still have to try different dimensions of solutions, and combined with their own industry, business and other characteristics, select suitable database analysis tools for their own and team use to ensure the stability of the system and business.

At this point, the study on "how to quickly locate slow query problems in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.