All Products
Search
Document Center

Database Autonomy Service:Enable the Performance Schema feature

Last Updated:Jan 22, 2024

Database Autonomy Service (DAS) provides a new version of the performance insight feature. The data that is collected by the new version of the performance insight feature is generated by the Performance Schema feature of MySQL. To use the new version of the performance insight feature, you must enable the Performance Schema feature. This topic describes how to enable the Performance Schema feature.

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 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).

Background information

For more information about the Performance Schema feature, see MySQL Performance Schema in MySQL official documentation.

Usage notes

  • To enable the Performance Schema feature for a database instance, you must restart the database instance. We recommend that you enable the feature during off-peak hours and make sure that your application can automatically reconnect to the database instance.

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

Procedure

  1. Log on to the console based on the type of the database instance for which you want to enable the Performance Schema feature.

    • If your database instance is an ApsaraDB RDS for MySQL instance, log on to the ApsaraDB RDS console.

    • If your database instance is a PolarDB for MySQL cluster, log on to the PolarDB console.

  2. In the top navigation bar, select a region. In the instance list, find the database instance for which you want to enable the Performance Schema feature and click the instance ID.

  3. Go to the parameter configuration page.

    • For an ApsaraDB RDS for MySQL instance, click Parameters in the left-side navigation pane.

    • For a PolarDB for MySQL cluster, choose Settings and Management > Parameters.

  4. Search for the performance_schema parameter and configure this parameter to enable the Performance Schema feature.

    • For an ApsaraDB RDS for MySQL instance that runs MySQL 5.6, find the performance_schema parameter and select ON from the drop-down list in the Running Value column.

      Important

      For an ApsaraDB RDS for MySQL instance that runs 5.7 or 8.0 and whose storage capacity is greater than or equal to 8 GB, the performance_schema parameter is set to ON by default.Prerequisites

    • For a PolarDB for MySQL cluster, select Cluster from the drop-down list, click Modify in the upper part of the page, find the performance_schema parameter, and then set the parameter to ON in the Cluster Parameter column.

      Important

      For a PolarDB for MySQL cluster that runs MySQL 5.6 or 5.7, set the loose_performance_schema parameter to ON in the Cluster Parameter column.

    You can run the SHOW GLOBAL VARIABLES LIKE'%performance_schema'; command to view the value of the performance_schema parameter of the database instance.

  5. For an ApsaraDB RDS for MySQL instance, check the following parameters and make sure that the values of these parameters are greater than 0. Otherwise, the new version of the performance insight feature fails to collect data. For a PolarDB for MySQL cluster, you do not need to configure the parameters.

    Note
    • For an ApsaraDB RDS for MySQL instance that runs MySQL 5.6, you need to only modify the parameters that can be configured.

    • For more information about the parameters, see Performance Schema System Variables in MySQL official documentation.

    performance_schema_accounts_size
    performance_schema_digests_size
    performance_schema_error_size
    performance_schema_max_digest_length
    performance_schema_hosts_size
    performance_schema_max_digest_sample_age
    performance_schema_max_index_stat
    performance_schema_max_sql_text_length
    performance_schema_max_statement_classes
    performance_schema_max_statement_stack
    performance_schema_users_size
    performance_schema_setup_actors_size
    performance_schema_setup_objects_size
    performance_schema_max_thread_classes
    performance_schema_max_thread_instances
    performance_schema_max_file_classes
    performance_schema_max_file_instances
    performance_schema_max_table_handles
    performance_schema_max_table_instances

    The following example provides sample parameter values. Replace the parameter values with the actual values when you modify the parameters.

    loose_performance_schema_accounts_size=1000
    loose_performance_schema_digests_size=10000
    loose_performance_schema_error_size=10000
    loose_performance_schema_max_digest_length=1024
    loose_performance_schema_hosts_size=1000
    loose_performance_schema_max_digest_sample_age=60
    loose_performance_schema_max_index_stat=10000
    loose_performance_schema_max_sql_text_length=1024
    loose_performance_schema_max_statement_classes=256
    loose_performance_schema_max_statement_stack=10
    loose_performance_schema_users_size=10000
    loose_performance_schema_setup_actors_size=10000
    loose_performance_schema_setup_objects_size=10000
    loose_performance_schema_max_thread_classes=100
    loose_performance_schema_max_thread_instances=10000
    loose_performance_schema_max_file_classes=256
    loose_performance_schema_max_file_instances=1000
    loose_performance_schema_max_table_handles=10000
    loose_performance_schema_max_table_instances=1000
  6. Submit parameter modifications.

    Warning

    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.

    After the parameter values are modified, you can run the SHOW GLOBAL VARIABLES LIKE'%performance_schema'; command to view the value of the performance_schema parameter of the database instance.