Configuring MySQL for Performance Investigator (PI) (4309267)

For MySQL PI to function properly, there are configurations required on monitored MySQL instances.

MySQL (On premises)

The configuration changes can be done by changing the MySQL options file (known as my.cnf or my.ini) to add or change prerequisite parameters. These changes will take effect only after the MySQL service restarts and will be permanent.

To make a permanent change using the config file

The lines below should be copied and added directly to your MySQL config file. This name and location of this file depends on the operating system used:

• • Linux – find the my.cnf file in /etc/my.cnf, /usr/my.cnf or the default installation directory.
  • Windows – find the my.ini or my.cnf file, usually in the Windows directory or the MySQL installation directory.

These changes should be added in the MySQL options file that is initialized during the MySQL server startup (known as my.cnf or my.ini). The lines below are in the format for this file.

Note: The default for performance_schema_max_digest_length is 200 on MySQL 5.6.

MySQL (on AWS RDS)

When installing MySQL on AWS RDS, there are two types of performance schema parameters: Both of these should be done in MySQL instances. 

1. Parameters that can be changed permanently by modifying parameters in the AWS DB Parameter Group using the AWS console (UI) or AWS CLI commands. These changes will take effect only after the MySQL RDS instance reboots and will be permanent. For more information how to work with AWS DB Parameter Group refer to the AWS documentation: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Creating
2. Parameters that can be changed in memory only by updating performance schema tables. After the MySQL RDS instance reboots, these parameters will return to the default values.

To make a permanent change using AWS CLI commands

The lines below should be copied and executed via the AWS CLI utility. These changes will take effect only after the MySQL RDS instance reboots.

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

aws rds modify-db-parameter-group ^
    --db-parameter-group-name < replace with parameters 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 < replace with parameters 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 < replace with parameters 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 < replace with parameters 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 < replace with parameters group name > ^
    --parameters "ParameterName=max_digest_length,ParameterValue=1024,ApplyMethod=pending-reboot"

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

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

To make in-memory changes (for in-memory only parameters)

The lines below also should be copied and executed on each monitored MySQL instance. Note that this script should be executed after each MySQL RDS instance 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%' );

CREATE TEMPORARY TABLES grant

In the 6.0.1.10 and higher releases of the MySQL PI cartridge, the CREATE TEMPORARY TABLES grant is required for the foglight account being used by the MySQL PI agent.

MySQL Configuration Scripts

Eight scripts have been attached to this Knowledgebase Article that the cartridge uses to run in the background with the account configured in the MySQL PI agent to check the prerequisite requirements. The account should have the ability to run one of these scripts which requires the CREATE TEMPOARY TABLES grant.

Please run the script corresponding to the MySQL or MariaDB version level and platform. The scripts will be listed under "Attachments" and visible after logging into Supportlink.

NOTE
MariaDB and MySQL (PAAS) versions on Azure are presently not supported and have not been certified by R&D. Only IAAS/on-premise and AWS RDS versions are presently supported. Enhancement Request FGMYSQL-I-14 has been logged for certification of these environments with MySQL PI.

NOTE
 

The attached Getting Started with MySQL PI document provides an is intended as a summary of the requirements and procedures needed for implementing Performance Investigation with MySQL. For more detailed information, please refer to the Foglight for MySQL Cartridge Guide. You must be signed into Supportlink to access the Attachments (above)

If a MySQL 8 server is unable to restart after updating the my.ini file on Windows, refer to Knowledgebase article 333598.

NOTE: Activating the MySQL performance_schema affects the resource usage of the instance. The parameters and values suggested in this article are merely recommendations. Modifying or disabling certain parameters can help lower resource consumption. For more details, refer to the MySQL documentation. Please be aware that disabling certain parameters may result in a reduction of the relevant information available in Foglight PI.