아키텍처 기반 튜닝 원리- SQL 전문가 가이드

출처 : SQL 전문가 가이드  요약 정리 

데이터베이스 성능 튜닝의 3대 핵심 요소 

1. SQL 부하 해소 
2. 데이터 베이스 Call 최소화 
3. I/O 효율화 

--> 성능 요소를 해결 하는데 90% 이상은 개발팀 역량에 달렸다. 

어플리케이션으로 부터 발생하는 일량을 근본 적으로 줄여야 한다. 

정의 데이터베이스 : 물리적인 디스크에 저장된 데이터집합(데이터파일, 리두로그파일, 컨트롤파일) 인스턴스 : 공유메모리(SGA)와 이를 엑세스하는 프로세스 집합 ★하나의 인스턴스는 하나의 데이터베이스를 엑세스(Single), 여러개의 인스턴스는 하나의 데이터베이스를 엑세스(RAC)

나.SQL Server 아키텍처

정의 하나의 인스턴스당 최고 32,767개의 데이터베이스를 정의해서 사용 기본적으로 시스템데이터베이스가 만들어지면, 사용자데이터베이스를 추가하여 생성하는 구조

시스템데이터베이스 : mster, model, msdb, tempdb 등
사용자데이터베이스 : 데이터파일(mdf), 트랜잭션로그파일(ldf), 보조데이터파일(ndf)

백그라운드프로세스

ORACLE	SQL Server	설명
SMON(System Monitor)	Database cleanup / Shrinking Thread	장애가 발생한 시스템을 재기동할 때 인스턴스 복구를 수행하고, 임시 세그먼트와 익스텐트를 모니터링한다
PMON(Process Minitor)	Open Data Services(OPS)	이상이 생긴 프로세스가 사용하던 리소스를 복구한다
DBWn(Database Writers)	Lazywriter Thread	버퍼 캐시에 있는 더티 버퍼를 데이터 파일에 기록
LGWR (Log Writer)	Log writer Thread	로그 버퍼 엔트리를 redo 로그 파일에 기록한다
ARCn(Archiver)	N/A	꽉찬 리두로그가 덮어 쓰여지기 전에 archive로그 디렉토리로 백업한다
CKPT(Checkpoint)	Database Checkpoint Thread	checkpoint 프로시스는 이전의 checkpoint 가 일어났던 마지막 시점 이후의 데이터베이스 변경 사항을 데이터파일에 기록하도록 트리거링하고, 기록이 완료되면 현재 어디까지 기록했는지를 컨트롤 파일과 데이터 파일 헤더에 기록한다. 좀더 자세히 설명하면 wirte Ahead Logging 방식을 사용하는 DBMS는 리두로그에 기록해 둔 버퍼 블록에 대한 변경사항 중 현재 어디까지를 데이터 파일에 기록했는지 체크 포인트정보를 관리해야 한다. 이는 버퍼캐시와 데이터 파일이 동기화된 시점을 가리키며, 장애가 발생하면 마지막 체크포인트 이후 로그 데이터만 디스크에 기록함으로써 인스턴스를 복구할수 있도록 하는 용도로 사용된다.이 정보를 갱신하는 주기가 길수록 장애 발생시 인스턴스 복구 시간도길어진다.
RECO(Recoverer)	Distributed Transaction Coordinator(DTC)	분산 트랜잭션 과정에 발생한 문제를 해결한다

파일구조

가.데이터파일

1) 블록(=페이지)

• 대부분의 DBMS에서는 I/O 블록단위로 이루어짐
• 데이터를 읽고 쓸때의 논리적인 단위
• SQL 성능을 좌우하는 가장 중요한 성능지표
• 옵티마이저의 판단에 가장 큰 영향을 미치는 요소

항목	오라클	SQL Server
명칭	블록	페이지
블록크기	2KB,4KB, 8KB, 16KB, 32KB, 64KB	8KB

2)익스텐트(Extent)

• 테이블스페이스로부터 공간을 할당하는 단위

항목	오라클	SQL Server
크기	다양한크기의 익스텐트	항상 64KB(페이지크기가 8KB이므로)
오브젝트	단일오브젝트사용	2개이상의오브젝트

• 균일익스텐트(Uniform) : 64KB 이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용됨
  8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용
• 혼합익스텐트(Mixed) : 한 익스텐트에 할당된 8페이지를 여러 오브젝트가 나누어 사용
  모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64KB가 넘으면서 두번째부터는 균일익스텐트 사용

3)세그먼트(Segment)

• 테이블, 인덱스,Undo 처럼 저장공간을 필요로하는 데이터베이스 오브젝트 (한개 이상의 익스테트 사용)
• 파티션은 오브젝트와 세그먼트가 1:M (파티션을 만들면 내부적으로 여러개의 세그먼트가 만들어짐)
• 한 세그먼트에 할당된 엑스텐트가 여러 데이터파일에 흩어져 저장됨(디스크 경합감소.I/O 분산효과)

오라클	SQL Server
세그먼트	힙구조 또는 인덱스구조 오브젝트

4) 테이블스페이스(Tablespace) 

• 세그먼트를 담는 콘테이너로서 여러개의 데이터파일로 구성됨
• 사용자는 데이터파일을 직접 선택할수 없으므로 실제 파일을 선택하고 익스텐트를 할당하는것은 DBMS의 몫

오라클	SQL Server
테이블스페이스	파일그룹