12.9.3 collectSlowQueryDiagnostics Utility

About the Utility

util.debug.collectSlowQueryDiagnostics() runs multiple iterations of diagnostic reporting on your MySQL server, enabling you to analyze multiple aspects of your server while a specified query is processed.

The diagnostic report is generated as a zip file to either the local directory or a specified path.

The utility generates files in both TSV and YAML format.

Requirements and Restrictions

The following requirements apply to exports using the diagnostics collection utility:

MySQL 5.7 or later is required.
The utility must be run as root.

Running the Utility

The diagnostics utility has the following signature:

      
      util.debug.collectSlowQueryDiagnostics("path", "query", {options})

Note

The data returned by this utility also includes the default data collected by util.debug.collectDiagnostics() and util.debug.collectHighLoadDiagnostics().

"path": the location the diagnostics archive is written to. If empty, it is written to the current directory.
"query": the SQL query to analyze.
"options": dictionary of optional arguments. See Options for Collecting Diagnostics.

In addition to the contents of the util.debug.collectHighLoadDiagnostics() diagnostics, util.debug.collectSlowQueryDiagnostics collects the following information:

The EXPLAIN output of the query.
The Optimizer trace of the query.
DDL of the tables used in the query.
Warnings generated by the query.

Options for Collecting Diagnostics

delay: number

Number of seconds to wait between iterations of data collection. Default is 30. Data is collected only as long as the defined query runs. When the query is complete, the data collection stops.

innodbMutex: true | false

If true, also collects the output of SHOW ENGINE INNODB MUTEX. This command is disabled by default, as it can have an impact on production performance.

pfsInstrumentation: [current | medium | full]

Defines which Performance Schema instruments and consumers are used. Possible values are:

current: Default. The currently enabled Performance Schema instruments and consumers. No changes are made to your server's configuration.
medium: Enables all consumers except %history and %history_long, and all instruments except wait/synch/%.
full: Enables all consumers and all instruments.

customSql: array

One or more SQL statements to run. You can control when the statements are run with the following prefixes:

BEFORE, or nothing: Default. The custom SQL is run once, before the metrics collection iterations begin.
AFTER: The custom SQL is run once, after the metrics collection iterations complete.
DURING: The custom SQL is run once for each iteration of the metrics collection.

For example:

            {"customSql": ["statement1", "statement2", "statement3"]}

customShell: array

One or more shell (DOS, BOURNE, and so on) commands to run. You can control when the commands are run with the following prefixes:

BEFORE: Default. The command(s) run once, before the metrics collection iterations begin.
DURING: The command(s) run once for each iteration of the metrics collection.
AFTER: The command(s) run once, after the metrics collection iterations complete.

For example:

            {"customShell": ["command1", "command2", "command3"]}

Important

These commands run with the privileges of the user running MySQL Shell and should be used with caution.