Perform SQL optimization when your database has slow-running SQL queries or their execution significantly affects application performance. Database Autonomy Service (DAS) supports automatic SQL optimization. Unlike traditional methods, DAS automatically identifies problematic SQL, generates index optimization suggestions, and creates the necessary indexes without causing table locks.
Prerequisites
| MySQL version | Supported RDS editions |
|---|---|
| MySQL 8.0 | RDS High-availability Edition, RDS Enterprise Edition, RDS Cluster Edition |
| MySQL 5.7 | RDS High-availability Edition, RDS Enterprise Edition, RDS Cluster Edition |
| MySQL 5.6 | RDS High-availability Edition |
| MySQL 5.5 | RDS High-availability Edition |
In some cases, overall performance may degrade after a slow-running SQL query is optimized. To improve the accuracy of automatic SQL optimization, enable SQL Explorer and Audit.
How it works
When automatically creating an index, DAS uses the Online DDL feature, which is natively supported by the MySQL kernel. This approach prevents the table locks that can occur during standard index creation. For more information, see SQL optimization technology.
Limitations
DAS cannot diagnose or optimize SQL statements that involve X-Engine tables.
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.
In the left-side navigation pane, choose . On the Autonomy Center page, click Autonomy Service Settings.
On the Autonomous Function Settings tab, turn on the main switch. On the Optimization and Throttling tab, select the Auto Index Creation and Deletion checkbox.
If you select SQL Diagnostics Only: DAS diagnoses SQL statements on a daily schedule and generates index optimization suggestions, but does not create the indexes on your instance.
If you select SQL Diagnostics and Automatic Index Creation: DAS performs daily diagnostics on SQL statements, generates index optimization suggestions, and automatically creates the recommended indexes on your instance.
NoteDAS creates indexes within the instance's Maintenance Window.
Click OK.
(Optional) Configure an alert template to receive alerts about the status of automatic SQL optimization on your database instance.
The system recommends an alert template and adds alert rules for related autonomy events. You can follow the on-screen instructions to complete the configuration.
NoteIf you have already configured an alert template for the instance, follow the prompts to add the alert rules for the relevant autonomy events to the existing template.
If you need to configure an alert template and alert rules yourself, see Configure alert templates and Configure alert rules.
Select an Alert Contact Groups to receive notifications.
Click Add Contact to add a new alert contact.
Click Add Contact Group to create a new alert contact group.
To edit or delete an existing contact, click Modify or Remove next to the contact.
For more information, see Manage alert contacts.
Confirm the resources in the Associated Resources section.
Click Submit Configuration and confirm the alert configuration in the dialog box that appears.
Next steps
On the Autonomy Center page, you can view Optimization Events from a selected time range.
Click Details for a target event. On the Root Cause Analysis and Suggestions tab of the Slow SQL Diagnosis (Diagnostic Optimization) page, you can view detailed information about Problematic SQL Statement, SQL Tuning, Index Suggestions, and Statement Optimization.
Related documents
If you need to manually optimize a target SQL statement, see SQL optimization.
API reference
API | Description |
Enables, modifies, or disables automatic SQL optimization for multiple database instances at once. | |
Retrieves diagnostic suggestions from the automatic SQL optimization feature of DAS. | |
Obtains statistics on automatic SQL optimization tasks for a specified time period, including the total number of tasks and the maximum performance improvement. |