SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server と NUMA

leave a comment

SQL Server と NUMA の対応について少しまとめてみたいと思います。
手探りで調べている内容なので、間違って理解している個所がいくつかあるかも知れませんが。

BOL としては Non-Uniform Memory Access について に記載されている内容となります。

■SQL Server と NUMA


SQL Server 2000 SP4 以降で SQL Server が NUMA の限定的なサポートをはじまりました。
NUMA がサポートされた環境での導入は携わったことがなく、この辺は知識として知っているだけなのですが、トレースフラグやアフィニティマスクを設定する必要があったようです。
非一様メモリ アクセス (NUMA) を使用するには、SQL Server 2000年を構成する方法

SQL Server 2005 以降で NUMA への対応が本格化されました。
SQL Server での NUMA のサポート状況

SQL Server 2005 以降で使用できるようになった動的管理ビュー (DMV) でも NUMA 関連の情報がかなりとれるようになっています。

そもそも NUMA とは何かということについては Wikipedia をざっくり見るとよいかなと思います。
NUMA

従来は複数の CPU プロセッサ (CPU の石) を持っている環境ではメモリへのアクセスは単一バス (Front Side Bus FSB) によりアクセスされていました。
このアーキテクチャはすべてのメモリがフラットに使えるのですが、CPU からメモリへのアクセスが FSB 経由になり、負荷が上がると、メモリアクセスをする際に FSB の帯域が圧迫してしまい、処理速度が出なくなるという問題がありました。
image

この問題の解決方法の一つとして NUMA (Non-Uniform Memory Acces : 非対称メモリアクセス) というアーキテクチャが使用されることになります。
NUMA のアーキテクチャが使用されている環境では、CPU とメモリのアクセスは以下のようになります。
image

CPU がメモリのバスと直結されており、直結されているメモリと CPU プロセッサで NUMA ノード (ローカルノード) を形成します。NUMA ノード間はインターコネクトで直結されており、他の NUMA (リモートノード) へのメモリアクセスが可能となっています。
リモートノードへのアクセス速度ははローカルノードと比較すると低い値になりますので、できるだけローカルノードで処理をすることで効率的なメモリアクセスをすることができる仕組みとなっています。

Windows Server 2008 R2 と Windows 7 における NUMA の新しいサポート で NUMA の API について記載がされています。
SQL Server ではエンジンのほうで NUMA への処理が透過で行われますので、この辺の API を把握していなくても使用することができますが、プログラムレベルで NUMA の対応をされる場合には、このあたりの API を確認しておくとよいのかもしれないですね。

 

■SQL Server の 2 種類の NUMA シナリオ


SQL Server では 2 種類の NUMA シナリオがあり、ハードウェア NUMA (ハード NUMA) とソフトウェア NUMA (ソフト NUMA) を使用することができます。

■ハード NUMA

ハード NUMA はハードウェアレベルで NUMA に対応している環境で使用することができます。
Windows Server 2012 以降の Hyper-V であれば、ゲスト OS でハード NUMA の構成を使用することができます。
Azure の PaaS の機能である仮想マシンでも NUMA 対応のインスタンスを用意することができますので、ハードウェアを持っていない場合はこの辺を使用するとよいかと思います。
# 以前、Windows Azure 仮想マシンで NUMA 構成の環境が利用できます で少し書いた内容になります。

Azure の A7 インスタンスで確認をしてみます。
SSMS のプロパティを確認すると [NumaNode0][NumaNode1] の 2 つの NUMA ノードで構成されていることが確認できます。
# 各 NUMA ノードでは 4 コアの CPU が使われています。
image

DMV で確認をしても同様の結果を取得することができます。

select parent_node_id,scheduler_id,cpu_id
from sys.dm_os_schedulers
where parent_node_id <= 16 and scheduler_id <= 256

image

 

■ソフト NUMA

ソフト NUMA は SQL Server のソフトウェアレベルで NUMA に対応させる方法となります。

これは、ハードウェアレベルで NUMA に対応していなくても使用することができます。

ソフト NUMA の設定に関しては、ソフト NUMA を使用するように SQL Server を構成する方法 (SQL Server) に記載されています。

レジストリを設定することで、CPU を NUMA ノードとして擬似的に分割させる方法になります。

こちらは私の検証環境の CPU の状態になります。

SSMS のサーバーのプロパティから NUMA ノードを確認することができるのですが、この環境では [NumaNode0] しか存在していません。

image

DMV からも確認してみます。

image

この環境は 1 CPU / 4 コア (HT で 8 コア) の環境ですので、1 ノードとなっていることが確認できます。

この環境にソフト NUMA の設定をしてみます。

New-Item -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode0" -Force
New-ItemProperty -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode0" -Name "CPUMask" -PropertyType Dword -Value 0x0F
New-ItemProperty -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode0" -Name "Group" -PropertyType Dword -Value 0
New-Item -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode1" -Force
New-ItemProperty -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode1" -Name "CPUMask" -PropertyType Dword -Value 0xF0
New-ItemProperty -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server110NodeConfigurationNode1" -Name "Group" -PropertyType Dword -Value 0
Restart-Service "MSSQL`$SQL2012" -Force


SSMS のプロパティの NUMA の状況は変わらないのですが、 DMV から情報を取得した場合は、NUMA ノードが 2 つ担っていることが確認できます。

imageimage

 

 

 

 

 

 

 

 

 

以下のクエリで実行中の要求の情報を取得してみます。

select command, count(*) from sys.dm_exec_requests
where session_id < 50
GROUP BY command
ORDER BY command ASC

左からハード NUMA (Azure A7 インスタンス) / ソフト NUMA 未設定の検証環境 / ソフト NUMA 設定済みの検証環境 となります。

ハード NUMA の場合はレイジーライター (LAZY WRITER) のスレッドが NUMA ノードにつき 1 つ起動されているのですが、それ以外の場合は 1 つとなっています。

ソフト NUMA の有無では、リソースモニター (RESOURCE MONITOR) とタスクマネージャー (TASK MANAGER) のスレッド数に差が出ているようですね。

imageimageimage

この辺が、ソフト NUMA を使用するように SQL Server を構成する方法 (SQL Server)

多くの I/O が発生するインスタンス A には、2 つの I/O スレッドと 1 つのレイジー ライター スレッドが存在するようになります。

レイジー ライター スレッドは、物理 NUMA メモリ ノードの SQL OS のビューに関連付けられています。  したがって、ハードウェアが物理 NUMA ノードとして表すものは、作成されるレイジー ライター スレッドの数と一致します。 詳細については、「動作方法: ソフト NUMA、I/O 完了スレッド、レイジー ライター ワーカー、およびメモリ ノード」を参照してください。

になってくるかと。

 

■NUMA ノード間のアクセスコスト


ここで一度 NUMA ノード間のアクセスコストについてみていきたいと思います。

NUMA ノードのアクセスコストを見るには Sysinternals の Coreinfo を使用した方法が有名かと思います。

Coreinfo には [-m] というオプションがあり、NUMA ノード間のアクセスコストを確認することができます。

image

NUMA ノード 0 と NUMA ノード 0 のアクセスはローカルノードアクセスですので 1.0 となっています。

リモートノード (NUMA ノード 1) にアクセスする場合にはアクセスコストが高くなるということがここから確認できます。

Coreinfo では他にも以下のような情報を取得できます。

各 NUMA ノードでどの CPU が使用されているかを確認することができます。

image

[/s] オプションの情報と組み合わせればどのソケットを使用しているかもわかるかと。

image

ほかにリモートへのアクセス状態を取得する方法としては、 sys.dm_os_memory_node_access_stats というアンドキュメントな DMV があります。

通常、この DMV を検索しても集計値は 0 となっています。

image

トレースフラグ 842 (-T842 / TF842) を設定することで情報の収集が開始されます。

なお、この手の収集は負荷がかかりますので、本番環境では有効にせずに検証環境での利用は問題発生時の一時的な情報収集にとどめておく必要があります。

スタートアップオプションのトレースフラグではなくグローバルトレースフラグの設定で取得ができますので、稼働中にサービスの再起動を行わずに収集を開始することができます。

DBCC TRACEON (842, -1)
-- DBCC TRACESTATUS
-- DBCC TRACEOFF (842, -1)

これを有効にすることで、SQL Server としてのローカルノードとリモートノードの目盛のアクセス状態を確認することができます。

image

 

■NUMA ノードを意識した情報の取得


先ほどはローカルノードとリモートノードのアクセスについて注視してみましたが、OS / SQL Server でも NUMA ノードを意識した情報を取得することが可能です。

OS

タスクマネージャーで NUMA ノード単位での CPU の使用率を取得することができるようになっています。

image

リソースモニターからも NUMA ノードを意識した CPU 利用状況の情報を取得することができます。

image

パフォーマンスモニターについては Windows Server 2012 以降は NUMA ノードを意識した情報取得が可能です。

# 2008 R2 も タスクマネージャーとリソースモニターはNUMA ノード単位で取得できるようです。

タスクマネージャーやリソースモニターではメモリについては NUMA ノードを意識した使用状況は取得できないですが、パフォーマンスモニターからは、メモリの使用状況を NUMA ノード単位で確認をすることができます。

image

 

SQL Server

SQL Server の NUMA の情報はいろいろな取得方法があるのでさわりだけまとめておきたいと思います。

SQL Server: Buffer Node を取得することで NUMA ノード単位のバッファプールの利用状況を確認することができます。

SQL Server ではバッファプールの情報を取得する際に、SQL Server:Buffer Manager を取得しますが、この情報を NUMA ノード単位に確認することができます。

image

メモリの使用状況については SQL Server:Memory Node からも取得することができます。

これについては SQL Server:Memory Manager を NUMA ノード単位に確認する感じでしょうか。

image

DMV からも様々な情報を取得することができます。

ここから、NUMA のメモリノードとして何ノードを認識しており、各ノードでスケジューラー (CPU コア) がどれだけ認識しているかを確認することができます。

sys.dm_os_nodes からは SQLOS (UMS) が認識している NUMA ノードの情報を取得することができます。

select node_id, node_state_desc,memory_node_id,
cpu_affinity_mask, online_scheduler_count
from sys.dm_os_nodes

image

sys.dm_os_nodes はスケジューラー (CPU) 側の情報取得になりますが、メモリに関しては sys.dm_os_memory_nodes  で確認をすることができます。

今回は Max Server Memory を 50GB に設定して、TF834指定しているため、起動時にコミット済みメモリとして各ノードに 25GB 程度ずつ確保されているのが確認できます。

select memory_node_id, virtual_address_space_reserved_kb,
virtual_address_space_committed_kb, locked_page_allocations_kb
from sys.dm_os_memory_nodes

image

 

■NUMA ノードとバッファキャッシュ


NUMA ノードとバッファキャッシュ (今回はデータキャッシュ部分) の使用状況ですがさまざまな要因によってローカルノードのアクセスかリモートノードのアクセスかが変わってきます。

SQL Server のデフォルトの設定では、

  • PROCESS AFFINITY CPU = AUTO
  • max degree of parallelism = 0

となっています。

この状態で、4,000 万件格納しているテーブル (50GB 程度) に COUNT(*) を実行してみます。

実行中に各種情報を以下のクエリを実行して取得します。

DBCC TRACEON (842, -1)
select session_id, scheduler_id, wait_type
from sys.dm_exec_requests
where session_id > 50 and session_id <> @@SPID
select memory_node_id, virtual_address_space_reserved_kb,
virtual_address_space_committed_kb, locked_page_allocations_kb
from sys.dm_os_memory_nodes
where memory_node_id < 64
select database_id, numa_node, COUNT(*) * 8 / 1024 AS memory_MB
from sys.dm_os_buffer_descriptors
where database_id = DB_ID(N'TESTDB')
group by database_id, numa_node

COUNT のクエリを 30 秒ほど実行して取得した結果がこちらになります。

image

それでは MAXDOP を 4 (NUMA ノード内のスケジューラー数) にして同様のクエリを実行してみます。

image

MAXDOP をスケジューラー内に抑えることで、リモートノードへのアクセスが抑制されているようです。

SQL Server ではどのスケジューラーが使用されて処理がされているかを、sys.dm_exec_requests から確認することができます。上記の結果では scheduler_id が [5] になっています。

スケジュラーとノードの対応がこちらになります。

厳密にテストする場合には、NUMA ノードへの TCP/IP ポートのマッピング を使用することになるかと思います。

SQL Server ではデフォルトでは 1 IP で一つの TCP ポートを使用する設定になりますので、複数の TCP ポートを設定できるように 複数の TCP ポートでリッスンするデータベース エンジンの構成 の方法 でエンドポイントを追加します。

USE master
GO
CREATE ENDPOINT [NUMA Node 0]
STATE = STARTED
AS TCP   (LISTENER_PORT = 1500, LISTENER_IP =ALL) FOR TSQL()
CREATE ENDPOINT [NUMA Node 1]
STATE = STARTED
AS TCP   (LISTENER_PORT = 1501, LISTENER_IP =ALL) FOR TSQL()
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
GRANT CONNECT ON ENDPOINT::[NUMA Node 0] to [public]
GRANT CONNECT ON ENDPOINT::[NUMA Node 1] to [public]
select * from sys.endpoints

エンドポイントを追加したら、NUMA ノードをマッピングする形で SQL Server 構成マネージャーでポートの設定を行いサービスを再起動します。

image

そうすると接続するポートによって NUMA ノードを明示的に分けることができるようになります。

厳密にはどのスケジューラーノードを使用するかを固定化するための設定となるようです。


スケジューラーノードとメモリノードの使い方がきちんと理解できておらず、メモリノード 1 のメモリを使用するパターンが試せていないのですよね。

スケジューラーノード 1 のスケジューラーに接続した場合、メモリノード 0 のメモリが利用されるのがよいのか (メモリノード 1 にキャッシュされなくてよいのか) が、きちんと理解できていません。

データを INSERT した場合は、スケジューラーノードとメモリノードが一致するような動作にはなっているデータを取得できることがありました。

# たまたまなこういうデータが取れただけな気がしますが。

左がスケジューラーノード 0 のスケジューラに接続してデータの挿入、右がスケジューラーノード 1 のスケジューラーに接続してデータの挿入を行った場合になります。

imageimage

大半の書き込みのアクセスがスケジューラーノードと同じメモリノードに対して行われていることが確認できます。

読み取りの捜査でこのようなデータをきちんととるための方法が分かれば、ローカルノードとリモートノードへのアクセスについて理解をすることができるのですがまだ、そこまでは実践できておらず…。

この動作を見ていると私がスケジューラーの動作を間違えて理解しているように思えてきました。

# 実行ごとに 0→1 / 1→0 のアクセスが交互に増えていく理由がわかりません。。。

細かな動作については、熊澤さん (Dr.K) の C11,12 SQL Server 2012 Performance Tuning by Yukio Kumazawa で語られていた、Windows ノードと SQL OS ノードで 0/1 が逆になるということについても考えないといけないと思うのですが、この辺がまだきちんと整理できていません。

image

 

■NUMA ノードを SQL Server レベルで考慮しないトレースフラグ


How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes で紹介されていますが、NUMA を SQL Server レベルでは考慮しないような動作にすることができるトレースフラグもあります。

TF8015 がそのためのトレースフラグとなります。

このトレースフラグを設定して SQL Server を起動すると NUMA ノードではなくフラットに管理をするように設定を変更することができます。

左がトレースフラグを設定していないもの、右が設定したものになります。

imageimage

imageimage

SQL Server としては、NUMA ノードを意識しないフラットな管理 (NUMA ノード 0 のみ) になっていることが確認できます。

ほかにも NUMA 関連の情報は探すとたくさん出てくるかと思います。

# 主に英語ですが。。。

私もまだまだ勉強中ですので、この辺の動作をきちんと理解できているわけではありませんが、調べる際の足掛かりになればと。

Share

Written by Masayuki.Ozawa

1月 2nd, 2014 at 3:22 pm

Posted in SQL Server

Tagged with

Leave a Reply