SQL Server 2008 データウェアハウスシナリオ Tips and Tricks Part 2
I would like to introduce briefly how SSIS 2008 have achieved great performance on loading TB data into SQL Server database to Japanese users. Here is Japanese SQL Server development team’s blog…
マイクロソフトの植田です。
前回のポストに引き続き「大規模データウェアハウス」シナリオ検証で得られたTipsや注意点についてご紹介していきたいと思います。
今回はSQL Server Integration Serviceを使用したデータロードを取り上げたいと思います。データロードを行う局面はいろいろあると思いますが、ここでは初期ロードのパフォーマンスについてお話したいと思います。
米国のSQL Server パフォーマンスチームが今年の初めに行った検証では、わずか30分の間に1.18TBものデータをロードしたという結果が公開されています。
https://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx
主なHW構成は以下のようになっています。
ü RDBMSサーバー（1台）
Ø Dual-coreのCPUを32機搭載（64コア）、256GB RAM
Ø 8ノードのハードウェアNUMA構成（1ノードあたりCPU8コア、32GB RAM）
Ø 各ノードが4GbitのDual-portのHBA、Giga-NICを搭載
Ø DB用ストレージには8スピンドルでRAID10を組んだLUNを17個使用
ü SSISサーバー（4台）
Ø Quad-coreのCPUを2機搭載（8コア）、4GB RAM
Ø 4Gbit Single-portのHBA×1、Giga-NIC×2
Ø フラットファイル用のストレージとして1GbitのFibre Channelで接続されたSANを使用
1TB、30分を実現するポイントは以下と考えています。
1. CPUコア数に応じて最適な同時実行多重度を決定し、CPUの利用効率をできるだけ高くする
2. SSISパッケージを別筐体で実行し、CPU負荷を分散させる
3. パーティション分割したテーブルを使用し、データロードをパーティション単位で行う
4. SSISサーバーと各NUMAノードを専用のネットワークで結び（1つのSSISサーバーは2つのNUMAノードに接続）、ノードの8つのCPU(コア)の内、1つをネットワーク処理専用に割り当て、ネットワーク負荷を軽減
Ø Soft-NUMAを構成し、1つのパーティションに対するデータロードが1つのCPU(コア)で行われるように設定する
ディスクの性能が低くない限り、一括データロードはCPUに負荷がかかる処理となります。一般的に、一つのデータロードプロセス（インスタンス）を一つのCPUで実行させるようにするのがベストプラクティスと言われています。上記の検証では56個のパーティションを用意し、56個のロードプロセスを同時に実行することにより最高のパフォーマンスを得ていました。64プロセスでない理由は、上記4に書かれているとおり、ノードあたり1つのCPU(コア)をネットワーク処理専用に割り当てているからです。また、SQL Server 以外にSSISパッケージを実行するDTExecのプロセスもCPUを消費します。従ってデータベースを保持するサーバーと、データロードを行うサーバーを分けた方がSQL Serverにより多くのCPUリソースを割り当てることができます。
データベースを保持するSQL Serverが動作するサーバーと、データロードを行うサーバーを別筐体に分けた場合、その間を結ぶネットワークがボトルネックになるケースが多く見られます。今回の検証では8つのネットワークインタフェースから効率的にデータが流れてくるように設計し、各ノードで1つのCPU（コア）がネットワーク処理（NICからの割り込み処理）を専門で行うように設定されています。この設定はWindows Server 2008、および、Interrupt-Affinity Policyツールを使用して行うことができます。Interrupt-Affinity Policyツールの詳細についてはWindows Hardware Developer CentralのWebサイトをご参照ください：Windows Hardware Developer Central。また、その他のCPU（コア）は同一ノード内のネットワーク処理専用CPU（コア）が受け付けたリクエストを処理するようにSoft-NUMAが設定されています。このように設定することにより、ネットワーク処理からローディング処理までを同一ノード内に局所化でき、オーバーヘッドが最小限に抑えられます。
Soft-NUMAの設定例
（CPUへのNUMAノードのマッピング）
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0]
"CPUMaks"=hex:01
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1]
"CPUMaks"=hex:02
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2]
"CPUMaks"=hex:04
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node3]
"CPUMaks"=hex:08
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node4]
"CPUMaks"=hex:10
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node5]
"CPUMaks"=hex:20
：
：
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node62]
"CPUMaks"=hex:00,00,00,00,00,00,00,40
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node63]
"CPUMaks"=hex:00,00,00,00,00,00,00,80
（TCP/IPポートへのNUMAノードのマッピング）
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.STAB1300_04\MSSQLSERVER\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"="2000[0x00000001],2001[0x00000002],2002[0x00000004],2003[0x00000008],2004[0x00000010],2005[0x00000020],
…
2061[0x2000000000000000],2062[0x4000000000000000],2063[0x8000000000000000]"
"TcpDynamicPorts"=""
"DisplayName"="Any IP Address“
Soft-NUMAの設定方法の詳細についてはオンラインブックをご参照ください。
ソフトNUMAを使用するようにSQL Serverを構成する方法
上記のように設定することで、SSISパッケージにおいて接続先のTCPポートを指定することにより、データロードプロセスを実行するCPUを選択することができます。
SSISデータロードパッケージ実行例
(IPアドレス10．0．0．1のポート2001に接続)
DTExec.exe /Conn
ConnectionString;"Data Source=10.0.0.1,2001; Initial Catalog=TPCHdestination; Provider=SQLNCLI10.1; Integrated Security=SSPI; PacketSize=32767;AutoTranslate=False;"
/F "C:\SSISpackages\DataLoadStream.dtsx"
以上が1TBのデータを約30分でロード完了するためのポイントになりますが、これらの設定は今回のデータロードに特化してオプティマイズされており、実際の環境に適応するのはそれほど容易ではありません。また、インデックスの作成など、実際の運用では不可欠と思われる項目もカバーされていません。私たちが日本で行ったDWHの検証では構成を単純にし、データベースサーバーには4Way（Quad-core=16コア）の汎用的なマシンを使用して、SSISとSQL Server DBエンジンを同居させる構成でデータロードパフォーマンスを測定しています。詳しい内容については今後公開されるホワイトペーパーの中で扱う予定ですが、簡単に結果だけお伝えすると、1.12TB（12億件）のデータをロードする処理を97分で完了することができました。1秒あたりのパフォーマンスに換算すると約201KB/sec、約207,000件/secとなります。また、3つのクラスタ化インデックスを作成してデータロードを行う場合は、146分で全ての処理を完了することができました。
以上ご参考にしていただければ幸いです。
次回は「緩やかに変化するディメンジョン」を取り上げたいと思います。
コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。