MySQL online slow log problems and optimization solutions, mysql online log Solutions

Source: Internet
Author: User

MySQL online slow log problems and optimization solutions, mysql online log Solutions

MySQL slow log is a type of information that MySQL DBA and other developers and O & M personnel need to pay attention to frequently. You can use slow logs to find SQL statements that have been executed for a long time or are not indexed, which provides a basis for system optimization.

This article will combine an online case to analyze how to correctly set MySQL slow log parameters and use the slow log function, and introduce the enhancement of the MySQL slow log function by the cloud-based RDS on the Internet.

MySQL parameter group Function

Netease cloud RDS instance provides the parameter group management function. You can view most common MySQL System Parameters on the parameter management interface. You can understand the current running value and recommended value:


You can also modify the parameters listed on the parameter management page. Click "Modify parameters" to set the parameters online and click "Save changes" to modify the parameters of the master-slave MySQL node in one click:


Looking at the parameter management interface, it is not difficult to find that there are many parameters related to slow queries, So how do these parameters work and how do they interact, what conditions are met before SQL statements are recorded in slow logs? Only by understanding this can we make better use of slow logs for system tuning and problem locating.

Next, based on this online case, we will introduce how to correctly configure slow log parameters:

Some users reported that the slow log of multiple RDS 5.7 instances they used was abnormal, and the SQL statements executed for more than a minute were not recorded in the slow log. SQL statements for recurrence are also provided.

Correct posture of slow Log Parameters

First, check whether the slow log feature is enabled for the instance. By default, the slow log feature of MySQL is disabled. The slow log switch parameter is slow_query_log, which can be explicitly specified in the mysqld startup command line or configuration file. If slow_query_log = 1 or no value is specified, the slow log is enabled, if the value is 0, the function is disabled. You can enable or disable it dynamically at runtime.

By default, the slow log feature is enabled for Netease apsaradb for RDS instances. We confirm that this user has not disabled the slow log switch for the instance.

Next, you need to confirm the location of the slow log record. MySQL uses the log_output parameter to specify whether to save the slow log as a FILE or as a TABLE. It should be emphasized that setting slow_query_log to 0 only by specifying log_output does not record slow logs, that is, slow_query_log is the switch of slow logs. If you use a file to record slow logs, you can specify the file name through slow_query_log_file. If you do not explicitly specify slow_query_log_file, MySQL initializes it as a host_name-slow.log, host_name is the host name that runs mysqld, the slow log file is in the MySQL data directory by default.

Netease cloud RDS instances do not allow users to modify the log file path, but you can configure the log_output parameter. by querying, make sure that the instance records slow logs as files, check the log file to confirm that the SQL statement described by the user is absent.

Because the user provided the recurrence statement, we executed the SQL statement and did return it after more than one minute. Through the explain command, we found that it had not taken the index and scanned a large number of records, viewing slow logs again does not record this SQL statement.

MySQL records the SQL statements that meet the execution time exceeding long_query_time seconds and the number of scan records exceeds the min_examined_row_limit row.

The minimum and default values of the long_query_time parameter are 1 and 10 s, which can be accurate to microseconds (MS), respectively ). If you choose to record slow logs to a file, the recorded time is accurate to microseconds. If you record the slow log table (mysql. in slow_log), it is only accurate to seconds, and the microsecond part is ignored.

The Netease cloud RDS instance allows users to set these two parameter values. Is it because the user has adjusted the above two thresholds that cannot meet the record conditions? Further queries are not the cause of the problem.

We noticed that MySQL also has a parameter named log_queries_not_using_indexes, which is used to control whether to record SQL queries that are not indexed. The Code is as follows:


Focus on the content indicated by the arrow. If the query is not indexed or the index is invalid and the related parameters are enabled, set warn_no_index to true. If the number of scanned records exceeds the threshold, it will also be recorded like a slow query. Is this parameter not enabled? The result is still negative.

Cause

Because the database instance may have many SQL statements that do not go through the index, if log_queries_not_using_indexes is enabled, there is a risk that the log file or table capacity will grow too fast, in this case, you can set log_throttle_queries_not_using_indexes to limit the number of SQL statements that do not take indexes into the slow log every minute. This parameter is set to 0 by default, indicating that the number of SQL statements written is disabled.

After the log_throttle_queries_not_using_indexes function is enabled, a 60-second window is opened after the first query without indexing is executed. When the time window ends, the number of slow queries that are blocked in the window and the total time spent on these slow queries are printed. The next statistical time window is not created immediately, but is enabled after the next query without indexing is executed.

Corresponding to this online issue, log_throttle_queries_not_using_indexes is set to 10, and the following content is periodically printed in the log file:


It does conform to the above description. Slow logs of users should be restrained and summarized to 359. We tried to set log_throttle_queries_not_using_indexes to 0 and then execute the corresponding SQL statement. The corresponding SQL statement was recorded in the log file.

This online problem seems to have been located, that is, the system generates too many slow logs without indexing, And the log_throttle_queries_not_using_indexes set is too small, which makes it impossible to properly record slow logs that the user has not taken the index. However, there is another problem that cannot be solved, that is, when log_throttle_queries_not_using_indexes is 0, no more than 10 slow logs are printed per minute, and no more than 359 slow logs are prompted by throttle, so when it is set to 10, the SQL statement provided by the user should be recorded in the slow log. Why is there no record? Why? In fact, you can find the answer by taking a closer look at the code logic of MySQL logs that do not go indexed:


It is the main logic for recording slow logs. Whether to record logs is controlled by the log_slow_applicable function. This function has previously analyzed a part of logs. For more information about this function, see the red box:


Suppress_logging is a decisive variable. This SQL statement can only be logged if it is false. The result is related to log_throttle_queries_not_using_indexes. Let's look at the implementation of log_throttle_qni.log, for example:


Eligible is warn_no_index. When the total number of statements that do not take the index within one minute exceeds limit, the return value is true. Only the return values of warn_no_index and inc_log_count () are true, and the value of suppress_current is true, suppress_current is suppress_logging.

By analyzing the above two items, you can answer the previous questions:

Log_throttle_queries_not_using_indexes counts all the statements that do not go through the index. Some of the statements are not recorded in the slow log because they do not meet the number of scan records. This is why the value is 10, there are no 10 records in the slow log file. Because eight of the 10 SQL statements are not recorded because the number of scan records is too small.


This also dispelled the number 359, which is the total number of SQL statements that do not take the index in this time window. Therefore, log_throttle_queries_not_using_indexes is a key parameter. Improper configuration will make it impossible to record slow queries without indexing, leading to the failure of the slow log function. Therefore, you must first try to avoid a large number of SQL statements that do not go through the index. You can optimize them through the RDS health check function. Again, if the above prompt appears in the slow log, increase the value of log_throttle_queries_not_using_indexes to further analyze the problem.

InnoSQL slow log Feature Enhancement

Some RDS instance users ask us why the execution time of My SQL statement has not exceeded the configured long_query_time and is indexed, but it is still recorded in the slow log, is there a Bug? In fact, this is not a Bug, but because the InnoSQL (MySQL open-source Branch maintained by Netease) version used by Netease cloud RDS optimizes slow logs, in addition to examining the execution time of SQL statements, pay attention to the number of Disk pages required for this query, because the number of required pages is too large, it may also have a great impact on the system load. To quantify statistics, we collected the total number of pages to be read by SQL queries and the actual number of I/O operations on these pages, respectively recorded as logical_reads and physical_reads, the former includes page requests that hit InnoDB Buffer Pool and do not hit IO.

This function is provided by introducing slow_query_type and long_query_io parameters. The former can be set to 0/1/2/3. "1" indicates enabling slow log recording Based on execution time, "2" indicates recording slow logs based on the total number of pages to search, and "3" indicates a collection of "1" and "2. Therefore, in InnoSQL, SQL queries can be recorded in slow logs only when the execution time is long enough or the total number of pages required is sufficient. The code implementation snippets are as follows:


The page count threshold is measured by the long_query_io parameter. You can set it dynamically. If the total number of pages m_logical_reads exceeds this threshold, the page is recorded even if the execution time is not too high. Correspondingly, new fields are added to the slow log table structure and slow log file output of the RDS instance.


It is the slow_log table structure of InnoSQL. Among them, logical_reads and physical_reads add fields for InnoSQL. Similarly, two fields are added to the output content of the slow log file, as shown below:


In addition to the details described above, the MySQL slow Log Module has the following features worth noting:

○ The time recorded in slow log statistics and slow logs does not include the waiting time required to obtain the lock before the SQL statement is executed;

○ MySQL writes the SQL statement to the slow log only after the SQL statement is executed and all the locks it holds have been released, therefore, the SQL statement record sequence in the slow log does not accurately reflect the actual execution sequence of these SQL statements;

○ Each slow log contains a timestamp. If a log is written to a file, the log_timestamps parameter is used to convert the slow log timestamp to the specified time zone. However, this parameter does not work for slow logs in the mysql. slow_log table;

○ You can set log_slow_slave_statements to enable the slow log feature of the MySQL slave database;

○ Alter table, analyze table, check table, create index, drop index, optimize table, and repair table can also be recorded in slow logs, you can enable log_slow_admin_statements.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.