대용량 데이터베이스 구축 및 관리 팁

1. 대용량 데이터베이스 구축 및 관리 팁 하성희 (dbconsultant@empal.com) Database Consultant

2. 대용량 환경 구축 • 대부분의 DBMS에 대해 일반적으로 적용되는 규칙이 적용되지 않음 • Auto-tuning이 적합하지 않음 • 전체 플랫폼, 하드웨어, OS, SQL Server Configuration에 대한 이해가 필요함 • 99%의 일반적인 응용 프로그램에는 적합한 디폴트 설정을 변경해야 할 수도 있음 • DOP, Recovery Interval 등 • Query / Index 힌트 • Table lock escalation • 백업/로딩 전략 • 데이터 로딩 전략 • 어렵지만, 가능함. 대용량 경험과 노하우 필요

3. 대용량 환경 구축에 필요한 기능 • Filegroup • Partitioned View • Indexed View • 부분 백업, 부분 복원 • 병렬 데이터 로딩

4. Configuration Option • awe enabled • max server memory (MB) • min server memory (MB) • cost threshold for parallelism • max degree of parallelism • lightweight pooling • affinity mask • query governor cost limit • network packet size (B)

5. Degree of Parallelism • max degree of parallelism • 다중 프로세서가 하나의 쿼리를 실행 • 디폴트 값 = 0  모든 프로세스 사용 (최대 32) • 스레드들이 CPU의 대다수를 사용할 때 문제 발생 • Context switching • 잘못 작성된 쿼리로 인해 발생하는 경우가 종종 있음 • 일반적인 권고 사항: • 운영 장비: DOP=0 • 프로세서 개수>8: DOP=8

6. 메모리 • 일반적으로, 메모리가 많을수록 좋다 • 데이터 캐시와 실행 계획 캐시가 빠르므로 • 4GB RAM의 경우에는, 디폴트로 OS가 2GB를 사용하고 SQL Server가 2GB를 사용한다 • SQL Server가 3GB를 사용하도록 하려면 /3gb 옵션을 지정 • 메모리가 4GB를 초과하는 경우에는, AWE와 PAE 옵션을 사용한다 • 메모리 크기와 Boot.ini 플래그 설정 • 4 GB /3GB • 8 GB /3GB /PAE • 16 GB /3GB /PAE • 16 GB 이상 (16 ~ 64 GB) /PAE

7. VLDB(OLTP) 시스템 환경성능 측면 • 잠금과 블로킹 • 성능 이슈 또는 교착상태(Deadlock)를 유발하는 Lock escalation 제거 • 로그 병목 제거 • 비효율적인 저장 프로시저 재컴파일 제거

8. 잠금과 블로킹VLDB 환경 • 블로킹 감소 방안 • 쿼리 최적화  테이블 스캔 방지 • Table Lock으로 인한 문제 유발 • 인덱스 튜닝 • 보다 향상된 데이터 로딩 계획 • 파티셔닝과 같은 scale-out 전략 활용

9. Locking과 Lock Escalation • Lock Escalation • 특정 비율의 메모리가 lock에 사용되거나 시스템이 2500개(SS SP2에서는 5000)의 lock을 할당한 경우에는 SQL Server가 table lock으로 전환시킨다 • Table Lock escalation은 할당되는 메모리양은 줄여 주지만 deadlock이나 기타 잠금으로 인한 문제를 유발시킬 수 있다 • 해결책: • 강제로 page lock을 사용하도록 함으로써 할당되는 lock의 수를 줄인다 • 또는 다음과 같은 팁을 사용한다 • 더미 데이터를 insert한다 (예:9999 99 99999 999 999) • Insert한 레코드에 대해서 update lock을 확보한다 • 이렇게 하면 동일 테이블에 이미 다른 lock이 걸려 있으므로, 그 다음에 수행되는 스레드에서는 table lock을 걸 수 없게 된다 • 최악의 경우 – Lock escalation을 방지하는 trace flag 사용 가능. 주의 요망 • 일반적인 잠금 관련 툴들을 통해 모니터링한다: 프로필러, sp_who, sp_who2, sp_lock …

10. 인덱스 옵션 • sp_indexoption • sp_indexoption ‘table’, ‘AllowRowLocks|AllowPageLocks’, TRUE|FALSE

11. 로그 병목 제거 • 로그를 별도의 드라이브 또는 별도의 RAID 10 stripe set에 배치 • 로그가 SAN에 있는 경우에는 전용 Controller 또는 전용 LUN(s) 할당 • 로그에서 대기가 발생하고 있지 않는지 확인 (Waitstats 모니터링) – DBCC waitstats • DBCC SQLPERF (Waitstats, clear) • go • DBCC SQLPERF (Waitstats)

12. Create Waitstats Script DBCC sqlperf(waitstats,clear) -- wait statistics 클리어 drop table waitstats create table waitstats (Wait_Type varchar(80), Requests numeric(18,1), Wait_Time numeric (18,1), Signal_Wait_Time numeric(18,1), timenow datetime default getdate()) declare @start int, @finish int select @start = 1, @finish = 10 while (@start < @finish) begin Begin transaction insert into waitstats (Wait_Type, Requests, Wait_Time,Signal_Wait_Time) exec ('DBCC sqlperf(waitstats)') commit select @start = @start + 1 waitfor delay '00:00:10' -- 10초 간격으로 수행 End Select * FROM WAITSTATS order by wait_time desc

13. 비효율적인 저장 프로시저 재컴파일 제거 • 재컴파일은 다음에 의해 유발된다 • SET 옵션의 변경 • DML/DDL 혼합 사용 • Tempdb 행 변경 등 • 감소 방안? • Temp table을 table 변수로 변경 • 먼저 필요한 INSERT 작업을 모두 수행 • 가능하다면 동일한 저장 프로시저 내에서 temp table을 만들고 삭제 • 전체 이름을 지정 • 옵션 사용 : “keep plan” 또는 “keepfixedplan” • 저장 프로시저를 분해 • MSDN에 있는 참고가 될 만한 white paper 자료 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

14. TempDB 속도 저하! • Peak 시점에 성능 저하 • 많은 동시 사용자들이 임시 테이블들을 생성하는 경우 • 성능 저하 • Sysprocesses에서 DBID=2 (TempDB) 에 대한 lock 정보가 확인됨 • 해결책 • TempDB 파일그룹에 파일 추가 • TempDB를 보다 많은 spindle에 걸쳐 배치 • 단일 페이지 할당을 비활성화하는 trace flag 사용 가능, 주의 요망

15. 인덱스 생성VLDB 기법 • 인덱스 생성 • 크기가 큰 인덱스의 경우에는 병렬 수행 • SMP의 경우 디폴트 • 보다 작은 크기의 인덱스들의 생성 작업은 동시에 수행 • Bulk_Logged 복구 모델 • 클러스터된 인덱스의 경우에는 작업 휴지 가능 시간과 재구성 작업 소요 시간 고려 • 대용량 테이블의 경우에는 Partitioned View를 사용하는 것이 좋음

16. 인덱스 재구성 VLDB 유의 사항 • DBCC DBREINDEX • 즉시 중단되지 않음 • 작업 가능 시간이 제한적인 경우, 롤백 소요 시간 감안

17. 인덱스 관리Partitioned View과 단일 테이블비교 • 목표: 단편화 제거, 데이터 로딩 시 페이지 분할 최소화 • DBCC SHOWCONTIG : 단편화 확인 • 단일 테이블 관리 팁 • 인덱스 삭제와 재생성이 용이하지 않음 • DBCC INDEXDEFRAG –온라인 수행 가능하지만 시간이 오래 소요 • CREATE INDEX … WITH DROP_EXISTING • Partitioned View가 훨씬 더 빠르고 단순 • 파티션별로 인덱스 재생성 관리 • 어떤 파티션이 가득 채워지고 일단 한번 reindex 작업을 수행하기만 하면, 그 인덱스에 대해서는 관리 불필요

18. Sales_200201 Sales_200201 시일이 경과된 데이터의 삭제 • 일반적으로 일정 기간 동안(24개월 또는 36개월)의 데이터를 유지, 주기적으로 오래된 데이터 삭제 Partitioned View 단일 테이블 } … Sales_200212 V_Sales_All Sales_200202 Sales_All

19. 데이터 삭제: 단일 테이블 • 작업이 느리다 • 대개 한번에 30일 내지 90일 간의 데이터를 삭제 • 삭제 작업 중의 로그 파일 크기 증가 • 블로킹 이슈 발생 • Lock escalation 주의 • 휴지 시간이 아닌 시간대에 수행하게 되면 데이터 로딩과 수행되는 쿼리들에 영향을 미칠 수 있다

20. 데이터 삭제: 단일 테이블 • 클러스터된 인덱스의 범위를 사용하여 삭제할 것 • Delete from <table> where <clustered_index_key> between <values> • 여러 단계로 나누어서 삭제 • 범위를 여러 개로 나누고 병렬 삭제 가능 • 작업으로 인한 잠금 결과를 받아들이기 어려운 경우에는 커서 사용 고려: • 삭제할 영역에 대하여 커서 오픈 • Delete ‘where current of’ • 1000개 행 단위로 커밋

21. 데이터 삭제: Partitioned View • 작업이 간단하고 신속하다 • Partitioned View에서의 삭제는 다음의 두 단계로 수행 가능하며 신속하게 수행 가능: • 오래된 데이터가 존재하는 테이블을 제외한 뷰를 다시 생성 • 삭제 대상 테이블 삭제

22. PartitionedView 사용 시 팁 • 데모

23. Cool Read only 데이터베이스 일관성 점검 VLDB 기법 • 전체 점검을 위해서는 TempDB 공간을 충분하게 확보할 것 • 필요 스페이스 확인 : ESTIMATEONLY 옵션 • 물리적 점검 • PHYSICAL_ONLY 옵션 • 한번에 데이터베이스의 일부분씩 점검할 것 • 파일그룹별로 점검 • 수정 빈도에 따라 점검 주기 차별화 전략 Hot

24. 데이터베이스 축소VLDB 기법 • 데이터베이스 축소 작업이 너무 오래 걸리는 경우 • TRUNCATEONLY 옵션을 사용하여 스페이스 회수

25. 대용량 테이블 스키마 변경VLDB 기법 • NOT NULL 컬럼 추가 • DEFAULT 정의 요 • 테이블 Sch-M lock으로 인한 문제 유발 • DEFAULT 정의 적용 불가한 경우 작업 팁 • 데모 • NOT NULL 속성의 컬럼 추가 시에 NOT NULL로 정의하는 것이 왜 좋은가?

26. ReplicationVLDB 기법 • 구독 데이터 초기화 • Automatic/None 모드 • 대용량 데이터 동기화 • Auto 모드를 사용할 수 없는 경우 • 사전 동기화, None 모드 사용 • 백업 복원, Log-shipping 활용

27. 백업과 복원 • 백업 방식 • 전체 데이터베이스 백업 • 트랜잭션 로그 백업 • 차등 백업 • 파일 그룹, 파일 백업 • 업무 단위 백업/복원 가능 • VLDB(Very Large Database)에 효과적인 방식

28. B-1_SUN LOG_MON LOG_TUE 원본 파일 그룹 LOG_WED B-2 B-1 LOG_THU LOG_FRI : : 백업과 복원 << 백업 예 >> File2 File1 File3 B-1 전체 데이터베이스 단위 파일/파일 그룹 단위

29. 백업과 복원 • 대용량 TB+ 데이터베이스의 경우 • 어떤 백업 미디어를 사용해야 하는가? • 가장 빠르고, 가장 합리적이며, 가장 비용 효율적인 방법은 무엇일까? • 특히 복원 측면에서 어떤 방법이 가장 신뢰성이 높을까? • 파일그룹과 파일을 사용하면 부분적인 복원 가능 • 복구에 소요되는 시간과 다운타임의 비용을 비교 계산 요

30. 파일 그룹과 파일 • 권장 사항 • 대부분의 데이터베이스에는 하나의 데이터 파일과 하나의 로그 파일만 있으면 됨 • 여러 파일을 사용할 때: 두 번째 파일 그룹 추가 & 그 파일 그룹을 기본 파일 그룹으로 지정  주 파일에는 시스템 파일과 개체만 있게 함 • 파일 그룹을 사용하여 특정 물리적 디스크에 오브젝트를 배치함 • 같은 조인 쿼리에서 사용되는 여러 테이블은 여러 파일 그룹에 배치함  조인된 데이터에서 병렬 디스크 I/O 검색을 하기 때문에 성능이 향상됨 • 자주 액세스되는 테이블과 그 테이블에 속하는 클러스터되지 않은 인덱스를 여러 파일 그룹에 배치 • 트랜잭션 로그 파일과 다른 파일 및 파일 그룹을 같은 물리적 디스크에 배치하지 않음

31. 백업과 복원 전략 • 파일 그룹과 파일을 사용하면 관리 용이 • 파일 그룹과 파일은 개별적으로 백업 및 복원 가능함 • 데이터의 확장이 용이함 • 파일 그룹과 파일을 사용하여 테이블과 인덱스 재배치 • 쿼리 성능 향상 • 다중 CPU의 경우에 병렬 스캔 • 특정 데이터를 Read-Only 파일그룹에 위치

32. 백업 및 복원VLDB 기법 • 온라인 백업 • 파일 백업과 로그 백업 사용 • 파일 차등 백업 고려 • 복구 시간 단축 • 자주 수정되지 않는 파일그룹은 자주 백업하지 않는다

33. VLDB 시나리오파일 그룹과 파일 백업 복원 • 신중한 계획이 필요 • 복원 시 integrity를 위해서는 Transactional Log Backup 필요 • Crash 발생 시점에 BACKUP LOG WITH NO_TRUNCATE를 수행할 수 없는 경우에는, (서로 다른 파일/파일그룹에 있는 테이블들 간에 관계가 있는 경우에는 데이터 무결성을 보장하기 위해서는) 모든 파일/파일그룹의 최신 백업본을 복원해야 하며, 가장 오래된 transaction log 백업이 복원된 이후의 모든 트랜잭션 로그도 복원해야 한다. • 테이블과 인덱스 분리한 경우 • 파일그룹간의 interdependency가 있는 경우 • 참고 : DB당 최다 256 파일그룹, 최다 32767 파일 지원 가능

34. 기타 백업 방식 • 디스크로 백업 • 차후 별도로 테이프로 백업 • 테이프로 직접 병렬 백업 • 전통적인 백업 기법을 사용하면 모든 경웨 상당량의 스페이스와 시간을 필요로 함

35. Split-Mirror Backup / Restore • High Availability에 유용 • 대개 수초 내에 데이터베이스 백업 완료 • 복구도 신속 • Standard SQL Server 백업 및 복구 기법에 비해 훨씬 신속하게 secondary server 구축 가능

36. 대량 데이터 로딩 • SQL Server로의 데이터 로딩 • 데이터 변환이 필요한 경우에만 DTS 사용 • 성능 이슈가 발생할 수 있음 • SQL 외부에서 변환하는 것을 고려 요망 • 그 이외의 경우에는 bulk load를 사용 • 8-웨이의 경우에 초당 50만건의 레코드를 처리할 수 있음 • 고객사들의 경우에 일일 10억건 처리하고 있음 • 프로세서가 많아지면 선형적으로 처리 능력이 향상됨

37. 대량 데이터 로딩VLDB 기법 • 빈 테이블로의 초기 로딩 • 데이터 로딩 • 인덱스가 없는 상태에서 로딩 • BULK INSERT 사용 • 데이터 파일을 분할하여 병렬 로드 • CPU당 하나의 로드 스트림 • Bulk_Logged (또는 가능한 경우 Simple) 복구 모델 • TABLOCK 옵션 사용 • 인덱스 생성 • 원하는 복구 모델로 전환 • 백업 수행

38. 대량 데이터 로딩VLDB 기법 • 데이터가 있는 테이블에 대한 로딩 • 인덱스가 있는 상태에서 데이터 로드 • 성능과 concurrency 요건에 따라 locking 단위를 결정 • 복구 모델 변경 • Full  Bulk_Logged • Point-in-time recovery가 필요한 경우가 아니라면 • Simple (변경 불필요)

39. 대량 데이터 로딩VLDB 기법 • Partitioned View • 가장 신속한 로딩 방법 • 로드 스크립트가 복잡

40. 요약 • 7X24 시스템은 이제 표준이 되었다. 고가용 시스템의 구축에 적절한 기술의 적용, 관련되는 모든 사람들의 노력이 필요하다. • 오늘날 SQL Server를 사용하여 VLDB 시스템을 구축하는 것이 가능할 뿐 아니라, 진행되고 있다. • VLDB의 경우에는 auto-tuning, auto configuration에 의존해서는 안 되며, 특성과 요구 사항을 분석하여 적절한 대응이 따라야 한다. • 대용량 시스템의 성공적인 구축 및 효율적인 관리를 위해서는 경험과 노하우, 그리고 그를 바탕으로 한 전략이 필요하다.