ApsaraDB RDS:Performance insight (new version)

Prerequisites

Before you begin, make sure that:

• The ApsaraDB RDS for MySQL instance does not run RDS Basic Edition or MySQL 5.5

• The instance has at least 8 GB of memory

• The instance resides in one of the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), or Indonesia (Jakarta)

• Performance Schema is enabled. See Enable the Performance Schema feature

Billing

Performance Insight is a value-added feature of DAS Economy Edition and DAS Enterprise Edition V3. The fee varies based on the edition you use.

• For supported regions, see Supported databases and regions.

• For pricing details, see Billing details.

How Performance Insight works

Performance Insight aggregates SQL execution statistics from Performance Schema to surface four key metrics for every SQL statement: Resource Usage, Executions, Scanned Rows, and Execution Duration. You can view these metrics over a selected time range, compare across two time periods, and drill into individual SQL statements to view samples.

Beyond SQL-level analysis, DAS collects no-traffic statistics every morning for all tables and indexes in your instance, recording how many consecutive days each table or index has had no reads or writes. This data appears in the Tables/Indexes tab after at least seven days of collection.

The following table compares the new and original Performance Insight features.

Enable Performance Insight

1. Go to the Instances page. In the top navigation bar, select the region where your instance resides, then click the instance ID.

2. In the left-side navigation pane, choose Autonomy Services > Performance Insight.

3. Click Enable Performance Insight. If Performance Schema is not enabled, the Parameter Settings dialog box appears. Click Configure to enable it. When Performance Insight is enabled, the no-traffic table/index identification feature is also enabled automatically.

   Warning

   Enabling Performance Schema requires restarting your instance. Perform this during off-peak hours and make sure your application is configured to reconnect automatically.

   Important

   The no-traffic table/index identification feature has been available since April 11, 2023. If you enabled Performance Insight before that date, turn on Identification for Tables and Indexes with No Traffic manually in the Service Settings dialog box.

View SQL metrics by time range

Use the Display by Time Range tab to analyze SQL performance over a specific period, identify top load-causing statements, and take action on individual queries.

1. On the Performance Insight page, click the Display by Time Range tab.

   

2. Select a time range to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts.

   Note

   The end time must be later than the start time. You can query data within a maximum of seven days in the last month.

3. Click Show More Metrics to view additional instance-level trend charts.

   Note

   Click Settings and select the metrics to display in the Metrics section.

   

4. In the SQL list, click Settings and configure Column Settings to select which performance metrics appear as columns.

5. Manage individual SQL statements using the following actions:

   • Click the SQL statement ID to view its Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts and SQL sample details.

   • Click Sample in the Actions column to view SQL sample details directly.

   • Click Throttling in the Actions column. In the SQL Throttling dialog box, configure throttling parameters. For more information, see SQL throttling.

   • Click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, review diagnostic results. If you accept the optimization suggestions, click Copy in the upper-right corner, then paste the optimized SQL statement into your database client or Data Management (DMS) for execution.

   Note

   DAS bases SQL diagnostics on statement complexity, table data volume, and instance workload. Results may take more than 20 seconds. After diagnostics completes, the SQL diagnostics engine provides results, optimization suggestions, and expected optimization benefits. You can also click (Database Expert Service) to access professional services including emergency solutions, health diagnostics, performance optimization, security assurance, and data migration.

6. Click Export to save the data as a file to your computer.

Compare SQL metrics across time periods

Use the Display by Comparison tab to compare the performance of all SQL statements within different periods of time, helping you confirm whether a recent change improved or degraded performance.

1. On the Performance Insight page, click the Display by Comparison tab.

2. Select a point in time to compare Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements at the specified point in time.

3. Review the comparison details in the SQL list.

   Note

   Click Settings to select the performance metrics to display in the list.

View tables and indexes with no traffic

Use the Tables/Indexes tab to identify tables and indexes that have not received any reads or writes for an extended period. You can use this information to reclaim unused storage or clean up stale schema objects.

1. On the Performance Insight page, click the Tables/Indexes tab.

2. Set filter conditions to query Tables with No Traffic or Indexes with No Traffic.

3. Click Export to save the results as a file to your computer.

Disable Performance Insight

1. Go to the Instances page. In the top navigation bar, select the region where your instance resides, then click the instance ID.

2. In the left-side navigation pane, choose Autonomy Services > Performance Insight.

3. Click Service Settings. In the dialog box, turn off Feature Setting and click OK. In the confirmation message, click OK. Disabling Performance Insight automatically disables the no-traffic table/index identification feature. You can also disable that feature separately—after disabling it, DAS deletes all collected no-traffic statistics and the data that is used by the tables and indexes.

   Important

   After Performance Insight is disabled, DAS stops collecting data and deletes all historical data.

   

What's next

Enable DAS autonomy features to automatically resolve performance issues:

• Use the automatic SQL throttling feature

• Use the automatic SQL optimization feature

• Use the automatic fragment reclamation feature