MySQL (remote monitoring)

MySQL (remote monitoring)

Remotely monitor your MySQL instances, collect key KPIs and slow queries details

Extension

Free trial Documentation

Product information
Release notes

Overview

Remotely monitor MySQL databases where you cannot install an OneAgent.

Get started

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

This applies to slow_queries feature set which retrieves data from mysql.slow_log Note: This approach will be deprecated in a future release:

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.

This applies to top_queries feature set which retrieves data from performance_schema.events_statements_summary_by_digest:

Enable the necessary statement instruments - this tells MySQL to track all statements:

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%';

Enable the consumers - this 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 RDS: While the above commands work, the most robust way to enable this on RDS is to create a custom Parameter Group. In the parameter group, set performance_schema to 1 and ensure the consumer settings are enabled. This ensures the configuration persists across restarts and instance modifications. Turn on the Performance Schema for Amazon RDS for MariaDB or MySQL - Amazon Relational Database Service

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

Compatibility information

MySQL >= 8.0

FAQ

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_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

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

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)

Subscribe to new releases

Copy to clipboard

Extension content

Content typeNumber of items included

screen chart groups

13

screen logs cards

4

screen injections

1

screen layout

3

alerts

1

metric metadata

37

screen entities lists

6

document dashboard

1

list screen layout

2

generic relationship

3

screen custom cards

2

screen actions

2

dashboards

1

screen properties

3

generic type

2

screen dql table

4

Feature sets

Below is a complete list of the feature sets provided in this version. To ensure a good fit for your needs, individual feature sets can be activated and deactivated by your administrator during configuration.

Feature setsNumber of metrics included

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

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

Metric name	Metric key	Description	Unit
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.	Count
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.	Count
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.	Count
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.	Count
Current Connections	mysql.global_status.current_connections	The number of currently open connections.	Count
Threads Running	mysql.global_status.threads_running	The number of threads that are not sleeping.	Count
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	Count
Innodb Buffer Pool Pages Dirty	mysql.global_status.innodb_buffer_pool_pages_dirty	The current number of dirty pages in the InnoDB buffer pool.	Count
Innodb Buffer Pool Pages Free	mysql.global_status.innodb_buffer_pool_pages_free	The number of free pages in the InnoDB buffer pool.	Count
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	Count
Innodb Data Reads	mysql.global_status.innodb_data_reads.count	The total number of data reads (OS file reads).	Count
Innodb Data Writes	mysql.global_status.innodb_data_writes.count	The total number of data writes.	Count
Innodb Data Read	mysql.global_status.innodb_data_read.count	The total amount of data read from files.	Byte
Innodb Data Written	mysql.global_status.innodb_data_written.count	The total amount of data written.	Byte
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.	Count
Innodb Buffer Pool Read Requests	mysql.global_status.innodb_buffer_pool_read_requests	The number of logical read requests.	Count
Availability	mysql.global_status.availability	Whether or not a connection can be made to the database	Percent
Status	mysql.global_status.status	A status of AVAILABLE is returned if we can query the database	State
Uptime	mysql.global_status.uptime	The time in seconds that the MySQL server has been running since it was started.	Second

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

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

Full version history

To have more information on how to install the downloaded package, please follow the instructions on this page.

ReleaseDate

Full version history

Version 2.1.8

Replace cpu_utime_abs and cpu_system_abs with cpu_utime_pct and cpu_system_pct to accurately represent the average CPU usage taking in consideration the amount of CPU cores in the machine.

Full version history

Version 2.1.7

Add metadata for the "Queries" metric.

Full version history

Breaking changes/action required:

Added new top_queries feature set to collect slow query data via performance schema.
Please see "Collecting Top Slow Queries" section of Hub documentation for instructions to enable monitoring.
Existing feature set slow_queries will be deprecated in a future release.

New in this version:

Added 2 new metrics for cache hit ratio: mysql.global_status.innodb_buffer_pool_reads, mysql.global_status.innodb_buffer_pool_read_requests
Updated display name of server_version instance attribute to "DB Instance Version"
Added Gen3/Platform dashboard "MySQL Overview"
Added dt.security_context attribute to entities created by this extension

Full version history

Version 2.1.3

Added server_version as metric dimension for global_status metrics, and instance entity attribute

Full version history

Version 2.1.2

Add variables for the data collection frequency

Full version history

Version 2.1.1

Fix an issue where the wait times metrics were not correctly converted to Milliseconds

Full version history

Changes

The bytes sent visualization no longer shows negative values

Full version history

Version 2.0.6

🚀 Features

There is a now a SAME_AS relationship between a MySQL instance and its Process Group Instance.

Full version history

Fixed in this version:

Sorting SQL statements would produce duplicate results

Full version history

Version 2.0.1

Add support for execution plan fetching, please check the hub page for details
Add screen definitions for the 3rd gen platform

Note: these features require Activegate 1.293+

Full version history

Version 1.1.3

Fix an issue with the configure extension link in the overview dashboard

Full version history

Version 1.1.2

Add metrics:

mysql.global_status.availability
mysql.global_status.status

Full version history

Version 1.1.1

Fix an issue where MySQL entities were created from unrelated logs

Full version history

Version 1.1.0

Add CPU metrics
Add Memory metrics
Add Slow Queries (log events)

Full version history

Version 1.0.0

First release, instance and database metrics

Discover recent additions to Dynatrace

Problems

Analyze abnormal system behavior and performance problems detected by Davis AI.

Logs

Explore all your logs without writing a single query.

Security Investigator

Fast and precise forensics for security and logs on Grail data with DQL queries.

Business Flow

Track, analyze, and optimize your critical business processes.

Cost & Carbon Optimization

Track, analyze, and optimize your IT carbon footprint and public cloud costs.

Davis Anomaly Detection

Detect anomalies in timeseries using the Davis AI

Analyze your data

Understand your data better with deep insights and clear visualizations.

Notebooks

Create powerful, data-driven documents for custom analytics and collaboration.

Dashboards

Transform complex data into clear visualizations with custom dashboards.

Automate your processes

Turn data and answers into actions, securely, and at scale.

Workflows

Automate tasks in your IT landscape, remediate problems, and visualize processes

Jira

Create, query, comment, transition, and resolve Jira tickets within workflows.

Slack

Automate Slack messaging for security incidents, attacks, remediation, and more.

Secure your cloud application

See vulnerabilities and attacks in your environment.

Security Overview

Get a comprehensive overview of the security of your applications.

Code-Level Vulnerabilities

Detect vulnerabilities in your code in real time.

Security Posture Management

Detect, prioritize, and remediate security and compliance findings with SPM.

Threats & Exploits

Understand, triage, and investigate detection findings and alerts.

Are you looking for something different?

We have hundreds of apps, extensions, and other technologies to customize your environment

Leverage our newest innovations of Dynatrace Saas

Kick-start your app creation

Kick-start your app creation

Whether you’re a beginner or a pro, Dynatrace Developer has the tools and support you need to create incredible apps with minimal effort.

Go to Dynatrace Developer

Upgrading from Dynatrace Managed to SaaS

Upgrading from Dynatrace Managed to SaaS

Drive innovation, speed, and agility in your organization by seamlessly and securely upgrading.

Log Management and Analytics

Log Management and Analytics

Innovate faster and more efficiently with unified log management and log analytics for actionable insights and automation.