DO’s&DONT’s #14: 絶対にやってはいけないこと - ひとつの CPU に対して affinity mask と affinity I/O mask の両方を ON にする
神谷 雅紀
Escalation Engineer
SQL Server の sp_configure 構成オプションとして、affinity mask, affinity64 mask および affinity I/O mask, affinity64 I/O mask があります。affinity mask は最初の 32 個の CPU 用、affinity64 mask は 33 個目以降の CPU 用である点を除けば、これら 2 つの機能は同じです。affinity I/O mask と affinity64 I/O mask も同様です。
affinity I/O mask が SQL Server の機能として実装された時点から、Books Online をはじめとする多くの技術文書で、affinity mask と affinity I/O mask は重複してはいけないことが述べられています。しかし、現在も重複した設定を見かけることがあるため、改めてこの blog でも書いておきたいと思います。
SQL Server のスレッドスケジューリング
SQL Server のスレッドスケジューリングをある程度知っていた方が、affinity mask と affinity I/O mask の設定が重複してはいけない理由を理解しやすいと思いますので、まずは、SQL Server のスレッドスケジューリングを簡単におさらいします。
SQL Server 内のすべてのワーカースレッドは論理スケジューラー (*1) 上で動作します。論理スケジューラーは、例えば、CPU 0 に対応する論理スケジューラ 0 のように CPU ごとに作成されます。
SQL Server のスレッドスケジューリングは非プリエンプティブであり、ある一時点を取った場合、ひとつの論理スケジューラー上で実行中のワーカースレッドはひとつのみです。一部の例外を除いて、その論理スケジューラーに割り当てられているその他のワーカースレッドは休眠状態です。例えば、ロック待ちになったワーカースレッドは、そのロックが獲得できるようになるまでは次の処理に進むことができないため、自分自身を休眠状態にすると同時に次のワーカースレッドを起こします。休眠状態から復帰したワーカースレッドは、休眠状態に入る前の処理を継続します。
このような方法を取ることで、SQL Server は SQL Server にとって最も効率のよい任意のタイミングでスレッドを休眠状態にしたり、復帰させたりすることができ、OS によるスレッド管理のオーバーヘッドを最小限に抑えています。
affinity mask オプションとは
affinity mask とは、特定のスレッドを特定の CPU に割り当てることにより context switch を抑えてパフォーマンスを向上させることを目的としたオプションです。その副作用として、SQL Server プロセスが使用する CPU を制限することもできます。
例えば、affinity mask として 15 (00001111) が指定された場合、SQL Server のワーカースレッドは CPU 0 ~ 3 のいずれかで実行され、CPU 1 で実行開始したワーカースレッドは CPU 0 や 2 に移動することはありません。
affinity I/O mask オプションとは
affinity I/O mask が既定値 0 である場合は、ReadFile/WriteFile API 呼び出しによる I/O のポストやチェックサムの計算などの I/O 完了処理を含む I/O 処理は、クエリを実行しているワーカースレッドによって実行されます。
これに対して、affinity I/O mask を 既定値である 0 以外に設定すると、I/O 専用ワーカースレッド (*2) が作成され、これらの I/O 処理はすべてこの専用スレッドによって処理されるようになります。クエリを実行しているワーカースレッドは、I/O が必要になるとその I/O 要求をキューに入れ、キューに入った I/O 要求は I/O 専用スレッドが処理します。
affinity mask と affinity I/O mask が重複すると何が起こるのか
SQL Server のパフォーマンスが低下する可能性があります。
affinity mask と affinity I/O mask が同じ CPU に対して重複して設定されていると、ひとつの CPU 上に 2 つの論理スケジューラが存在することになり、これは、ひとつの CPU 上で 2 つのスレッドが同時に実行される可能性があることを意味します。さらに、大量のクエリが実行されたり、大きなクエリが実行されるなど、クエリを実行するワーカースレッド群が忙しい状況にある場合には、一般的に、I/O 要求も多く発生するため、I/O 専用ワーカースレッドも忙しい状況になります。I/O 完了処理は、チェックサムの計算など読み取ったデータの正当性の検査を含み、比較的 CPU リソースを消費する処理であるため、CPU がボトルネックとなり、パフォーマンスが低下する可能性があります。
Books Online affinity I/O mask オプション にも「affinity I/O mask オプションを指定する場合は、affinity mask 構成オプションと共に使用する必要があります。affinity I/O mask スイッチと affinity mask オプションの両方で同じ CPU を有効にしないようにしてください。」と書かれているように、affinity mask と affinity I/O mask は重複しないように設定する必要があります。
また、「affinity I/O mask オプションを設定することは特殊な操作なので、特に必要な場合にのみこのオプションを使用することをお勧めします。」と書かれているように、このオプションは高度なオプション (advanced options) であり、十分なテストによりその効果が確認できた場合以外には、設定すべきオプションではありません。
以下は、問題のある設定を Management Studio GUI で表示した場合の例です。
補足
SQL Server 2008/2008 R2 では、このような設定は reconfigure を実行した時点でエラーとなります。ただし、reconfigure with override で強制的に設定することはできます。
メッセージ 5834、レベル 16、状態 2
指定された関係が、指定された IO 関係マスクと競合します。IO 関係マスクで指定されている CPU とは異なる CPU を使用するように関係の設定を変更してください。
(*1) この論理スケジューラーは、SQL Server 2005/2008/2008 R2 では、SQLOS スケジューラーまたは SOS スケジューラーと呼ばれています。SQL Server 7.0/2000 では UMS スケジューラと呼ばれています。
(*2) I/O 専用の lazyriwter スレッドが作成されます。このスレッドが動作する論理スケジューラーは hidden scheduler として作成されます。