MySQL (remote monitoring)

Remotely monitor MySQL databases where you cannot install an OneAgent.

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'@'%';

• MySQL >= 5.7

Why don't I see Waits metrics?

To gather wait metrics, data collection need to be enabled for the MySQL instance, please refer to the documentation: https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-wait-tables.html

Can I monitor MariaDB with this extension?

This extension was only tested against MySQL instances, MariaDB is not officially supported via this extension.

I get an error "RSA public key is not available client side"

This means that the username is not identified by a native password, please refer to Get Started.

I can't see CPU and Memory metrics

Make sure that data collection is enabled for your instance.

• For CPU: SET GLOBAL innodb_monitor_enable='cpu%';
• For Memory: Visit https://dev.mysql.com/doc/refman/8.0/en/monitor-mysql-memory-use.html

You can test that you can query the data by running:

SELECT * FROM information_schema.INNODB_METRICS WHERE name IN ('cpu_utime_abs', 'cpu_stime_abs');

And:

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
                SUM(current_alloc) AS current_alloc
          FROM sys.x$memory_global_by_current_bytes
          GROUP BY SUBSTRING_INDEX(event_name,'/',2)
          ORDER BY SUM(current_alloc) DESC;

I can't see slow queries

Refer to the Collecting Top Slow Queries in the Getting Started session.