[DB] 친절한 SQL 튜닝 - 속도, 저장 구조

0. 속도

RDB의 속도가 느린 주 원인

 

DB가 느린 이유는 디스크 I/O로 인한 문제 때문이다.

우리가 CS를 공부할적에 배운 메모리 계층 구조를 보게 되면 CPU와 하드디스크 간에 속도차이 간극은 매우 심하다는 것을 알 수 있다.

메모리 계층 구조

 

이 속도 개선을 위해서 메모리를 두어 자주 필요한 정보는 하드디스크단 까지 가지않고 메모리에 올려둔 정보로 읽게 되는데 일반적인 RDB와 REDIS의 차이도 이것과 유사한 상황이라고 볼 수 있다.

 

CPU에서 실행시킨 작업이 DB 프로세스에서 I/O (input / output) 속도로 인해서 잠시 CPU를 반환하고 프로세스 작업이 멈칫하는데 이과정에서 속도 손실이 꽤 일어난다고 볼 수 있다.

 

생각해보면 엘라스틱서치의 분산 데이터 구조에서 작은 루씬단위의 검색엔진은 최대한 fsync라는 하드디스크와의 동기화 작업을 최대한 늦추고 일정한양의 되었을때 비로소 동기화하는 작업을 진행했다. 꽤나 I/O 비용이 컸기 때문에 지연시켰고 속도 측면에서 이득을 얻었다.

 

1. 데이터베이스 저장 구조

DB의 저장구조는 다음과 같다.

DB 저장 구조

DB 구조의 정의

데이터를 저장하려면 먼저 테이블 스페이스를 생성해야한다.

 

테이블 스페이스 : 세그먼트를 담는 컨테이너로 여러개의 데이터파일로(디스크 상의 물리적 OS파일) 구성된다.

 

익스텐트 : 공간을 확장하는 단위로 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면 해당 오브젝트가 속한 테이블 스페이스로부터 추가로 익스텐트를 할당받으며 연속된 블록의 집합이기도 하다.

추가로 익스텐트를 배정받으면 같은 데이터파일에 위치하지 않을 수 있다.

파일 경합을 줄이기 위해서 DBMS가 데이터를 가능한 여러 데이터파일에 분산해서 저장하기 때문이다.

 

세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트를 의미한다.

파티션구조가 아니라면 인덱스, 테이블 모두 하나의 세그먼트라고 볼 수 있다.

 

파티션 : 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 된다.

 

LOB : 자신이 속한 테이블과 별도의 공간에 값을 저장한다.

// 세그먼트 조회 쿼리
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME from DBA_EXTENTS where OWNER = 'TEST'

 

세그먼트 조회 결과

조회 결과

오라클이 읽는 최소 단위

테이블, 인덱스 모두 동일하게 블록 단위로 읽고 쓴다.

// 하나의 레코드를 읽고 싶지만 블록단위로 읽는 구조이다.
// 기본 블록 사이즈를 확인하는 쿼리는 다음과 같다.
select value from V$PARAMETER where NAME = 'db_block_size'

 

읽는 방식

스캔방식 도식화

1. 시퀀셜 액세스 

논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다.

인덱스 리프 블록은 앞뒤를 가르키는 주소값을 통해 논리적으로 서로 연결돼 있다.

이 주소 값을 따라서 앞뒤로 순차적으로 스캔하는 방식이다. 

이것이 Index Full Scan을 활용하여 스캔하는 방식이라고 볼 수 있다.

 

*Index Range Scan : 정상적으로 인덱스의 시작점을 찾아서 중간에 멈추는 경우

*Index Full Scane : 인덱스의 시작점과 멈추는 지점을 찾을 수 없는경우

 

만일 논리적인 연결고리가 없다면 오라클은 세그먼트에 할당된 헤더의 맵(map)형태로 관리한다.

익스텐트 맵은 각 익스텐트의 첫번째 블록 주소 값을 갖는다.

읽어야 할 익스텐트 목록을 맵에서 얻고 저장된 순서대로 읽는데 이것이 Table Full Scan의 원리이다.

 

2. 랜덤 액세스

논리적, 물리적인 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

2. 버퍼캐시

코드 캐시

이전에 작성한 SQL 파싱작업과정에서 자주사용하는 SQL의 경우 처음 한번 하드 파싱후에 SGA(System Global Area)에 라이브러리 캐시에 SQL을 저장한다음 추후에 한번더 같은 쿼리를 호출시 지정된(소프트 파싱) 실행계획으로 SQL이 실행된다.

 

버퍼캐시 (데이터 캐시)

데이터에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 반복 적인 I/O Call을 줄이는 목적이 있다.

서버 프로세스와 데이터파일 사이에 버퍼캐시가 있음으로 운좋게 버퍼캐시에서 데이터를 찾으면 물리적인 I/O 없이 빠르게 데이터를 전달할 수 있다.

 

// 조회 쿼리
select * from V$SGA

 

조회된 결과

버퍼 캐시의 사이즈

현재 로컬 오라클의 버퍼 캐시 사이즈는 (byte단위) 360MB이다.

 

버퍼캐시 히트율 공식

 

BCHR ( Buffer Cache Hit Rtio ) = ( 캐시에서 곧바로 찾은 블록 수 / 총읽은 블록 수 ) X 100

= ( ( 논리적 I/O - 물리적 I/O ) / 논리적 I /O ) X 100

= ( 1- ( 물리적 I/O ) / (논리적 I/O) X 100

 

변형하면 물리적 I/O = 논리적 I/O X (100% - BCHR) 이라는 것을 알 수 있다.

예를 들어 BCHR이 70퍼센트일때 논리적 I/O가 10,000개 이면 물리적 I/O는 3000개가된다.

만약 논리적 I/O가 1,000개이면 물리적 I/O는 300개가 된다.

즉 논리적 I/O를 줄이면 물리적 I/O가 줄어든다는 의미이다.

 

논리적 I/O를 줄이는 방법

 

SQL을 튜닝해서 읽는 총 블록 개수를 줄이면된다.

이후에 튜닝방법에 대해서 여러방면으로 진행.

그리고 또한 꼭 BCHR이 높다고 효율적인 SQL이 아닐 수 있다.

예를 들어서 SQL이 조인한번으로 끝날것을 여러번해서 조인해서 조회한다던지 한다면 버퍼 캐시 적중률과 상관없이 효율적이지 못한 SQL이다.

 

4. SingleBlock Vs MultiBlock

single block은 인덱스를 활용하여 인덱스의 루트 부터 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때 동작한다.

말그대로 블록을 하나씩 읽어서 가져오는 방법이다.

 

multi block은 Table Full Scan과 같이 많은 데이터를 읽을때 사용된다.

말그대로 블록을 한번에 여러개씩 가져온다는 것.

 

동작하는 예시)

 

Multiblock (읽어야하는 블록 목록이 10개일때)

1번은 버퍼 캐시에 있어서 그대로 가져온다.

2~5번은 버퍼에 없어서 디스크 I/O를 발생시키면 버퍼에 다시 올리고 가져온다.

6번 버퍼 캐시에 있어서 그대로 가져온다.

7번은 버퍼 캐시에 없어서 디스크 I/O를 발생시키면 버퍼에 다시 올리고 가져온다.

8번은 버퍼 캐시에 있어서 그대로 가져온다. 7번은 8번이 버퍼 캐시에 있기때문에 Single Block으로 동작하게된다.

또한 익스텐트의 마지막 블록인 경우 앞에 블록이 버퍼캐시에 있다면 SIngleBlock으로 동작하며 가져오게된다.

 

5. 동시성 문제

누군가 데이터를 읽고있는데 같은 타이밍에 누군가 해당 데이터를 수정한다면 곤란해진다.

따라서 공유하는 데이터에 대해서는 줄서기 매커니즘인 래치가 필요하다.

SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데 원하는 불록을 찾으면 곧바로 래치를 해제해야한다.

그래야 다른 프로세스들도 블록을 읽기 위해 순서대로 입장이 가능하기 때문이다.

근데 만일 다른 프로세스에서 수정작업이 진행중에 읽게 되면 문제가 생길 수 있기 때문에 버퍼락을 사용하게 된다.

일반적인 로우락으로 로우를 수정해도 락을 건다는 메타정보를 위해서 해당 블록 어딘가에 쓰기 작업을 진행해야 하는데 그 쓰기 작업 조차도 충돌날 수 있기 때문에 블록에 버퍼락을 거는것이다. 쓰기작업이 끝난 후에는 로우락 해제, 버퍼락 해제 수순으로 진행된다.