Configuring MySQL Performance Investigator (PI)

MySQL PI in Foglight serves the primary function of providing comprehensive monitoring and performance analysis capabilities for MySQL database environments. It continuously monitors various performance metrics of MySQL databases in real-time. It collects data on metrics such as waits statistics, memory utilization, disk I/O, query execution times, and more. For MySQL PI to function properly, configurations on monitored MySQL instances are necessary.

This section covers the following key areas:

• Pre-requisites for configuring MySQL in Foglight
  
• Pre-requisites for configuring MySQL PI on AWS RDS
  • Making Permanent Changes Using AWS CLI Commands
  • Making In-Memory Changes (for In-Memory Only Parameters)
• Enabling MySQL PI for an Agent

Pre-requisites for configuring MySQL in Foglight

To implement configuration changes, users can modify the MySQL options file, commonly referred to as my.cnf on Unix-like systems or my.ini on Windows platforms. This involves adding or altering prerequisite parameters within the file. It is important to note that these adjustments will only become effective following a restart of the MySQL service. Once applied, the changes will persist permanently.

To enact a permanent change using the configuration file, you should copy and add the lines below directly to your MySQL configuration file. The name and location of this file depends on the operating system used:

• Linux – Locate the my.cnf file in /etc/my.cnf, /usr/my.cnf or the default installation directory.
• Windows – Locate the my.ini or my.cnf file, typically found in either 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 default for performance_schema_max_digest_length is 200 on MySQL version 5.6.

#Make sure the following Performance schema consumers are set
[mysql]
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-statements-digest=ON
performance-schema-consumer-events-statements-history-long=ON
 
#Make sure the following Performance schema instruments are on
[mysql]
performance_schema=ON
performance_schema_instrument='statement/%=on'
performance_schema_instrument='wait/%=on'```
 
#Make sure the following server variables are configured more than a default (which is too low)
#To gather more digests (Nomalized) 
[mysql]
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
 
#To see larger digest text (Normalized)
[mysql]
max_digest_length=1024
performance_schema_max_digest_length=1024  
 
#To see larger SQL text
[mysql]
performance_schema_max_sql_text_length=1024

Pre-requisites for configuring MySQL PI on AWS RDS

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

• Parameters that can be changed permanently by modifying parameters in the AWS DB Parameter Group using the AWS Management Console (UI) or AWS CLI commands. These changes will take effect only after the MySQL RDS instance is rebooted and will be permanent. For more information how to work with AWS DB Parameter Group refer to the AWS documentation.
• Parameters that can be changed temporarily by updating performance schema tables in memory. After the MySQL RDS instance reboots, these parameters will revert to their default values.

Making Permanent Changes Using AWS CLI Commands

The commands below should be copied and executed using the AWS CLI utility. These changes will take effect only after the MySQL RDS instance is rebooted.

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

Making In-Memory Changes (for In-Memory Only Parameters)

The following lines should also be copied and executed on each monitored MySQL instance. Please 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 releases 6.0.1.10 and higher of the MySQL PI cartridge, the foglight account being used by the MySQL PI agent requires the CREATE TEMPORARY TABLES grant.

MySQL Configuration Scripts

Following are the eight scripts that the cartridge uses to run in the background with the account configured in the MySQL PI agent to check the prerequisite requirements. You need to run the script corresponding to the MySQL or MariaDB version level and platform.

• CheckPrereq_RDS_MySql5_7
• CheckPrereq_RDS_MySql5_6
• CheckPrereq_OnPrem_MySql5_7
• CheckPrereq_OnPrem_MySql5_6
• Check_Prereq_RDS_MariaDB_10_5
• Check_Prereq_RDS_MariaDB_10_0
• Check_Prereq_MariaDB_10_5
• Check_Prereq_MariaDB_10_0

Enabling MySQL PI for an Agent

MySQL PI helps to optimize the performance, reliability, and scalability of MySQL databases. To enable MySQL PI for an agent:

1. Click Databases in the left navigation pane.
2. On the Databases page, click on the MySQL tile. The page displays a list of agent names along with their SQL PI activation status under the Monitoring Agent column.
3. Click Enable PI for the agent you want to activate PI. The Administration page for SQL Performance Investigator appears.
4. Click the checkbox against the agent name, and then click Enable. Once all prerequisites are fulfilled, MySQL PI is enabled for the selected agent.

For more information about the requirements and procedures required to implement Performance Investigation with MySQL, refer to the Getting Started with MySQL PI guide.