MySQL Shellの診断ユーティリティをつかってみた

MySQL Shellには、いくつかのユーティリティが含まれていますが、本記事では、その中の診断ユーティリティに関して記載しています。

公式のドキュメントでは、以下になります。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-diagnostics.html
日本語版が作成されたタイミングではまだ診断ツールはまだリリースされていなかったため、英語版のドキュメントだけになっています。

現時点の機能としては、以下の３つがあります。

• collectDiagnostics()
  • MySQL Shell 8.0.29から追加されました
• collectHighLoadDiagnostics()
  • MySQL Shell 8.0.31から追加されました
• collectSlowQueryDiagnostics()
  • MySQL Shell 8.0.31から追加されました

本記事では、MySQL Shell 8.0.31を使って動作確認を行っています。

collectDiagnostics()をつかってみた

collectDiagnostics()は、MySQL Shell 8.0.29から追加された機能です。
指定したインスタンス(MySQL 5.7以降であればOK)の診断情報を取得し、TSVとYAML形式の2つの形式で出力しています。

早速つかってみます。
MySQL Shellを起動して、対象のインスタンスにログインします。

shell > ./mysql-shell-8.0.31-linux-glibc2.12-x86-64bit/bin/mysqlsh 
JS > \c root@localhost:8031

util.debug.collectDiagnostics()コマンドで実行します。
指定したディレクトリ配下（以下の例では、"/MYSQL/"）に、mysql-diagnostics-<取得時間情報>.zip といったファイル名で生成されます。
どのような情報を取得しているのかは、以下の実行ログをみてもらえればわかると思います。

JS > util.debug.collectDiagnostics("/MYSQL/")
Collecting diagnostics information from mysql://root@localhost:8031...
Copying shell log file...
 - Gathering schema tables without a PK...
 - Gathering schema routine size...
 - Gathering schema table count...
 - Gathering schema unused indexes...
 - Copying MySQL error log file (/MYSQL/msb_8_0_31/data/msandbox.err)
 - Gathering performance_schema.host_cache...
 - Gathering performance_schema.persisted_variables...
 - Gathering performance_schema.replication_applier_configuration...
 - Gathering performance_schema.replication_applier_filters...
 - Gathering performance_schema.replication_applier_global_filters...
 - Gathering performance_schema.replication_applier_status...
 - Gathering performance_schema.replication_applier_status_by_coordinator...
 - Gathering performance_schema.replication_applier_status_by_worker...
 - Gathering performance_schema.replication_asynchronous_connection_failover...
 - Gathering performance_schema.replication_asynchronous_connection_failover_managed...
 - Gathering performance_schema.replication_connection_configuration...
 - Gathering performance_schema.replication_connection_status...
 - Gathering performance_schema.replication_group_member_stats...
 - Gathering performance_schema.replication_group_members...
 - Gathering mysql.audit_log_filter...
 - Gathering mysql.audit_log_user...
 - Gathering global variables...
 - Gathering XA RECOVER CONVERT xid...
 - Gathering SHOW BINARY LOGS...
 - Gathering SHOW REPLICAS...
 - Gathering SHOW MASTER STATUS...
 - Gathering SHOW REPLICA STATUS...
 - Gathering replication master_info...
 - Gathering replication relay_log_info...
 - Gathering pfs actors...
 - Gathering pfs objects...
 - Gathering pfs consumers...
 - Gathering pfs instruments...
 - Gathering pfs threads...
 - Gathering performance_schema.metadata_locks...
 - Gathering performance_schema.threads...
 - Gathering sys.schema_table_lock_waits...
 - Gathering sys.session_ssl_status...
 - Gathering sys.session...
 - Gathering sys.processlist...
 - Gathering performance_schema.events_waits_current...
 - Gathering information_schema.innodb_trx...
 - Gathering information_schema.innodb_metrics...
 - Gathering sys.memory_by_host_by_current_bytes...
 - Gathering sys.memory_by_thread_by_current_bytes...
 - Gathering sys.memory_by_user_by_current_bytes...
 - Gathering sys.memory_global_by_current_bytes...
 - Gathering SHOW GLOBAL STATUS...
 - Gathering SHOW ENGINE INNODB STATUS...
 - Gathering SHOW ENGINE PERFORMANCE_SCHEMA STATUS...
 - Gathering SHOW FULL PROCESSLIST...
 - Gathering SHOW OPEN TABLES...
Collecting system information for rhel8 (linux)
 -> Executing date
 -> Executing uname -a
 -> Executing getenforce
 -> Executing free -m
 -> Executing swapon -s
 -> Executing lsb_release -a
 -> Executing mount -v
 -> Executing df -h
 -> Executing cat /proc/cpuinfo
 -> Executing cat /proc/meminfo
 -> Executing cat /etc/fstab
 -> Executing mpstat -P ALL 1 4
 -> Executing iostat -m -x 1 4
 -> Executing vmstat 1 4
 -> Executing top -b -n 4 -d 1
 -> Executing ps aux
 -> Executing ulimit -a
 -> Executing for PID in `pidof mysqld`;do echo "# numastat -p $PID";numastat -p $PID;echo "# /proc/$PID/limits";cat /proc/$PID/limits;echo;done
 -> Executing dmesg
 -> Executing egrep -i 'err|fault|mysql' /var/log/*
 -> Executing pvs
 -> Executing pvdisplay
 -> Executing vgs
 -> Executing vgdisplay
 -> Executing lvs
 -> Executing lvdisplay
 -> Executing netstat -lnput
 -> Executing numactl --hardware
 -> Executing numastat -m
 -> Executing sysctl -a
 -> Executing dmidecode -s system-product-name
 -> Executing lsblk -i
 -> Executing sudo sosreport

Diagnostics information was written to /MYSQL/mysql-diagnostics-20221212-033200.zip

出力したファイル内は、以下のようなファイルが格納されています。(yamlファイルは表示上除いています)

shell > unzip mysql-diagnostics-20221212-033200.zip
shell > ls | grep -v yaml
0.error_log
0.global_variables.tsv
0.information_schema.innodb_metrics.tsv
0.information_schema.innodb_trx.tsv
0.instance
0.metrics.tsv
0.mysql.audit_log_filter.tsv
0.mysql.audit_log_user.tsv
0.performance_schema.events_waits_current.tsv
0.performance_schema.host_cache.tsv
0.performance_schema.metadata_locks.tsv
0.performance_schema.persisted_variables.tsv
0.performance_schema.replication_applier_configuration.tsv
0.performance_schema.replication_applier_filters.tsv
0.performance_schema.replication_applier_global_filters.tsv
0.performance_schema.replication_applier_status_by_coordinator.tsv
0.performance_schema.replication_applier_status_by_worker.tsv
0.performance_schema.replication_applier_status.tsv
0.performance_schema.replication_asynchronous_connection_failover_managed.tsv
0.performance_schema.replication_asynchronous_connection_failover.tsv
0.performance_schema.replication_connection_configuration.tsv
0.performance_schema.replication_connection_status.tsv
0.performance_schema.replication_group_member_stats.tsv
0.performance_schema.replication_group_members.tsv
0.performance_schema.threads.tsv
0.pfs_actors.tsv
0.pfs_consumers.tsv
0.pfs_instruments.tsv
0.pfs_objects.tsv
0.pfs_threads.tsv
0.replication_master_info.tsv
0.replication_relay_log_info.tsv
0.SHOW_BINARY_LOGS.tsv
0.SHOW_ENGINE_INNODB_STATUS.tsv
0.SHOW_ENGINE_PERFORMANCE_SCHEMA_STATUS.tsv
0.SHOW_FULL_PROCESSLIST.tsv
0.SHOW_GLOBAL_STATUS.tsv
0.SHOW_MASTER_STATUS.tsv
0.SHOW_OPEN_TABLES.tsv
0.SHOW_REPLICA_STATUS.tsv
0.SHOW_REPLICAS.tsv
0.sys.memory_by_host_by_current_bytes.tsv
0.sys.memory_by_thread_by_current_bytes.tsv
0.sys.memory_by_user_by_current_bytes.tsv
0.sys.memory_global_by_current_bytes.tsv
0.sys.processlist.tsv
0.sys.schema_table_lock_waits.tsv
0.sys.session_ssl_status.tsv
0.sys.session.tsv
0.uri
0.XA_RECOVER_CONVERT_xid.tsv
host_info
mysqlsh.log
schema_routine_size.tsv
schema_table_count.tsv
schema_tables_without_a_PK.tsv
schema_unused_indexes.tsv

util.debug.collectDiagnostics()コマンドだけで初期調査に必要な情報が一通り取得できるので、便利です。
オプションは、他にも設定できます。詳しくは、以下のページをご参照ください。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-debug-diagnostics.html

collectHighLoadDiagnostics()をつかってみた

collectHighLoadDiagnostics()は、サーバ負荷の原因調査で利用できます。MySQL Shell 8.0.31から追加されました。
指定した回数、指定した間隔で継続してデータを取得することができます。
collectDiagnostics()で取得する内容に加えて、スロークエリログや、performance_schemaやsysから多くの統計情報を取得しています。

以下は、5秒間隔で30回取得する実行例です。
こちらも、どのような情報を取得しているのかは、以下の実行ログをみてもらえればわかると思います。
取得タイミングや回数などは、それぞれの診断ユーティリティで異なりますが、collectDiagnostics()と比べて、追加取得しているものの先頭に !! マークをつけています。

 JS > util.debug.collectHighLoadDiagnostics("/MYSQL/",{iterations: 30, delay:5})
Collecting diagnostics information from mysql://root@localhost:8031...
Collecting Schema Information and Statistics
 - Gathering schema tables without a PK...
 - Gathering schema routine size...
 - Gathering schema table count...
 - Gathering schema unused indexes...
!! - Gathering schema object overview...
!! - Gathering schema top biggest tables...
!! - Gathering schema table engines...
!! - Gathering schema table info...
Executing BENCHMARK()...
 - Copying MySQL error log file (/MYSQL/msb_8_0_31/data/msandbox.err)
!! - Gathering slow queries in 95 pctile...
!! - Gathering slow queries summary by rows examined...
!! - Gathering slow_log...
Collecting system information for rhel8 (linux)
 -> Executing date
 -> Executing uname -a
 -> Executing getenforce
 -> Executing free -m
 -> Executing swapon -s
 -> Executing lsb_release -a
 -> Executing mount -v
 -> Executing df -h
 -> Executing cat /proc/cpuinfo
 -> Executing cat /proc/meminfo
 -> Executing cat /etc/fstab
 -> Executing mpstat -P ALL 1 4
 -> Executing iostat -m -x 1 4
 -> Executing vmstat 1 4
 -> Executing top -b -n 4 -d 1
 -> Executing ps aux
 -> Executing ulimit -a
 -> Executing for PID in `pidof mysqld`;do echo "# numastat -p $PID";numastat -p $PID;echo "# /proc/$PID/limits";cat /proc/$PID/limits;echo;done
 -> Executing dmesg
 -> Executing egrep -i 'err|fault|mysql' /var/log/*
 -> Executing pvs
 -> Executing pvdisplay
 -> Executing vgs
 -> Executing vgdisplay
 -> Executing lvs
 -> Executing lvdisplay
 -> Executing netstat -lnput
 -> Executing numactl --hardware
 -> Executing numastat -m
 -> Executing sysctl -a
 -> Executing dmidecode -s system-product-name
 -> Executing lsblk -i
 -> Executing sudo sosreport
 - Gathering performance_schema.host_cache...
 - Gathering performance_schema.persisted_variables...
 - Gathering performance_schema.replication_applier_configuration...
 - Gathering performance_schema.replication_applier_filters...
 - Gathering performance_schema.replication_applier_global_filters...
 - Gathering performance_schema.replication_applier_status...
 - Gathering performance_schema.replication_applier_status_by_coordinator...
 - Gathering performance_schema.replication_applier_status_by_worker...
 - Gathering performance_schema.replication_asynchronous_connection_failover...
 - Gathering performance_schema.replication_asynchronous_connection_failover_managed...
 - Gathering performance_schema.replication_connection_configuration...
 - Gathering performance_schema.replication_connection_status...
 - Gathering performance_schema.replication_group_member_stats...
 - Gathering performance_schema.replication_group_members...
 - Gathering mysql.audit_log_filter...
 - Gathering mysql.audit_log_user...
 - Gathering global variables...
 - Gathering XA RECOVER CONVERT xid...
 - Gathering SHOW BINARY LOGS...
 - Gathering SHOW REPLICAS...
 - Gathering SHOW MASTER STATUS...
 - Gathering SHOW REPLICA STATUS...
 - Gathering replication master_info...
 - Gathering replication relay_log_info...
 - Gathering pfs actors...
 - Gathering pfs objects...
 - Gathering pfs consumers...
 - Gathering pfs instruments...
 - Gathering pfs threads...
 - Gathering pfs actors...
 - Gathering pfs objects...
 - Gathering pfs consumers...
 - Gathering pfs instruments...
 - Gathering pfs threads...
!! - Gathering sys.host_summary...
!! - Gathering sys.host_summary_by_file_io...
!! - Gathering sys.host_summary_by_file_io_type...
!! - Gathering sys.host_summary_by_stages...
!! - Gathering sys.host_summary_by_statement_latency...
!! - Gathering sys.host_summary_by_statement_type...
!! - Gathering sys.io_by_thread_by_latency...
!! - Gathering sys.io_global_by_file_by_bytes...
!! - Gathering sys.io_global_by_file_by_latency...
!! - Gathering sys.io_global_by_wait_by_bytes...
!! - Gathering sys.io_global_by_wait_by_latency...
!! - Gathering sys.schema_index_statistics...
!! - Gathering sys.schema_table_statistics...
!! - Gathering sys.schema_tables_with_full_table_scans...
!! - Gathering sys.user_summary...
!! - Gathering sys.user_summary_by_file_io...
!! - Gathering sys.user_summary_by_file_io_type...
!! - Gathering sys.user_summary_by_stages...
!! - Gathering sys.user_summary_by_statement_latency...
!! - Gathering sys.user_summary_by_statement_type...
!! - Gathering sys.wait_classes_global_by_avg_latency...
!! - Gathering sys.wait_classes_global_by_latency...
!! - Gathering sys.waits_by_host_by_latency...
!! - Gathering sys.waits_by_user_by_latency...
!! - Gathering sys.waits_global_by_latency...
!! - Preparing statement performance analyzer...
Collecting performance metrics (iteration #1 of 30)...
 - Gathering performance_schema.metadata_locks...
 - Gathering performance_schema.threads...
 - Gathering sys.schema_table_lock_waits...
 - Gathering sys.session_ssl_status...
 - Gathering sys.session...
 - Gathering sys.processlist...
 - Gathering performance_schema.events_waits_current...
 - Gathering information_schema.innodb_trx...
 - Gathering information_schema.innodb_metrics...
 - Gathering sys.memory_by_host_by_current_bytes...
 - Gathering sys.memory_by_thread_by_current_bytes...
 - Gathering sys.memory_by_user_by_current_bytes...
 - Gathering sys.memory_global_by_current_bytes...
 - Gathering SHOW GLOBAL STATUS...
 - Gathering SHOW ENGINE INNODB STATUS...
 - Gathering SHOW ENGINE PERFORMANCE_SCHEMA STATUS...
 - Gathering SHOW FULL PROCESSLIST...
 - Gathering SHOW OPEN TABLES...
Sleeping for 5s...
Collecting performance metrics (iteration #2 of 30)...
(中略)
Performance metrics collection done
 - Writing delta of collected metrics...
 - Gathering sys.host_summary...
 - Gathering sys.host_summary_by_file_io...
 - Gathering sys.host_summary_by_file_io_type...
 - Gathering sys.host_summary_by_stages...
 - Gathering sys.host_summary_by_statement_latency...
 - Gathering sys.host_summary_by_statement_type...
 - Gathering sys.io_by_thread_by_latency...
 - Gathering sys.io_global_by_file_by_bytes...
 - Gathering sys.io_global_by_file_by_latency...
 - Gathering sys.io_global_by_wait_by_bytes...
 - Gathering sys.io_global_by_wait_by_latency...
 - Gathering sys.schema_index_statistics...
 - Gathering sys.schema_table_statistics...
 - Gathering sys.schema_tables_with_full_table_scans...
 - Gathering sys.user_summary...
 - Gathering sys.user_summary_by_file_io...
 - Gathering sys.user_summary_by_file_io_type...
 - Gathering sys.user_summary_by_stages...
 - Gathering sys.user_summary_by_statement_latency...
 - Gathering sys.user_summary_by_statement_type...
 - Gathering sys.wait_classes_global_by_avg_latency...
 - Gathering sys.wait_classes_global_by_latency...
 - Gathering sys.waits_by_host_by_latency...
 - Gathering sys.waits_by_user_by_latency...
 - Gathering sys.waits_global_by_latency...

Server load diagnostics information was written to /MYSQL/mysql-diagnostics-20221212-042102.zip

出力したファイル内は、以下のようなファイルが格納されています。(yamlファイルは表示上除いています)

shell > ls | grep -v yaml
diagnostics-applied_current-pfs_actors.tsv
diagnostics-applied_current-pfs_consumers.tsv
diagnostics-applied_current-pfs_instruments.tsv
diagnostics-applied_current-pfs_objects.tsv
diagnostics-applied_current-pfs_threads.tsv
diagnostics-delta.host_summary_by_file_io.tsv
diagnostics-delta.host_summary_by_file_io_type.tsv
diagnostics-delta.host_summary_by_stages.tsv
diagnostics-delta.host_summary_by_statement_latency.tsv
diagnostics-delta.host_summary_by_statement_type.tsv
diagnostics-delta.host_summary.tsv
diagnostics-delta.io_by_thread_by_latency.tsv
diagnostics-delta.io_global_by_file_by_bytes.tsv
diagnostics-delta.io_global_by_file_by_latency.tsv
diagnostics-delta.io_global_by_wait_by_bytes.tsv
diagnostics-delta.io_global_by_wait_by_latency.tsv
diagnostics-delta.schema_index_statistics.tsv
diagnostics-delta.schema_table_statistics.tsv
diagnostics-delta.schema_tables_with_full_table_scans.tsv
diagnostics-delta.user_summary_by_file_io.tsv
diagnostics-delta.user_summary_by_file_io_type.tsv
diagnostics-delta.user_summary_by_stages.tsv
diagnostics-delta.user_summary_by_statement_latency.tsv
diagnostics-delta.user_summary_by_statement_type.tsv
diagnostics-delta.user_summary.tsv
diagnostics-delta.wait_classes_global_by_avg_latency.tsv
diagnostics-delta.wait_classes_global_by_latency.tsv
diagnostics-delta.waits_by_host_by_latency.tsv
diagnostics-delta.waits_by_user_by_latency.tsv
diagnostics-delta.waits_global_by_latency.tsv
diagnostics-metrics.summary.tsv
diagnostics-raw
diagnostics-statement_performance_analyzer-delta.tsv
error_log
global_variables.tsv
host_info
instance
mysql.audit_log_filter.tsv
mysql.audit_log_user.tsv
performance_schema.host_cache.tsv
performance_schema.persisted_variables.tsv
performance_schema.replication_applier_configuration.tsv
performance_schema.replication_applier_filters.tsv
performance_schema.replication_applier_global_filters.tsv
performance_schema.replication_applier_status_by_coordinator.tsv
performance_schema.replication_applier_status_by_worker.tsv
performance_schema.replication_applier_status.tsv
performance_schema.replication_asynchronous_connection_failover_managed.tsv
performance_schema.replication_asynchronous_connection_failover.tsv
performance_schema.replication_connection_configuration.tsv
performance_schema.replication_connection_status.tsv
performance_schema.replication_group_member_stats.tsv
performance_schema.replication_group_members.tsv
pfs_actors.tsv
pfs_consumers.tsv
pfs_instruments.tsv
pfs_objects.tsv
pfs_threads.tsv
replication_master_info.tsv
replication_relay_log_info.tsv
schema_object_overview.tsv
schema_routine_size.tsv
schema_table_count.tsv
schema_table_engines.tsv
schema_table_info.tsv
schema_tables_without_a_PK.tsv
schema_top_biggest_tables.tsv
schema_unused_indexes.tsv
SHOW_BINARY_LOGS.tsv
SHOW_MASTER_STATUS.tsv
SHOW_REPLICA_STATUS.tsv
SHOW_REPLICAS.tsv
slow_log.tsv
slow_queries_in_95_pctile.tsv
slow_queries_summary_by_rows_examined.tsv
uri
XA_RECOVER_CONVERT_xid.tsv

負荷原因の調査に役立つ情報が、collectHighLoadDiagnosticsコマンドで一通り取得できますので、便利です。
オプションは、他にも設定できます。詳しくは、以下のページをご参照ください。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-debug-highload-diagnostics.html

collectSlowQueryDiagnosticsをつかってみた

collectSlowQueryDiagnosticsは、特定のスロークエリ調査で利用できます。MySQL Shell 8.0.31から追加されました。
collectHighLoadDiagnostics()で取得する内容に加えて、クエリ実行中の情報や、スロークエリ調査で利用できる情報を取得しています。

以下は、実行例です。
こちらも、どのような情報を取得しているのかは、以下の実行ログをみてもらえればわかると思います。
取得タイミングや回数などは、それぞれの診断ユーティリティで異なりますが、collectHighLoadDiagnostics()と比べて、追加取得しているものの先頭に !! マークをつけています。

 JS > util.debug.collectSlowQueryDiagnostics("/MYSQL/","SELECT * FROM test.t1,test.t2 WHERE t1.id=t2.id GROUP BY t1.id")
Collecting diagnostics information from mysql://root@localhost:8031...
!! Collecting information for referenced table `test`.`t1`
!! Collecting information for referenced table `test`.`t2`
Collecting Schema Information and Statistics
 - Gathering schema tables without a PK...
 - Gathering schema routine size...
 - Gathering schema table count...
 - Gathering schema unused indexes...
 - Gathering schema object overview...
 - Gathering schema top biggest tables...
 - Gathering schema table engines...
 - Gathering schema table info...
Executing BENCHMARK()...
 - Copying MySQL error log file (/MYSQL/msb_8_0_31/data/msandbox.err)
 - Gathering slow queries in 95 pctile...
 - Gathering slow queries summary by rows examined...
 - Gathering slow_log...
Collecting system information for rhel8 (linux)
 -> Executing date
 -> Executing uname -a
 -> Executing getenforce
 -> Executing free -m
 -> Executing swapon -s
 -> Executing lsb_release -a
 -> Executing mount -v
 -> Executing df -h
 -> Executing cat /proc/cpuinfo
 -> Executing cat /proc/meminfo
 -> Executing cat /etc/fstab
 -> Executing mpstat -P ALL 1 4
 -> Executing iostat -m -x 1 4
 -> Executing vmstat 1 4
 -> Executing top -b -n 4 -d 1
 -> Executing ps aux
 -> Executing ulimit -a
 -> Executing for PID in `pidof mysqld`;do echo "# numastat -p $PID";numastat -p $PID;echo "# /proc/$PID/limits";cat /proc/$PID/limits;echo;done
 -> Executing dmesg
 -> Executing egrep -i 'err|fault|mysql' /var/log/*
 -> Executing pvs
 -> Executing pvdisplay
 -> Executing vgs
 -> Executing vgdisplay
 -> Executing lvs
 -> Executing lvdisplay
 -> Executing netstat -lnput
 -> Executing numactl --hardware
 -> Executing numastat -m
 -> Executing sysctl -a
 -> Executing dmidecode -s system-product-name
 -> Executing lsblk -i
 -> Executing sudo sosreport
Collecting EXPLAIN
!!  - Gathering explain_json...
!!  - Gathering explain_analyze...
 - Gathering performance_schema.host_cache...
 - Gathering performance_schema.persisted_variables...
 - Gathering performance_schema.replication_applier_configuration...
 - Gathering performance_schema.replication_applier_filters...
 - Gathering performance_schema.replication_applier_global_filters...
 - Gathering performance_schema.replication_applier_status...
 - Gathering performance_schema.replication_applier_status_by_coordinator...
 - Gathering performance_schema.replication_applier_status_by_worker...
 - Gathering performance_schema.replication_asynchronous_connection_failover...
 - Gathering performance_schema.replication_asynchronous_connection_failover_managed...
 - Gathering performance_schema.replication_connection_configuration...
 - Gathering performance_schema.replication_connection_status...
 - Gathering performance_schema.replication_group_member_stats...
 - Gathering performance_schema.replication_group_members...
 - Gathering mysql.audit_log_filter...
 - Gathering mysql.audit_log_user...
 - Gathering global variables...
 - Gathering XA RECOVER CONVERT xid...
 - Gathering SHOW BINARY LOGS...
 - Gathering SHOW REPLICAS...
 - Gathering SHOW MASTER STATUS...
 - Gathering SHOW REPLICA STATUS...
 - Gathering replication master_info...
 - Gathering replication relay_log_info...
 - Gathering pfs actors...
 - Gathering pfs objects...
 - Gathering pfs consumers...
 - Gathering pfs instruments...
 - Gathering pfs threads...
 - Gathering pfs actors...
 - Gathering pfs objects...
 - Gathering pfs consumers...
 - Gathering pfs instruments...
 - Gathering pfs threads...
 - Gathering sys.host_summary...
 - Gathering sys.host_summary_by_file_io...
 - Gathering sys.host_summary_by_file_io_type...
 - Gathering sys.host_summary_by_stages...
 - Gathering sys.host_summary_by_statement_latency...
 - Gathering sys.host_summary_by_statement_type...
 - Gathering sys.io_by_thread_by_latency...
 - Gathering sys.io_global_by_file_by_bytes...
 - Gathering sys.io_global_by_file_by_latency...
 - Gathering sys.io_global_by_wait_by_bytes...
 - Gathering sys.io_global_by_wait_by_latency...
 - Gathering sys.schema_index_statistics...
 - Gathering sys.schema_table_statistics...
 - Gathering sys.schema_tables_with_full_table_scans...
 - Gathering sys.user_summary...
 - Gathering sys.user_summary_by_file_io...
 - Gathering sys.user_summary_by_file_io_type...
 - Gathering sys.user_summary_by_stages...
 - Gathering sys.user_summary_by_statement_latency...
 - Gathering sys.user_summary_by_statement_type...
 - Gathering sys.wait_classes_global_by_avg_latency...
 - Gathering sys.wait_classes_global_by_latency...
 - Gathering sys.waits_by_host_by_latency...
 - Gathering sys.waits_by_user_by_latency...
 - Gathering sys.waits_global_by_latency...
 - Preparing statement performance analyzer...
Starting background diagnostics collector...
Collecting metrics...
!! Executing query: SELECT * FROM test.t1,test.t2 WHERE t1.id=t2.id GROUP BY t1.id
!! Query finished in 0.0005 sec
!! Results fetched in 0.0001 sec
- Gathering performance_schema.metadata_locks...
- Gathering performance_schema.threads...
- Gathering sys.schema_table_lock_waits...
- Gathering sys.session_ssl_status...
- Gathering sys.session...
- Gathering sys.processlist...
- Gathering performance_schema.events_waits_current...
- Gathering information_schema.innodb_trx...
- Gathering information_schema.innodb_metrics...
- Gathering sys.memory_by_host_by_current_bytes...
- Gathering sys.memory_by_thread_by_current_bytes...
- Gathering sys.memory_by_user_by_current_bytes...
- Gathering sys.memory_global_by_current_bytes...
- Gathering SHOW GLOBAL STATUS...
- Gathering SHOW ENGINE INNODB STATUS...
- Gathering SHOW ENGINE PERFORMANCE_SCHEMA STATUS...
- Gathering SHOW FULL PROCESSLIST...
- Gathering SHOW OPEN TABLES...
- Writing delta of collected metrics...
- Gathering sys.host_summary...
- Gathering sys.host_summary_by_file_io...
- Gathering sys.host_summary_by_file_io_type...
- Gathering sys.host_summary_by_stages...
- Gathering sys.host_summary_by_statement_latency...
- Gathering sys.host_summary_by_statement_type...
- Gathering sys.io_by_thread_by_latency...
- Gathering sys.io_global_by_file_by_bytes...
- Gathering sys.io_global_by_file_by_latency...
- Gathering sys.io_global_by_wait_by_bytes...
- Gathering sys.io_global_by_wait_by_latency...
- Gathering sys.schema_index_statistics...
- Gathering sys.schema_table_statistics...
- Gathering sys.schema_tables_with_full_table_scans...
- Gathering sys.user_summary...
- Gathering sys.user_summary_by_file_io...
- Gathering sys.user_summary_by_file_io_type...
- Gathering sys.user_summary_by_stages...
- Gathering sys.user_summary_by_statement_latency...
- Gathering sys.user_summary_by_statement_type...
- Gathering sys.wait_classes_global_by_avg_latency...
- Gathering sys.wait_classes_global_by_latency...
- Gathering sys.waits_by_host_by_latency...
- Gathering sys.waits_by_user_by_latency...
- Gathering sys.waits_global_by_latency...

Server and query diagnostics information was written to /MYSQL/mysql-diagnostics-20221212-045940.zip

出力したファイル内は、以下のようなファイルが格納されています。(yamlファイルは表示上除いています)
collectHighLoadDiagnostics()で取得する情報に加えて、実行計画、オプティマイザートレース情報などが加わっています。

 ls | grep -v yaml
diagnostics-applied_current-pfs_actors.tsv
diagnostics-applied_current-pfs_consumers.tsv
diagnostics-applied_current-pfs_instruments.tsv
diagnostics-applied_current-pfs_objects.tsv
diagnostics-applied_current-pfs_threads.tsv
diagnostics-delta.host_summary_by_file_io.tsv
diagnostics-delta.host_summary_by_file_io_type.tsv
diagnostics-delta.host_summary_by_stages.tsv
diagnostics-delta.host_summary_by_statement_latency.tsv
diagnostics-delta.host_summary_by_statement_type.tsv
diagnostics-delta.host_summary.tsv
diagnostics-delta.io_by_thread_by_latency.tsv
diagnostics-delta.io_global_by_file_by_bytes.tsv
diagnostics-delta.io_global_by_file_by_latency.tsv
diagnostics-delta.io_global_by_wait_by_bytes.tsv
diagnostics-delta.io_global_by_wait_by_latency.tsv
diagnostics-delta.schema_index_statistics.tsv
diagnostics-delta.schema_table_statistics.tsv
diagnostics-delta.schema_tables_with_full_table_scans.tsv
diagnostics-delta.user_summary_by_file_io.tsv
diagnostics-delta.user_summary_by_file_io_type.tsv
diagnostics-delta.user_summary_by_stages.tsv
diagnostics-delta.user_summary_by_statement_latency.tsv
diagnostics-delta.user_summary_by_statement_type.tsv
diagnostics-delta.user_summary.tsv
diagnostics-delta.wait_classes_global_by_avg_latency.tsv
diagnostics-delta.wait_classes_global_by_latency.tsv
diagnostics-delta.waits_by_host_by_latency.tsv
diagnostics-delta.waits_by_user_by_latency.tsv
diagnostics-delta.waits_global_by_latency.tsv
diagnostics-metrics.summary.tsv
diagnostics-raw
diagnostics-statement_performance_analyzer-delta.tsv
error_log
explain_analyze.tsv
explain_json.tsv
explain-optimizer_trace.tsv
explain.tsv
global_variables.tsv
host_info
instance
mysql.audit_log_filter.tsv
mysql.audit_log_user.tsv
performance_schema.host_cache.tsv
performance_schema.persisted_variables.tsv
performance_schema.replication_applier_configuration.tsv
performance_schema.replication_applier_filters.tsv
performance_schema.replication_applier_global_filters.tsv
performance_schema.replication_applier_status_by_coordinator.tsv
performance_schema.replication_applier_status_by_worker.tsv
performance_schema.replication_applier_status.tsv
performance_schema.replication_asynchronous_connection_failover_managed.tsv
performance_schema.replication_asynchronous_connection_failover.tsv
performance_schema.replication_connection_configuration.tsv
performance_schema.replication_connection_status.tsv
performance_schema.replication_group_member_stats.tsv
performance_schema.replication_group_members.tsv
pfs_actors.tsv
pfs_consumers.tsv
pfs_instruments.tsv
pfs_objects.tsv
pfs_threads.tsv
replication_master_info.tsv
replication_relay_log_info.tsv
schema_object_overview.tsv
schema_routine_size.tsv
schema_table_count.tsv
schema_table_engines.tsv
schema_table_info.tsv
schema_tables_without_a_PK.tsv
schema_top_biggest_tables.tsv
schema_unused_indexes.tsv
SHOW_BINARY_LOGS.tsv
SHOW_MASTER_STATUS.tsv
SHOW_REPLICA_STATUS.tsv
SHOW_REPLICAS.tsv
slow_log.tsv
slow_queries_in_95_pctile.tsv
slow_queries_summary_by_rows_examined.tsv
uri
XA_RECOVER_CONVERT_xid.tsv

util.debug.slowQueryDiagnostics()コマンドだけでクエリ調査時に必要になる情報が一通り取得できるので、便利です。
オプションは、他にも設定できます。詳しくは、以下のページをご参照ください。
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-debug-slowquery-diagnostics.html