先日、ZOZOTOWNで最大級のトラフィックを記録する福袋発売イベントで実施した負荷対策 という、ZOZOTOWN さんの負荷対策についての記事が公開されました。
はてブをみると、かなりの方がブックマークをされているようですね。
私も案件の中で、ボトルネック調査をすることがあるのですが、その際の基本的なアプローチをまとめる、良い機会かなと思って本投稿を書いてみました。
Contents
ボトルネックを調査する対象となる環境はどのようなものか？
ボトルネックの調査を行うための環境ですが、色々ケースがあるかと思います。
例えば、次のようなものがあるのではないでしょうか。
- 自分が保守に携わっている本番環境で発生するパフォーマンス問題
- 開発環境で発生するパフォーマンス問題
- 特定のタイミング (例 : 夜間バッチ) 発生するパフォーマンス問題
ここ数年はフリーランスとして業務をしているため、保守 / 運用を通して「自分が恒常的に面倒を見ているシステムに対してのボトルネック解消」を受けるような機会はありません。
私がボトルネックの調査に携わる機会が多いのは、次のようなケースです。
- 自分が開発 / 保守に携わっておらず、システムの中身が全くわからない環境で発生しているパフォーマンス問題
このようなケースのパフォーマンス問題に対しての調査の依頼というものは、毎年相談を受けます。
本投稿は、「自分が中身を知っているシステムではない環境」でパフォーマンスのボトルネックを調査する場合の、私が実際に行っているアプローチの一つとなります。
SQL Server の処理の実行状態
SQL Server が処理を実施する際には、基本的には「次の 3 種類の状態」を切り替えながら処理が行われていきます。
- 実行状態 : クエリがアクティブであり処理が実行されている状態
- CPU を使用している状態
- 待機状態 : クエリが何らかのリソースの確保を待機している状態
- CPU を使用していない状態
- 実行可能状態 : 待機状態が解除され、CPU の割り当てを待機している状態
- CPU が使用可能になるのを待機している状態
「待機状態」は、何らかのリソースが使用可能になるのを待機している状態となります。
この状態は、CPU を使用しておらず、処理を勧められていない状態になっていると言えます。
この「待機状態」となっている時間を減らすことで、処理時間を短縮することにつなげることができます。
SQL Server では、この「待機状態」の情報のことを、一般的には「待ち事象」(Wait Statistics) と呼び、「待機状態の理由毎に待機時間を取得することができる」ようになっています。
待ち事象を起点にするとは？
それでは、本投稿のタイトルとした「待ち事象を起点とする」という点について考えてみましょう。
「待ち事象」は「待機状態」の情報であり、待機状態の理由毎に情報を取得できるということを、前項で記載しました。
このことから、次のことを言うことができます。
- 待機状態が多い時間となっている待機理由は、サーバーのパフォーマンスを低下させるボトルネックとなっている可能性がある
待ち事象の情報を正しく確認することで、「サーバーのパフォーマンスを低下さる原因としてどのような理由が起因しているか」ということを判断することができます。
これができると「自分が中身を知らないシステム」に対しても、「どの待機理由が多いので、どのリソースやどの論理的な事象がボトルネックとなっている」という推測を行うことができます。
この考え方が「待ち事象を起点にする」というものになります。
基本的には次の 2 つのアプローチを行います。
- どの待ち事象の発生頻度 / 待ち事象による待機時間が多いのかを整理する
- その待機状態がなぜ発生したのかを多面的に判断する
どの待ち事象の発生頻度 / 待ち事象による待機時間が多いのかを整理する
SQL Server 2019 CTP 2.2 の場合、待ち事象は 1,025 種類ほどあり、全ての待ち事象を確認するというのは現実的ではなく、実際には「どのような待ち事象がトップになっているのか」を整理します。
確認をすべき待ち事象の代表的なものは次のような内容になります。
- SOS_SCHEDULER_YIELD : スケジューラーの利用権の譲渡待ち
- LCK_M_xx : 特定のモードのロックの取得待ち
- PAGEIOLATCH_xx : ディスクとの IO 要求を伴うページのラッチ待ち (ディスクアクセスの状況)
- PAGELATCH : 論理ページの操作要求を伴うラッチ待ち (ページヘッダーやシステムアロケーションページの更新)
- WRITELOG : トランザクションログの書き込み
- THREADPOOL : スレッドプールからワーカースレッドの確保待ち
- ASYNC_NETWORK_IO : 非同期ネットワークの待ち (クライアント要求に対しての N/W 経由でのデータ転送)
これらの情報を整理することで、「システムの中身が全く分からないシステム」でも次のような判断ができるようになります。
- サーバーとして、CPU / メモリ / ディスク のどのリソースの利用頻度が多く、どのリソースがボトルネックとなっているのか
- サーバーの物理リソースを消費しているのではなく、ロックやスレッドプールのような論理的な要因がボトルネックとなっているのか
ポイントとなるのは次の内容です。
- サーバーの物理リソースの利用を起因してボトルネックが発生しているのか？
- ロック等の論理的な要因に起因してボトルネックが発生しているのか？
サーバーの CPU 使用率やディスク I/O が高いのであれば、サーバーのリソースの増強をすることで、ボトルネックを解消することができる可能性があります。
しかし、ロックのような論理的な要因により、ボトルネックが発生している場合、サーバーリソースの増強では、ボトルネックの解消につながらない可能性も考えられます。
ボトルネックが、物理的要因なのか論理的要因なのかを、整理してと見極めることは重要です。
このような調査をしないで、負荷が高い / 処理性能が伸びやむ場合の対応として、
- サーバーをスケールアップしていないからだ
- データを水平分割 (シャーディング) してスケールアウトしていないからだ
- クラウドを使っていないからだ
となってしまうのは、もったいないかなと思います。
- サーバーをスケールアップしていないからだ
- どのリソースをスケールアップすればよいのか、調査ができていますか？
- データを水平分割 (シャーディング) してスケールアウトしていないからだ
- ロックのような論理的な事象が原因となっている場合、水平分割や同一データのレプリカを持つサーバーを増やして効果があるデータ構造ですか？
- クラウドを使っていないからだ
- クラウドってサーバー上で動いているので、どのリソースがボトルネックなのかの見極めは必要ですよね？
というようなことが重要だと思いますので。
その待機状態がなぜ発生したのかを多面的に判断する
最初のステップとしては待ち事象を起点にするだけでも様々な情報を確認することができるようになりますが、待ち事象以外の情報とも組み合わせて、多角的に見ることで、様々な気付きを得ることができます。
「PAGEIOLATCH_xx の待ち事象が多い」場合を例として考えてみましょう。
この待ち事象は「ディスク I/O 要求に対しての待ち」となります。
ディスク I/O 要求に対しての待ちですが、様々な事象が考えられます。
すぐに思いつくものとしては、次のようなものがあるのではないでしょうか。
- 大量のデータアクセスの発生によるディスク I/O の発生
- メモリ不足のため、キャッシュ領域が不足してディスク I/O の発生
- ディスク性能が不足しているため、ディスク I/O が発生した場合に待ちが発生
このような予測の裏付けをするためには、待ち事象の情報だけでなく、「メモリの使用状況」や「実際にどの程度、ディスクに対して? I/O 要求が発生しているか」と組み合わせて確認する必要があります。
待ち事象をベースにしながら、「それがなぜ発生したのか？」を他のデータと組み合わせて、多面的に判断するということも重要となります。
私の場合、「二つ以上の情報と組み合わせてその理由を考える」というようなことを心がけています。
待ち事象を取得するためには？
それでは、待ち事象を取得するためにはどのような方法があるでしょうか？
代表的なものは次の 2 種類の方法となります。
- パフォーマンスモニター
- 動的管理ビュー (DMV : Dynamic Management View)
これらの特徴は次のようになります。
パフォーマンスモニター
OS のパフォーマンスモニターの、次の情報から待ち事象の情報を確認することができます。
SQL Server on Linux や、PaaS のサービスのような、OS のパフォーマンスモニターを利用することができないような環境では、次の動的管理ビューを使用することで、SQL の実行により情報を取得することできます。
パフォーマンスモニターでは、次の項で紹介する DMV の待ち事象の情報とは異なり、代表的な待ち事象の情報のみ確認ができますが、「時系列データを作りやすい」というメリットがあります。
動的管理ビュー
動的管理ビュー は、SQL Server 2005 から実装されたもので、SQL Server の内部の情報を確認することができるものになります。
この DMV の中で、「待ち事象」を確認するためのビューとしては、次の 3 種類が代表的なものになります。
- sys.dm_os_wait_stats
- 待ち事象を確認するためのベースとなる情報、最初に確認すべき内容となり、待機理由毎に待機情報のレコードが生成されている
- 「LATCH_xx」の情報については、次の段落の latch_stats から詳細を確認することができる
(LATCH_xx : 非バッファラッチと呼ばれ、PAGELATCH_xx / PAGEIOLATCH_xx 以外のラッチの情報)
- sys.dm_os_latch_stats
- 待ち事象の情報の中で非バッファラッチを詳細に確認するための情報
- 待ち事象で「LATCH_xx」の待機事象が多い場合に確認する
- sys.dm_os_spinlock_stats
- スピンロックを詳細に確認するための情報
この中で最初に確認すべき重要な情報は「sys.dm_os_wait_stats」となります。
この情報を元にすると、「SQL Server から確認できる全待ち事象」から「どのリソースの待機が多いか」を判断することが可能となります。
SQL Server の新しいバージョンになると、「セッション単位の待ち事象」や「クエリの中で多く発生していた待ち事象」を取得できるようになる、といったような取得の方法が増えます。
バージョンアップをすることで「SQL Server のトラブルシューティングの判断材料が増える」というようなメリットもありますので、この辺もバージョンアップの指標として意識しておくと良いかと。
パフォーマンスモニターと sys.dm_os_wait_stats をどう使い分ける？
パフォーマンスモニターと sys.dm_os_wait_stats (以降、DMV) では、類似の情報を取得することができます。
それでは、これらの情報はどのように使い分ければよいでしょうか？
ポイントとなるのは「時系列データの作りやすさ」です。
DMV の情報は、「SQL を実行したタイミングの累積値」を取得することになります。
これは「スナップショットとしての静止データを取得する」ということが言えるのではないでしょうか。
パフォーマンスモニターについては、標準でログを取得する機能が含まれており、この機能を取得することで「定期的な情報の取得」を行うことができます。
ログとして情報を取得することで「時系列データとして取り扱う」ことができるようになります。
まずは、ファーストステップとしては、次のように使い分けると良いのではないでしょうか。
- DMV : スナップショットとして累積値を取得
- パフォーマンスモニター : 時系列データとして波形を取得
DMV の情報を定期的に取得し、テーブルやテキストに出力することで、「連続したスナップショットを利用して時系列データを作成」することもできますので、慣れてくるとパフォーマンスモニターのデータに頼らなくても DMV のみで待ち事象のデータを整理することも可能だったりします。
投稿が長くなってきましたので、まずはその 1 としてはここまでで。
その 2 では、待ち事象のデータの整理方法などをまとめて行ければと思います。