Turning on the Performance Schema for Performance Insights on Aurora MySQL - Amazon Aurora

Turning on the Performance Schema for Performance Insights on Aurora MySQL

The Performance Schema is an optional feature for monitoring Aurora MySQL runtime performance at a low level of detail. The Performance Schema is designed to have minimal impact on database performance. Performance Insights is a separate feature that you can use with or without the Performance Schema.

Overview of the Performance Schema

The Performance Schema monitors events in Aurora MySQL databases. An event is a database server action that consumes time and has been instrumented so that timing information can be collected. Examples of events include the following:

  • Function calls

  • Waits for the operating system

  • Stages of SQL execution

  • Groups of SQL statements

The PERFORMANCE_SCHEMA storage engine is a mechanism for implementing the Performance Schema feature. This engine collects event data using instrumentation in the database source code. The engine stores events in memory-only tables in the performance_schema database. You can query performance_schema just as you can query any other tables. For more information, see MySQL Performance Schema in the MySQL Reference Manual.

Effect of turning on the Performance Schema

Performance Insights and the Performance Schema are separate features, but they are connected. When the Performance Schema is turned on for Aurora MySQL, Performance Insights does the following:

  • Collects detailed, low-level monitoring information

  • Collects active session metrics every second

  • Displays DB load categorized by detailed wait events, which you can use to identify bottlenecks

Effect of not turning on the Performance Schema

When the Performance Schema is not turned on for Aurora MySQL, Performance Insights does the following:

  • Doesn't collect wait events, per-SQL metrics, or other detailed, low-level monitoring information

  • Collects active session metrics every five seconds instead of every second

  • Reports user states such as inserting and sending, which don't help you identify bottlenecks

Automatic management of the Performance Schema by Performance Insights

When you create an Aurora MySQL DB instance with Performance Insights turned on, the Performance Schema is also turned on. In this case, Performance Insights automatically manages your Performance Schema parameters. This is the recommended configuration.

For Performance Insights to manage the Performance Schema automatically, the performance_schema must be set to 0. By default, the value of Source is system.

You can also manage the Performance Schema manually. If you choose this option, set the parameters according to the values in the following table.

Parameter name Parameter value

performance_schema

1 (Source column has the value system)

performance-schema-consumer-events-waits-current

ON

performance-schema-instrument

wait/%=ON

performance_schema_consumer_global_instrumentation

ON

performance_schema_consumer_thread_instrumentation

ON

If you change the performance_schema parameter value manually, and then later want to change to automatic management, see Configuring the Performance Schema for automatic management.

Important

When Performance Insights turns on the Performance Schema, it doesn't change the parameter group values. However, the values are changed on the DB instances that are running. The only way to see the changed values is to run the SHOW GLOBAL VARIABLES command.

Effect of a reboot on the Performance Schema

Performance Insights and the Performance Schema differ in their requirements for DB instance reboots:

Performance Schema

To turn this feature on or off, you must reboot the DB instance.

Performance Insights

To turn this feature on or off, you don't need to reboot the DB instance.

If the Performance Schema isn't currently turned on, and you turn on Performance Insights without rebooting the DB instance, the Performance Schema won't be turned on.

Determining whether Performance Insights is managing the Performance Schema

To find out whether Performance Insights is currently managing the Performance Schema for major engine versions 5.6, 5.7, and 8.0, review the following table.

Setting of performance_schema parameter Setting of the Source column Performance Insights is managing the Performance Schema?
0 system Yes
0 or 1 user No

To determine whether Performance Insights is managing the Performance Schema automatically

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Choose Parameter groups.

  3. Select the name of the parameter group for your DB instance.

  4. Enter performance_schema in the search bar.

  5. Check whether Source is the system default and Values is 0. If so, Performance Insights is managing the Performance Schema automatically. If not, Performance Insights isn't managing the Performance Schema automatically.

    
						Shows the settings for the performance_schema parameter

Configuring the Performance Schema for automatic management

Assume that Performance Insights is turned on for your DB instance but isn't currently managing the Performance Schema. If you want to allow Performance Insights to manage the Performance Schema automatically, complete the following steps.

To configure the Performance Schema for automatic management

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Choose Parameter groups.

  3. Select the name of the parameter group for your DB instance.

  4. Enter performance_schema in the search bar.

  5. Select the performance_schema parameter.

  6. Choose Edit parameters.

  7. Select the performance_schema parameter.

  8. In Values, choose 0.

  9. Choose Reset and then Reset parameters.

  10. Reboot the DB instance.

    Important

    Whenever you turn the Performance Schema on or off, make sure to reboot the DB instance.

For more information about modifying instance parameters, see Modifying parameters in a DB parameter group. For more information about the dashboard, see Analyzing metrics with the Performance Insights dashboard. For more information about the MySQL performance schema, see MySQL 8.0 Reference Manual.