[SQLP] 1장. 아키텍처 기반 튜닝 원리 - 데이터베이스 아키텍처

데이터베이스 아키텍처

1. 아키텍처 개관

가. 모델링의 정의

데이터베이스 : 물리적인 디스크에 저장된 데이터의 집합 
인스턴스 : 공유메모리와 이를 엑세스 하는 프로세스의 집합

나. SQL Server 아키텍처

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

• 시스템 데이터베이스 : mster, model, msdb, tempdb 등

• 사용자 데이터베이스 : 데이터파일(mdf), 트랜잭션 로그파일(ldf), 보조데이터파일(ndf)

2. 프로세스

• 서버프로세스 : 전면에 나서서 사용자가 던지는 각종 명령을 처리

• 백그라운드프로세스 : 뒤에서 묵묵히 주어진 역할을 수행

가. 서버 프로세스

• Oracle : Server Process

• SQL Server : Worker Thread

• 처리절차

  • 사용자의 요청

  • SQL 파싱

  • 커서를 열어서 SQL을 실행하면서 블록 READ

  • 읽은 데이터를 정렬하여 요청한 결과집합을 만들어 네트워크를 통해 전송

클라이언트가 서버프로세스와 연결하는 방식 (예: 오라클)

1) 전용서버 방식(Dedicated Server)

• 클라이언트 세션과 전용 서버가 1:1로 매핑

• 오라클의 가장 일반적인 방식

• 클라이언트 요청에 의해 리스너 프로세스는 dedicated server 생성

• 새로운 dedicated server 프로세스는 리스너에 의해 새로운 권한을 상속 받음

• 데이터베이스와 물리적인 커넥션을 맺음

2) 공유서버 방식(Shared Server)

• 클라이언트 세션과 서버프로세스가 1:N 로 매핑

• 클라이언트 요청에의해 리스너 프로세스는 현재 사용가능한 dispatcher pool 탐색확인

• 리스너는 사용가능한 dispatcher커넥션 정보를 클라이언트에 되돌려줌

• 클라이언트는 리스너 접속을 끝내고 바로 dispatcher 로 접속

3. 파일구조

가. 데이터파일

1) 블록(=페이지)

• 대부분 DBMS에서는 I/O 블록단위로 이루어짐

• 데이터를 읽고 쓸때의 논리적인 단위

• SQL 성능을 좌우하는 가장 중요한 성능지표

• 옵티마이저의 판단에 가장 큰 영향을 미치는 요소

2) 익스텐트(Extent)

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

• 균일 익스텐트(Uniform) : 64kb 이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용됨

• 혼합 익스텐트(Mixed) : 한 익스텐트에 할당된 8 페이지를 여러 오브젝트가 나누어 사용 
  모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64kb가 넘으면서 두번째 부터는 균일 익스텐트 사용

3) 세그먼트(Segment)

• 테이블, 인덱스, Undo 처럼 저장공간을 필요로하는 데이터베이스 오브젝트 (한개 이상의 익스텐트 사용)

• 파티션은 오브젝트와 세그먼트가 1:M (파티션을 만들면 내부적으로 여러개의 세그먼트가 만들어짐)

• 한 세그먼트에 할당된 엑스텐트가 여러 데이터 파일에 흩어져 저장됨 (디스크 경합감소, I/O 분산효과)

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

• 세그먼트를 담는 콘테이너로서 여러개의 데이터 파일로 구성됨

• 사용자는 데이터 파일을 직접 선택할 수 없으므로 실제 파일을 선택하고 익스텐트를 할당하는 것은 DBMS 의 몫

나. 임시파일

• 대량의 정렬이나 해시 작업을 수행하다가 메모리 공간이 부족해지면 중간 결과집합을 저장하는 용도

• 오라클에서는 임시 테이블스페이스를 여러개 생성해두고, 사용자마다 별도의 임시 테이블스페이스를 지정해 줄 수 있음

다. 로그파일

• DB 버퍼 캐시에 가해지는 모든 변경사항을 기록하는 파일

• 로그 기록은 Append 방식으로 이루어지기 때문에 상대적으로 매우 빠름

• 빠른 커밋 지원

4. 메모리 구조

• 여러 프로세스가 동시에 엑세스할 수 있는 메모리영역

• 모든 DBMS는 공통적으로 사용하는 캐시 영역이 있음(DB 버퍼캐시, 공유풀, 로그 버퍼)

• 그 외에 Large Pool, Java Pool, 시스템 구조와 제어 구조를 캐싱하는 영역 포함하고 있음

• 여러 프로세스가 공유되기 때문에 내부적으로 Latch, 버퍼Lock, 라이브러리 캐시 Lock/Pin같은 엑세스 직렬화 매커니즘 사용

가. DB 버퍼캐시

데이터파일로부터 읽어들인 데이터 블록을 담는 캐시영역

1) 버퍼블록상태

Free Buffer 
인스턴스 기동 후 아직 데이터가 읽혀지지 않아 비어 있는 상태이거나, 데이터파일과 서로 동기화 되어 언제든지 덮어써도 되는 상태 
Dirty Buffer 
버퍼가 캐시된 이후 변경이 발생하지만, 아직 디스크에 기록되지 않아 데이터파일 블록과 동기화가 필요한 버퍼 블록. 이 버퍼 블록이 재사용 되려면 디스크에 먼저 기록되어야 하고 디스크에 기록된 순간 Free 버퍼로 변경 
Pinned Buffer 
읽기 또는 쓰기 작업이 현재 진행중인 버퍼 블록

2) LRU (Least Recently Used) 알고리즘

버퍼 캐시는 유한한 자원이므로 모든 데이터를 캐싱해 둘 수 없기 때문에 사용 빈도가 높은 데이터 블록 위주로 버퍼 캐시가 구성 되도록 LRU 알고리즘을 사용

나. 공유풀

딕셔너리 캐시와 라이브러리 캐시로 구성되며 버퍼 캐시처럼 LRU 알고리즘을 사용

1) 딕셔너리 캐시

테이블, 인덱스같은 오브젝트는 물론 테이블스페이스, 데이터파일, 세그먼트, 익스텐트, 사용자, 제약사항과 같은 메타정보 저장

2) 라이브러리캐시

SQL 실행에 관련된 모든 객체에 대한 정보 관리 
서버 프로세스가 SQL을 작업할때 사용되는 작업공간 
SQL에 대한 분석정보 및 실행계획 저장 
공유 SQL을 저장하기 위해 사용

라이브러리 캐시는 캐싱된 SQL과 그 실행계획의 재사용성을 높이는 것이 수행 성능을 높이고 DBMS 부하를 최소화 하는 핵심원리임

바인드변수 사용 및 기준에 맞는 SQL 작성으로 재사용성을 높여 줘야 함.

다. 로그버퍼

Only Recovery를 위해 사용됨. DB버퍼에 가해지는 모든 변경사항을 로그퍼버에 먼저 기록.

1) Physiolosical logging

physical logging과 logical logging의 장점을 결합한것으로 변경된 데이터에 대한 before/after 이미지를 저장하고 opcode(명세서)를 기록하여 완벽한 복구를 보장

2) page fix rule

변경이 시작되는 시점부터 완료되는 시점까지 해당 블록을 보호해주는 아키텍처로 os에서 세마포어를 할당받아서 세마포어가 해당 블록을 보호

3) log a head

데이터 변경작업시에 DBWR에 의한 블록 변경보다 로그를 먼저 기록하는 기법

4) log force at commit

커밋시 리두로그를 먼저 기록하는 기법, 기록하는 속도가 빠른 리두를 먼저 기록하게 하여 중간에 발생하는 장애로부터 완벽한 복구를 보장하는 기법

5) logical odering of redo

로그를 기록할 때 정해진 위치가 아닌 순서와 무관하게 기록하되, scn과 RBA 를 이용하여 복구에 대한 순서를 결정하여 빠른 복구 보장

라. PGA(Process Global Area)

• 오라클의 서버 프로세스는 자신만의 PGA 메모리 영역을 할다받아 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용

• PGA 는 다른 프로세스와 공유되지 않은 독립적인 메모리 공간으로 똑같은 개수의 블록을 읽더라도 SGA 버퍼 캐시에서 읽는것보다 훨씬 빠름

1) UGA(User Global Area)

각 세션을 위한 독립적인 공간 
Dedicated Server : PGA 에 UGA 영역 할당 
Shared Server: SGA의 Large Pool 또는 Shared Pool 에 UGA 영역 할당

2) CGA(Call Global Area)

오라클은 하나의 데이터베이스 call을 넘어서 다음 call까지 계속 참조되는 정보를 UGA 에 담고, call이 진행되는 동안 필요한 데이터는 CGA에 담는다.

Parse Call, Execute Call, Fetch Call 마다 매번 항당 받음 
Call이 진행되는동안 Recursive call이 발생하면 그 안에서도 Parse, Execute, Fetch 단계별로 CGA 할당 
할당된 공간은 call이 끝나자마자 해제되어 PGA에 반환

3) Sort Area

데이터 정렬을 위해 사용되며, 부족할 때마다 chunk 단위로 조금씩 할당됨 
세션마다 sort_area_size 파라미터로 설정가능 
9i 이상부터는 workarea_size_policy 파라미터를 auto로 설졍하면 내부적으로 알아서 sort area를 할당해줌

5. 대기 이벤트

• DBMS 내부에서 활동하는 수많은 프로세스간에서는 상호작용이 필요하며, 그 과정에서 다른 프로세스가 일을 마칠 때까지 기다려야하는 상황이 발생

• 그때마다 해당 프로세스는 자신이 일을 계속 진행할 수 있는 조건이 충족될때까지 수면(Sleep)상태로 대기

Reponse Time = 
Service Time + Wait Time + CPU Time + Queue Time

• 서비스시간(Service Time = CPU Time) : 프로세스가 정상적으로 동작하며 일을 수행한 시간

• 대기시간(Wait Time = Queue Time) : 프로세스가 잠시 수행을 멈추고 대기한 시간

• Response Time Analysis 방법론은 CPU Time과 Wait Time을 각각 break down 하면서 서버의 일량과 대기시간을 분석

• CPU Time은 파싱작업에 소비한 시간인지 쿼리 본연의 오퍼레이션 수행을 위해 소비한 시간인지 분석

• Wait Time은 각가 발생한 대기 이벤트를 분석해서 가장 시간을 많이 소비한 이벤트 중심으로 해결방안 모색

가. 라이브러리캐시 부하

• 라이브러리 캐시에서 SQL 커서를 찾고 최적화 하는 과정에서 경합이 발생하여 나타난 대기이벤트

  • latch : shared pool

  • latch : library cache

• 라이브러리 캐시와 관련해서 자주발생하는 대기이벤트로 수행중인 SQL이 참조하는 오브젝트에 다른 사용자가 DDL문장을 수행할때

  • library cache lock

  • library cache pin

나. 데이터베이스 Call 과 네트워크 부하

• 애플리케이션과 네트워크 구간에서 소모된 시간에 의해 나타난 이벤트

  • SQL*Net message from client : client로부터 다음 명령이 올때까지 idle 상태로 기다릴때 발생(데이터베이스 경합과 관계없음)

  • SQL*Net message to client : 메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는경우 이거나 , 클라이언트가 너무 바쁠경우.

  • SQL*Net more data to client: 메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는경우 이거나 , 클라이언트가 너무 바쁠경우.

  • SQL*Net more data from client : 클라이언트로부터 더 받을 데이터가 있는데 지연이 발생한 경우

다. 디스크 부하

• 디스크 I/O 발생할 때 나타나는 대기 이벤트

  • db file sequential read : Single Block I/O. 한번의 I/O call에 하나의 데이터 블록만 읽음. 인덱스 블록을 읽을때 발생

  • db file scattered read : Multi Block I/O . Table Full Scan 또는 Index Fast Full Scan 시 나타남

  • direct path read

  • direct path write

  • direct path write temp

  • direct path read temp

  • db file parallel read

라. 버퍼캐시 경합

• 버퍼캐시에서 블록을 읽는 과정에서 경합이 발생하여 나타나는 대기 이벤트

  • latch : cache buffers chains

  • latch : cache buffers lru chain

  • buffers busy waits

  • free buffer waits

• 해소 방법은 I/O부하 해소 방법과 비슷함

마. LOCK관련 대기이벤트

• Lock과 관련된 대기이벤트

  • enq : TM - contention

  • enq : TX - row lock contention

  • enq : TX - index contention

  • enq : TX - allocate ITL entry

  • enq : TX contention

  • latch free : 특정 자원에 대한 래치를 여러차례(2000번 가량) 요구했지만 해당 자원이 계속 사용중이어서 잠시 대기 상태로 빠질때마다 발생

• Lock은 사용자 데이터를 보호하는 반면, Latch는 SGA에 공유되어 있는 갖가지 자료구조를 보호할 목적으로 사용하는 가벼운 Lock

• Latch도 일종의 Lock 이지만 큐잉(Queueing) 매커니즘을 사용하지 않음

• 특정자원에 액세스하려는 프로세스는 래치 획득에 성공할 때까지 반복해서 시도하나, 우선권은 부여받지 못함 (처음 시도한 래치가 맨 나중에 래치획득에 성공할 수도 있음)

• 그 외 대기이벤트

  • log file sync

  • checkpoint completed

  • log file switch completion

  • log buffer space