データベース

パフォーマンス向上のためにMySQL Performance Schemaを効果的に活用する

より良くするために MySQL のパフォーマンス パフォーマンス・スキーマを使用して、実行時間データ、待機、ロック、メモリ、I/OをSQLで直接分析しています。これにより、ステートメントの実行が遅い原因を迅速に特定し、具体的な対策を講じることができます。 チューニング およびモニタリングについては[2][3][15]を参照。.

中心点

以下の重点事項は、パフォーマンス・スキーマを効果的に活用する上で役立っています。.

活性化 適切な計器類や消費機器を備えた、コンパクトでスリムな構成
ステートメント・ダイジェスト 高価なパターンやホットスポットを特定するために活用する
待機イベント, ロックとI/Oをまとめて分析し、真のボトルネックを特定する
Sysスキーマ 迅速かつ実践的な洞察を表す略語として
反復的な ワークフロー：測定、特定、修正、再測定

パフォーマンススキーマを有効化し、適切に設定する

私はまず、次のことを確認する。 performance_schema 有効になっているか確認してください。現在のMySQLバージョンでは、通常、これが有効な状態で出荷されているためです [1][12]。もし設定されていない場合は、 [mysqld]-ブロック my.cnf 変数 performance_schema=ON そしてサーバーを再起動します。その後、すべての設定を常に最大にするのではなく、計測機器や負荷対象を個別に設定します。私は主に statement/%, wait/% および関連するI/Oパスにより、余分なオーバーヘッドを伴わずに有意義なデータを収集できるようにする [6]。新しい測定シリーズを開始する際は、関連する履歴テーブルを空にして、クリーンな状態から開始する。 ベース.

Sysスキーマで短期間で成果を上げる

一目で把握するために、私はよく sys-スキーマは、パフォーマンス・スキーマの生データを適切に集約してくれるためです [13]。これにより、実行時間に最も大きな割合を占めているクエリを数分で特定できます。まず上位のステートメントから始め、ファイルI/Oビューを確認し、スレッドごとの待機サマリーを検証します。ホットスポットを特定したら、すぐに生データテーブルに戻り、分析をさらに絞り込みます。クエリプランを追跡すれば、適切なオプティマイザーのヒント多くの場合、短期間でその効果が実感できる勝利達成する。.

適切な機器と機器の選定

まずは広く切り出しますが、観察は制御可能な範囲に留めます。まず、最も重要なものを活性化させます。楽器ステートメント、待機時間、I/Oについて分析し、その後、有益な情報が得られないものはすべて除外する [6]。イベント履歴やサマリーテーブルといったデータソースは、私が解明しようとしている疑問を裏付けるものでなければならない。例えば、レイテンシのピークが問題となる場合は、 events_waits_summary_global_by_event_name そして、これを以下と比較してみてください イベント・声明・要約（ダイジェスト別）. I/Oの待ち時間が発生した場合は、以下を確認する イベント名別のファイル概要 そして table_io_waits_summary_by_table. この厳選された選択により、オーバーヘッドを最小限に抑えつつ、信頼性の高い データ.

ステートメントダイジェスト：パターンを認識し、負荷を軽減する

ステートメントダイジェストを使えば、個々のクエリでリテラルが異なっていても、どのパターンが恒常的にコストが高いかを把握できます [17]。優先順位を決定するために、合計時間、実行回数、平均レイテンシで並べ替えます。その際、補足としてスロークエリログの分析戻って、まれな外れ値を見逃さないようにします。ダイジェストにピークが見られる場合は、インデックス、JOIN戦略、およびフィルタの順序を 説明する. 。その後、パフォーマンス・スキーマで再度測定を行い、その効果を確認します。これにより、最適化の成果を定量的に把握できるようにします。.

待機イベント、ロック、およびI/Oの解析

リクエストが滞っている場合は、WaitテーブルとLockテーブルを照会して、実際の原因を原因 [3]に記載されている。同じテーブルに対して多数のスレッドが実行されている場合、 table_lock-競合が発生するのを待ちます。ファイルI/Oイベントに高いレイテンシが見られる場合は、ストレージやキャッシュ、および大規模なスキャンによるクエリパターンを確認します。InnoDBの行ロックが確認された場合は、ホットレコード、トランザクションの所要時間、およびインデックスカバレッジを分析します。これらのパズルのピースがすべてはまって初めて、サーバーパラメータ、スキーマ、あるいはコードに手を加えます。.

メモリの監視：メモリとバッファプール

メモリの問題については、メモリテーブルとInnoDBバッファの使用状況を併せて確認することで対処しています。個々のコンポーネントのメモリ使用量が増加した場合は、制限値を調整し、キャッシュに誤ったデータが保持されていないかを確認します。InnoDBキャッシュが不足している場合は、その割合を増やすか、クエリの局所性を改善します。さらに深く掘り下げたい場合は、バッファプールの最適化大幅なレイテンシー改善を実現しました。その効果を以下のデータで確認しました。概要- テーブルを確認し、LRUヒット数とI/O待ち時間が適切な方向に推移しているかを追跡する。.

日常業務における反復的な診断ワークフロー

私は時間を無駄にせず、変更の効果を測定可能に保つため、常に明確なループで作業を行っています [3]。まず、制御された負荷条件下で問題を再現します。その後、測定値を少数の的を絞った表にまとめ、最も目立つ要因を特定します。続いて、最も大きな効果が期待できるもの――インデックス、クエリ、パラメータ、あるいはコード――を変更します。最後に、再度測定を行い、簡潔な ビフォー／アフター-表を作成し、チームがその効果をすぐに把握できるようにする。.

クエリの例：生データから意思決定へ

よくある質問に対して、日常業務で直接活用している簡潔なSQLスニペットをまとめておきました。以下の表には、私が頻繁に使用する例とその用途を示しています。フィルターは次のように調整しています。 リミット 或いは ORDER BY 用途に応じて。重要なのは、まず仮説を立て、次に的を絞った分析を行い、明確な判断を下すことです。そうすることで、分析の焦点を絞り、不必要な負荷.

パフォーマンススキーマテーブル	ゴール	重要な列	クエリの例
`イベント・声明・要約（ダイジェスト別）`	高価なサンプルを見つける	`digest_text`, `count_star`, `sum_timer_wait`	`SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;`
`events_waits_summary_global_by_event_name`	待機ホットスポット	`event_name`, `sum_timer_wait`	`SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;`
`table_io_waits_summary_by_table`	テーブルI/Oの確認	`object_schema`, `object_name`, `read_timer_wait`	`SELECT object_schema, object_name, (read_timer_wait+write_timer_wait)/1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;`
`イベント名別のグローバルメモリ概要`	メモリを大量に消費するプログラムを見つける	`event_name`, `current_alloc`	`SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;`

生産現場：間接費を最小限に抑え、効果を最大化

ライブ演奏中、私はやみくもに楽器を切り替えるのではなく、自分の疑問に答えとなるものだけを選択します [6]。高頻度のイベントには慎重に対処し、履歴ウィンドウは短く保つようにしています。長期的な観察については、要約されたサマリーを優先し、スナップショットは外部に保存しています。また、 performance_schema_setup_consumers, 、コレクションをただ放置するのではなく、私が管理できるようにするためです。この重点的な取り組みにより、分析は 効率的 そして、サーバーを保護します。.

微調整：セットアップ用ツールとコンシューマの実践

迅速に信頼性の高い結果を得るために、計測器や負荷を的確に設定しています。特に重要なのは statement/%, wait/%, wait/io/% そして、必要に応じて、選定された memory/%-パス。まずは必要最低限のものだけを有効にし、具体的な疑問がまだ残っている場合にのみ機能を拡張します。パフォーマンス・スキーマのタイマーはピコ秒単位で計測します。秒単位にするには、レイテンシの列の値を 1e12.

実行時の一般的な開始点：

-- 主要な計測ツールを有効にする
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
 OR NAME LIKE 'wait/io/%'
 OR NAME LIKE 'wait/lock/%';

-- 重要なコンシューマーを選択する
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
 'thread_instrumentation',
 'statements_digest',
 'events_statements_current',
                 'events_statements_history',
 'events_waits_current',
 'events_waits_history');

-- 新しい測定シリーズのためにサマリーをクリアする
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

メモリ分析が必要な場合は、必要に応じて個別に有効にします memory/%-ツール。オーバーヘッドは増えますが、リークが発生した場合や、アロケーターへの負荷が激しい場合にはその価値があります。.

ディメンション：ユーザー、ホスト、スキーマの理解

ピーク負荷は、多くの場合、全体的なものではなく、特定の ユーザー, ホスト または スキーム 制限されています。パフォーマンス・スキーマでは、アカウントおよびホストごとのサマリーが提供されます。さらに、ダイジェストには次の列が含まれています。 スキーマ名, 、データベースごとにホットスポットを絞り込むため。.

私がよく使う例：

総実行時間順のトップスキーマ： SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
（アカウント別）最も大きな遅延の原因となっているユーザー／ホスト： SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
待ち時間が最も長いスレッド： SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

これらのビューを使用することで、トラフィックセグメントを的確に分類し、クライアントごとにトラフィックの制限、キャッシュ、またはクエリのバリエーションを設定することができます。.

長時間のトランザクションとメタデータロックを可視化する

長時間実行されているトランザクションや非アクティブなトランザクションは、チェックポイント、パージ、および競合するDMLをブロックします。そのため、私は定期的にトランザクションビューとMDL待機を確認しています：

アクティブな取引： SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
メタデータロック（DDL/DMLの競合）を検出する： SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

MDLが優勢な場合は、DDLウィンドウを再計画し、コード内のロック保持時間を最小限に抑え（トランザクションを短縮）、不要な AUTOCOMMIT=0-セッションを不必要に長く開いたままにしておく。.

レプリケーション、バックアップ、およびその副作用について

レプリケーションおよびバックアップのプロセスは、待機およびI/Oビューに表示されます。遅延の原因は、ワーカーのステータスやファイル待機を調査することで特定できます。私は、アプライヤー・ワーカー、SQLスレッド、およびファイルI/Oイベントを確認しています：

レイテンシの高いApplier-Worker： SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
バックアップ中のファイルI/Oのボトルネック： SELECT event_name, (sum_timer_read+sum_timer_write)/1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

ここでボトルネックが見られた場合は、ワークロードがスケーラブルである限り、I/Oフェーズ（例：ウィンドウ処理、I/Oスケジューラ、バックアップのスロットリング）を切り離すか、並列アプライヤーワーカーの数を増やします。.

時間枠、スナップショット、およびリセット戦略

測定には明確な時間枠が必要です。「測定前／測定後」を比較するために、私は意図的なリセットとスナップショットを活用しています：

新しい間隔を取得するには、サマリーをリセットしてください： TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
スナップショットを外部に保存する： CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
短い履歴データは保持し（コンシューマー）、長期的なトレンドは外部で収集する。.

これにより、デプロイメント、パラメータの変更、スキーマの変更をまたいで、最適化を確実に比較・記録することができます。.

オーバーヘッドとメモリ使用量の制御

パフォーマンススキーマは「コストがかかりすぎる」という偏見がよく見られます。実際には、私は次の3つの対策によってオーバーヘッドを最小限に抑えています。関連するインストルメントのみを有効にすること、アクセス頻度の高い履歴コンシューマーのサイズを小さく保つこと、そしてメモリパラメータを適切に設定することです。ダイジェストのばらつきが大きい場合は、的を絞って増やすようにしています。 performance_schema_digests_size また、必要に応じて―― performance_schema_max_sql_text_length, 、アイデンティティが安定した状態を保つためです。メモリ操作が必要な場合は、問題のあるサブシステムに限定します。.

典型的な調整項目として my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# パターンが多い場合のオプション：
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

変更を行うたびに、CPU、レイテンシ、メモリ使用量が安定しているかどうかを確認します。診断が完了次第、設定を「運用上の最小限」の状態に戻します。.

よくあるパターンと迅速な対処法

多額の金額が イベント・声明・要約（ダイジェスト別）, 、スキャン画像多数： インデックス、フィルターの順序、およびSargabilityを確認し、以下で検証する。 説明する そして、測定を繰り返す（消化時間が明らかに短くなる必要がある）。.
ドミナント table_io_waits いくつかのテーブルで： I/Oの局所化を改善する（クラスタ化インデックスへのアクセス、カバリングインデックス）、ステートメントごとのデータ量を削減し、必要に応じてフルテーブル処理の代わりにバッチ処理を行う。.
待ち時間 wait/lock/innodb/%: ホットレコードを特定し、小規模なトランザクション、適切なインデックス、またはキューイングによって書き込み競合を緩和する。.
多くの wait/lock/metadata/sql/mdl: DDLウィンドウの計画、, オンライン-対応の操作を優先し、トランザクションを短くすることでリーダーとライターを分離する。.
貯蔵量の増加は イベント名別のグローバルメモリ概要: リミットの再調整、クエリキャッシュの適切な制限、問題のあるコンポーネントの memory/% 詳細に内訳を示す。.
„平均値には特に異常が見られないものの、「スパイク状」の遅延が認められる場合： パーセンタイルを用いたシステムビューを活用し、必要に応じて負荷のピークを個別に測定する（より狭い範囲、短い履歴、対象を絞った指標）。.

相関関係：スレッドからステートメント、そして待機へ

原因を素早く関連付けるために、私は performance_schema.threads ステートメントや待機に関する「Current」および「History」テーブルを活用します。これにより、対象のスレッドが最後に何を行ったか、そして何を待機しているかを確認できます。その手順を簡潔にまとめると：

影響を受けた方々 PROCESSLIST_ID それぞれ THREAD_ID より performance_schema.threads 取りに行く。.
最新の声明は以下より イベント・声明・履歴 算出する（～に基づき） THREAD_ID および時間順に並べ替える）。.
並列待機を解除 events_waits_history 確認して、ロックやI/O待ちの原因を確認する。.

個々のセッションやWebリクエストが正常な動作から外れてしまった場合、この「ドリルダウン＆ジョイン」という手法が私の定番です。.

クオリティ・ゲートと継続的パフォーマンス

最適化の成果が無駄にならないよう、私はスリムなクオリティゲートを確立しています。パフォーマンススキーマから定義されたクエリを、各リリースの前後に実行します。スナップショットを保存し、主要指標（トップダイジェスト、トップウェイト、テーブルごとのI/O）を比較して、乖離を記録します。 CI/CDでは、代表的な負荷プロファイルと95パーセンタイルの閾値を追加しています。メトリクスが許容範囲を外れた場合、明確な対応フローが確立されています。仮説を検証し、監視対象を絞り込み、修正をデプロイし、再度測定を行うという流れです。.

エラーの原因を未然に防ぐ

長期的には楽器が多すぎる： 診断モードは一時的なものです。通常運転時は、最小限の設定のみを有効にしておいてください。.
測定期間の混在： 新しいテストを行う前にサマリーを空にしておかないと、古いデータによって分析結果の信頼性が損なわれてしまいます。.
誤った時間単位： タイマーはピコ秒単位で表示されます。一貫して 1e12 を共有します。
ダイジェストの氾濫： 変動リテラルはパターンを崩す可能性がある；SQLを正規化し、 performance_schema_max_sql_text_length チェックする。
履歴が長すぎる： イベントの発生頻度が高く、履歴が長くなると負荷がかかるため、履歴ウィンドウは短く保ち、スナップショットは外部で取得する。.

実践チェックリスト

問題を定義し、仮説を立てる。.
適切な手段／消費者を活用し、間接費を最小限に抑える。.
サマリーをクリアし、短い測定ウィンドウを選択する。.
トップダイジェスト、待機時間、I/Oを確認し、ホットスポットを特定する。.
インデックス／クエリ／コード／パラメータを的確に調整する。.
再度測定し、スナップショットを保存し、決定内容を記録する。.
設定を最小稼働レベルまで引き下げる。.

簡単にまとめると：私の実際の取り組み

パフォーマンススキーマを目的を明確にして有効化し、まずは広範囲に適用してから、最も有用なものに絞り込んでいきます楽器 [1][2][12]。概要を素早く把握するためにSysスキーマを参照し、必要に応じて生データまで掘り下げて確認します [13]。まず、ダイジェストや待機イベントでボトルネックを特定し、その後でパラメータを調整します [3][15][17]。その後、進捗が可視化され、再現性がある状態を維持できるよう、変更のたびに新たな測定値で確認を行います。このようにして、長期的に信頼性の高い 応答時間 そして、余計な手間を省く。.