How do I enable the Performance Schema feature? - Database Autonomy Service

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

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

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

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.