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)
• Pre-requisites for configuring MySQL PI on Azure flexible server
  • Making Permanent Changes Using Azure 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%' );

Pre-requisites for configuring MySQL PI on Azure flexible server

When MySQL is hosted on Azure Flexible Server, there are two types of performance schema parameters. Both types should be configured at the MySQL instance level to ensure accurate performance monitoring:

• Permanent parameters – These can be modified using the Server parameters section in the Azure portal or by using Azure CLI commands. Changes to these parameters take effect only after the MySQL Azure Flexible Server instance is rebooted and will persist across restarts.

• Temporary parameters – These are modified by updating performance schema tables in memory. Changes take effect immediately but are lost when the MySQL Azure Flexible Server instance is restarted, as the parameters revert to their default values.

Making Permanent Changes Using Azure CLI Commands

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

az mysql flexible-server parameter set --name performance_schema --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name events_waits_current --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name events_waits_history --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name statements_digest --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name thread_instrumentation --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name events_statements_current --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name events_statements_history --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name events_statements_history_long --resource-group <replace with resource group name> --server-name <replace with server name> --value ON

az mysql flexible-server parameter set --name max_digest_length --resource-group <replace with resource group name> --server-name <replace with server name> --value 1024

az mysql flexible-server parameter set --name performance_schema_max_digest_length --resource-group <replace with resource group name> --server-name <replace with server name> --value 1024

az mysql flexible-server parameter set --name performance_schema_max_sql_text_length --resource-group <replace with resource group name> --server-name <replace with server name> --value 1024

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

The following lines should also be copied and executed on each monitored MySQL Azure Flexible Server instance. Please note that this script should be executed after each MySQL Azure Flexible Server 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

There are the few 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.
To download the scripts, refer to the Attachments section in the KB.

Enabling MySQL PI for an Agent

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.

Once all prerequisites are fulfilled, PostgreSQL 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 Foglight for MySQL Cartridge Guide🔗.