Database Autonomy Service (DAS) offers automatic SQL optimization for ApsaraDB RDS for PostgreSQL. When slow SQL queries occur on an instance, DAS automatically diagnoses them and creates indexes to ensure peak database performance.
Prerequisites
-
The ApsaraDB RDS for PostgreSQL instance must be of the High-availability series.
-
The target database instance has SQL Explorer and Audit enabled. For more information, see SQL Explorer and Audit.
NoteAfter you optimize slow SQL queries, the overall performance may degrade. To improve the accuracy of Automatic SQL Optimization, we recommend that you enable SQL Explorer and Audit.
Procedure
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.
-
You can go to the Autonomous Function Management page in the following two ways:
-
In the left navigation bar, select .
-
In the left navigation bar, select
-
-
In the Autonomous Function Settings tab, turn on the autonomous function switch, and in the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
-
If you select SQL Diagnostics Only, the system performs daily scheduled diagnostics on your SQL statements and provides index optimization suggestions, but the indexes are not created on your instance.
-
If you select SQL Diagnostics and Automatic Index Creation: SQL statements are diagnosed on a daily schedule to provide index optimization suggestions, and the suggested indexes are created on your instance.
NoteDAS will create the index during the Maintenance Window of the database instance.
-
-
Click OK.
-
(Optional) Configure an Alert Template to receive alerts about the Automatic SQL Optimization status of your database instance.
The system recommends an alert template and adds alert rules for the relevant autonomy events. Follow the on-screen instructions to complete the configuration.
Note-
If you have already configured an alert template for the instance, follow the on-screen instructions to add alert rules for the relevant autonomy events to the template.
-
To configure an alert template and alert rules manually, see Configure an alert template and Configure alert rules.
-
Select the Alert Contact Group to notify.
-
Click Add Contact to add a new alert contact.
-
Click Create Contact Group to add a new alert contact group.
-
Click Edit or OK for the target contact to modify or delete the contact's information.
For more information, see Manage alert contacts.
-
-
Confirm Associate with Resources.
-
Click Submit Configuration and confirm the alert configuration in the dialog box that appears.
-
Next steps
On the Autonomy Center page, you can view the Optimization Events that occurred within the selected time range.
Click Details for the target event. You can view detailed information about Problematic SQL Statement, SQL Statement Optimization, Index Suggestions, and Statement Optimization in the Root Cause Analysis and Suggestions tab of Slow SQL Statement Diagnostics. The optimization event details page displays basic event information (such as name, time, severity level, and duration) and Root Cause Analysis and Suggestions. The Problematic SQL Statements section lists statistics such as SQL template, database name, number of executions, average/maximum execution time, lock wait time, and number of scanned rows. The SQL Statement Optimization section displays the diagnosis ID, recommendation level, performance improvement factor, and execution status. You can click Verify Optimization Effect. The Index Recommendation section displays the suggestion type, database name, and DDL statement, and supports the Apply, Ignore, or Terminate operations. The Statement Optimization section displays statement-level optimization suggestions.