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
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 gran execution permission for the dynatrace
user
GRANT EXECUTE ON PROCEDURE dynatrace.dynatrace_execution_plan TO 'dynatrace'@'%';