SQL은 두가지 파일을 저장한다 (데이터/로그)
첫번째 데이터 파일은 .mdf
다음부터는 .ndf
로그파일은 .ldf
mem <-> disk
page(8KB)
extent는 연속적인 8개의 pages로 구성 , 공간의 할당 단위를 의미
왜 이렇게? 흩어진 테이블을 찾을 때 디스크암이 왔다갔다하면서 찾아야 하기 때문에, 퍼포먼스를 올리기 위해
*테이블과 인덱스 데이터는 page로, page는 extent로 그룹화
-페이지의 구성
[헤더][데이터행1][데이터행2][데이터행3][사용가능한공간][3][2][1]오프셋
-익스텐트의 종류
1.혼합 익스텐트 모드 테이블 인덱스 섞여있음
2.균일 익스텐트 모드 는 테이블만(성능이 좋다,대용량시스템사용) - 테이블1만 들어오게 유도
*처음엔 혼합으로 시작하다가 8페이지가 넘어가면 단일로 넘어가는데 보통 대용량 db에선 처음부터 지정을 해주어야한다
[T1][I1][T2][I2]이렇게 혼합모드로 가다가..
T1이 8개가 넘어가면 EXTENT를 새로할당하고 T1만 배치..
이때 퍼포먼스가 내려가기 때문에
처음에 디비생성시에 설정해준다
Logical Physical
DB
table/index file
extent
page o/s page
4-4
1page = 8KB = 8192byte = 8060bytes(헤더를 제외한 실제 바이트)
-RDB 설계과정
입사일자 7byte
이름 10byte
부서명 40byte
직급 10byte
주민번호 14byte
급여 4byte
10000000만명 * 85바이트 = 850M
*위와 같이 통으로(2차원으로(엑셀처럼)) 관리하면 문제가 발생한다.
Anormaly발생
1.insert 2.update 3.delete
따라서 부서명(40)을 따로빼고 부서번호(4)와 같이 관리
85바이트에서 부서번호로 관리하기때문에 49 * 1천만건 = 490M
(테이블을 쪼개는 과정 = 정규화)
사원(자식테이블) >- 부서(부모테이블)
여러개의 데이터 데이터 하나만
1NF - 중복되는 데이터 제거 , 필드에는 DATA가 한개만
2NF - 키가아닌 속성들은 키에 완전 종속
3NF - 키가 아닌 속성은 또 다른 키가아닌 일반속성을 참조해선 X
3.5(BCNF)-다중키일때 키값끼리의 종속성 제거
4-5
복구할때 필요한 파일들이 담기는 곳 - 로그파일
4-6
로그와 데이터파일을 물리디스크 레벨에 나눠넣어라
적절한 레이드 레벨 선택
파일과 위치를 적절하게 분산
커밋을 만날때 로그파일은 순차적으로 내려간다
ex) c:\ mdf d:\ndf l:\ldf
4-8
데이터가 얼마나 차있는지 미리 확인하고.. 데이터베이스의 처음크기를 미리 늘려준다.
안그러면 자동증가 (1MB씩 무제한 증가..)하기 때문에 퍼포먼스에 문제
파일그룹을 추가하여서 기존에 사용한 디스크가 다 사용되면 데이터파일을 추가한다
4-10
MASTER - 시스템만사용
MSDB - 백업과 복구에 관련된 설정과 이력정보
MODEL - 템플릿 정보
TEMPDB - 해쉬테이블 등 임시 사용, 혹은 메모리가 충분하지 않을때 ex1억건의 데이터를 sorting할때 한번에처리를 할수없기 때문에 디스크로 그룹핑된 데이터가 내려간다. 그때 사용하는 db
RESOURCE - 읽기전용 DB의 SYS스키마, 사용자에겐 보이지 않는다
4-12
tempdb 실환경만큼 부하를 걸어본다. 패턴 확인
인스턴스를 주기적으로 리스타트해주면 자동 truncated버려지고 / rebuilt재생성 해준다
빠른 디스크에 생성되야한다
4-16
filegrowth를 %로 증가하지않고 적당한 사이즈를 줘서 증가하게 한다.
4-23
Shiriking Database files 가능하지만 매우 번거로운 작업
자주 발생하면 단편화 조각화가 발생한다
[1] [1] [] [1] ->3 번데이터를 삭제했을시에 빈 값인데도 풀테이블 스캔시에 읽힌다.
DB조각모음 -> 테이블을 옮긴다 select into 를 사용해서 다른 이름으로 변경한다.
*index는 업데이트가 없다. 지워졌다고 마킹만 하고 새로 추가를 한다.
*alter index rebuild 옵션
index data
11 12
12 10->13
13
4-22
database altering , expanding , shrinking실습
4-26
?왜 파일그룹을 따로 하게 만들어놨을까?
>파일그룹 단위로 오브젝트를 따로 만들수 있게. table a -> a그룹 b는 b그룹
>파일그룹은 백업과 복구를 그룹단위로 따로 할 수있다.
규모가 커지고 트랜잭션이 많을 수록 primary / data / index를 분산하라
4-32
인스턴스를 detach/attach 할 수 있다.
file은 os의 copy명령어로 옮겨야한다.
detach
attach
4-36
backup / restore
detach / attach
copy database wizard
backup 이랑 detach랑 차이는 ?
detach는 로그 적용이 안된다.
sql agent job 에서 스케줄링 가능
Module5. backup and recovery
5-5 적절한백업주기
data based backups = full,differentiol,copy,filegroup,file
log based backups = log, tail log
업무중단 회복불가능
| |
|--------------------------
| <--- MTD(한계복구시간)->| * 장애 난 이후 견딜 수 있는 시간
|<---->RTO(복표복구시간) |
|<-->RP(실제복구시간) |
<-->RPO(손실되어도 무방DATA)
RPO/RTO 를 기준으로 백업전략을 세운다
백업주기는 RPO를 초과해서는 안된다.
5-7
하나의 백업은 backup set으로 불린다. (복수 sets)
미디어sets 는 여러개를 담을 수 있다.
5-9
복구 테스트 - 주기적으로 적절하게 테스트를 수행해야한다
5-12
1.사용자가 데이터 변경
2.메모리의 버퍼캐시 변경
3.만약 메모리에 있으면 데이터를 변경하고, 없으면 디스크에서 Physical Read수행후 변경
4.사용자 commit을 때림
5.무조건 로그파일에 먼저 기록
6.체크포인트가 발생하면, dirty pages(메모리의 변경공간)를 database에 내려씀
*왜 체크포인트를 기다려야하나? 디스크에 원래 썼던 공간에 써줘야한다..
10억건을 내려쓸때.. 쪼금쪼금.. 체크포인트 기다리면서..
따라서.. shutdown하고 메모리를 비우고 내려써야한다..
5-14
로그파일에는 트랜잭션이 실패하면 롤백하기위한 정보
데이터베이스에 문제가 발생하면 복구를 하기위한 정보가 들어간다
write ahead logging (디스크에 쓸때 로그가 먼저 내려간다)
복구모델에 따라 로그가 증가할수도 감소할수도 있다.
-로그파일의 삭제
1.백업되었으니 버린다
2.복구모델에 따라 로그를 쌓을 필요가 없는 경우
Recovery models
1.simple데이터베이스를 백업하면 자동으로 로그를 버린다, 일부는 버려져도 상관없다.
2.full 데이터베이스가 백업이되도 로그파일을 쌓아둔다 (운영환경)
3.bulk logged 100% 복구 못한다
5-20백업/복구 실습
5-22
[DB] <- [DB]
FULL BACKUP - 걸려있는 transaction 까지 백업
Differential backup - 변경되 있는 부분만 찾아서 백업
Transaction Log - 로그파일만 백업
5-24풀백업
small database
5-26트랜잭션로그 백업 전략
1.tail log백업
2.full backup(recover)
3.transaction log backup
복구를 할때 데이터 통째로 저장해놔야, 자체백업을 하다가 실패해도 ms 지원을 받는다
5-28 differential backup전략
1.전체 카피
2.tail log backup
3.full recovery
4.differncial recovery
5.transaction log 적용
6.taillog 적용
Module6. MSSQL 2008 Backup
DB백업과 트랜잭션로그 백업
6-4
WITH INIT;
오버라이팅(덮어쓰기)
6-8
2008에서 백업을하면 옵션으로 압축을 할 수 있다(,COMPRESSION;) 그러면 I/O가 줄고(압축을 했으니가) CPU가 늘어난다(파일을 읽으니까)
