슬라이드 1

Size: px

Start display at page:

Download "슬라이드 1"

동긴 주
6 years ago
Views:

1 SQL Server 2008 데이터압축및암호화 전형철에이디컨설팅

2 이주제를이해하는데필요한지식 SQL Server Administration SQL Server 데이터저장구조 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍처지식필요

강사소개 2006. 9 ~ : 에이디컨설팅 컨설턴트 (SQL Server) 2005.12 ~ 2006. 8: CJ 인터넷 DBA 실장 (SQL Server) 2000.

3 강사소개 ~ : 에이디컨설팅 컨설턴트 (SQL Server) ~ : CJ 인터넷 DBA 실장 (SQL Server) ~ : 옥션 DBA, DBA 팀장 (SQL Server) ~ : 포스데이타 포스코생산관제 SM 포스코포항지역 DBA (Sybase) 3

4 SQL SERVER 2008 데이터압축

5 효율적인대용량데이터관리 급격한기업보유데이터증가는기업들에비용및업무부담으로작용 OLTP 시스템 : 수테라바이트데이터관리 DW 시스템 : 수십테라바이트데이터관리요구증가 효율적인데이터저장공간관리 Data Retention Policy 시스템설계시또는시스템운영시정책수립? 정책수립의어려움 법적의무 : 의무적인데이터보관기간 정책수립을위한시스템분석노력및비용 광범위한어플리케이션수정등 데이터압축 데이터크기감소 저장공간감소 데이터크기감소 성능향상가능성증가 5

6 데이터압축 Enterprise/Developer Edition 데이터가압축된 DB 를 Standard Edition 에복원또는연결할수없음 테이블및인덱스에대해데이터행및페이지압축으로데이터크기를줄임 테이블, 인덱스, 파티션에각각적용가능 테이블및인덱스는서로독립적으로압축가능 분할테이블의경우전체또는일부파티션압축가능 압축방식 행압축 고정길이데이터형식을가변길이데이터형식처럼저장 행메타데이터최적화 페이지압축 행압축 접두사및사전 6

7 장점 데이터압축장단점 물리적인저장공간감소 메모리내데이터압축 동일한쿼리의결과반환을위해더적은데이터페이지를참조하므로 I/O 가감소됨 어플리케이션수정필요없음 단점 CPU 자원사용률이증가함 7

8 데이터압축사례 8

9 데이터압축사례 : Gomez OLTP + Reporting 크기기준상위 5개테이블페이지압축 특정테이블 476개파티션 모든파티션에대해페이지압축 압축이전데이터크기 : 7,437 GB 압축이후데이터크기 : 4,540 GB (40% 감소 ) 압축이후 CPU 사용률 6% 증가 압축전후어플리케이션성능에큰변화없음 9

10 데이터압축사례 : Caregroup OLTP + Reporting 크기기준상위 10개테이블페이지압축 압축이전데이터크기 : 120 GB 압축이후데이터크기 : 80 GB (33% 감소 ) 압축전후 CPU 사용률에큰변화없음 압축전후어플리케이션성능에큰변화없음 10

11 데이터행구조 : 압축이전 2 Bytes 2 Bytes n Bytes 2 Bytes Ceiling (#cols/8) bytes 2 Bytes 2 X # var cols n Bytes Variable-length data Column offset array Number of variable-length columns NULL bitmap 1 bit for each column Number of columns Fixed-length data Length of fixed-length portion of row Status 11

12 데이터행구조 : 압축이후 Header CD (Column Descriptor) Region Short Data Region Long Data Region Special Information Forwarding Pointer, Back Pointer, Versioning Info 8 바이트초과데이터저장 8 바이트이하데이터저장 Column 수 + Column 길이배열 데이터정보 (1 바이트 ) 12

13 행압축 고정길이데이터형식을가변길이데이터형식처럼저장 INT 데이터형식 SQL Server 바이트 SQL Server 2008 압축 1 1 바이트 13

14 페이지압축 : 압축정보 페이지헤더다음에추가되는압축정보 Header PageModCount Offsets Anchor Record Dictionary 사전수 + 사전오프셋 + 사전 접두사 ( 행압축데이터구조와동일 ) 접두사의끝과압축정보의끝오프셋 압축재평가를위한페이지변경추적 접두사및사전존재여부 14

15 페이지압축 : 접두사 각컬럼에서저장공간을줄이는데사용할수있는접두사식별 각컬럼의접두사값을나타내는행이압축정보에저장됨 컬럼에서반복되는값은해당참조접두사로대체됨. 접두사값과정확히일치하지않을경우부분일치로표시될수있음 15

16 페이지압축 : 접두사 페이지헤더 페이지헤더 ABCD SEED NULL ABCD FFF ABC FFF ABC ABC SEED FDW NULL SEA XLJ FFF KDS FFF 3 FDW NULL 2A XLJ FFF KDS FFF [ 적용이전 ] [ 적용이후 ] 16

17 페이지압축 : 사전 접두사압축이완료된후적용 데이터형식에상관없이모든컬럼및행에대하여반복되는값식별 식별된값이압축정보에저장됨 페이지에서반복되는값은해당참조사전으로대체됨 17

18 페이지압축 : 사전 페이지헤더 페이지헤더 ABCD SEED NULL ABCD SEED NULL S1=FFF FFF ABC S1 ABC 3 FDW NULL 2A XLJ FFF KDS FFF 3 FDW NULL 2A XLJ S1 KDS S1 [ 적용이전 ] [ 적용이후 ] 18

19 Demo (1) 고정길이데이터압축 데이터압축행구조 19

20 데이터압축대상 압축대상 IN_ROW_DATA : 압축 ROW_OVERFLOW_DATA : 압축되지않음 LOB_DATA : 압축되지않음 압축률예상 EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' 어플리케이션부하 행압축이데이터공간을절약하고, CPU 사용률이 10% 정도증가할경우행압축검토 페이지압축시유의사항 CPU 자원에여유가없을경우충분한테스트수행 DW 또는대용량데이터마트의경우 CPU 자원에여유가있으면페이지압축검토 OLTP 시스템의경우데이터스캔및업데이트비율분석필요 20

21 데이터압축대상 sp_estimate_data_compression_savings / sys.dm_db_index_operational_stats S: 테이블스캔비율 / U: 데이터업데이트비율 ROW, PAGE 압축비율유사 ROW High Update, Low Scan / High Update, High Scan ROW High non-append inserts, some update ROW High Scan / Read-only / Append Only PAGE 21

22 압축계획 테이블및인덱스압축은인덱스재작성압축과동일한메커니즘사용 고려항목 : 작업공간, CPU 및 I/O 자원 압축시필요자원에대한고려사항 힙, 클러스터형인덱스, 비클러스터형인덱스 ONLINE 옵션 SORT_IN_TEMPDB 옵션 DB 복구모델 22

23 압축계획 : 작업공간 필요작업여유공간 사용자 DB 트랜잭션로그 Tempdb 참고사항 힙테이블압축시해당테이블의비클러스터형인덱스가재작성됨 ONLINE 옵션비활성화시 : 순차적재작성 ONLINE 옵션활성화시 : 동시재작성 비클러스터형인덱스재작성작업공간필요 23

24 압축계획 : 작업공간 테이블및인덱스압축 사용자 DB 내작업공간필요 sp_estimate_data_compression_savings 결과로예측 복수의테이블압축시작은테이블에서큰테이블순서로진행 : 데이터파일증가가능성줄임 매핑인덱스 ONLINE 옵션활성화시 동시사용 DML 트랜잭션을지원하기위해압축이전 Bookmark 를신규 Bookmark 로구성 힙또는클러스터형인덱스압축시 SORT_IN_TEMPDB 옵션활성여부에따라 tempdb 또는사용자 DB 공간사용 24

25 압축계획 : 작업공간 트랜잭션로그크기에영향을미치는요소 ONLINE 옵션지정여부 DB 복구모델 버전스토어 ONLINE 옵션지정시 동시사용 DML 트랜잭션이있을경우 Tempdb 내작업공간필요 버전스토어크기에영향을미치는요소 데이터업데이트양 DML 트랜잭션길이 25

26 압축계획 : CPU 자원 인덱스재작성시평균 CPU 자원사용량 행압축 : 약 1.5배필요 페이지압축 : 약 2~5배필요 ONLINE 작업시 CPU 사용률이더증가함 26

27 압축계획 OFFLINE 이 ONLINE 보다빠르고자원을적게사용하며, ONLINE 의경우 CPU 자원을 2 배정도사용 압축이인덱스재작성보다작업공간을더작게사용하고 I/O 가적으나, CPU 자원은더사용함 DB 복구모델이 전체 일경우데이터압축을위해더많은 I/O 가필요 한번에하나의테이블, 인덱스, 파티션단위작업권장 자원에여유가많을경우병렬처리수행가능 압축작업중작업공간이문제가될경우테이블크기가작은순서부터수행 SORT_IN_TEMPDB 옵션사용권장 사용자 DB 의공간을작게사용함 27

28 데이터압축시저장공간처리 향후사용을위해그대로둠 DBCC SHRINKFILE, REORGANIZE 특정파일그룹내모든테이블압축시 새파일그룹생성 압축시테이블과인덱스를새파일그룹으로이동 구파일그룹및파일삭제 새파일그룹에빈압축테이블생성 새테이블로데이터복사이후기존테이블삭제 28

29 행압축의경우 압축률이낮은경우 고정길이컬럼이없음 고정길이컬럼이있더라도최대길이만큼사용됨 압축행크기가 4 KB 이상 페이지압축의경우 접두사적용효과가낮음 사전적용효과가낮음 페이지당데이터행수가작음 대부분 LOB 데이터인경우 29

30 Demo (2) 데이터압축대상 30

31 Switch 데이터압축과테이블분할 원본및대상파티션의압축속성이서로다를경우스위치할수없음 Split 새파티션이기존파티션의압축속성을상속받음 Merge 대상파티션의압축속성을상속받음 31

32 테스트장비 압축테스트 Lenovo X61 노트북 Intel Core 2 Duo CPU T GHz 2 GB RAM Windows Vista SQL Server 2008 Developer Edition Service Pack 1 Tempdb: Data 100 MB + Log 50 MB 32

33 압축테스트 : BULK INSERT 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) DB 복구모델 : 단순 TABLOCK 옵션지정 DB 파일자동확장되지않음 시스템환경및데이터성격에따라결과가달라질수있음 33

34 압축테스트 : 인덱스생성 BULK INSERT 대상힙테이블에클러스터형인덱스생성 DB 복구모델 : 전체 압축되지않은테이블인덱스생성시데이터파일 17 MB 확장됨 행및페이지압축테이블인덱스생성시 DB 파일자동확장되지않음 ONLINE 및 SORT_IN_TEMPDB 옵션비활성화 시스템환경및데이터성격에따라결과가달라질수있음 34

35 압축테스트 : 인덱스재작성 클러스터형인덱스생성후재작성 DB 복구모델 : 전체 압축되지않은테이블인덱스생성시데이터파일 39 MB 확장됨 행및페이지압축테이블인덱스생성시 DB 파일자동확장되지않음 ONLINE 및 SORT_IN_TEMPDB 옵션비활성화 시스템환경및데이터성격에따라결과가달라질수있음 35

36 압축테이블에새행이입력될때 테이블구분행압축페이지압축 힙 클러스터형인덱스 행압축 행압축 페이지압축 기존페이지압축된페이지로입력시 TABLOCK 옵션이지정된 BULK INSERT INSERT INTO... (TABLOCK) SELECT... FROM 기타행압축 페이지압축 기존페이지압축된페이지로입력시 기타행압축 페이지분할이일어날때페이지압축이시도됨 압축으로인한공간감소폭이내부관리임계값을넘을때페이지단위로압축됨 페이지에대한 DML (INSERT / UPDATE / DELETE) 수행횟수가내부관리임계값을넘을때접두사와사전은다시평가됨 36

37 데이터압축과 TDE 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) TDE 활성화 vs. TDE 비활성화비교 테이블압축 ( 페이지압축 ) 크기동일 실행시간및 CPU 사용시간차이가크지않음 시스템환경및데이터성격에따라결과가달라질수있음 37

38 백업압축 SQL 2008: Enterprise Edition SQL 2008 R2: Standard Edition 가능 압축된백업은모든 Edition에서복원할수있음 특징 백업공간감소 백업및복원시간단축 I/O 자원사용량감소 CPU 자원사용량증가 38

39 백업압축사례 39

40 백업압축테스트 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) 압축옵션활성화 vs. 압축옵션비활성화비교 CPU 사용시간차이가거의없음 DB 백업소요시간및크기감소 시스템환경및데이터성격에따라결과가달라질수있음 40

41 백업압축과로그전달 트랜잭션로그백업압축가능 주서버의로그백업시간단축 주서버의로그백업크기감소 네트워크를통한로그백업복사부하감소 보조서버의로그복원시간단축 DB 미러링로그백업압축가능 CPU 부하보다네트워크부하비용이더큼 41

42 백업압축과튜닝 BUFFERCOUNT 백업 / 복원시데이터읽기및쓰기를내부버퍼사용 데이터파일볼륨수및백업장치수에따라백업버퍼수가결정됨 BUFFERCOUNT 옵션값에따라백업 / 복원성능이결정될수있음 MAXTRANSFERSIZE SQL Server 와백업미디어간에사용되는가장큰전송단위 ( 바이트 ) 를지정 기본 1 MB 백업 / 복원에서사용되는데이터버퍼메모리 SQL Server 버퍼풀외부사용 32 비트시스템에서는중요한고려사항 백업시사용되는메모리 = 3 * BUFFERCOUNT * MAXTRANSFE RSIZE 42

43 NULL 백업장치 백업압축과튜닝 BACKUP DATABASE AdventureWorks2008 TO DISK = 'NULL' WITH COMPRESSION, BUFFERCOUNT=50, MAXTRANSFERSIZE= 데이터파일을읽지만, 실제백업을수행하지않음 최대백업성능을달성하기위한최적의옵션을찾기위함 WITH 와함께 BUFFERCOUNT, MAXTRANSFERSIZE 등의옵션지정및성능테스트 CPU 또는 I/O 병목확인 43

44 백업압축과튜닝사례 [ 백업 ] [ 복원 ] 44

45 백업압축과 TDE 암호화된데이터는압축률이좋지않음 TDE 기능을사용하는 DB 백업의경우압축되지않은백업사용권장시스템환경및데이터성격에따라결과가달라질수있음 45

46 결론 백업 / 로그 / 미러압축 대부분의경우압축을사용하는것이좋음 CPU 자원이제한적일경우사용을피함 데이터압축 DW 시스템의경우대부분사용가능 OLTP 시스템 저장공간이감소될경우행압축사용 CPU 자원이제한적일경우페이지압축사용에는충분한검토필요 46

47 TDE (TRANSPARENT DATA ENCRYPTION)

48 정보보호 개인정보보호 : 개인정보취급에관한법적의무 데이터암호화도구 도구 추가비용 암호화단위 암호화방법 어플리케이션수정 자체개발없음컬럼수동수정필요 3 rd -Party 암호화모듈 SQL Server 암호화모듈 SQL Server TDE 비용발생컬럼수동수정필요 없음컬럼수동수정필요 없음 DB 자동없음 48

49 TDE Enterprise/Developer Edition DB 데이터를암호화하고, 인증서와함께데이터를암호화하는데사용된키보호 DB 수준암호화설정 페이지단위암호화및복호화 데이터페이지쓰기시디스크의데이터페이지암호화 데이터페이지읽기시메모리의데이터페이지복호화 통신채널을통한암호화는제공하지않음 49

50 TDE 암호화아키텍처 50

51 전체 DB 보호 TDE 를사용하는이유 물리적미디어 ( 디스크또는백업파일 ) 를도난당한경우악의적인사용자에의한 DB 복원 / 연결방지 키없이는데이터를사용할수없음 DB 개체에대한스키마를수정할필요가없음 데이터를암호화 / 복호화하기위해어플리케이션을수정할필요가없음 인덱스나데이터타입들에대한아무런제한이없음 성능저하가비교적적음 51

52 Demo (3) 컬럼수준암호화 TDE 암호화이전 DB 파일내용보기 TDE 암호화설정 TDE 암호화이후 DB 파일내용보기 52

53 TDE 일반사항 물리적저장공간에영향이없음 자동으로데이터암호화수행 DB 암호화키 (DEK, Database Encryption Key) 를이용하며, DB 의 Boot Page 에저장됨 디스크에있는데이터만암호화되고, 메모리에있는데이터는암호화되지않음 암호화 / 복호화시 AES, 3DES 알고리즘사용 기존의 SQL Server 컬럼수준암호화를대체하는것은아님 53

54 TDE 일반사항 FILESTREAM 데이터는암호화되지않음 DB 미러링 / 로그전달 미러서버 / 복원대상서버에모두동일한인증서가설치되어야함 로그트랜잭션은암호화되어전송됨 클러스터링 / 복제는특별한영향없음 TDE 설정시 DB 는즉시파일초기화 (Instant File Initialization) 기능을사용할수없음 데이터파일이증가할때성능이저하될수있음 54

55 TDE 백업및복원 인증서를백업할때개인키를함께백업해야함 인증서만백업할경우정상적으로 DB 복원또는연결을수행할수없음 백업파일을복원하거나분리된 (Detach) 데이터파일을연결 (Attach) 할때 DEK 가필요함 DEK 를분실하면복원할수있는방법은없음 서버인증서백업도필요함 TDE 를사용한 DB 의백업파일도암호키를사용하여암호화됨 TDE 를사용한 DB 의압축백업시압축률이낮음 55

56 관련 DMV sys.databases sys.certificates sys.dm_database_encry ption_keys DB 정보를보여주는카탈로그뷰 DB 인증서를보여주는카탈로그뷰 DB 에사용된암호화키및 DB 암호화의상태에대한정보를보여주는동적관리뷰 SELECT DB_NAME(database_id), CASE encryption_state when 1 then 'Unencrypted' when 2 then 'Encryption in Progress' when 3 then 'Encrypted' when 4 then 'DEK change in progress' when 5 then 'Decryption in progress' end as encryption_state_desc, key_algorithm, key_length, percent_complete FROM sys.dm_database_encryption_keys 56

57 암호화시작후수행되는동작 페이지단위로암호화가시작됨 암호화작업중에도 DB 는정상동작 어플리케이션에서 DB 사용시문제없음 백업작업도정상수행 모든페이지들이차례대로암호화됨 백그라운드작업으로암호화수행 Tempdb 는가장강력한방식인 AES 256 으로암호화됨 사용자 DB 에서어떤알고리즘을사용하든지상관없음 하나의사용자 DB 라도암호화되면자동으로 Tempdb 도암호화됨 사용자 DB 가모두암호화를사용하지않더라도자동으로암호화가해제되지않음 57

58 TDE 로인한성능영향 암호화작업을하는동안에는성능이슈가발생할수있음. 데이터파일들을각기다른 LUN 을가진디스크에분리하여성능저하를감소시킬수있음 암호화작업동안에는일반적인작업형태의쿼리는 3~5%, CPU 를많이사용하는작업형태의쿼리는 30% 정도의성능저하발생 Tempdb 암호화로인해 Tempdb 를많이사용하는다른 DB 의성능저하가발생할수있음 현재까지는암호화관련된성능카운터항목이없음 암호화로인한대기유형은없음. 암호화가진행되는동안에는 sys.dm_exec_requests 에는 ALTER DATABASE 및 background 작업형태로나타남 58

59 Demo (4) 인증서가없는상태에서복원 59

60 감사합니다.

슬라이드 1

슬라이드 1 SQL Server 2008 데이터압축및암호화 에이디컨설팅젂형철 이주제를이해하는데필요한지식 SQL Server Administration SQL Server 데이터저장구조 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가 수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍처지식필요