SQL 처리과정과 I/O_데이터 저장 구조 및 I/O 메커니즘

데이터 저장 구조 및 I/O 메커니즘

 

논리적 I/O vs. 물리적 I/O

 

DB 버퍼 캐시

 

데이터를 캐싱하는 'DB Buffer Cache'는 SGA의 구성요소 중 하나다.

Library Cache가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라고 한다면, DB Buffer Cache는 '데이터 캐시'라고 할 수 있다. 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 불 록에 대한 반복적인 Disk I/O Call을 줄이는 데 목적이 있다.

 

 서버 프로세스는 데이터 블록을 읽을 때 항상 버퍼캐시부터 탐색한다. 캐시해서 블록을 찾으면 Disk I/O Call을 하지 않고, 못 찾으면 I/O Call을 한다. 버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.

 

오라클 SQL*Plus에서 V$SGA 뷰를 통해 버퍼캐시 사이즈를 확인할 수 있다.

 

논리적 I/O vs. 물리적 I/O

 

 논리적 블록 I/O는 SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다. Direct Path I/O가 작동하는 경우가 있으므로 논리적 I/O와 메모리 I/O가 정확히 같은 의미는 아니지만, 일반적 으로 같다고 생각해도 무방하다.

 물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다. SQL 처리 도중 읽어야 할 블록을 버퍼캐시 에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적 I/O 한다.

 메모리 I/O는 전기적 신호인 데 반해, 디스크 I/O는 액세스 암(Arm)을 통해 물리적 작용이 일어나므로 메 모리 I/O에 비해 상당히 느리다. 보통 10,000배쯤 느리며, 디스크 경합이 심할 경우에는 더 느리다.

왜 논리적 I/O 인가?

 

 SQL을 수행하려면 데이터가 담긴 블록을 읽어야 한다. SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 조건절에 같은 변수 값을 입력하면, 아무리 여러번 실행해도 매번 읽는 블록 수 는 같다. SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O다.

 Direct Path Read 방식으로 읽는 경우를 제외하면 모든 블록은 DB 버퍼캐시를 경유해서 읽는다. 따라서 논리적 I/O 횟수는 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다. 논리적 I/O가 메모리 I/O와 같은 개념은 아니지만, 결과적으로 수치는 같다.

 DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O다

 데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL을 실행할 때마다 다르다. 연속해서 실행하면 DB 버퍼 캐시에서 해당 테이블 블록의 점유율이 점점 높아지기 때문에 첫 번째 실행할 때보다 두 번째 실행할 때 줄어들고, 세 번째 실행할 땐 더 줄어든다. 한참 후에 다시 실행하면 DB 버퍼캐시가 다른 테이블 블록으 로 채워지기 때문에 반대로 물리적 I/O가 늘어난다.

 

버퍼캐시 히트율

 

버퍼캐시 효율을 측정하는 데 가장 많이 사용해 온 지표는 버퍼캐시 히트율(Buffer Cache Hit Ratio, 'BCHR')이다. 구하는 공식은 아래와 같다.

 

BHCR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) × 100

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

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

 

공식에서 BCHR은 읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다. 애플리케이션 특성에 따라 다르지만, 온라인 트랜잭션을 주로 처리하는 애플리케이션 이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다.

 

BCHR 공식을 보면 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논 리적 I/O를 줄여야 한다. BCHR 공식을 아래와 같이 변형하면, 쉽게 알 수 있다.

 

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

 

논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다. BCHR은 시스템 상황에 따라 달라지므로 물리적 I/O는 결국 시스템 상황에 의해 결정되는 통제 불가능한 외생변수다.

 

SQL 성능을 높이기 위해서는 할 수 있는 일은 논리적 I/O를 줄이는 일뿐이다. 예를 들어, 시스템 레벨 BCHR이 평균 70%라고 할 때, 특정 SQL의 논리적 I/O가 10,000개면 물리적 I/O는 대략 3,000개쯤 발생 할 것으로 예상할 수 있다.

 

- 물리적 I/O = 논리적 I/O × ( 100 - 70 )% = 10,000 × 30% = 3,000

 

논리적 I/O를 1,000개로 줄이면 물리적 I/O도 300으로 감소하고, 성능도 열 배 향상된다

 

- 물리적 I/O = 1,000 × 30% = 300

 

논리적 I/O를 줄이려면 SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다. 논리적 I/O는 항상 일정하게 발생하지만, SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생변수다. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

BCHR을 실제로 계산해보자. 아래는 SQL 트레이스를 통해 수집한 Call 통계 정보다. Query와 Current 항 목을 더한 값이 DB 버퍼캐시에서 읽은 총 블록 개수다. Disk 항목은 디스크에서 물리적으로 읽은 블록 개수다.

 

논리적 I/O는 메모리에서 1,364,044(=1,351,677 + 12,367)개 블록을 읽었으므로 총 1,364,044개이다. 블 록을 읽을 때는 해당 블록을 먼저 버퍼캐시에서 찾아보고 없을 때만 디스크에서 읽는다. 디스크에서 곧 바로 읽는 게 아니라 먼저 버퍼캐시에 적재하고서 읽기 때문에 DB 버퍼캐시에서 읽은 1,364,044개 블록 에는 디스크에서 읽은 601,458개 블록이 이미 포함돼 있다.

버퍼캐시에서 읽은 1,364,044개 블록 I/O가 논리적 I/O이므로 BCHR은 아래와 같이 계산한다.

 

BCHR = ( 1 - ( Disk / ( Query + Current ) ) ) × 100

        = ( 1 - ( 601,458 / ( 1,351,677 + 12,367 ) ) ) × 100

        = 55.9 %

 

- BCHR 주의할 점

BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다. 그 이유는 같 은 블록을 비효율적으로 반복해서 읽는 것도 BCHR이 높아지기 때문이다.

 

Single Block I/O vs. Multiblock I/O

 

 메모리 캐시가 클수록 좋지만, 비용적인 한계, 기술적인 한계 때문에 데이터를 모두 캐시에 적재할 수는 없다. 따라서 전체 데이터 중 일부만 캐시에 적재해서 읽을 수 있다.

 캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽는다.

 I/O Call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청 해서 메모리에 적재하는 방식을 'Single Block I/O'라 하고, 한 번에 여러 블록씩 요청해서 메모리에 적 재하는 방식을 'Multiblock I/O'라 한다.

 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다. 인덱스 는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다. (밑에 그림에서 ①,②,③이 해당됨. )

 

Single Block I/O 가 나타나는 오퍼레이션

- 인덱스 루트 블록을 읽을 때

- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

반대로 많은 데이터 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다. 그래서 인덱스를 이용하지 않 고 테이블 전체를 스캔할 때 이 방식을 사용한다. (그림에서 ④,⑤,⑥,⑦이 해당됨.)

테이블이 클수록 Multiblock I/O 단위도 크면 좋다. 그 이유는 I/O Call 수를 줄일 수 있기 때문이다.

읽고자 하는 블록을 DB 버퍼캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call을 한다. 그동안 프로세스는 대기 큐(Wait Queue)에 있게 된다. 대용량 테이블일수록 수많은 블록을 디스크에서 읽는 동안 여러번 대기 큐에 있게 되는데 이 때 Multiblock I/O 단위를 늘려 한 번에 많은 양을 요청해서 대기 큐 수를 줄인다면 성능을 높일 수 있다.

 

오라클에서 Multiblock I/O 단위는 db_file_multiblock_read_count 파라미터로 정한다.

블록 사이즈가 얼마건 간에 OS 단에서 보통 1MB 단위로 I/O를 수행한다(OS마다 다름). 일반적으로 OS 레벨 I/O 단위가 1MB, 오라클 레벨 I/O 단위가 8KB이므로 이 파라미터를 128로 설정하면 담을 수 있는 만큼 최대한 담게 된다(8KB × 128 = 1MB). OS는 자신의 I/O 단위만큼씩만 읽을 수 있으므로 오라클에서 그 이상으로 설정을 해도 1MB 이상을 담을 수 없다.

 

※ 추가설명

Multiblock I/O에서 '인접한 블록'이란 같은 익스텐트에 속한 블록을 의미한다. 따라서 Multiblock I/O 방 식으로 읽더라도 익스텐트 경계를 넘지 못한다. 예를 들어 한 익스텐트에 20개 블록이 담겨있고 Multiblock I/O 단위가 8이라고 할 때, 세 번째 I/O Call에서는 네 개 블록만 얻게되고, 네 개를 더 읽기 위 해 다음 익스텐트까지 읽지 않는다.

 

Table Full Scan vs. Index Range Scan

 

테이블에 저장된 데이터를 읽는 방식으로 테이블 전체를 스캔해서 읽는 방식(Table Full Scan)과 인덱 스를 이용해서 읽는 방식(Index Range Scan)이 있다.

Table Full Scan은 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식이고, Index Range Scan은 인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다

 

 Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 I/O Call을 통해 인접한 수십 ~ 수백 개 블록을 한 꺼번에 I/O하는 매커니즘이다. 시퀀셜 액세스와 Multiblock I/O는 수십 ~ 수백 건의 소량 데이터 찾을 때 수백만 ~ 수천만 건 데이터를 스캔하는 것은 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반 드시 인덱스를 이용해야 한다.

 

 Index Range Scan은 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록 을 못 찾으면, 레코드 하나를 읽기 위해 매번 대기 큐에 있어야하는 I/O 매커니즘이다. 따라서 많은 데이 터를 읽을 때는 Table Full Scan보다 불리하다. 이 방식은 읽었던 블록을 반복해서 읽는 비효율이 있다. 많은 데이터를 읽을 때 물리적인 블록 I/O 뿐만 아니라 논리적인 블록 I/O 측면에서도 불리하다. 한 블록 에 평균 500개 레코드가 있으면, 같은 블록을 최대 500번 읽는다. 만약 인덱스를 이용해 '전체 레코드 코 드'를 액세스한다면, '모든 블록'을 평균 500번씩 읽게 된다. 각 블록을 단 한 번 읽는 Table Full Scan보다 훨씬 불리하다.

 

캐시 탐색 매커니즘

 

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다. 구체적으로 아래 오퍼레이션은 모두 버퍼캐시 탐색 과정을 거친다.

- 인덱스 루트 블록을 읽을 때

- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

- 테이블 블록을 Full Scan 할 때

위 그림은 해시함수로 모듈러(mod) 함수를 사용하는 경우를 표현하고 있다. 실제로는 훨씬 더 정교한 알 고리즘으로 되어있지만 이 책 예시에서는 5로 나누었을 때의 나머지 값을 반환하는 모듈러 함수로 캐시 탐색 매커니즘을 설명한다. 버퍼캐시에서 20번 블록을 찾고자 한다. 블록 번호를 5로 나누면 나머지가 0 이다. 이 블록이 캐싱돼 있다면 버퍼 헤더가 첫 번째 해시 체인(해시 값 = 0)에 연결돼 있을 것이므로 찾 을 때 항상 첫 번째 해시 체인만 탐색하면 된다. 다른 예로 27번 블록을 찾고자 할 때, 블록 번호를 5로 나 누면 나머지가 2이다. 이 블록이 캐싱돼 있다면 버퍼 헤더가 세 번째 해시 체인(해시 값 = 2)에 연결돼 있 을 것이다. 그러나 현재 세 번째 해시 체인에는 27번 블록이 보이지 않는다. 이 때 세 번째 체인이 없다고 해서 다른 해시 체인을 탐색할 필요가 없다. 디스크로부터 읽어서 세 번째 체인에 연결해서 읽으면 된다.

 

버퍼캐시에서 블록을 찾을 때 이처럼 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식을 사용한다. 해시 구조의 특징을 요약하면 다음과 같다.

- 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨.

- 다른 입력 값(예를 들어, 4와9)이 동일한 해시 체인(=버킷)에 연결될 수 있음.

- 해시 체인 내에서는 정렬이 보장되지 않음.

 

 

메모리 공유자원에 대한 액세스 직렬화

 

 버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. 공유자원은 누구나 접근할 수 있다. 문제는 하나의 버퍼블록을 두 개 이상 프로세스가 '동시에' 접근하려고 할 때 발생한다. 동시에 접근하면 블록 정합성에 문제가 생길 수 있기 때문이다.

 

 따라서 자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 직렬화 (serialization) 메커니즘이 필요하는데, 이런 순차적인 접근이 가능하도록 지원하는 것이 래치(Latch) 다.

 

 SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데, 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다. 빠른 데이터베이스를 구현하려면 버퍼캐시 히트율을 높여야하지만, 래치 에 의한 경합이 생길 수 있기 때문에 캐시 I/O도 생각만큼 빠르지 않을 수 있다. 캐시버퍼 체인뿐만 아니 라 버퍼블록 자체에도 직렬화 메커니즘이 존재하는데, 바로 '버퍼 Lock'이다. 이런 직렬화 메커니즘에 의 한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O)자체를 줄여야 한다.