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.
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.
The diagnostics utility has the following signature:
util.debug.collectSlowQueryDiagnostics("path", "query", {options}) The data returned by this utility also includes the default data collected byutil.debug.collectDiagnostics() andutil.debug.collectHighLoadDiagnostics().
": the location the diagnostics archive is written to. If empty, it is written to the current directory.path"": the SQL query to analyze.query"": dictionary of optional arguments. SeeOptions for Collecting Diagnostics.options"
In addition to the contents of theutil.debug.collectHighLoadDiagnostics() diagnostics,util.debug.collectSlowQueryDiagnostics collects the following information:
The
EXPLAINoutput of the query.The Optimizer trace of the query.
DDL of the tables used in the query.
Warnings generated by the query.
- 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%historyand%history_long, and all instruments exceptwait/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"]}ImportantThese commands run with the privileges of the user running MySQL Shell and should be used with caution.