Database Autonomy Service (DAS) provides the new performance insight feature for ApsaraDB RDS for MySQL. The new performance insight feature aggregates statistics on SQL statements based on the Performance Schema feature provided by MySQL to help you evaluate the loads on your ApsaraDB RDS for MySQL instance and identify the root causes of performance issues. The new performance insight feature also provides solutions to the issues. This helps increase the stability of your database system and allows you to view the statistics on the tables and indexes on which no operations are performed.

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
  • The memory capacity of your RDS instance is greater than or equal to 8 GB.
  • The Performance Schema feature is enabled for your RDS instance. For information, see Enable the Performance Schema feature.
  • Your RDS 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), and Indonesia (Jakarta).

Billing rules

The new performance insight feature is in public preview. You can use it free of charge during the public preview.

Feature description

The new performance insight feature aggregates statistics on SQL statements based on the Performance Schema feature provided by MySQL to help you evaluate instance loads and identify the root causes of performance issues. The new version provides the following features:
  • Identifies the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements.
  • Allows you to view the trends of performance metrics for your RDS instance and the details of specified SQL statements.
  • Allows you to query the details of specified SQL statements by using SQL IDs and keywords.
  • Allows you to view the information about the trends of the Resource Usage, Executions, Scanned Rows, and Execution Duration of the specific SQL statements, and the information about the SQL statements.
  • Throttles and optimizes the specified SQL statements.
  • Allows you to compare and view the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements within different periods of time. You can also view the performance metrics of the specific SQL statements within different periods of time.
  • Allows you to view the statistics on the tables and indexes on which no operations are performed. DAS collects the statistics on the tables and indexes every early morning, determines whether operations are being performed on the tables and indexes, and records the number of accumulated days during which no operations are performed on the tables and indexes of your RDS instance.
The new performance insight feature provides more metrics than the original performance insight feature and more methods to view performance metrics. The following table compares the two versions.
ItemNew performance insightUse the original performance insight feature for an ApsaraDB RDS for MySQL instance
Metric
  • Allows you to view the following trend charts of all SQL statements: Resource Usage, Executions, Scanned Rows, and Execution Duration.
  • Allows you to view the following trend charts of a database: Executions/Errors, Scanned Rows/Updated Rows/Returned Rows, Logic Read, Physical Reads, Average Execution Duration/AvLock Duration, Number of Created Temporary Tables/Number of Created Temporary Disk Tables, Number of Sorted Rows, Select_Scan/Select_Range/Full Join/FUll Range Join/Sort_Scan/Sort_Range, and Top 5 SQL Trends.
  • Allows you to view the Memory Usage/CPU Utilization, Session, Traffic Throughput, and IOPS trend charts of your RDS instance.
  • Allows you to view the trend of active sessions and multidimensional loads of different categories such as SQL statements.
Method
  • Allows you to view the metric trends and the details of specified SQL statements within a specified period of time.
  • Allows you to compare and view the metric trends and the details of specified SQL statements within specified periods of time.
  • Allows you to query the details of specified SQL statements by using SQL IDs and keywords.
  • Allows you to view the statistics on the tables and indexes on which no operations are performed.
Allows you to view the metric trends and the details of specified SQL statements within a specified period of time.
SolutionThrottles and optimizes the specified SQL statements. Optimizes specified SQL statements.

Procedure

  1. 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.
  2. In the left-side navigation pane, choose Autonomy Service > Performance Insight.
  3. On the page that appears, click Enable Performance Insight.Performance Insight
    If the Performance Schema feature is disabled, the Parameter Settings dialog box appears. You need to click Configure to configure parameters related to the Performance Schema feature. For more information, see Enable the Performance Schema feature.
    Warning To modify the parameters, you must restart the RDS instance. We recommend that you perform this operation during off-peak hours and make sure that your application is configured to automatically reconnect to the RDS instance.
    When you enable the new performance insight feature, the system automatically enables the no flow meter/index identification feature. This feature identifies tables and indexes on which no operations are performed.
    Important Starting April 11, 2023, the no flow meter/index identification feature is supported. When you enable the new performance insight feature for an RDS instance, the no flow meter/index identification feature is automatically enabled for the RDS instance. If the new performance insight feature is enabled for an RDS instance before April 11, 2023, you need to manually turn on No flow meter/index identification in the Service Settings dialog box.
  4. On the Performance Insight page, click one of the following tabs based on your business requirements.
    New version
    • Display by Time Range:
      • Select a time range to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of all SQL statements within the specified time range.
        Note The end time must be later than the start time. You can query data within up to seven days of the last month.
      • Click Export to save the data as a file to your computer.
      • Click Show More Metrics to view the trend charts of key performance metrics.
        Note You can click Settings and select the metrics that you want to view in the Metrics section.
        Settings
      • In the SQL list section, perform the following operations to view the details of SQL statements.
        Note You can click Settings and select the performance metrics that need to be displayed in the list in the Column Settings section.
        • Click the ID of the SQL statement that you want to manage to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of the SQL statement within a specified time range. You can also view the sample information about the SQL statement.
        • Click Sample in the Actions column of the SQL statement that you want to manage to view the sample information about the SQL statement.
        • Click Throttling in the Actions column of the SQL statement that you want to manage. In the SQL Throttling dialog box, configure throttling parameters to throttle the SQL statement. For more information, see SQL throttling.
        • Click Optimize in the Actions column of the SQL statement that you want to manage. In the SQL Diagnostic Optimization dialog box, view the diagnostic results.

          If you accept the SQL diagnostic suggestions, click Copy in the upper-right corner and paste the optimized SQL statement to your database client or Data Management (DMS) for execution. If you do not accept the SQL diagnostic suggestions, click OK.

          Note DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the instance workloads. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnostic results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the provided information.

          You can also click (Database Expert Service) to purchase Database Expert Service. Database Expert Service provides value-added professional database services, such as emergency solutions, health diagnostics, performance optimization, security assurance, and data migration.

    • Display by Comparison: Select a point in time at which you want to compare the performance insight results and view the comparison results of the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements at the selected point in time. You can also view the comparison details in the SQL list section.
      Note You can click Settings to select the performance metrics to be displayed in the list.
    • Table/index: You can specify filter conditions to query the No flow meter and No traffic index that meet the conditions.

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

Disable the new performance insight feature

  1. 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.
  2. In the left-side navigation pane, choose Autonomy Service > Performance Insight.
  3. On the Performance Insight tab, click Service Settings, turn off Feature Setting, and then click OK. In the message that appears, click OK to disable the new performance insight feature.Performance Insight

    After the new performance insight feature is disabled, the system automatically disables the no flow meter/index identification feature. You can also separately disable the No flow meter/index recognition is not enabled feature. After the feature is disabled, DAS deletes all collected statistics on the tables and indexes on which no operations are performed.

    Important After the new performance insight feature is disabled, DAS no longer collects data and DAS deletes historical data.
    Disable the new performance insight feature