All Products
Search
Document Center

Database Autonomy Service:Performance insight (new version)

Last Updated:Mar 05, 2024

Database administrators and developers focus on performance optimization and monitoring. 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 to help you identify performance issues at the earliest opportunity and provide solutions. This improves the performance and stability of your databases. The new version also collects statistics on tables and indexes on which no operations are performed to help you analyze the data on tables and indexes and identify the data that can be deleted. This increases the storage usage and database performance.

Prerequisites

  • The database instance that you want to manage is of one of the following types:

    • ApsaraDB RDS for MySQL

      Note
      • ApsaraDB RDS for MySQL Basic Edition is not supported.

      • ApsaraDB RDS for MySQL V5.5 is not supported.

    • ApsaraDB MyBase for MySQL

    • PolarDB for MySQL

  • The memory capacity 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.

    Note

    After you enable the Performance Schema feature for the database instance, the resources of the instance are consumed. For more information, see Stress testing report of Performance Schema.

  • 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

The new version of performance insight is in public preview. You can use the new version for free during the public preview.

Introduction

The new version of performance insight aggregates the 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, number of executions, number of 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 the 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.

  • Throttles and optimizes 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.

  • Allows you to view the statistics on tables and indexes on which no operations are performed. DAS collects statistics on tables and indexes every early morning to determine whether operations have been performed on these tables and indexes and records the number of days during which no operations are performed on the tables and indexes.

    Note

    The shortest period that identifies a table or index on which no operations are performed is seven days. After you enable this feature, you can view the statistics on the tables and indexes on which no operations are performed at least seven days later.

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 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 the specified SQL statements by using SQL IDs and keywords.

  • Allows you to view the statistics on tables and indexes on which no operations are performed.

Allows you to view the metric trends and the details of the specified SQL statements within a specific period of time.

Solution

Throttles and optimizes the specified SQL statements.

Optimizes the specified SQL statements.

Supported database engine

ApsaraDB RDS for MySQL, PolarDB for MySQL, and ApsaraDB MyBase for MySQL

ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, PolarDB for MySQL, PolarDB for PostgreSQL, ApsaraDB MyBase for MySQL, and PolarDB for PostgreSQL (Compatible with Oracle)

Procedure

  1. Log on to the DAS console.

  2. In the left-side navigation pane, click Instance Monitoring.

  3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

  4. In the left-side navigation pane, click Performance Insight.

  5. On the Performance Insight tab, click Enable Performance Insight.

    If the Performance Schema feature is disabled, the Parameter Settings dialog box appears. You must click Configure to configure the parameters that are related to the Performance Schema feature. For more information, see Enable the Performance Schema feature.

    Warning

    To make the modifications to the parameters take effect, you must restart the database 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 database instance.

    When you enable the performance insight feature, the system automatically enables the feature that identifies tables and indexes on which no operations are performed.

    Important

    From April 11, 2023, the performance insight feature supports the feature that identifies tables and indexes on which no operations are performed. This feature is automatically enabled when you enable the performance insight feature. If the performance insight feature was enabled for your database instances before April 11, 2023, you can manually turn on the Identification for Tables and Indexes with No Traffic switch in the Service Settings dialog box.

  6. On the Performance Insight tab, click one of the following tabs 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 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, find the Column Settings section in the dialog box that appears, and then select the performance metrics to be displayed in the SQL 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 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 corresponding to 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 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 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 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, number of 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 SQL list.

    • Tables/Indexes: You can specify filter conditions to query the statistics on tables and indexes on which no operations are performed in the Tables with No Traffic and Indexes with No Traffic sections.

      You can click Export to save the filtered data to your computer.

Disable the performance insight feature

  1. Log on to the DAS console.

  2. In the left-side navigation pane, click Instance Monitoring.

  3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

  4. In the left-side navigation pane, click Performance Insight.

  5. 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 to disable the Performance Insight feature.

    After you disable the performance insight feature, the feature that identifies tables and indexes on which no operations are performed is also disabled. You can also disable only the feature that identifies tables and indexes on which no operations are performed. After this feature is disabled, DAS deletes the statistics on tables and indexes on which no operations are performed and the statistics on tables and indexes on which operations are performed.

    Important

    After the performance insight feature is disabled, DAS does not collect data and deletes historical data, including the statistics on tables and indexes on which no operations are performed and the statistics on tables and indexes on which operations are performed.

    关闭新版

References

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