Performance insight (new version) - Database Autonomy Service

Database Autonomy Service (DAS) provides the performance insight feature. The new version of the feature aggregates the statistics on SQL statements based on the Performance Schema feature provided by MySQL. The new version helps you evaluate database workloads and identify the root causes of database performance issues. The new version also provides solutions to improve the stability of your databases.

Prerequisites

The database instance that you want to manage is of one of the following types:
- ApsaraDB RDS for MySQL instance
- ApsaraDB MyBase for MySQL instance
The memory size of the database instance is greater than or equal to 8 GB.
The Performance Schema feature is enabled for the database instance. For more information about how to enable this feature, see Enable the Performance Schema feature.
The database 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 version of performance insight is in public preview. You can use the new version for free during the public preview.

Overview

The new version aggregates statistics on SQL statements based on the Performance Schema feature provided by MySQL to help you evaluate database workloads and identify the root causes of database 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 databases and the details of the 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, number of executions, number of scanned rows, and execution duration of the specified SQL statements, and the information about SQL samples related to the SQL statements.
Performs throttling and optimization on the specified SQL statements.
Allows you to compare and view the resource usage, number of executions, number of 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.

The new version provides more metrics than the original version and allows you to use more methods to view the trends of database performance metrics. The following table compares the new version and the original version.


Item	Performance insight (new version)	Performance insight (original version)
Metric	Allows you to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of all SQL statements. Allows you to view the CPU Utilization, 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 trend charts of your databases.	Allows you to view the Memory Usage/CPU Utilization, Session, Traffic Throughput, and IOPS trend charts of your databases. Allows you to view the active session trends and multidimensional loads of different categories such as SQL statements.
Displayed details	Allows you to view the metric trends and the details of the specified SQL statements within a specific period of time. Allows you to compare and view the metric trends and the details of the specified SQL statements within different periods of time. Allows you to query the details of specified SQL statements by using SQL IDs and keywords.	Allows you to view the metric trends and the details of the specified SQL statements within a specific period of time.
Solution	Performs throttling and optimization on the specified SQL statements.	Performs optimization on the specified SQL statements.
Supported database engine	ApsaraDB RDS for MySQL instances, PolarDB for MySQL clusters, and ApsaraDB MyBase for MySQL instances	ApsaraDB RDS for MySQL instances, ApsaraDB RDS for PostgreSQL instances, PolarDB for MySQL clusters, PolarDB for PostgreSQL clusters, ApsaraDB MyBase for MySQL instances, and PolarDB for PostgreSQL (Compatible with Oracle) clusters

Procedure

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Performance Insight.
On the Performance Insight tab, click Enable performance insight.
If the Performance Schema feature is disabled, the Parameter settings dialog box appears. Click Go to Settings to configure parameters related to performance_schema. For more information, see Enable the Performance Schema feature.
Important To modify the parameters, you must restart the database instance. We recommend that you perform this operation during off-peak hours and make sure that your application can automatically reconnect to the database instance.
On the Performance Insight tab, click one of the following subtabs based on your business requirements:
- Display by Time Range:
  - Select a time range that you want to monitor 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 within the last month.
  - Click Export to save the data as a file to your computer.
  - Click ShowMore Metrics to view the trend charts of key performance metrics.
    Note You can click Settings to select the metrics that you want to view.
  - In the SQL list section, perform the following operations to view the details of SQL statements.
    Note You can click Settings. Then, in the Column Settings section, select the performance metrics that need to be displayed in the list.
    - 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 specific time range. You can also view the details of the SQL sample related to the SQL statement.
    - Click Sample in the Actions column corresponding to the SQL statement that you want to manage to view the details of the SQL sample related to the SQL statement.
    - Click Throttling in the Actions column corresponding to the SQL template. 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 corresponding to the SQL statement that you want to manage. In the SQL Diagnostic Optimization dialog box, view the diagnostic results.
      If you want to adopt the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not adopt the suggestions, click OK.
      Note DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned 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 diagnostic 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 assistance, 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, number of scanned rows, and execution duration of all SQL statements at the specified point in time. You can also view the detailed comparison results in the SQL list section.
  Note You can click Settings to select the performance metrics to be displayed in the list.

Disable the performance insight feature

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Performance Insight.
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 performance insight feature.
Important After the performance insight feature is disabled, DAS does not collect data and deletes historical data.