Configuring MySQL for Performance Investigator (PI) (4309267)

This article outlines the required configuration changes for enabling MySQL Performance Schema to support Performance Investigator (PI) in Foglight. These changes apply to on-premises MySQL instances and AWS RDS deployments.

The MySQL Performance Schema is not fully enabled or not configured with sufficient instrumentation and memory settings required for Foglight PI to collect and analyze SQL performance data. Without these configurations, PI cannot capture digest-level and wait-event data necessary for deep-dive performance investigation.

MySQL (On-Premises)

For on-premises MySQL installations, configuration changes should be made in the MySQL configuration file (my.cnf on Linux or my.ini on Windows). These changes take effect only after the MySQL service is restarted.

1. Locate the MySQL Configuration File

• Linux: /etc/my.cnf, /usr/my.cnf, or in the MySQL installation directory
• Windows: my.ini or my.cnf in the MySQL installation directory or Windows directory

2. Add the Following Parameters to the Configuration File

Add the following under the [mysql] section:

[mysql]
performance_schema=ON

# Enable Consumers
performance-schema-consumer-events-waits-current=ON
performance-schema-consumer-events-waits-history=ON
performance-schema-consumer-statements-digest=ON
performance-schema-consumer-thread-instrumentation=ON
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON

# Enable Instruments
performance_schema_instrument='statement/%=on'
performance_schema_instrument='wait/%=on'

# Increase Limits
performance_schema_digests_size=10000
performance_schema_events_waits_history_size=100
performance_schema_events_waits_history_long_size=10000
performance_schema_events_statements_history_size=1000
performance_schema_events_statements_history_long_size=20000
max_digest_length=1024
performance_schema_max_digest_length=1024
performance_schema_max_sql_text_length=1024

Note: On MySQL 5.6, the default value for performance_schema_max_digest_length is 200.

MySQL (AWS RDS)

On AWS RDS, configuration must be applied using DB Parameter Groups. These changes are persistent but require a reboot of the MySQL RDS instance.

1. Permanent Changes Using AWS CLI

Replace <parameter-group-name> with the actual DB parameter group name. These commands must be executed via the AWS CLI:

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema,ParameterValue=1,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_digests_size,ParameterValue=10000,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_events_waits_history_size,ParameterValue=100,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_events_waits_history_long_size,ParameterValue=10000,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_events_statements_history_size,ParameterValue=1000,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_events_statements_history_long_size,ParameterValue=20000,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=max_digest_length,ParameterValue=1024,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_max_digest_length,ParameterValue=1024,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group ^
    --db-parameter-group-name <parameter-group-name> ^
    --parameters "ParameterName=performance_schema_max_sql_text_length,ParameterValue=1024,ApplyMethod=pending-reboot"

For more information, refer to the AWS documentation on Parameter Groups.

2. In-Memory Changes (Re-applied After Reboot)

Execute the following SQL on each monitored instance after every reboot:

UPDATE performance_schema.setup_consumers 
SET enabled = 'YES'
WHERE NAME IN (
    'events_waits_current',
    'events_waits_history',
    'statements_digest',
    'thread_instrumentation',
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long');

UPDATE performance_schema.setup_instruments 
SET enabled = 'YES', timed = 'YES' 
WHERE NAME LIKE 'wait%';

UPDATE performance_schema.setup_instruments 
SET enabled = 'YES', timed = 'YES' 
WHERE NAME LIKE 'statement%';

Grant Requirements for PI Script Execution

Starting with MySQL PI cartridge version 6.0.1.10 and later, the CREATE TEMPORARY TABLES privilege is required for the Foglight user.

None prerequisite check scripts are attached to this article. Use the script matching your MySQL or MariaDB version and platform. These scripts are visible under the Attachments section after logging into SupportLink.

• See the attached Getting Started with MySQL PI document for a summarized setup guide.
• For advanced configuration details, refer to the Foglight for MySQL Cartridge Guide.
• If a MySQL 8 server fails to restart after modifying my.ini on Windows, refer to KB 4226566.

Important Notes

• Enabling the Performance Schema increases resource usage. Tune the settings to fit your performance and memory goals.
• Foglight PI currently supports on-premises/IAAS MySQL and AWS RDS. Azure PaaS is not supported.
• Enhancement Request FGMYSQL-I-14 has been submitted to add support for Azure environments.