When database performance degrades due to high workloads or query bottlenecks, Database Autonomy Service (DAS) helps you identify the cause. DAS collects and analyzes SQL statements whose execution duration exceeds the configured threshold, then provides diagnostics results and optimization suggestions so you can resolve performance issues quickly.
Prerequisites
Before you begin, ensure that you have:
An ApsaraDB RDS for PostgreSQL instance (Basic Edition is not supported)
How slow query logs are generated
The database kernel generates slow query logs when a query's execution time exceeds the threshold defined by engine parameters. Relevant parameters and thresholds vary based on the database engine. See the official documentation for your engine version for the full list of parameters and their defaults.
View and analyze slow query logs
Log on to the DAS console.
In the left-side navigation pane, choose Intelligent O&M Center > Instance Monitoring.
Find the database instance you want to manage and click the instance ID.
In the left-side navigation pane, choose Request Analysis > Slow Logs.
On the Slow Log Analysis tab, set a time range to view trends, statistics, and details of slow query logs. On the Slow Log Analysis tab, you can do the following:
NoteThe end time must be later than the start time, and the interval cannot exceed 24 hours. You can query slow query logs within the previous month.
View trends: In the Slow Log Trend section, click a point in the trend chart to view statistics and details at that point in time.
NoteIf an SQL statement is truncated due to display length limits, move the pointer over the statement to view the full content.
Download logs: Click
to download slow query logs to your computer.Debug with the API: Click
to populate the selected and entered parameters to the OpenAPI console for API debugging.Inspect events: In the Event Distribution section, view slow query log events within the selected time range. Click an event to see its details.
On the Slow Log Statistics tab, filter and diagnose SQL templates:
Use the filter conditions at the top of the list to narrow results.
To view a slow query sample for a specific SQL template, click Details in the Actions column. The Slow Log Sample dialog box appears.
To run a diagnosis, click Optimize in the Actions column. The SQL Diagnostic Optimization dialog box shows the diagnostics results. If you accept the optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statement into your database client or Data Management (DMS) for execution.
NoteDAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database workload. Suggestions may take more than 20 seconds to appear. After the SQL diagnostics engine completes its analysis, it returns diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics results.
On the Slow Log Details tab, find the SQL statement you want to diagnose and click Optimize in the Actions column to run SQL diagnostics.
What's next
Enable the automatic SQL optimization feature so that DAS automatically diagnoses and optimizes slow SQL queries when they are detected. See Automatic SQL optimization.