Slow SQL queries significantly decrease database stability. When issues such as high workloads and performance jitters occur on databases, database administrators (DBAs) or developers first check whether slow query logs are generated. Database Autonomy Service (DAS) provides the slow query log analysis feature. DAS collects statistics on and analyzes SQL statements whose execution duration exceeds the specified threshold value and provides solutions. You can troubleshoot database performance issues with ease. This enhances system reliability and stability.
Usage notes
The maximum length of a slow query log is 16 KB. Any content that exceeds this length is truncated.
Limits
RDS for MySQL Basic Edition instances support the view and export of slow query logs but do not support the SQL diagnostics and optimization feature.
Access the slow SQL statement page
Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services > Slow Query Logs.
NoteIf you use the slow query log analysis feature as an RAM user, make sure that the RAM user is granted the
AliyunHDMFullAccesspermission.On the Slow Log Analysis tab, specify a time range and view the Slow Query Log Trends, Event Distribution, Slow Query Log Statistics, and Slow Query Log Details within the specified time range.
NoteWhen you select a time range, the end time must be later than the start time. The maximum time range is 7 days. You can query slow query log information generated within the last month.
In the Slow Query Log Trends chart, you can select a point in time to view the corresponding Slow Query Log Statistics, and Slow Query Log Details.
NoteIf a slow SQL statement is too long to be fully displayed, you can hover the pointer over the statement. The complete statement appears in a dialog box.
Click
to save the slow log information locally.Click
to populate the selected and entered parameters to the OpenAPI console for API debugging.In the Event Distribution section, perform the following operation:
In the Slow Query Log Statistics section:
Above the list, you can select filter conditions to filter the data. The available filter conditions vary based on the database engine.
Click the ID of a specific SQL template in the SQL ID column to view its correlations and a list of details, such as user distribution, client distribution, and metric trends.
In the Actions column for the target SQL template, click Optimize. In the Optimize dialog box, you can view the SQL diagnosis results.
NoteDAS diagnoses the SQL statement based on its complexity, the data volume of the related table, and the database payload. The diagnosis may take more than 20 seconds to complete. After the diagnosis is complete, the diagnostics engine provides a diagnosis result, optimization suggestions, and expected performance improvements. You can decide whether to accept the suggestions based on the result.
In the Actions column for the target SQL template, click Throttling. On the Create Throttling Rule page, configure the throttling parameters for the SQL statement. For more information, see SQL throttling.
For a PolarDB for MySQL database instance, click IMCI in the Actions column for the target SQL template to view the In-Memory Column Index (IMCI) documentation.
NoteThe IMCI button is displayed for a PolarDB for MySQL instance if it does not have an IMCI node, the Maximum Execution Duration of its slow query log exceeds 20 seconds, and the Maximum Scanned Rows exceeds 200,000.
You can use IMCI to improve query performance for complex queries on large data volumes.
In the Slow Query Log Details section, you can also perform SQL Diagnostic Optimization and SQL Throttling for a target SQL statement by clicking Optimize and Throttling in the Actions column.
Additional information
The following items are the key metrics for the Slow Log Analysis feature:
Maximum Scanned Rows: one of the metrics in slow query log statistics. The metric indicates the maximum number of rows that are scanned in slow queries within the query time range.
SQL Template: the SQL template that is generated for SQL statements of the same type.
Slow SQL Sample: the data that is sampled from the SQL template at regular intervals. Sample statistics cannot cover all slow query logs. For more information, see View details about the slow query logs.
Slow Log Details: the details of the slow queries. The slow query log analysis feature is enabled by default. The SQL statements whose execution duration is longer than the value of the
long_query_timeparameter are displayed in the ApsaraDB RDS console.In ApsaraDB RDS for MySQL, you can enable the log_queries_not_using_indexes parameter to record SQL queries that do not use indexes. For more information, see official MySQL documentation.
FAQ
Q: Why is the execution completion time of SQL statements recorded in slow query logs different from the actual execution completion time of the SQL statements?
A: This issue usually occurs when the time zones recorded in the slow query logs are modified by the executed SQL statements. The execution completion time of an SQL statement varies with the time zone recorded in slow query logs at the following levels: session level, database level, and system level. If a time zone is specified for the database, the execution completion time of an SQL statement is recorded based on the time zone of the database. Otherwise, the execution completion time of an SQL statement is recorded based on the time zone of the system. If an SQL statement modifies the time zone at the session level, the time zone recorded in slow query logs may not be properly converted.
References
You can enable the autonomy features of DAS. This way, when slow SQL queries are detected on your database instance, DAS automatically optimizes the slow SQL queries.