Database Autonomy Service (DAS) provides the SQL diagnostics feature. This feature allows DAS to perform diagnostics on SQL statements, provide index optimization suggestions, and then create indexes for your database instance based on the suggestions. You can enable automatic SQL diagnostics and index creation in the DAS console.

Prerequisites

  • The destination instance that you want to manage is of one of the following types:
    • ApsaraDB RDS for MySQL High-availability Edition and Enterprise Edition
    • PolarDB for MySQL Cluster Edition and X-Engine
    • ApsaraDB MyBase for MySQL High-availability Edition
    • ApsaraDB RDS for PostgreSQL High-availability Edition
  • Enable Autonomy Service is turned on for the database instance. For more information, see Autonomy center.
  • SQL Explorer is enabled for the database instance. For more information, see SQL Explorer.
    Note After slow SQL queries are optimized, the overall performance of the database instance may degrade. To ensure that the automatic SQL optimization feature optimizes only abnormal SQL statements, we recommend that you enable SQL Explorer.

Background information

Automatic index creation relies on the automatic SQL optimization feature and is implemented by using online DDL statements that are supported by the native MySQL kernel. This prevents table locking issues that may be caused by conventional indexing. For more information, see SQL optimization technology.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
  4. In the left-side navigation pane, click Autonomy Center.
  5. On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
    Autonomy Service Settings
  6. On the Autonomous Function Settings tab of the Autonomous Function Management panel, turn on Enable Autonomy Service. On the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
    • SQL Diagnostics Only: DAS performs daily diagnostics on SQL statements and provides index optimization suggestions. However, DAS does not create indexes on your database instance.
    • SQL Diagnostics and Automatic Index Creation: DAS performs daily diagnostics on SQL statements, provides index optimization suggestions, and then creates indexes on your database instance based on the suggestions.
      Note DAS creates indexes within the maintenance window of your database instance.
  7. In the message that is displayed, click OK.

Related API operations

Best practices

What to do next

  1. On the Autonomy center page in the DAS console, you can view optimization events that occurred within a specified period of time. dd
  2. On the right side of Slow SQL Statement Diagnostics (Diagnostics and Optimization), click Details. Then, on the Root Cause Analysis and Suggestions tab, you can view information about Problematic SQL Statements, SQL Statement Optimization, Index Recommendation, and Statement Optimization. ss