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 디스크 sybsystem과 공유 됨)을 가진 가상 시스템이라는 점을 감안할 때 물리적 시스템에서 처리량이 상당히 향상 될 것으로 기대했지만 실제로 발생하지 않았거나 성능 향상은 무시할 정도였습니다. 아마도 물리적 인 기계에 25 % 빠른 삽입을 할 수 있습니다. 단일 드라이브 (벤치마킹 소프트웨어로 측정)보다 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 ID가 없습니다.
  • 데이터를 삽입하는 스레드는 각각 16 개의 모든 테이블을 순환합니다. 따라서 각 대량 삽입 작업이 자체 테이블을 사용한다는 것을 거의 보장합니다.

벌크 삽입시 20 % 향상되었습니다. CPU 코어, LAN 인터페이스, 드라이브 I / O는 최대화되지 않았으며 최대 용량의 약 25 %에서 사용되었습니다.

UPDATE4 나는 그것이 지금 얻는만큼 좋다고 생각합니다. 다음 기술을 사용하여 합리적인 속도로 인서트를 푸시 할 수있었습니다.

  • 각 대량 삽입물은 자체 테이블로 이동 한 다음 결과를 주요 테이블로 병합합니다.
  • 모든 대량 삽입물에 대해 테이블이 새로 생성되고 테이블 잠금이 사용됩니다.
  • DataTable 대신 여기에서 IDataReader 구현 사용했습니다.
  • 여러 클라이언트에서 수행 된 대량 삽입
  • 각 클라이언트는 개별 기가비트 VLAN을 사용하여 SQL에 액세스합니다.
  • 메인 테이블에 접근하는 사이드 프로세스는 NOLOCK 옵션을 사용한다.
  • 경합을 제거하기 위해 sys.dm_os_wait_stats 및 sys.dm_os_latch_stats를 검사했습니다.

이 질문에 답을 얻은 사람은이 시점에서 결정하기가 힘듭니다. "답변"을받지 못한 여러분, 사과드립니다. 정말 어려운 결정이었고, 모두에게 감사드립니다.

UPDATE5 : 다음 항목에서 최적화를 사용할 수 있습니다.

방대한 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는 합법적입니까? 예, 이유를 알아보십시오.