DO’s&DONT’s #17: やっておいた方がいいこと - tempdb データファイル数を CPU 数に一致させる

  • Article

神谷 雅紀
Escalation Engineer

 

KB 2154845 で述べられていますが、一般的には、tempdb データファイルの数は、SQL Server が使用可能な CPU の数に一致させた方が高負荷時のパフォーマンス劣化を防ぐことができるとされています。ただし、2011 年の PASS Conference (PASS : Professional Assosiation for SQL Server) におけるセッション Inside Tempdb で発表されたテスト結果を受けて、8 を超える CPU が使用可能な SQL Server では、tempdb データファイル数は 8 を基準とし、状況を見ながら必要に応じて 4 の整数倍の数のファイルを追加するという方法が推奨されています。

ただ、これを必ずやらなければならないかと言えば、tempdb 負荷の低い環境では必ずしもやる必要はなく、そのような環境では、やったとしても害はありませんが、これといった効果もありません。tempdb 負荷が高いのか低いのか分からないのであれば、やっておいた方が無難でしょう。

 

推奨事項

  1. tempdb データファイルの数は SQL Server に割り当てている CPU の数と一致させる。
  2. ただし、CPU 数が 8 を超える場合には、ファイル数は 8 を基準とし、割り当て管理ページのアクセス競合状況を見ながら、必要に応じて 4 の整数倍の数のファイルを追加する。
  3. 各ファイルのサイズは同一サイズにする。 

 

SQL Server が使用可能な CPU とは？

「tempdb データファイルの数を SQL Server が使用可能な CPU 数に一致させる」と言う場合の「SQL Server が使用可能な CPU」は、affinity mask 構成値によって SQL Server に割り当てられている論理 CPU の数です。もっと厳密に言えば、DAC 以外の visible かつ online である SQLOS scheduler の数です。

既定では、affinity mask は 0 に設定されているため、SQL Server が動作している Windows が認識しているすべての論理 CPU になります。ただし、SQL Server 2012 では、ラインセンスモードによって制限されている場合もあります。

SQL Server が使用可能な論理 CPU の数は、SQL Server の Errorlog ファイルで確認することができます。

 

SQL Server 2012 Errorlog のサンプル

以下は、ハイパースレッディングが有効になっている 4 コア 2 ソケットの CPU (2 sockets with 4 cores per socket) であるため、16 論理 CPU が使用可能であること (using 16 logical processors based on SQL Server licensing) を示しています。

 

2013-01-06 19:21:50.50 サーバー SQL Server detected 2 sockets with 4 cores per socket and 8 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

 

以下は、NUMA ノード 0 には CPU 0 ～ 7 (CPU mask: 0x00000000000000ff) までがあり、それらすべてが SQL Server が使用可能 (Active CPU mask: 0x00000000000000ff) であることを示しています。また、ノード 1 については CPU 8 ～ 15 までがあり、それらすべてが SQL Server が使用可能であることを示しています。

 

2013-01-06 19:21:51.81 サーバー Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2013-01-06 19:21:51.81 サーバー Node configuration: node 1: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x000000000000ff00:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

 

つまり、このサンプルの SQL Server が使用可能な論理 CPU 数は 16 ということになりますので、tempdb データファイル数は、まずは 8 とし、その後様子を見ながら、必要に応じて増すことになります。

 

なぜファイル数を CPU 数と一致させるのか？

tempdb データファイルの数を増やす目的は、tempdb での割り当て管理ページへのアクセス競合を回避するためです。

データベースに新たにテーブルなどのデータベースオブジェクトを作成する場合、それをデータベースファイル内のどこに割り当てるのかを決定するために、PFS, GAM, SGAM ページへのアクセスが必要になります。割り当てに際しては、それらページの更新も必要になります。データベースオブジェクトの削除時には、そのオブジェクトが使用していた領域の割り当て解除が行われます。この時にも、これらページの更新が必要になります。

多数のセッションが同時にデータベースオブジェクトの割り当てを行おうとすると、PFS, GAM, SGAM ページへのアクセスが競合する場合があります。このようなアクセスの競合は、Latch 獲得待ちとして表面化します。SQL Server では、ページへのアクセス時には必ず Latch と呼ばれる同期オブジェクトを獲得する必要があるためです。KB 2154845 において、Latch 待ちの対象となるページが 2:1:1, 2:1:3 と書かれていますが、これらはそれぞれ tempdb の PFS ページおよび SGAM ページのページ番号です。

PFS Page Free Space
  • 各ページ内の割り当て状況、空き状況を管理。
  • 最初の PFS ページは各ファイルのページ 1 にあり、それ以降は 8088 ページごと。
  • PFS 1 ページで 64MB 分の領域を管理。
GAM Global Allocation Map
  • 各エクステントが使用中かどうかを管理。
  • 最初の GAM ページは各ファイルのページ 2 にあり、それ以降は 511232 ページごと。
  • GAM 1 ページで 4GB 分の領域を管理。
SGAM Shared (Secondary) Global Allocation Map
  • 各エクステントが混合エクステントかどうか、また、空きページがあるかどうかを管理。
  • 最初の SGAM ページは各ファイルのページ 3 にあり、それ以降は 511232 ページごと。
  • SGAM 1 ページで 4GB 分の領域を管理。

* SGAM の S が Books Online では Shared となっていますが、SGAM が最初に製品に取り入れられた当時は、Secondary であったように思います。そのため、Shared に違和感のある人もいるかもしれないと思い Secondary も併記しました。ページの役割からすればどちらも間違ってはいないと思いますし、普通は「エスガム」としか呼ばれませんので、どちらであっても大きな違いはないかと思います。

ユーザーデータベースでは、これらページへのアクセスが競合するほど頻繁にオブジェクトが作成されたり、削除されることは非常に稀です。しかし、tempdb では、作成されるオブジェクトが一時オブジェクトであるため、ユーザーデータベースよりも頻繁にオブジェクトの作成や削除が行われることがあります。KB 2154845 で述べられている対処方法が tempdb でのみ必要となるのは、このような理由からです。

では、なぜファイル数を CPU 数と一致させるのでしょうか？

その答えには、ふたつの要素が関連します。

ひとつ目の要素は、データベースオブジェクトへの領域割り当て方法です。

データベースが複数のデータファイルから構成されている場合の割り当ては、proportional fill algorithm と呼ばれる方法により、すべてのファイルに対して割り当てが分散されます。個々のデータファイルのサイズが同じである場合には、基本的に、各ファイルへの割り当てはラウンドロビンになります。

ふたつ目の要素は、SQL Server におけるスレッド (ファイバー) スケジューリングです。

SQL Server は、SQLOS Scheduler と呼ばれる論理スケジューラーを用いて、ユーザーモードスケジューリングを行っています。SQLOS Scheduler は論理 CPU に対応しています。SQL Server 内のすべてのワーカースレッドまたはファイバーは、Worker と呼ばれる内部オブジェクトに結び付けられ、SQLOS Scheduler 上で動作します。ある一時点を取った場合、ひとつの SQLOS Scheduler 上で実行している Worker はひとつのみです。これは、ある一時点を取った場合に、ひとつの CPU 上で動作しているスレッドがひとつであることに似ています。このような方法により、Windows によるスケジューリングオーバーヘッドや SQL Server 内での同期オーバーヘッドを減らすことで、数百数千というスレッドやファイバーの同時実行を可能にしています。

実際に試したことはありませんが、何の管理も行わずに数千スレッドを自由に動かしたら、CPU 使用率が急騰してしまうのではないかと思います。

例えば、SQL Server が 2 CPU を使用して動いているとします。SQLOS Scheduler 0 上の Worker 0 が 2:1:1 (tempdb ファイル 1 の PFS) ページに Latch を獲得している状態である場合、Scheduler 0 上の他の Worker は休眠状態であるため、それらが同じページに Latch を獲得しようとすることはありません。ここで、Scheduler 1 上の Worker 1 が新しい一時オブジェクトを作成しようとしたとします。tempdb が 1 データファイルで構成されている場合、Worker 1 は 2:1:1 へのアクセスが必要になります。ここで Worker 0 との競合が発生します。一方、tempdb が 2 データファイルで構成されている場合には、Worker 1 の割り当て要求は、次のファイルであるファイル 2 で処理されることになります。つまり、Scheduler 1 上の Worker 1 は 2:2:1 へのアクセスは必要としますが、Worker 0 が Lacth を獲得している 2:1:1 へのアクセスは必要としません。つまり、競合は発生しないことになります。

では、データファイルは多ければ多いほどいいのでしょうか？

いいえ、多ければいいというものではありません。

ファイルの数が多くなると、個々のファイルに対する I/O 単位が小さくなる場合があります。例えば、ファイルがひとつであれば、ひとつのファイルに 256KB 単位の I/O を行うところが、4 つのファイルに分かれていることで、64KB 単位の I/O になるかもしれません。一般的に、ファイル I/O はある程度大きなサイズの方がパフォーマンスがよくなります。そのため、データベースオブジェクト作成時の割り当て管理ページへのアクセス競合を回避しても、ファイル I/O パフォーマンスの劣化度合いの方が大きければ、データファイル数を増やしても全体としてのパフォーマンスは悪化することになります。バランスが重要です。

 

tempdb 負荷が高いのか高くないのかをどうやって判断するのか？

確認すべきポイントは、tempdb の PFS, GAM, SGAM ページにおける Latch 競合が発生しているかどうかです。

tempdb で Latch 競合が発生しているかどうかは、sys.dm_os_waiting_tasks DMV で確認することができます。以下のクエリの結果中の wait_duration_ms 列の値が大きくなる (待機時間が長くなる) と、パフォーマンスへの影響が出ます。

select session_id,wait_duration_ms,wait_type,resource_description from sys.dm_os_waiting_tasks where wait_type like 'PAGE%LATCH%' -- PAGELATCH_ または PAGEIOLATCH_ and resource_description like '2:%' -- 2 は tempdb の DBID

 

Latch 獲得待ちは、パフォーマンスカウンタ SQLServer:Wait Statistics\Page latch waits, Page IO latch waits でも確認することができます。ただし、これらのパフォーマンスカウンタでは、どのデータベースでの待ちであるのかを区別できませんので、厳密な確認ではなく、全体の傾向を把握するという意味での確認に留まります。