SQL Server上でSqlBulkCopyでエンベロープをプッシュする際の推奨事項が必要

bulkinsert database-performance scalability sqlbulkcopy sql-server

質問

私は、SQLデータベースに膨大な量のデータを受け取ることができるようになっているアプリケーションの設計を行っています。私はbigintのIDを持つ単一のテーブルとしてデータベースの構造を設計しました。次のようなものです。

CREATE TABLE MainTable
(
   _id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    field1, field2, ...
)

私は質問を実行しようとしているのですが、それは私の質問とは無関係なので、私は省略します。

私はSqlBulkCopyを使用してこのテーブルにデータを挿入するプロトタイプを作成しました。それは実験室でうまくいくように見えました。私は〜3Kレコード/秒の速度で数千万のレコードを挿入することができました(完全なレコード自体がかなり大きく、〜4K)。このテーブルの唯一のインデックスはbigintの自動インクリメントであるため、大量の行がプッシュされた後も減速は見られませんでした。

ラボSQLサーバーが比較的弱い構成(4Gb RAM、他のVMディスクシステムと共有)の仮想マシンであることを考慮すると、物理マシンではかなり高いスループットを期待していましたが、パフォーマンスの向上はごくわずかでした。おそらく、物理マシン上で25%高速にインサートすることができます。 1台のドライブ(ベンチマークソフトウェアで測定)の3倍のパフォーマンスを達成した3ドライブRAID0を設定した後でも、改善はありませんでした。基本的には、より高速のドライブサブシステム、専用の物理CPUとダブルRAMはほとんど性能を向上させませんでした。

私はAzureの最大インスタンス(8コア、16Gb)を使ってテストを繰り返しましたが、同じ結果が得られました。だから、コア数を増やしてもインサート速度は変わらなかった。

現時点では、私は、重要な性能向上なしに、以下のソフトウェアパラメータを試してみました。

  • SqlBulkInsert.BatchSizeパラメータの変更
  • 複数のスレッドから同時に挿入し、スレッド数を調整する
  • SqlBulkInsertでテーブルロックオプションを使用する
  • 共用メモリ・ドライバを使用してローカル・プロセスから挿入することによりネットワーク待ち時間をなくす

私はパフォーマンスを少なくとも2〜3倍に増やそうとしています。私の元の考えは、ハードウェアを増やすと鳴き声が出ることになりましたが、今のところはそうではありません。

だから、誰かが私を推薦することができます:

  • ここでボトルネックと思われるリソースはどれですか?確認する方法?
  • 単一のSQLサーバーシステムがあると考えて、確実にスケーラブルな一括挿入の改善を試みる方法論はありますか?

更新私はロードアプリが問題ではないことを確信しています。それは別のスレッドの一時的なキューにレコードを作成するので、挿入があると次のようになります(単純化):

===>start logging time
int batchCount = (queue.Count - 1) / targetBatchSize + 1;
Enumerable.Range(0, batchCount).AsParallel().
    WithDegreeOfParallelism(MAX_DEGREE_OF_PARALLELISM).ForAll(i =>
{
    var batch = queue.Skip(i * targetBatchSize).Take(targetBatchSize);
    var data = MYRECORDTYPE.MakeDataTable(batch);
    var bcp = GetBulkCopy();
    bcp.WriteToServer(data);
});
====> end loging time

タイミングが記録され、キューを作成する部分は決して重要なチャンクをとらない

UPDATE2私は、そのサイクルでの各操作の所要時間を収集し、レイアウトは次のように実装しました。

  • queue.Skip().Take() - 無視できる
  • MakeDataTable(batch) - 10%
  • GetBulkCopy() - 無視できる
  • WriteToServer(data) - 90%

UPDATE3私はSQLの標準バージョンを設計しているので、エンタープライズ版でしか利用できないため、パーティショニングに頼ることはできません。しかし、私はパーティション方式の変種を試しました:

  • 作成された16のファイルグループ(G0〜G15)、
  • 個々のグループにバインドされた16個のテーブルのみを挿入用に作成しました(T0〜T15)。テーブルにはインデックスがまったくなく、クラスタ化されたint同一性もありません。
  • データを挿入するスレッドは、それぞれ16個のテーブルすべてを循環します。これにより、各バルク挿入操作で独自のテーブルが使用されることがほとんど保証されます

これにより、バルクインサートの〜20%の改善が得られました。 CPUコア、LANインタフェース、ドライブI / Oは最大化されておらず、最大容量の約25%で使用されていました。

UPDATE4私は今それが得られるほど良いと思う。私は、以下の手法を使ってインサートを合理的な速度にプッシュすることができました:

  • 各バルクインサートはそれ自身のテーブルに入り、結果はメインのものにマージされます
  • 一括挿入ごとにテーブルが新しく再作成され、テーブルロックが使用されます
  • DataTableではなくIDataReaderの実装をここから使用します。
  • 複数のクライアントからの一括挿入
  • 各クライアントは、個々のギガビットVLANを使用してSQLにアクセスしています
  • メインテーブルにアクセスする側のプロセスはNOLOCKオプションを使用します
  • 競合を解消するためにsys.dm_os_wait_statsとsys.dm_os_latch_statsを調べました

私はこの時点で答えられた質問のクレジットを得た人を決定するのは難しい時期です。あなたが「答えた」人がいない人は、私は謝罪します。それは本当に厳しい決定でした。

UPDATE5 :以下の項目はいくつかの最適化を使用できます:

  • DataTableではなくIDataReaderの実装をここから使用します。

大規模なCPUコア数を持つマシン上でプログラムを実行しない限り、いくつかのリファクタリングを使用することができます。 get / setメソッドを生成するためにリフレクションを使用しているので、それがCPUの大きな負荷になります。パフォーマンスがキーの場合は、リフレクションを使用する代わりに、IDataReaderを手動でコーディングしてコンパイルするときに多くのパフォーマンスを追加します

受け入れられた回答

バルクロード用のSQL Serverのチューニングに関する推奨事項については、MSのデータロードおよびパフォーマンスガイドのペーパー、およびオンラインブックからのバルクインポートの最適化に関するガイドラインを参照してください。 SQL Serverからのバルクロードに重点を置いていますが、ほとんどのアドバイスはクライアントAPIを使用したバルクロードに適用されます。このペーパーはSQL 2008に適用されます。あなたが目標としているSQL Serverのバージョン
どちらも詳細な情報を持っています。しかし、いくつかのハイライト:

  • 一括操作を最小限にログします。バルクログまたはシンプルリカバリを使用します。 traceflag 610を有効にする必要があるかもしれません(ただし、これについては注意が必要です)
  • バッチサイズを調整する
  • ターゲット表をパーティション化することを検討する
  • バルクロード中にインデックスを削除することを検討する

このフローチャートのデータロードとパフォーマンスガイドここに画像の説明を入力

あなたの実験でIOが制限ではないかもしれないことが示唆されているので、他の人が言っているように、ボトルネックの原因を確立するためにいくつかの性能カウンターを取得する必要があります。 データロードとパフォーマンスガイドには、監視するSQL待機タイプとパフォーマンスカウンタのリストが含まれています(リンクするドキュメントにはアンカーはありませんが、ドキュメントの「バルク負荷の最適化」の項では、ドキュメントの約75%です)。

更新

リンクを見つけるのに私はしばらく時間がかかりましたが、Thomas KejserによるこのSQLBitsの講演は価値があります。 スライド全体を見る時間がない場合はスライドが利用できます。ここにリンクされている資料の一部を繰り返しますが、特定のパフォーマンスカウンターの発生率が高いことに対処する方法については、他にもいくつかの提案があります。


人気のある回答

SqlBulkCopyに関連するパフォーマンスを考慮するいくつかの要素について説明するAlberto Ferrari SqlBulkCopyパフォーマンス分析レポートを勉強する機会があったかどうかはわかりません。私はその論文で議論されたことの多くは、最初に試してみる価値があると言います。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ