Activating the extension
- Activate Extension in the Hub by going to:
- Extensions → MySQL → Add to environment
- Add a new monitoring configuration for every MySQL instance you'd like to monitor
Creating a MySQL user
Create a user that is identified by a native password, customize the username and password as you please
CREATE USER 'dynatrace'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Give the user the permissions:
GRANT SELECT ON performance_schema.* TO 'dynatrace'@'%';
- Allows the user to query the performance_schema schema
GRANT PROCESS ON *.* TO 'dynatrace'@'%';
- Allows the user to see thread and connection metrics for other users
GRANT SHOW DATABASES ON *.* TO 'dynatrace'@'%';
- Allows the user to see database metrics for all databases
GRANT SELECT ON mysql.slow_log TO 'dynatrace'@'%';
- Allows the user to query slow queries
GRANT SELECT ON sys.x$memory_global_by_current_bytes TO 'dynatrace'@'%';
- Allow the user to query memory statistics
NOTE: Due to a MySQL limitation, to calculate database sizes you MUST grant SELECT permissions on the individual databases where you want to collect size from.
Collecting Infrastructure metrics
To enable CPU metrics collection, run this query on the MySQL instance:
SET GLOBAL innodb_monitor_enable='cpu%';
Collecting Top Slow Queries
Enable slow queries logging to a table:
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
The default slow query threshold is 10 seconds
You can chose the threshold of what is a "slow query" by executing:
SET GLOBAL long_query_time = 2;
This would set slow queries threshold to 2 seconds.
Execution Plan Fetching
To fetch execution plans, you must create a stored procedure for the dynatrace user:
CREATE SCHEMA IF NOT EXISTS dynatrace;
DELIMITER $$
CREATE PROCEDURE dynatrace.dynatrace_execution_plan(IN query TEXT)
SQL SECURITY DEFINER
BEGIN
SET @explain := CONCAT('EXPLAIN FORMAT=JSON ', query);
PREPARE stmt FROM @explain;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
And then grant execution permission for the dynatrace user
GRANT EXECUTE ON PROCEDURE dynatrace.dynatrace_execution_plan TO 'dynatrace'@'%';
