Slow SQL queries significantly decrease the 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 log analysis feature for slow queries. 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.
Prerequisites
The database instance that you want to manage is of one of the following types:
ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB MyBase for MySQL, and self-managed MySQL database
ApsaraDB RDS for SQL Server and ApsaraDB MyBase for SQL Server
ApsaraDB RDS for PostgreSQL and PolarDB for PostgreSQL
ApsaraDB for Redis and self-managed Redis database
PolarDB for PostgreSQL (Compatible with Oracle)
PolarDB-X 2.0
ApsaraDB for MongoDB and self-managed MongoDB database
ImportantApsaraDB RDS for MySQL and ApsaraDB RDS for PostgreSQL instances of Basic Edition do not support the log analysis feature for slow queries.
You can view the global trends of slow query logs only for ApsaraDB RDS for MySQL instances, ApsaraDB MyBase for MySQL clusters, and PolarDB for MySQL clusters that reside in the Chinese mainland.
The database instance is connected to DAS. For more information, see Connect a database instance to DAS.
Background information
Slow query logs are generated by the database kernel. Relevant parameters and thresholds vary based on the database engine. For more information, see the corresponding official documentation.
View the slow query logs of a single database instance
In this example, an ApsaraDB RDS for MySQL instance is used.
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, choose Request Analysis > Slow Logs.
On the Slow Log Analysis tab, specify a time range and view the trends, statistics, and details of the slow query logs within the specified time range.
NoteWhen you specify the time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs within the previous month.
In the Slow Log Trend section, you can click a point in time in the trend chart and view the statistics and details of the slow query logs at the point in time.
You can click Export Slow Log to download slow query logs to your computer.
On the Slow Log Statistics tab, you can perform the following operations:
Find the SQL template that you want to manage and click Sample in the Actions column. In the Slow Log Sample dialog box, view the slow query log sample of the SQL template.
Find the SQL template that you want to manage and click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, view the SQL diagnostics results.
If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statement to the database client or Data Management (DMS) for execution. If you do not accept the SQL optimization suggestions, click Cancel.
NoteDAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database workload. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics results.
You can also click Database Expert Service to purchase the expert service. Database Expert Service provides value-added professional database services, such as emergency assistance, health diagnostics, performance optimization, security assurance, and data migration.
Find the SQL template that you want to manage and click Throttling in the Actions column. In the SQL Throttling dialog box, configure the parameters. For more information, see SQL throttling.
For a PolarDB for MySQL cluster, find the SQL template that you want to manage and click IMCI in the Actions column to view the documentation of the In-Memory Column Index (IMCI) feature.
NoteThe IMCI button is displayed in the Actions column if no IMCI nodes are purchased for the PolarDB for MySQL cluster, the maximum execution duration of the SQL template exceeds 20 seconds, and the maximum number of scanned rows of the SQL template exceeds 200,000.
We recommend that you use the IMCI feature to improve query performance when you require complex queries on a large amount of data.
On the Slow Log Details tab, you can find the SQL statement that you want to manage and click Optimize or Throttling in the Actions column. This way, you can also perform SQL diagnostics or SQL throttling.
View the global trends of slow query logs
If you have multiple database instances, you can view the global trends of slow query logs for the database instances and the statistics of slow query logs for the top 10 database instances in the Global Slow Log Trend and Top Instance Statistics sections of the Slow Logs page.
You can view the global trends of slow query logs only for ApsaraDB RDS for MySQL instances, ApsaraDB MyBase for MySQL clusters, and PolarDB for MySQL clusters that reside in the Chinese mainland.
Log on to the DAS console.
In the left-side navigation pane, choose Request Analysis > Slow Logs. On the page that appears, view the statistics in the Global Slow Log Trend and Top Instance Statistics sections.
NoteWhen you select a time range, the end time must be later than the start time. You can query slow query logs within the previous 14 days.
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 for ApsaraDB RDS for MySQL instances or PolarDB for MySQL clusters?
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.