What are the consequences of a slow query? At the beginning of MySQL development, junior DBA thought it was simple query slow, experience a little bit of impact, but they do not know, slow query damage is far more than that. During the peak of business, this SQL is not finished, a large number of new query requests are piled up, and the CPU usage remains high for a long time, even as high as 100%, and the system crashes directly… Slow query such black swan event, may directly affect business stability, resulting in huge economic losses.

Slow query literally means slow query. For example, for a certain type of query, the consumption is about 100ms under normal circumstances, but may soar to 15s in abnormal cases. To locate slow query problems, you can perform the following steps:

1. Enable slow log;

2. Use slow log query, analysis and management tools;

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

Enable slow logs to locate faults

Slow log is disabled by default. You can enable this function if you need to optimize SQL. After logging in to MySQL, run the following SQL statement to enable slow logging (MySQL 5.7.33 is used as an example and is generally used in other versions) :

SET GLOBAL slow_query_log = 'ON'; Set global log_QUERies_NOT_USING_indexes = 'ON'; set global log_QUERies_NOT_USING_indexes = 'ON';Copy the code

/var/lib/ mysql.log (); /var/lib/ mysql.log ();

SELECT sleep(11); SELECT sleep(11);Copy the code

Take a look at the slow query log:

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

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

  • Generation time: 2021-03-12T08:52:54.227174Z

  • Source: df-test[df-test] @ [10.100.64.118], i.e. user df-test performed the query on 10.100.64.118

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

  • Specific SQL statements

From this information, we can better understand the context of this slow query and more precisely locate the specific business code. However, there is a problem here. To ensure the security of MySQL database, MySQL requires that only when you log in to a specific server can you see the details of slow query logs. This directly affects the processing efficiency of exceptions and slows down the status, analysis and resolution of exceptions.

Besides turning on the system’s built-in slow logging, what other effective ways can developers solve this problem quickly, directly, and accurately?

MySQL slow log analysis tool

Common analysis tools used to optimize slow SQL are mysqlDumpslow, mysqlSLA, mysql-explain-slow-log, mysql-log-filter, and myprofi.

Mysqldumpslow and mysql-log-filter are used as examples.

01 mysqldumpslow

Mysqldumpslow is an official slow query log analysis tool. Main features include statistics of different slow SQL

  • Number of occurrences (Count)

  • Average and cumulative Time spent executing (Time)

  • Time spent waiting for a Lock

  • Total number of Rows sent to the client (Rows)

  • Total number of Rows scanned (Rows)

  • The user and the SQL statement itself (limit 1, 20, limit N,N)

For details, see 4.6.9 mysqlDumpSlow – Summarizing Slow Query Log Files.

Dev.mysql.com/doc/refman/…

02 mysql-log-filter

An analysis tool found on Google Code provides executable scripts in both Python and PHP. The basic function has more query time statistics (average, maximum, cumulative) than the official mysqlDumpslow, and other similar features. Features In addition to statistics, the output content is typeset and formatted to ensure the overall output is concise. Recommended for friends who like concise reports.

For reference: code.google.com/p/mysql-log…

Other tools, here will not repeat, interested friends can directly from the Internet search. The tools described above have more or less minor usage problems, such as missing data, troublesome configuration, and so on. Here is the solution of one-stop data monitoring cloud platform (DataFlux).

03 DataFlux scheme

As mentioned above, slow logging can cause great damage. To further optimize MySQL database performance, we need to solve the following problems:

  • The data collection

  • Data parsing

  • Data is stored

  • Data presentation and query

Within DataFlux there is a tool dedicated to all kinds of data acquisition – DataKit. For MySQL, it provides various MySQL log collection capabilities. Here we introduce the basic use of DataKit collector on Linux platform.

First, we log on to the DataFlux website to register and log in to our membership account. Then we can follow the following figure (console-Administration-Data Gateway) or refer to link 2 to find and install the DataKit.

Reference links:

1. DataFlux: www.dataflux.cn/

2. DataKit Installation:

Help. Dataflux. Cn/doc / 0 c6ebce…

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

$sudo cp mysqlog.conf.sample mysqlog.conf [[inputs. Tailf]] # enter the file path for the various MySQL logs. MySQL 5.7.33 logfiles = [" /var/lib/mysql.log ", "/var/log/mysql.log "," /var/log/mysql.log ", "/var/log/mysql.log "," /var/log/mysql.log ", ] source = "mysqlog" # service = "mysqlog" # pipeline = "mysql.p" [elsions.tailf. tags] # Here you can add some tags, for example: biz = "order system" # omit other default Settings...Copy the code

MySQL log collection is now configured, restart the DataKit log collection (the data will take a while to see the Dataflux platform).

Reference links: the DataKit different system restart way help. Dataflux. Cn/doc / 0 c6ebce…

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

As you can see from the figure, the SQL execution time (query_time) has been extracted, which is the time seen in the slow log above. Click on this log to see the log details:

From the log detail graph, we can see the slow query SQL statements marked in red boxes, along with other extracted log information, such as query time, source, server host name, number of data rows sent in the request, and so on.

In addition, we can also see the resource usage (such as CPU, memory, disk, network, etc.) of the current host near the time point when the slow log is generated (red vertical line) in the pulled log details, which can help developers solve problems better to some extent.

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

This is how we solve the problem of MySQL slow log query. In the actual application process, we still need to try different dimensions of the solution, and combined with their own industry, business characteristics, choose suitable for themselves and the team to use database analysis tools, to ensure the stability of the system and business.