Advanced configuration for MySQL monitoring with NRDOT

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

You can configure the following options to get deeper insights into your MySQL instances:

Query plans for write statements
Enable lock-wait duration tracking

Query plans for write statements

Setting explain_mode to procedure enables NRDOT Collector to collect EXPLAIN plans for write statements without granting DML privileges to the monitoring user. When configured, the receiver searches for a SQL SECURITY DEFINER procedure named <schema>.explain_statement. If that procedure doesn't exist or the monitoring user lacks execution privileges on it, the receiver falls back to inline mode. In this state, write statements fail to collect query plans, and the collector log displays the following warning: unable to execute explain statement.

To set the explain_mode to procedure, run the following as a privileged account (for example, root) once per schema:

DELIMITER //
CREATE DEFINER = 'root'@'%' PROCEDURE <YOUR_SCHEMA>.explain_statement(IN q TEXT)
SQL SECURITY DEFINER
BEGIN
  SET @nr_explain_sql = CONCAT('EXPLAIN FORMAT=JSON ', q);
  PREPARE nr_explain_stmt FROM @nr_explain_sql;
  EXECUTE nr_explain_stmt;
  DEALLOCATE PREPARE nr_explain_stmt;
END//
DELIMITER ;
GRANT EXECUTE ON PROCEDURE <YOUR_SCHEMA>.explain_statement TO '<YOUR_DB_USERNAME>'@'%';

The GRANT EXECUTE step is required for more than executing the CALL statement. Without this privilege, the monitoring user cannot view the procedure in information_schema.ROUTINES, which the receiver uses to verify existence. Omitting GRANT EXECUTE produces the same result as a missing procedure, causing the receiver to fall back to inline mode in both cases.

Tip

This configuration is stateful per schema. Creating or restoring a database, or dropping a procedure, silently disables write-statement plan collection for that schema and triggers a fallback to inline mode. The SQL SECURITY DEFINER stored routines apply the definer's privileges to dynamic SQL executed through PREPARE/EXECUTE statements within them. This allows a SELECT-only monitoring user to retrieve plans for statements for which it lacks direct execution privileges.

Enable lock-wait duration tracking

The events_waits_current consumer provides lock-wait duration information but requires platform-specific configuration:

Platform	Method	Persistent across restarts?
Self-managed MySQL	`performance-schema-consumer-events-waits-current=ON` in `[mysqld]` (`my.cnf`)	Yes
AWS RDS	`UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_waits_current';`	No: Resets on restart/failover. Re-run after each restart, or grant the receiver user this `UPDATE` so it can re-enable the consumer automatically on reconnect.

Compatibility and prerequisites

Learn about supported MySQL versions, network requirements, and recommended server parameters.

Instrumentation in self-hosted environments

Learn how to set up MySQL monitoring in self-hosted environments with New Relic.

Instrumentation in RDS environments

Learn how to set up MySQL monitoring in RDS environments with New Relic.