Using MySQL sys.diagnostics Stored Procedure for Database Performance Diagnosis

Introduction

Oracle provides the Automatic Workload Repository (AWR) for performance diagnostics; MySQL offers a similar capability through the sys.diagnostics stored procedure, which gathers information from information_schema , performance_schema , and sys schema to produce a diagnostic report.

Basic Information Collected

Metrics view data.

Other related sys schema views, such as 95th‑percentile query statistics.

If the server is an NDB Cluster, information from the ndbinfo schema.

Master‑slave replication status.

Some sys views are presented as initial, overall, and delta snapshots, where the initial view reflects the state at the start of the procedure, the overall view reflects the final state, and the delta view shows the difference between the two.

Note: The procedure temporarily disables binary logging by changing the session value of sql_log_bin . Sufficient privileges are required to modify this restricted variable.

Command Examples

Method 1

Create a diagnostic report that runs for up to 120 seconds, taking a snapshot every 30 seconds using the current Performance Schema configuration:

CALL sys.diagnostics(120, 30, 'current');

Capture the output to a file using the MySQL client’s tee and notee commands:

tee diag.out;
CALL sys.diagnostics(120, 30, 'current');
notee;

Method 2

Run the procedure from the shell and redirect the result to an HTML file:

mysql -uroot -p -S /opt/mysql/data/6666/mysqld.sock -H -e "CALL sys.diagnostics(120, 30, 'current');" >report.html

Procedure Parameters

CALL sys.diagnostics(in_max_runtime, in_interval, 'current');

in_max_runtime (INT UNSIGNED) : Maximum collection time in seconds (default 60).

in_interval (INT UNSIGNED) : Sleep time between collections in seconds (default 30).

in_auto_config (ENUM) : Performance Schema configuration; allowed values are current , medium , and full . current : Use the current instrument and consumer settings. medium : Enable a subset of instruments and consumers. full : Enable all instruments and consumers (may impact performance).

Note: Enabling more instruments and consumers increases overhead; for most cases, current is sufficient. Using medium or full requires SUPER privileges, and the original settings are restored after the procedure finishes.

Configuration Options

The diagnostics procedure can be tuned via options stored in sys.sys_config (or the corresponding user‑defined variables).

debug (default OFF): When ON, produces debug output. UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'debug';

diagnostics.allow_i_s_tables (default OFF): Allows the procedure to scan INFORMATION_SCHEMA.TABLES , which can be expensive on many tables. UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'diagnostics.allow_i_s_tables';

diagnostics.include_raw (default OFF): Includes the raw output of the metrics view in the report. UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'diagnostics.include_raw';

statement_truncate_len (default 64): Maximum length of statements returned by format_statement() . UPDATE `sys`.`sys_config` SET `value` = '32' WHERE `variable` = 'statement_truncate_len';

Report Contents

The generated report typically includes the following sections (illustrated with screenshots in the original article):

MySQL server basic information.

Basic configuration parameters.

Performance Schema instrument and consumer settings.

Initial state of the sys schema.

Information for each iteration (depends on the chosen interval and runtime).

Schema‑level statistics.

Overall state information.

Delta state information.

Images in the original article show example tables and charts for each of these sections.

Conclusion

The sys.diagnostics procedure provides a one‑click way to collect extensive performance data from a MySQL instance, making it easier for DBAs to analyze the health and behavior of their databases.

References: sys_config documentation: https://dev.mysql.com/doc/refman/8.0/en/sys-sys-config.html Diagnostic report example (download link in original article).