第234回　MySQL Shellの診断ユーティリティツールを使ってみよう

はじめに

データベース運用において、性能の問題やトラブルの原因を特定するためのトラブルシューティングは、日々の業務で避けて通れない重要な作業です。しかし、複雑化するシステム環境や増大するデータ量の中で、何が問題の原因なのかを見つけるのは容易ではありません。MySQL Shellにはパフォーマンス問題やシステムの異常を検出し、原因特定を支援するためのツールとして「診断ユーティリティ」というものがあります。

本記事では、MySQL Shellの診断ユーティリティを活用して、実際の運用環境で起こり得るトラブルをどのように効率的に解決できるかを解説します。具体的には、以下の3つの主要な診断ユーティリティに焦点を当てます。

util.debug.collectDiagnostics（8.0.29）
util.debug.collectHighLoadDiagnostics（8.0.31）
util.debug.collectSlowQueryDiagnostics（8.0.31）
※（）は機能が追加されたバージョン番号

これらのツールを使いこなすことで、システムの問題を早期に発見したり、さまざまな側面での原因調査が可能になります。なお、この記事ではMySQL Shell 8.4.3のバージョンを使用します。

診断ユーティリティ

MySQL Shellにはバージョン8.0.29から、診断ユーティリティという機能が加わりました。診断ユーティリティは先に挙げた3種類があります。これらのツールはシステム全体の状態を一括で収集し、問題の原因を特定するために役立つデータを提供します。

診断ユーティリティは取得したデータをtsv、yamlの2つのファイル形式で取得し、1つのアーカイブファイル（ZIP形式）にまとめて出力します。

util.debug.collectDiagnostics

このユーティリティはサーバー全体の状態を一括で診断するためのツールです。を実行すると、以下のような情報を取得します。

MySQLの構成情報（PKのないテーブル一覧、設定されているシステム変数など）
MySQLのステータス情報（稼働中のセッション、接続情報など）
レプリケーション関連情報
エラーログ

主な用途

サーバーの全体像を把握したいとき
トラブル発生時に初期情報を収集したい場合

util.debug.collectHighLoadDiagnostics

このユーティリティはutil.debug.collectDiagnosticsの内容に加えて、テーブルに関する追加の情報やスロークエリに関する情報などを追加で取得します。また、util.debug.collectHighLoadDiagnosticsは実行回数、実行間隔を指定することができます。デフォルトは、300秒間隔で2回実行します。

取得できる情報

util.debug.collectDiagnosticsで取得できる情報
テーブルに関する情報（サイズの大きいテーブル、テーブルの各種情報など）
sysスキーマから取得できるサマリ情報

主な用途

高負荷状態の原因となっている状態でさまざまな側面から調査を行いたい場合

util.debug.collectSlowQueryDiagnostics

スロークエリが特定できている状態で、その詳細な実行状況を調査するために使用されます。このユーティリティは、指定したクエリに対して実行計画やクエリを実行して、実際にかかった時間の情報などを取得することができます。

主な収集データ

util.debug.collectDiagnosticsの情報
クエリに関する実行計画
クエリに関連するテーブルの情報
クエリ実行前後のメトリックス

主な用途

遅いクエリの最適化
インデックスの見直しやクエリ修正の検討⠀

診断ユーティリティを利用してみる

実際に診断ユーティリティを利用してみましょう。診断ユーティリティを利用するには、MySQL Shellで対象のMySQLに接続する必要があります。ただ、接続するときに注意点があります。

接続する対象のMySQLのバージョンが5.7以上であること
rootで実行すること
スローログ関連の情報を取得する場合はlog_output = TABLEであること

また、接続時先がlocalhost（local_target == true）で接続するのか、それ以外かでも、取得する情報が変わってきます。実際にlocalhostで取得する場合のみ、errorログファイルやコマンドで取得できるhost_infoの情報を取得します。

実際にrootで接続して診断ユーティリティを実行してみます。

JS > util.debug.collectDiagnostics("diagnostics")
Collecting diagnostics information from mysql://root@/tmp%2Fmysql.sock...

通常引数に指定したファイル名でカレントディレクトリに収集されます。

もし。引数にディレクトリ名（例：/tmp/）とした場合には、ディレクトリ配下にmysql-diagnostics-YYYYMMDD-HHMMSS.zipという形式で生成されます。ここでの注意として、ディレクトリを指定する場合は最後の/を忘れないようにしてください。たとえば引数に/tmpを指定してしまった場合、/tmp/配下ではなく/tmp.zipというファイルが生成されてしまいます。

実際に実行してみると、以下のように取得している項目が確認できます。

JS > util.debug.collectDiagnostics("diagnostics")
Collecting diagnostics information from mysql://root@/tmp%2Fmysql.sock...
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 (/data/mysql/error.log)
 - 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 global variables...
 - Gathering XA RECOVER CONVERT xid...
 - Gathering SHOW BINARY LOGS...
 - Gathering SHOW REPLICAS...
 - Gathering SHOW BINARY LOG 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 test-db01 (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 diagnostics.zip

出力量は多いですが、さまざまなクエリやコマンドを使って多様な情報が取得できていることが確認できます。出力されたdiagnostics.zipを解凍して中身を確認すると、さまざまな情報がyamlとtsvファイルとして出力されています。

tsv形式のファイルは除外して表示

$ unzip diagnostics.zip && cd diagnostics
$ ll | grep -v yaml
total 5864
-rw------- 1 root root 2530928 Nov 20 00:19 0.error_log
-rw------- 1 root root   26790 Nov 20 00:19 0.global_variables.tsv
-rw------- 1 root root   55745 Nov 20 00:19 0.information_schema.innodb_metrics.tsv
-rw------- 1 root root     594 Nov 20 00:19 0.information_schema.innodb_trx.tsv
-rw------- 1 root root     475 Nov 20 00:19 0.instance
-rw------- 1 root root   33794 Nov 20 00:19 0.metrics.tsv
-rw------- 1 root root     366 Nov 20 00:19 0.performance_schema.events_waits_current.tsv
-rw------- 1 root root     786 Nov 20 00:19 0.performance_schema.host_cache.tsv
-rw------- 1 root root     397 Nov 20 00:19 0.performance_schema.metadata_locks.tsv
〈省略〉

試しにschema_tables_without_a_PK.yamlを見てみると、PKのないテーブル一覧がyaml形式で出力されていることがわかります。

$ cat schema_tables_without_a_PK.yaml
# Query:
#	SELECT t.table_schema, t.table_name, t.table_rows, t.engine, t.data_length, t.index_length
#	            FROM information_schema.tables t
#	              LEFT JOIN information_schema.statistics s on t.table_schema=s.table_schema and t.table_name=s.table_name and s.index_name='PRIMARY'
#	            WHERE s.index_name is NULL and t.table_type = 'BASE TABLE'
#	                and t.table_schema not in ('performance_schema', 'sys', 'mysql', 'information_schema')
#
# Started: 2024-11-20T00:19:13.303793
# Execution Time: 0.0114 sec
#
DATA_LENGTH: 16384
ENGINE: InnoDB
INDEX_LENGTH: 0
TABLE_NAME: user
TABLE_ROWS: 4
TABLE_SCHEMA: test
---
DATA_LENGTH: 16384
ENGINE: InnoDB
INDEX_LENGTH: 0
TABLE_NAME: t10
TABLE_ROWS: 0
TABLE_SCHEMA: d1
...

便利なオプション

診断ユーティリティにはそれぞれ便利なオプションがあるので、いくつか紹介します。

util.debug.collectHighLoadDiagnostics

allMembers（defautl: false）: trueの場合にInnoDB Clusterの管理対象トポロジの情報も取得します。
schemaStats（defautl: false）: trueの場合にschema_object_overview（viewの一覧）とtop_biggest_tablesを追加で取得します。
slowQueries（defautl: false）: trueの場合にスロークエリの情報(mysql.slow_log)を収集します。ただし、スロークエリの出力先がTABLEである必要があります。
ignoreErrors（defautl: false）: trueの場合に収集に利用されるクエリがエラーの場合でも無視されます。
customSql/customShell: 収集時に指定したSQL、Shellを実行することができます。以下はcustomSqlを1つ指定する例です。収集が完了すると、0.custom_sql-script_0.tsvというファイルに実行結果が出力されます。

JS> util.debug.collectDiagnostics("/tmp/", {"customSql": ["{ここにSQLを追加する}"]})

収集されたファイルの中にカスタムSQLを実行した結果を出力してくれる

# ls *custom*
0.custom_sql-script_0.tsv
...

util.debug.collectHighLoadDiagnostics

iterations: 指定した回数実行します。
delay: 指定した秒数分実行間隔を空けます。

10秒間隔で5回実行する例

JS> util.debug.collectHighLoadDiagnostics("/tmp/", {iterations: 5, delay: 10})

まとめ

今回は、MySQL Shellの診断ユーティリティの機能について紹介しました。今回取り上げたオプション以外にも、パフォーマンスに影響を与えるものの、SHOW ENGINE INNODB MUTEXを用いてInnoDBのミューテックス情報を取得することも可能です。

デフォルト設定でも多くの情報を収集できますが、customSqlやcustomShellオプションを設定することで、さらに詳細な情報を取得することができます。これらの診断ユーティリティは、頻繁に実行して定点観測を行うよりも、問題が発生した際に迅速に情報を収集し、その後の分析に役立てる運用が適していると言えるでしょう。

より深い理解を得るために、診断ユーティリティの詳細については、公式ドキュメント Diagnostics Utilitiesを参照してご利用ください。