For MySQL PI to function properly, there are configurations required on monitored MySQL instances.
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.
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:
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.
When installing MySQL on AWS RDS, there are two types of performance schema parameters: Both of these should be done in MySQL instances.
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"
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%' );
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.
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center