Optimize SQL statements that run slowly or whose execution efficiency significantly affects your application's performance. Database Autonomy Service (DAS) supports automatic SQL optimization. DAS automatically detects problematic SQL statements, suggests index optimizations, and creates indexes without locking tables.
Prerequisites
Your RDS instance runs one of the following MySQL versions and RDS editions:
MySQL 8.0 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.7 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.6 on RDS High-availability Edition
MySQL 5.5 on RDS High-availability Edition
After slow SQL statements are optimized, global performance may degrade. To improve the accuracy of automatic SQL optimization, enable SQL Explorer and Audit.
Function introduction
When DAS automatically creates an index, it uses the online Data Definition Language (DDL) feature that is natively supported by the MySQL kernel. This prevents table locks that can occur when you create a normal index. For more information, see SQL optimization techniques.
Limitations
DAS does not support diagnosing or optimizing SQL statements for tables that use the X-Engine storage engine.
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 navigation pane on the left, choose . On the Autonomy Center page, click Autonomy Service Switch.
On the Autonomy Service Settings tab, turn on the switch for the autonomy service. Then, on the Optimization And Throttling tab, select Automatic Index Creation And Deletion.
If you select SQL Diagnostics Only: DAS runs daily diagnostics on SQL statements and provides index optimization suggestions. The indexes are not created on your instance.
If you select SQL Diagnostics And Automatic Index Creation: DAS runs daily diagnostics on SQL statements, provides index optimization suggestions, and creates the indexes on your instance.
NoteDAS creates indexes during the Maintenance Window of the database instance.
Click OK.
(Optional) Configure an alert template and subscribe to alerts to stay informed about the automatic SQL optimization status of your database instance.
DAS recommends an alert template and adds alert rules for the corresponding autonomy events. Follow the on-screen instructions to complete the configuration.
NoteIf you have already configured an alert template for the instance, follow the on-screen instructions to add the required alert rules for autonomy events to the template.
If you want to configure the alert template and alert rules yourself, see Configure an alert template and Configure an alert rule.
Select an Alert Contact Group to receive alert notifications.
Click Add Contact to add a new alert contact.
Click Add Contact Group to add a new alert contact group.
Click Modify or Remove next to a contact to modify or delete the contact.
For more information, see Manage alert contacts.
Confirm the Linked Instance.
Click Submit Configuration and confirm the alert configuration in the dialog box.
What to do next
On the Autonomy Center page, you can view the Optimization Events that occurred within a specified time range.
Click Details for the target event. On the Root Cause Analysis And Suggestions tab of the Slow SQL Diagnostics (Diagnostic Optimization) page, view details about the Problem SQL, SQL Optimization, Index Recommendation, and Statement Optimization.
References
For more information about how to manually optimize a target SQL statement, see SQL optimization.
Related API operations
API | Description |
Enables, modifies, or disables the automatic SQL optimization feature in batches. | |
Queries the diagnostic suggestions that are generated by the automatic SQL optimization feature of DAS. | |
Obtains the statistics of automatic SQL optimization tasks within a specified time range, including the total number of tasks and the maximum benefits. |