Database I/O

Database 읽기 단위 

PC에서 한글 파일이나 Word 파일을 저장할 때, 파일 단위로 읽고 저장한다. 물론 내부 메카니즘은 좀 더 섬세한 면이 있겠지만, 대용량 파일을 저장/수정할 때 시간이 걸리는데는 이러한 이유일 것이다.

Database는 익히 알려진대로, 블록(Block) 단위로 읽고 저장한다. 오라클은 기본적으로 블록 사이즈가 8kb이다. 즉, database가 아주 작은 데이터를 가져온다고 하더라도, 최소한 8kb의 블록을 읽는 셈이다. Database 튜닝에 있어서, 가장 중요한 것은 바로 이 블록 단위 I/O를 줄이는 것이다.

참고로 오라클은 아래와 같은 방법으로 블록 사이즈를 확인할 수 있다.

[그림1] block size

Database 액세스 방법(Sequential VS Random Access)

데이터 블록을 액세스 하는 방법은 시퀀셜 액세스와 랜덤 액세스가 있다.  

먼저 시퀀셜 액세스는 연결된 순서에 따라 차레대로 블록을 읽는 방식을 말한다. 인덱스 리프 블록은 앞뒤를 가리키는 주소값, 즉 DBA(Data Block Address)를 통해 논리적으로 서로 연결돼 있다. 이 주소 값을 이용해서 순차적으로 액세스 하는 방식이 시퀀셜 액세스다. 

테이블 블록 간에는 서로 논리적인 연결고리를 갖고 있지 않는데, 테이블은 어떻게 시퀀셜 방식으로 액세스할까?

오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다. 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 곧 Full Table Scan이다. ^[각주:1]

랜덤 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 레코드 한 건을 읽기 위해 한 블록씩 접근하는 방식이다.

DB 버퍼캐시

DB 버퍼 캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다. 

[그림2] SGA 구조

라이브러리 캐시가 Server Request로 들어오는 여러 SQL을 저장하고 있다면, DB Buffer Cache는 이러한 SQL을 실행해서 얻은 데이터를 저장해놓은 공간으로 이해하자. 따라서 Buffer Cache에서는 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.

참고로 아래와 같이 SGA 뷰를 통해 SGA의 각 영역별 사이즈를 확인할 수 있다.

[그림3] sga뷰

논리적 I/O, 물리적 I/O

논리적 I/O는 SQL을 처리하는 과정에서 그림 2의 Buffer Cache에서 발생한 총 블록 I/O를 말한다. 이에 반해 물리적 I/O는 디스크에서 발생한 I/O를 말한다. SQL을 처리하면서, 찾고자 하는 데이터가 없을 경우, 디스크를 액세스하는 경우가 물리적 I/O이다. 

버퍼캐시 히트율(Buffer Cache Hit Ratio)

버퍼캐시 히트율이란 버퍼 캐시 효율을 측정하는 지표로써 전체 읽은 블록 중, 메모리 버퍼 캐시에서 찾은 비율을 말한다. 구하는 공식은 아래와 같다.

BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100

또는 ((논리적/IO - 물리적I/O) / 논리적 I/O) * 100

또는 (1 - (물리적 I/O) / (논리적 I/O) * 100

버퍼캐시 히트 비율이 낮은 것일 수록 SQL 성능을 떨어뜨리는 주범이다.

애플리케이션 특성에 따라 다르지만, 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다. 핵심 트랜잭션이 시스템 전체 부하의 대부분을 차지하므로 열심히 튜닝하면 99%는 결코 달성하기 어려운 수치가 아니다. ^[각주:2]

논리적인 블록요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야할 블록수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리이다.

BCHR을 계산해보자.

call   count  cpu  elapsed disk query current rows 
------ ----- ----- ------- ---- ----- ------ ---- 
Parse     15  0.00    0.08    0    0      0   0 
Execute   44  0.03    0.03    0    0      0   0 
Fetch     44  0.01    0.13   18  822      0  44 
------ ----- ----- ------- ---- ----- ------ ---- 
total    103  0.04    0.25   18  822      0  44 

총 읽은 블록 수 = 822

버퍼 캐시에서 찾은 블록수 = 822 - 18 = 804

BCHR = (822-18)/822 = 97.8%

^[각주:3]

Single Block I/O, MultiBlock I/O

Single Block I/O란 한번에 I/O Call에 하나의 데이터 블록만 읽어서 메모리에 적재하는 방식으로, 인덱스와 테이블을 액세스할 때, 이 방식을 사용한다.

Multi Block I/O란 I/O Call이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 방식으로 Full Table Scan할 때, 이 방식을 사용한다.

1. 조시형, 친절한 SQL 튜닝, DBian, 2018, p48 [본문으로]
2. 조시형, 친절한 SQL 튜닝, DBian, 2018, p53 [본문으로]
3. 제4절 데이터베이스 IO 원리, http://wiki.gurubee.net/pages/viewpage.action?pageId=26744692 [본문으로]