Validate MySQL settings for monitoring

Ensure that the following performance schemas are enabled in MySQL. If they are not, add the settings to the my.ini file (for windows) (Windows Path: C:\ProgramData\MySQL\MySQLx.x\my.ini) or my.cnf (for Linux) (Linux Path: etc/my.cnf or etc/mysql/my.cnf). Note that modifying these settings will require a restart of MySQL.

Connect to MySQL and execute the following queries one by one to check the variable and their values.

Query 1:

SHOW VARIABLES WHERE variable_name IN ('performance_schema')

[Screenshot 1 is not available on this page.]

If the value is incorrect, add the following statement to the end of the MySQL configuration file.

performance-schema=ON

Query 2:

SHOW VARIABLES WHERE variable_name IN ('performance_schema_events_statements_history_size','performance_schema_events_statements_history_long_size')

[Screenshot 2 is not available on this page.]

If the value is not correct, add the following statement to the end of the MySQL configuration file.

performance-schema-events-statements-history-long-size=10000
performance-schema-events-statements-history-size=1024

Query 3:

SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%'

[Screenshot 3 is not available on this page.]

If the value is incorrect, add the below statements to the end of the MySQL configuration file.

performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-statements-digest=ON

Query 4:

SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%'

[Screenshot 4 is not available on this page.]

In the query results, all rows should have the value "YES". If any values are incorrect, add the following statements to the end of the MySQL configuration file..

performance-schema-instrument='statement/%=ON'

If you make any changes to the MySQL configuration file, a restart of MySQL restart is required.