Performance insight (new) - PolarDB

PolarDB for MySQL provides the diagnostics feature that integrates some features of Database Autonomy Service (DAS). You can use the performance insight (new) feature to aggregate the statistics on SQL statements, rapidly evaluate the workloads on your cluster, identify the root causes of performance issues, and seek appropriate solutions. This helps you improve the stability of your cluster.

Prerequisites

The memory of the cluster must be greater than or equal to 8 GB.
The loose_performance_schema or performance_schema parameter is set to ON. For more information, see Specify cluster and node parameters.
This feature is available only on clusters in the China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), and China (Chengdu) regions.

Billing

The performance insight (new) feature is in the public preview. You can use it for free during the public preview.

Background information

The performance insight (new) feature is based on performance_schema provided by MySQL and can help you aggregate the statistics on SQL statements, rapidly evaluate the workloads on your cluster, and identify the root causes of performance issues. The feature can serve the following purposes:

Identify the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements.
View the trends of performance metrics for your cluster and the details of specified SQL statements.
Query the details of specified SQL statements by using SQL IDs and keywords.
View the trends of the Resource Usage, Executions, Scanned Rows, and Execution Duration of specified SQL statements, and sampling information.
Implement throttling and optimization on specified SQL statements.
Compare and view the Resource Usage, Executions, Scanned Rows, and Execution Duration of all SQL statements and performance metrics of specified SQL statements within specified periods of time.

The new version provides more metrics over the original version and more methods to view performance metrics. The following table compares the two versions.

Item	Performance insight (new)	Performance insight (original)
Metrics	The Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of all SQL statements. The CPU Utilization, Executions/Errors, Scanned Rows/Updated Rows/Returned Rows, Logic Read, Physical Reads, Average Execution Duration/Average Locking 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 cluster.	The Memory Usage/CPU Utilization, Session, Traffic Throughput, and IOPS trend charts of your cluster. The active session trends and multidimensional loads of different categories such as SQL statements.
View methods	View the metric trends and the details of specified SQL statements within a specified period of time. Compare and view the metric trends and the details of specified SQL statements within specified periods of time. Query the details of specified SQL statements by using SQL IDs and keywords.	View the metric trends and the details of specified SQL statements within a specified period of time.
Governance solutions	Throttling and optimization on specified SQL statements.	Optimization on specified SQL statements.

Procedure

Log on to the PolarDB console.
In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
On the Clusters page, find the cluster for which you want to enable the autonomy service, and click the cluster ID.
In the left-side navigation pane, choose Diagnostics and Optimization > Diagnosis.
On the Performance Insight tab, click Enable Performance Insight.
Note
If you want to use the original version, click Back to Previous Version. For more information about the original version, see Performance Insight (original version).
On the Performance Insight tab, select 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 Show More 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 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. 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 adopt the SQL diagnostic 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 accept the SQL diagnostic suggestions, click OK.
      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, 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 SQL list.
- Tables/Indexes: You can view table information and index information of specified tables by database name, table name, or days without traffic.

Disable the performance insight feature

Important

After the performance insight feature is disabled, DAS stops data collection and deletes historical data.

Log on to the PolarDB console.
In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
On the Clusters page, find the cluster for which you want to enable the autonomy service, and click the cluster ID.
In the left-side navigation pane, choose Diagnostics and Optimization > Diagnosis.
On the Performance Insight tab, click Service Settings.
In the Service Settings dialog box, turn off Feature Setting and click OK.
In the message that appears, click OK.