MySQL (remote monitoring) extension

Latest Dynatrace
Extension

Monitor your MySQL instances remotely, collect key KPIs, and slow query details.

Get started

Overview

Remotely monitor MySQL databases where you can't install OneAgent.

Requirements

Designate an ActiveGate group or groups that will remotely connect to your MySQL Database server to pull data. All ActiveGates in each group must connect to your MySQL database.
Create a dedicated database user in your database instance with necessary permissions. Dynatrace uses this user to run monitoring queries against your MySQL database.
- Configure a user with permissions
  1. Create a user that is identified by a native password, and customize the username and password.
    
    CREATE USER 'dynatrace'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
  2. Give the user the following 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'@'%';
    Allows the user to query memory statistics.
  Due to a MySQL limitation, to calculate database sizes, you need to grant SELECT permissions on the individual databases from which you want to collect the size.

Compatibility information

MySQL version 8.0 or higher is required.

Activation and setup

Add DB instance

Go to Dynatrace Hub .
Select and install MySQL extension.
Select Add DB Instance in the Databases. This opens the Add DB Instance wizard.
Select MySQL section in the wizard.

Select hosting type

Select a hosting type from the options. This choice determines which script generates the necessary database objects later in the process.

Select the host type that matches your requirement.
Select Next.

Select ActiveGate group

Select the ActiveGate group to determine which ActiveGates will run the extension.
Select Next.

Create a connection

Set up the connection to your database instance. Provide the required credentials directly in the wizard or use secure alternatives:

Name the connection, so you can identify it later.
Add the details in the Configure connection section.
1. Select connection. Use Select from existing hosts or Enter manually to add connection details.
2. Add Database name
Provide the Authenticate credentials for the dynatrace monitoring user you have created directly or use secure alternatives.
- Basic credentials: Authentication details passed to Dynatrace when activating monitoring configuration are masked to prevent them from being retrieved.
- Credential vault: Use vault credentials to securely store and retrieve database credentials.
Select Next.

Install instance

Add manual configurations based on the monitoring requirements.
- Collect infrastructure metrics
  
  To enable CPU metrics collection, run this query in the MySQL instance:
```
SET GLOBAL innodb_monitor_enable='cpu%';
```
- Collect memory metrics
  
  To enable memory metrics collection, run this query in the MySQL instance:
```
SET GLOBAL innodb_monitor_enable='memory%';
```
- Collect table statistics
  
  To enable table statistics collection, run this query in the MySQL instance:
```
SET GLOBAL innodb_monitor_enable='table%';
```
- Collect top slow queries
  
  The following steps apply to the slow_queries feature set, which retrieves data from mysql.slow_log.
  
  This approach will be deprecated in a future release.
  
  Enable slow queries logging to a table by executing:
```
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
```
  The default slow query threshold is 10 seconds.
  
  Optionally, choose the threshold of what is a "slow query" by executing:
```
SET GLOBAL long_query_time = 2;
```
  This sets the slow queries threshold to 2 seconds.
  - The following steps apply to the top_queries feature set, which retrieves data from performance_schema.events_statements_summary_by_digest.
    
    Enable the necessary statement instruments, which tell MySQL to track all statements:
    
    UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE 'statement/%';
    
    Enable the consumers, which tells MySQL where to store the tracked statements:
    
    UPDATE performance_schema.setup_consumers
    SET ENABLED = 'YES'
    WHERE NAME IN ('events_statements_history', 'events_statements_summary_by_digest');
    
    Note for AWS Relational Database Service (RDS)
    The recommended way to enable this on RDS is to create a custom parameter group. In the group, set performance_schema to 1 and enable the required consumer settings. This configuration persists across restarts and instance changes. For more details, see Turn on the Performance Schema for Amazon RDS for MariaDB or MySQL.
- Fetch execution plans
  
  To fetch execution plans:
  1. 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 ;
```
  1. Grant execution permission for the dynatrace user:
```
GRANT EXECUTE ON PROCEDURE dynatrace.dynatrace_execution_plan TO 'dynatrace'@'%';
```
Select Create DB instance monitoring.

Feature sets

When activating your extension using monitoring configuration, you can limit monitoring to one of the feature sets. To work properly, the extension has to collect at least one metric after the activation.

In highly segmented networks, feature sets can reflect the segments of your environment. Then, when you create a monitoring configuration, you can select a feature set and a corresponding ActiveGate group that can connect to this particular segment.

All metrics that aren't categorized into any feature set are considered to be the default and are always reported.

A metric inherits the feature set of a subgroup, which in turn inherits the feature set of a group. Also, the feature set defined on the metric level overrides the feature set defined on the subgroup level, which in turn overrides the feature set defined on the group level.

waits

Metric name	Metric key	Description
Waits IO	mysql.waits.waits_io.count	The number of times that the server has waited for an I/O operation to complete.
Waits IO Time	mysql.waits.waits_io_time.count	The total time the server has waited for I/O operations to complete.
Waits Sync	mysql.waits.waits_sync.count	The number of times that the server has waited for a synchronization operation to complete.
Waits Sync Time	mysql.waits.waits_sync_time.count	The total time the server has waited for synchronization operations to complete.

slowQueries

Metric name	Metric key	Description

statements

Metric name	Metric key	Description
Commits	mysql.statements.commit.count	The number of COMMIT statements executed by this server
Deletes	mysql.statements.delete.count	The number of DELETE statements executed by this server
Multi-Table Deletes	mysql.statements.delete_multi.count	The number of multi-table DELETE statements executed by this server
Inserts	mysql.statements.insert.count	The number of INSERT statements executed by this server
Selects	mysql.statements.select.count	The number of SELECT statements executed by this server
Updates	mysql.statements.update.count	The number of UPDATE statements executed by this server
Executes	mysql.statements.execute.count	The number of EXECUTE statements executed by this server
Multi-Table Updates	mysql.statements.update_multi.count	The number of multi-table UPDATE statements executed by this server
Bytes Sent	mysql.statements.bytes_sent.count	The number of bytes sent to all clients
Bytes Received	mysql.statements.bytes_received.count	The number of bytes received from all clients

databases

Metric name	Metric key	Description
Database Size	mysql.databases.size	The size of the Database

topQueries

Metric name	Metric key	Description

infrastructure

Metric name	Metric key	Description
CPU User	mysql.infrastructure.cpu.user	The percentage of CPU time spent in user mode.
CPU System	mysql.infrastructure.cpu.system	The percentage of CPU time spent in system mode.
Memory	mysql.infrastructure.memory	The amount of memory used per each code area.

globalStatus

Metric name	Metric key	Description
Queries	mysql.global_status.queries.count	The total number of queries executed by the server. This includes statements executed within stored procedures, unlike the Questions variable.
Slow Queries	mysql.global_status.slow_queries.count	The number of queries that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled. For information about that log, see Section 5.4.5, The Slow Query Log.
Table Locks Waited	mysql.global_status.table_locks_waited.count	The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
Table Locks Immediate	mysql.global_status.table_locks_immediate.count	The number of times that a request for a table lock could be granted immediately.
Current Connections	mysql.global_status.current_connections	The number of currently open connections.
Threads Running	mysql.global_status.threads_running	The number of threads that are not sleeping.
Innodb Buffer Pool Pages Data	mysql.global_status.innodb_buffer_pool_pages_data	The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages. When using compressed tables, the reported Innodb_buffer_pool_pages_data value may be larger than Innodb_buffer_pool_pages_total
Innodb Buffer Pool Pages Dirty	mysql.global_status.innodb_buffer_pool_pages_dirty	The current number of dirty pages in the InnoDB buffer pool.
Innodb Buffer Pool Pages Free	mysql.global_status.innodb_buffer_pool_pages_free	The number of free pages in the InnoDB buffer pool.
Innodb Buffer Pool Pages Total	mysql.global_status.innodb_buffer_pool_pages_total	The total size of the InnoDB buffer pool, in pages. When using compressed tables, the reported Innodb_buffer_pool_pages_data value may be larger than Innodb_buffer_pool_pages_total
Innodb Data Reads	mysql.global_status.innodb_data_reads.count	The total number of data reads (OS file reads).
Innodb Data Writes	mysql.global_status.innodb_data_writes.count	The total number of data writes.
Innodb Data Read	mysql.global_status.innodb_data_read.count	The total amount of data read from files.
Innodb Data Written	mysql.global_status.innodb_data_written.count	The total amount of data written.
Innodb Buffer Pool Reads	mysql.global_status.innodb_buffer_pool_reads	The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
Innodb Buffer Pool Read Requests	mysql.global_status.innodb_buffer_pool_read_requests	The number of logical read requests.
Availability	mysql.global_status.availability	Whether or not a connection can be made to the database
Status	mysql.global_status.status	A status of AVAILABLE is returned if we can query the database
Uptime	mysql.global_status.uptime	The time in seconds that the MySQL server has been running since it was started.

FAQ

Why don't I see wait metrics?

Data collection must be allowed for the MySQL instance to gather wait metrics. For more details, see Performance Schema Wait Event Tables.

Can I monitor MariaDB with this extension?

This extension was tested only with MySQL instances. MariaDB is not officially supported.

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

This error means the username is not identified by a native password. For more details, see the Activation and setup section.

I can't see CPU and Memory metrics.

Ensure that data collection is enabled for your instance.

For CPU: SET GLOBAL innodb_monitor_enable='cpu%';.
For Memory: Visit Monitoring MySQL Memory Usage.

To test that you can query the data, run:

SELECT * FROM information_schema.INNODB_METRICS WHERE name IN ('cpu_utime_pct', 'cpu_stime_pct');

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.

See the Collect top slow queries section in Add monitoring configurations.

How can I view top queries in a Managed environment?

On Managed tenants, log records can be retrieved by filtering logs using 2 attributes:

dt.extension.name: com.dynatrace.extension.mysql.
event.group: top_queries (versions 2.1.6+) or event.group: query_performance (versions <2.1.6).

Explore in Dynatrace Hub

Monitor your MySQL instances remotely, collect key KPIs, and slow query details.