http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
번역 : 박노철(maceo.park@gmail.com)
이 프로젝트는 SQL Server 2005 엔터프라이즈 에디션 (SP1 beta) 에서 60개의 병렬 입력파일에 대해서 BULK INSERT 를 사용해서 수행되었다. 각 파일의 크기는 약 16.67GB 이다. 최고 기록은 50분이다. HP는 TPC-C 테스트에 사용하는 기계를 하룻밤 빌려주었다. 1.6GHZ Itanuum2 CPU 64개가 달린 수퍼돔이었다. insert 테스트를 위해서 256GB 램을 장착했다. HP SAN 설정은 초당 14GB의 스루풋을 보여주었다. http://tpc.org/tpcc/results/tpcc_result_detail.asp?id=103082701 에서 상세한 설정을 찾아볼 수 있다.
BEST PRACTICES and LESSIONS LEARNED
- 최대한 많은 수의 CPU를 사용하라. 32개의 CPU가 있으면 32개를 동시에 로딩하라. 8개면 8개를 로딩하라.
- 만약 입력 파일을 생성하는 것을 조절할 수 있는 권한이 있으면 병렬로 로드하려는 스레드 갯수로 나눈 수만큼 파일크기가 동일하게 조절하라. switch 파티션 전략을 사용하고 싶으면 모든 레코드들이 하나의 파티션에 속하는 것을 확인하라.
- 만약 작업을 SQL Server 머신에서 수행한다면 BCP 대신에 BULK INSERT 를 사용하라.
- 추가적인 8~10% 의 속도개선을 위해서 테이블 파티셔닝을 사용하라. 단, 입력 파일들이 파티셔닝 함수에 적합하다는 것이 보장되어야 한다. (하나의 파일에 있는 모든 레코드가 같은 파티션에 있어야 함을 의미)
- TABLOCK 을 사용하라.
- 만약 복수개의 스트림을 하나의 테이블에 로드한다면 ROW PER BATCH = 2500 이나 그 근방의 값을 사용하라.
TEST SETUP
파일들은 database 와 동일한 SAN에 존재했다. 디스크 어레이는 달랐다. 하나의 드라이브 어레이에 12개의 파일들이 있었고 60개의 입력파일들을 핸들링하기 위해서 5개의 디스크 어레이가 필요했다. DB는 두개의 파일그룹과 96개의 파일을 가지고 있다. insert 테스트를 위해 사용된 테이블은 LINEITEM 이고, 자기 자신의 파일그룹에 존재하고 있다. 디스크상에는 바깥쪽 트랙에 위치한다.
DB는 BULK LOGGED 모드였다. 따라서 트랜잭션 로깅은 거의 일어나지 않았다. 익스텐트 할당과 관련된 메타데이터는 로깅되었지만 개개의 페이지 변화나 레코드 인서트는 기록되지 않았다.
입력파일을 | 를 컬럼 델미미터로 사용하는 문자열 파일이다. 이러한 flatfile 들은 DBGen 을 이용해 만들어졌다. DBGen 은 TPC 협회에서 제공하는 표준 도구다. flatfile 는 깨끗한 것으로 간주할 수 있다. 행길이는 가변 길이였는데, 평균 130바이트 정도 된다.
최종 설정에서 60개의 job을 만들었고, 각각은 하나의 BULK INSERT 를 담고 있었다. 굳이 여러개의 쿼리 윈도우를 열지않고 작업을 컨트롤하기 위해서 이렇게 했다. 각각의 job 을 컨트롤하기 위해서 sp_startjob 이 사용되었다.
최초의 테스트는 ROWS PER BATCH=2500 으로 해서 하나의 테이블에 대해서 수행되었다. 40개의 스레드가 시작되기 전에는 블러킹이 거의 없었다. 배치 사이즈가 2500이었기 때문에 락 유지시간은 상당히 짧았다. Data Management View 를 통해서 보니 하나의 테이블에 대한 익스텐트 할당때문에 락이 잡혔다. 이 테스트를 조금씩 바꿔가면서 해보았는데 대체로 65분 안에 끝났다. 익스텐트 할당으로 인한 대기를 피하기 위해서 다음 테스트는 복수개의 테이블에 대해서 수행되었다.
다음 테스트는 각각의 입력에 대해서 각각의 테이블을 사용하는 것으로 수행되었다. 각 테이블은 하나의 파티션을 가지고 있었고 최종적으로 파티션들은 60개의 파티션을 가진 하나의 거대한 테이블로 switch in 되었다. (스위칭은 SQL 2005 테이블 파티셔닝에서 사용되는 새로운 용어이다) 스위칭은 메타데이터 스위치에 불과하기 때문에 매우 빠르다. 테스트결과 한번 스위칭하는데 평균 22~35ms 정도 걸렸다. 여기서 어려운 점은 각 입력파일이 정말로 그 파티션에 속하는지를 확인하는 작업이다. 이 테스트에서 배치사이즈는 테스트결과에 어떤 차이를 만들어내지 못했다. 왜냐하면 테이블당 하나의 BULK INSERT 스레드가 사용되기 때문이다. TABLOCK 은 잠금 관리를 회피하기 위해서 매우 중요하다.
최종결과를 볼 때 각각의 테이블로 분리함으로써 8~10%의 향상을 이룰 수 있었다. 이게 바로 1TB를 로드하는데 50분이 걸린 시나리오다. 인덱스 빌드나 파티션 검증을 위한 제약조건 생성을 위한 시간은 없았다. 다음 테스트로 미룬다.
CPU BOTTLENECK
주된 제약조건은 CPU였다. IO가 아니었다. SAN은 초당 14GB의 스루풋을 가진다. SQLIO를 사용해서 드라이브의 최대 스루풋을 측정해볼 수 있다. 우리의 테스트에서 BULK INSERT를 돌리는 60개의 스레드에 대해서 최대 초당 333MB의 쓰기 작업이 일어났다. 이는 초당 210만개 조금 넘게 BULK COPY하는 것을 의미한다. PERFMON을 사용하여 이 카운터를 관찰했다.
하나의 BULK INSERT 스레드는 유휴 CPU 를 99~100% 사용한다. 다음 job은 다른 CPU를 사용했다. 한참 바쁜 CPU에 스케쥴링되지는 않는 것으로 보였다.
TEST PROCESS
첫번째 테스트는 한번에 하나의 job 을 시작해서 정상상태에서 도달해서 작동하도록 내버려 두는 것으로 시작했다. 모든 측정은 기록되었고 다른 job이 시작되었다. 관찰하고 있던 카운터값의 증가는 매우 예측가능한 수준이었다. 그러나 정확하게 선형적이지는 않았다. 즉, 두번째 job이 첫번째 job이 도는 속도로 돌지는 않았다. 사실, 로그곡선의 형태로 판명되었고 60개의 job이 동시에 돌 때는 아주 평평했다.
첫번째 테스트는 BCP가 CPU를 몇%정도 더 사용한다는 것을 알아채기 전까지는 BCP를 사용해서 진행되었다. 55개의 job이 실행될 때까지 CPU 총사용량은 100%였다. 그래서 BULK INSERT 로 바꿨는데, 이렇게 하면 import 스레드가 SQL Server process 안에서 돌아간다. BULK INSERT로 바꾼 후에 60개의 job을 동시에 돌릴 수 있었고, 60개를 돌릴때까지 스루풋은 계속 증가했다. 이때 CPU사용량은 95%를 유지했다. 61~64개의 job은 60개일때와 스루풋이 거의 똑같다.
첫번째 테스트 도중, 8~9GB짜리 파일이 96개 있었다. 동시에 60개의 job을 돌리지만 않는다면, 첫번째 48개의 파일들을 30분안에 로딩할 수 있었고 다른 49개도 마찬가지였다. 60분에 1TB를 로딩하는 것은 꽤 훌륭한 결과다.
일단 CPU가 제약조건이라는 것을 안 이후로, 60개의 파일을 16.67GB로 만들었다. 이렇게 하니까 최고 50분이 나왔다. 모든 고객들이 입력파일에 대한 세세한 권한을 가질 수는 없기 때문에 이런 테스트는 만들어진 측면이 있다. 초반부에 언급했듯이 입력파일들은 큰 테이블을 위해서 설계된 최종 파티셔닝 scheme 에 정확하게 들어맞았다.
최종 테스트는 CPU 밸런싱이 만족할만한 수준에 있는지 확인하기 위해 60개의 job을 동시에 돌리는 것이었다. 결과는 괜찮았다. 이는 동시에 두개의 BULK INSERT 스레드가 하나의 CPU를 사용하지 않음을 의미한다. SQL product team의 개발자들은 UMS스케줄러당 하나의 bulk command만 수행하도록 UMS스케줄러를 특별히 체크한다고 이야기해주었다. 물론 CPU갯수보다 더 많은 명령을 돌리면 CPU당 복수개의 bulk command를 할당할 것이다.
NOT FINISHED YET
여기에서 우리는 모든 데이트를 60개의 분리된 테이블에 가지고 있었다. 이것은 로컬 파티션드 뷰를 사용하기 원하는 사람들에게는 좋은 시나리오다. (테이블들에 대해서 union all 을 하는 하나의 뷰를 만드는 것)
테이블 파티셔닝을 사용하기 원하는 고객들은 모든 파티션들을 하나의 테이블로 모으기 위해서 몇가지 단계를 더 거쳐야 한다. 다음과 같다.
- Create a File Scheme 파일 Scheme 을 생성
- Create a Partition Function 파티션 함수를 생성
- Create a clustered index on each file 각 파일에 클러스터드 인덱스를 생성
- Create the check constraint that will be used for partitioning. 파티셔닝을 위해 사용되는 제약조건을 생성
- Switch the partitions into the final large table. 각 파티션들을 하나의 큰 테이블로 스위칭
제약조건을 걸기전에 인덱스를 생성하는 이유가 있다. 우리가 이 두가지 작업의 순서를 바꾸게 되면, 제약조건에 맞는지 검사하기 위해서 테이블 스캔이 유발될 것이다. 클러스터드 인덱스를 먼저 생성함으로써 제약조건은 모든 행들이 제약조건에 맞는지 검사하기 위해서 첫번째 값과 마지막 값만 검사하면 된다.
NEXT TESTS
다음 테스트로 무엇을 해볼 것인지 수많은 아이디어가 있다 하지만 이걸 하기 위해서 TPC-H run 사이에 하룻밤만 시간을 빌려왔기 때문에 머신이 사용가능해지는 다음 시간까지 기다려야만 한다. 하지만 주어진 시스템 가용성 한도 내에서 HP는 이런 테스트를 할 수 있도록 해주었다. 실제로는 패턴이 매우 예측가능하기 때문에 다른 테스트는 패턴과 타이밍을 보기 위해서 더 적은 CPU를 가진 머신에서 이뤄질 수도 있다. 물론 많은 CPU를 가진 것이 아니라면 1TB를 로딩하는데 1시간에 끝나지는 않을 것이다.
- SQL Agent와 SQL Server가 파일을 직접 읽지 않아도 되도록 BCP나 BULK insert 를 다른 클라이언트 머신에서 수행.
- 네트웍이 끼어들어가면 패턴이 어떻게 변하는지 모기 위해서 입력파일을 다른 컴퓨터로 이동.
- SSIS의 bulk insert task를 이용해서 테스트. 이것을 SQL Server 기계의 CPU부담을 낮춰주기 위해서 다른 기계에서 수행. 만약 이것을 SQL Server 머신에서 수행한다면 BCP를 사용하는 것과 거의 동일한 결과가 나올 것임.
- 파일에 인덱스를 생성.
- 더 작은 기계에서 수행. 모든 고객들이 64-way 머신을 가지고 있지는 않을 것이기 때문.
- 입력파일들을 미리 정렬해놓고 로딩전에 클러스터드 인덱스를 먼저 만듦. 만약 미리 정렬이 되어 있다면 인덱스 생성 시간을 절약해줄 것임.
- NUMA 설정이 어떤 영향이 있는지 관찰. CPU 병목현상이 있다면 그다지 도움될 것 같지는 않아 보임. 하지만 해당 노드에 foreign memory(NUMA아키텍쳐에서 다른 셀의 CPU를 뜻함)가 할당된다면 더 느려질 것이다. foreign memory 할당을 피하는 방법이 있다. 슬라바 오크의 블로그를 참조.(http://blogs.msdn.com/slavao)
감사의 말씀 : MS SQL Server product group 의 Sunil Agarwal 와 그의 팀, HP의 Mike Fitzner 에게 감사드립니다.
enjoy -- Kevin
Published Friday, May 19, 2006 9:58 PM by kevincox
Filed Under: Performance and Scalability
Comments
# Microsoft SQL Server Development Customer Advisory Team - Load 1TB in less than 1 hour @ Sunday, May 21, 2006 7:46 PM
Professional Association for SQL Server (PASS) SIG
# SQL Server 2005: Load 1TB in less than 1 hour @ Tuesday, May 23, 2006 5:50 AM
STEFANO DEMILIANI WeBlog
# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 3:45 PM
흥미롭습니다. 추가적인 테스트를 기대합니다 (특히 SSIS테스트요) 그런데 다음 사항에 대해서 더 상세하게 설명해주시겠어요?
“Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.”
어떻게 이 값이 제일 좋은 값이라고 결론내신건지요? 테스트에 사용된 데이터에만 해당하는 수치는 아닌가요? 제가 큰 데이터를 마이그레이션 테스트할 때는 정확한 배치 사이즈는 테이블의 데이터 타입을 관찰함으로써 결정할 수 있었습니다. SQL 2000 에서 최대 로그 블럭은 60KB입니다. 따라서 저는 배치당 60KB 넘게 insert 하지 않도록 배치 사이즈를 조절합니다.(의역했음)
60KB limit에 도달하기 위해서는 몇개의 익스텐트 할당이 필요한가요?(뭔 소린지 잘 모르겠음. 익스텐트당 64KB아닌가..?)
당신의 테스트에서는 익스텐트 할당 대기와 관련된 여러 이슈들을 언급한 것 만큼은 이건 크게 고려되지 않은 것 같네요. 하지만 더 작은 시스템에서는 60KB 로그 플러쉬와 관련해 BULK INSERT 사이즈를 조정하는 법을 이해하는 것이 좋을 것 같습니다.
Bert
# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 8:23 PM
Amazing statistics.. Interesting that disk IO on the SAN configuration did NOT turn out to be the bottleneck in this case. What was the memory consumption during this test?
Ted Malone
# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 9:50 PM
Response to Bert: 배치당 2500 rows 는 lock escalation 을 피하기 위해서 사용되었습니다.
잘 모르겠습니다. 그리고 걱정할만큼 중요한 속도저하인지도 잘 모르겠군요.
불행하게도 디스크 공간 확보를 위해서 perfmon 로그를 지웠습니다. 256GB 메모리의 거의 다 사용하지는 않았던 것 같습니다. 메모리 대기가 있다면 더 주의하도록 하지요.
kevincox
# re: Load 1TB in less than 1 hour @ Monday, May 29, 2006 12:46 PM
NUMA에 따른 영향에 대해서 관심이 있습니다. SQL Server는 몇개의 log 기록 스레드를 사용할 수 있나요? 만약 하나 이상이라면 affinity bw(???) 를 조정할 수 있나요? 만약 로그 기록 스레드가 동일한 NUMA노드나 동일한 프로세서/코어에 존재하는 것이 어떤 효용이 있다요? 컨텍스트 스위칭과 스레드간의 통신의 트레이드 오프는 어떤가요?(???) 만약 하이퍼 스레딩 머신이라면 로그 기록 스레드가 insert스레드와 다른 논리 프로세서에 있는 것이 어떤 효용이 있습니까?
# re: Load 1TB in less than 1 hour @ Tuesday, May 30, 2006 9:29 AM
SQL Server 2005 에서 최대 페이지 사이즈는 얼마인가요? 변경가능한가요?
Francisco
Posted by maceo
