performance insights from RDS MySQL Performance Schema - ApsaraDB RDS

Database administrators and developers focus on performance optimization and monitoring. Database Autonomy Service (DAS) provides the new performance insight feature. The feature collects information about SQL statements by using the Performance Schema feature of MySQL to identify performance issues at the earliest opportunity and provide solutions to improve the performance and stability of the database system. The feature also collects statistics on tables and indexes on which no operations are performed to help you analyze the collected data and identify the data that can be deleted. This increases the storage usage and database performance.

Prerequisites

The RDS instance runs MySQL.
Note
- The RDS instance does not run RDS Basic Edition.
- The RDS instance does not run MySQL 5.5.
The memory capacity of your RDS instance is greater than or equal to 8 GB.
The Performance Schema feature is enabled for the RDS instance. For more information, see Enable Performance Schema.
Note
If you enable the Performance Schema feature for the RDS instance, a specific amount of resources of the RDS instance are consumed. For more information, see Performance Schema stress test report.
The 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

If you enable this feature for an instance in a supported region, you are charged according to your selected edition.

For the regions supported by DAS Economy Edition and Enterprise Edition V3, see Supported databases and regions for each edition.
For more information about the billing of DAS Economy Edition and Enterprise Edition V3, see Billing details.

Note

In regions where DAS Economy Edition and Enterprise Edition V3 are not yet available, Performance Insight remains in free public preview. When these editions become available in a region, the free preview for Performance Insight ends, and it becomes a paid, value-added feature.

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 capabilities:

Identifies the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements.
Allows you to view the trends of performance metrics 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 information about the trends of the Resource Usage, Executions, Scanned Rows, and Execution Duration of the specified SQL statements, and the information about SQL samples related to 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, and the performance metrics of the specified 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.
Note
The minimum number of accumulated days during which no operations are performed on the tables and indexes is set to seven days. After the no flow meter/index identification feature is enabled, you can view the data of the tables and indexes on which no operations are performed at least seven days later.

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.

Item	New performance insight	Original performance insight
Metrics	Allows you to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of all SQL statements. Supports viewing trend charts for the following database metrics: Executions/Errors, Scanned Rows/Updated Rows/Returned Rows, Logical Reads, Physical Reads, Average Execution Duration/Average Lock Duration, Number of Created Temp Tables/Number of Created Disk Temp Tables, Number of Sorted Rows, Select_Scan/Select_Range/Full Join/Full Range Join/Sort_Scan/Sort_Range, and Top 5 SQL Trends.	Supports viewing trend charts for Memory/CPU Usage, Session Connection, Traffic throughput, and IOPS of the target database. Allows you to view the trend of average active sessions and multidimensional loads of different categories such as SQL statements.
View methods	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.
Solutions	Throttles and optimizes the specified SQL statements.	Optimizes specified SQL statements.

Enable Performance Insight

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 left-side navigation pane, choose Autonomy Services > Performance Insight.
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 Performance Schema.
Warning
You must restart the RDS instance for the parameter configuration to take effect. 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 can manually turn on Identification for Tables and Indexes with No Traffic in the Service Settings dialog box.
On the Performance Insight page, select a feature tab as needed:
- 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
    When you select a time range, 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 trend charts for important metrics.
    
    Note
    You can click Settings and select the metrics for which to display trend charts in the Index section.
  - In the SQL list section, perform the following operations to view the details of SQL statements.
    
    Note
    You can click Settings and select the statistical metrics to display in the list from 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 details of the SQL sample related to the SQL statement.
    - Click Sample in the Actions column of the SQL statement that you want to manage to view the details of the SQL sample related to the SQL statement.
    - In the Actions column for a SQL statement, click Throttling. On the SQL Throttling page, configure its throttling parameters. 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 optimization suggestions, click Copy in the upper-right corner of the page and paste the optimized SQL statement to the database client or Data Management (DMS) for execution. If you do not accept the SQL optimization suggestions, click Cancel.
      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 diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics results.
      You can also click (Database Expert Service) to purchase the 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 to view the comparison results of the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements at the specified point in time. You can also view the comparison details in the SQL list section.
  
  Note
  You can click Settings to select the statistical metrics to display in the list.
- Tables/Indexes: You can set filter conditions to query data that meet the requirements of Tables with No Traffic and Indexes with No Traffic.
  
  Click Export to save the filtered data to a local file.

Disable the new performance insight feature

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 left-side navigation pane, choose Autonomy Services > Performance Insight.
On the Performance Insight tab, click Service Settings. Turn off the Feature Setting switch and click OK. In the confirmation dialog box, click OK again to disable Performance Insight.

When you disable Performance Insight, the feature for identifying tables and indexes with no traffic is also disabled. You can also separately disable Idle Table and Index Identification. After this feature is disabled, DAS deletes all data about inactive tables and indexes, including their usage history.

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

References

You can enable the autonomy features of DAS to implement automatic governance to resolve performance issues.