MDF와 LDF 이야기 DaumKakao 데이터 플랫폼 파트 강동운 2015년 1월 22일

Level 200 이 지식을 이해하는데 필요한 지식 SQL Server에 관심이 많은 개발자 SQL Server 초급 DBA 대상 SQL Server에 관심이 많은 개발자 SQL Server 초급 DBA Level 200

Speaker 강동운(Eastluck.Kang) DaumKakao 데이터 플랫폼 파트 DBA(SQL Server, MySQL) (전) 스피어헤드(EA Seoul Studio) FIFA Online 3(MongoDB) DBA (전) J2M Soft Raycity, Debut(SQL Server) DBA 커뮤니티 활동(이스트럭) SQL Server MVP 2012 ~ 2015 SQLer.com MySQL Power Group(http://cafe.naver.com/mysqlpg) Facebook MongoDB Korea(https://www.facebook.com/groups/krmug) Email : eastluck.kang@daumkakao.com Blog : http://eastluck.tistory.com

목차 - SQL Server Storage · Datafile(MDF), Extent, Page Extent 관리 IAM, Table Or Index Scan 방법 - Balanced Tree vs Fractal Tree - LDF, SQL Server Backup, BCM, DCM - Query Internal · CheckPoint, Lazy Writer

영단기

전문가로 가는 지름길(Unplugged 4th – Deadlock) 1단계: Query의 달인이 되어보자!(실행계획, 인덱스는 당장 상관없다) 2단계: 인덱스의 원리인 B-Tree(Balanced Tree)를 공부하자 3단계: 데이터 및 인덱스 내부를 살펴보자(DBCC IND, PAGE, EXTENTINFO 등) 4단계: 1단계를 되돌아보며 실행계획 및 인덱스를 공부하자. 5단계: Lock과 트랜잭션 로그, 트랜잭션 격리 수준 등등 원리에 대해 공부하자. 6단계: SQL Server 관심 있는 분야에 Deep Dive! 7단계: 꾸준한 영어공부…

SQL Server Storage

master / tempdb / msdb / model / user databases SQL Server Storage 구조 Instance Database master / tempdb / msdb / model / user databases FileGroup PRIMARY FILEGROUP / 로그(LDF) 파일은 없음 Datafile MDF, NDF / LDF Extent 순차적인 8개 Page의 집합(공간 할당 단위) Page 8kb

SQL Server Datafile(MDF/NDF) Page 0 File Header Page 1 PFS Page 2 GAM Page 3 SGAM Page 4 NonUsed Page 5 NonUsed Page 6 BCM Page 7 DCM PFS: Page Free Space GAM: Global Allocation Map SGAM: Shard Allocation Map BCM: Bulked Change Map DCM: Differential Change Map 8088 Pages 관리 511,230 Pages 관리

Mixed Extent(개체당 최대 8개) 와 Uniform Extent 구성 SQL Server 공간 할당 기본 단위 연속하는 8개의 페이지 집합 Mixed Extent(개체당 최대 8개) 와 Uniform Extent 구성 GAM 과 SGAM이 Mixed or Uniform Extent 관리 예) Extent 0: 0 ~ 7 Page Extent 1: 8 ~ 15 Page Extent 2: 16 ~ 23 Page Page 72 Page 73 Page 74 Page 75 Page 76 Page 77 Page 78 Page 79

Extent(Mixed & Uniform Extent)

DBCC EXTENTINFO(DBName, TableName) ext_size: 1(Mixed Extent), 8(Uniform Extent) pg_alloc: 익스텐트에 할당된 페이지 수

Extent 관리 8의 비밀(산수의 시간…) 0000 0011: 1,2번 Extent 사용 중 Page 0 Page 7 ~ Extent 0 1 byte로 8개의 Extent로 관리 가능! Page 8 Page 15 ~ Extent 1 1 Page => 8000 bytes 8000 * 8 = 64,000 Extents 관리 가능! 64,000 * 8 = 512,000 Pages 512,000 * 8 = 4,096,000 KB(=4 GB) 정확히는 511,230마다 등장 Page 16 Page 23 ~ Extent 2

왜?! 8KB 일까? 2KB 혹은 16KB면 안될까? 위 말고 아래서부터 채우면 안될까? Page Header 96 Bytes - Header: Page의 헤더정보 - Data: 각각의 Row가 저장 - Offset: Row가 시작되는 바이트 위치 Data Row 0 Row 1 Row 2 왜?! 8KB 일까? 2KB 혹은 16KB면 안될까? 위 말고 아래서부터 채우면 안될까? 8060 Bytes Offset 2 1 36 Bytes

마이너스 숫자 어떻게 저장할까? Page 남은 공간은 어떻게 관리되지? Row Header Row 0 Data Row 1 Status ① 고정컬럼 ② 가변컬럼 Row 2 ① 항목 - 고정컬럼 사이즈 + 4 ② 항목 - 총 컬럼 수 - NULL 비트맵 - 가변컬럼의 수 - 가변 컬럼의 끝나는 위치 마이너스 숫자 어떻게 저장할까? Page 남은 공간은 어떻게 관리되지? Offset 2 1

2) 0=>1, 1=>0으로 변환(1의 보수) 3) 더하기 1(+0, -0의 구분하기 위해) 2의 보수 – 마이너스 수를 저장하는 방법 1) 10진수를 2진수로 변환 2) 0=>1, 1=>0으로 변환(1의 보수) 3) 더하기 1(+0, -0의 구분하기 위해) 예) -8를 표현하기 위해 8을 2진수로 표현 => 0000 0000 0000 1000(0을 1로, 1을 0으로) => 1111 1111 1111 0111(더하기 1) => 1111 1111 1111 1000(16진수로 변환) => F F F 8 FFF8(Big Endian) – IBM 계열 F8FF(Little Endian) – Intel 계열

------------------------- 1111 1111 1111 1010 마이너스를 저장하는 방법 - 2의 보수 예) -8 + 2 = -6 -8의 2의 보수 1111 1111 1111 1000 <- -8 & 0000 0000 0000 0010 <- 2 ------------------------- 1111 1111 1111 1010 0000 0000 0000 0110(6을 2진수로 변환) 1111 1111 1111 1001(0=>1, 1=>0) 1111 1111 1111 1010(더하기 1)

PFS(Page Free Space) 1 byte로 하나의 페이지의 여유 공간 관리 1개 PFS는 8088개의 Page(64 MB)를 관리할 수 있다. bits 0-2 0x00 is empty 0x01 is 1 to 50% full 0x02 is 51 to 80% full 0x03 is 81 to 95% full 0x04 is 96 to 100% full bit 3 (0x08): is there one or more ghost records on the page? bit 4 (0x10): is the page an IAM page? bit 5 (0x20): is the page a mixed-page? bit 6 (0x40): is the page allocated?

PFS(Page Free Space) Header Data 1 2 3 4 5 6 7 8 9 10 11 12 13 14 … Data 1 2 3 4 5 6 7 8 9 10 11 12 13 14 … 8086 8087 Offset

Page(Row Migration) Header Row 0 Row 3 Row 0 Ghost Record Data Row 1 Offset 2 1

Page Split Header Header Row 0 Data Row 250 Data Row 1 Row 251 Row 2 … Double Linked List … … Row 499 Row 249 Ghost Records Row 499 Offset Offset

Page Split Page 72 Page 73 Page 500 테이블 조각화 발생

GAM SGAM IAM

GAM(Global Allocation Map) - bit 1: 사용 가능한 Extent(empty) GAM, SGAM GAM(Global Allocation Map) - bit 1: 사용 가능한 Extent(empty) - bit 0: 이미 사용된 Extent SGAM(Shared Global Allocation Map) - bit 1: 1개 이상의 페이지를 사용할 수 있는 Mixed Extent - bit 0: Uniform Extent or Mixed Extent로 할당 혹은 비어있는 익스텐트

Mixed Extent with free pages GAM, SGAM GAM SGAM Free 1 Uniform or Mixed Extent Mixed Extent with free pages

tempdb에 Mixed Extent에 Page 할당 경합이 발생할 수 있다. Uniform Extent만 사용 - DBCC TRACEON(-1, 1118) Sqlserver 시작 옵션 –T1118 시스템 테이블의 페이지들은 SGAM을 계속 사용한다 예) GAM, SGAM, BCM 등

IAM(Index Allocation Map) 데이터 혹은 인덱스에 할당된 Page를 관리 8 Mixed Pages + Uniform Extents 4GB의 데이터 관리(511,230 Page) 예) 1개 Clustered Index, 2개 NonClustered Index IAM => 3개

IAM(Index Allocation Map) Page Page Page Page Page Page Page Mixed Extent Uniform Extent Uniform Extent Uniform Extent Data or Index

DBCC IND(DBName, TableName, Option) - Option -2: All IAM -1: All Pages 0: Heap 1이상: sys.indexes(index_id)

Table or Index Scan 방법(Index Ordered Scan) Btree의 구조로 Scan 하는 방법 Allocation Ordered Scan IAM을 참고하여 Mixed Extent -> Uniform Extent 순서로 Scan 하는 방법 조건 65 Pages 이상 ReadOnly or READ UNCOMMITTED 실행계획 ordered 연산자 false

Table or Index Scan 방법(Allocation Ordered Scan) 정렬 순서가 달라질 수 있다. IAM Page Page Page Page Page Page Page ① Extent Extent Extent ② Data or Index

Table or Index Scan 방법(Index Ordered Scan) Page Page Page ① Page Page Page Page Page Page … … ②

Balanced Tree Fractal Tree

Balanced tree vs Fractal Tree Balanced Tree(btree) 모든 검색 경로에 대한 비용이 동일 DML에 따른 Random I/O 증가되는 문제 Table Fragmentation 발생 Fractal Tree Btree의 단점을 보완 노드에 Insert/Delete Node를 두어 Random I/O를 Sequential I/O로 구현 GNU General Public License(오픈소스 라이선스 정책) MySQL, MariaDB 사용 가능

Btree(Balanced Tree) Page Page Page Page Page Page Page Page Page … …

Fractal Tree Insert Buffer Delete Buffer … … Page Page Page Page Page

쉬어가는 코너

영어공부 이렇게 하진 않았을까?

읽어서 이해가 안 되는 문장은 절~~대로 들리지 않는다. 읽어서 이해가 안 되는 문장은 쓰지도 못한다. 지겨운 영어공부 어떻게 할까? 결론: 읽기 읽어서 이해가 안 되는 문장은 절~~대로 들리지 않는다. 읽어서 이해가 안 되는 문장은 쓰지도 못한다. 말하는 속도보다 느리게 읽는 경우도 마찬가지 챕터북 부터 시작하시죠.(하루에 챕터 2개씩) 추천 드리는 원서 Magic Tree House 1 ~ 28 Marvin Redpost 1 ~ 8 A to Z Mystery 1 ~ 26

LDF SQL Server Backup BCM DCM

LDF – VLF(Virtual Log File) 초기화(대기 발생) 무한정 커질 수 있다. Transaction Log Backup LDF 사이즈 줄이기 LDF 사이즈 축소 DBCC SHRINKFILE ~ Transaction Log Backup Transaction Log Backup

VLF 사용현황 조회 DBCC LOGINFO Q) LDF 파일이 여러 개면 어떻게 될까? 성능상 이점이 전혀 없다.

Transaction Log Backups Differential Backups Partial Backups SQL Server Backup Full Backups Transaction Log Backups Differential Backups Partial Backups Backing Up Read-Only Databases ① ② ③ ④ ⑤ ⑥ ⑦ Full Backups Start Full Backups End Tran saction Log Backups Tran saction Log Backups Dfferential Backups Tran saction Log Backups Tran saction Log Backups Dfferential Backups 2 1 이전 풀백업 + 로그백업 파일들 + ② ① + ② + ③ + ⑤ ① + ④ + ⑤

DaumKakao SQL Server Backup 정책 - Full Backup: 매일 0시 Transaction Log Backup: 1시 ~ 23:30분(30분 단위) 보관방법 로컬에 1차 백업(C => RAID 1) MDF/LDF(D => RAID 1+0) 2차 백업 서버 전송 및 Restore(통계 추출) 3차 백업 서버 전송

BCM(Bulked Change Map) · BACKUP LOG 이후 대량 변경한 Extent 관리 BCM & DCM BCM(Bulked Change Map) · BACKUP LOG 이후 대량 변경한 Extent 관리 · 1비트로 1개의 Extent를 관리(4GB) DCM(Differential Change Map) · 마지막 BACKUP DATABASE 이후 변경된 Extent 관리

Query Internal Checkpoint Lazy Writer

Query Engine Relational Query Internal Query Engine Relational + Storage Buffer Cache Update Query CheckPoint Lazy Writer Log Cache Log Cache COMMIT MDF LDF

CheckPoint & Lazy Writer · BufferPool에 있는 모든 Dirty Page를 디스크로 기록 · 수동 & 자동 모두 가능 Lazy Writer · 체크포인트 사이에 Dirty Page를 디스크로 기록 · 메모리 Swap을 피하기 위해 작동 · 자동

· Datafile, Extent, Page, PFS(Page Free Space) 정리 SQL Server Storage · Datafile, Extent, Page, PFS(Page Free Space) · 2의 보수, Row Migration, Page Split GAM, SGAM, IAM, Table Or Index Scan 방법 Balanced Tree, Fractal Tree LDF, SQL Server Backup, BCM, DCM Query Internal · CheckPoint · Lazy Writer

Q/A

감사합니다.