Database Autonomy Service (DAS) automatically identifies problematic SQL queries, generates index recommendations, and creates indexes without causing table locks — replacing manual optimization with a hands-off approach.
How it works
Automatic SQL optimization uses the native online DDL (Data Definition Language) feature of the MySQL kernel, which prevents the long-running table locks that can occur with conventional indexing methods. For more information, see SQL optimization technology.
Prerequisites
|
Database |
Region |
|
China (Hangzhou), China (Shanghai), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Nanjing - Local Region - Being Discontinued), China (Fuzhou - Local Region - Being Discontinued), China (Chengdu), China (Zhengzhou), China (Hong Kong), Japan (Tokyo), South Korea (Seoul), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), Thailand (Bangkok), UAE (Dubai), Saudi Arabia (Riyadh), Germany (Frankfurt), US (Silicon Valley), US (Virginia), and UK (London) |
Billing
Subscribe to the DAS O&M service to use automatic SQL optimization. For information about the fees for the DAS O&M service, see O&M Service (formerly Economy Edition).
Limitations
DAS does not support diagnosing or optimizing SQL queries on tables that use the X-Engine.
Procedure
Log on to the DAS console.
In the navigation pane on the left, click .
Find the target instance and click the instance ID to open the instance details page.
In the navigation pane on the left, click Autonomy Center.
On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
-
In the tab, turn on the autonomous function switch. Then, on the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
-
SQL Diagnostics Only: DAS provides only recommendations and does not create or modify any index.
-
SQL Diagnostics and Automatic Index Creation: DAS automatically implements the optimization recommendations.
NoteDAS will create an index within the database instance's maintenance window or maintenance window.
-
-
Click OK.
-
Configure an Alert Templates to receive notifications about automatic SQL optimization events.
The system recommends an alert template and adds alert rules for the corresponding autonomy events. Follow the prompts to complete the configuration.
Note-
If you have already configured an alert template for the instance, follow the prompts to add alert rules for the relevant autonomy events to the existing template.
-
If you need to manually set up an alert template and alert rules, see Configure alert templates and Configure alert rules.
-
-
Select an Alert Contact Groups for notifications.
-
Click Add Contact to add a new alert contact.
-
Click Create Contact Group to add a new alert contact group.
-
To modify or delete an existing contact, click Modify or Remove next to the target contact.
For more information, see Manage alert contacts.
-
-
Click Submit Configuration and confirm the alert settings in the subsequent dialog box.
Next steps
-
On the Autonomy Center page, view automatic SQL optimization events from a specified time range. Then, select Optimization Events in the filter bar, and find the Slow SQL Diagnosis (Diagnostic Optimization) event in the event list below.
-
Click Details. On the Root Cause Analysis and Suggestions tab of the Slow SQL Diagnosis (Diagnostic Optimization) panel, you can view information in the Problematic SQL Statement and SQL Optimization sections. The SQL Optimization section provides details on Index Suggestions and Statement Optimization.
References
To optimize a specific SQL statement manually, see SQL optimization.
FAQ
-
Q: Does automatic index creation by DAS cause long-running table locks?
A: No. DAS uses native online DDL operations to create indexes, which prevents long-running table locks.
-
Q: Why does DAS not optimize a slow SQL query that is detected in the console?
A: Not all slow log entries are optimized. DAS implements an optimization only after verifying that a recommendation is available and that applying it will significantly improve performance. If the SQL statement already has a suitable index, or if the potential performance gain is insignificant, DAS skips the optimization.
API reference
|
API |
Description |
|
Enables, modifies, or disables the automatic SQL optimization feature for multiple instances. |
|
|
Queries the diagnostic recommendations generated by automatic SQL optimization in DAS. |
|
|
Queries statistics on automatic SQL optimization tasks within a specified time range, including the total number of tasks and the maximum performance gain. |