데이터 저장 구조 및 I/O 매커니즘
I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다! 튜닝의 원리를 제대로 이해하려면, I/O에 대한 이해가 중요할 수 밖에 없다.
SQL 튜닝을 본격적으로 시작하기에 앞서 데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘을 살펴보자
1. SQL이 느린 이유
SQL이 느린 이뉴는 디스크 I/O 때문이다.
OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스가 잠을 자기 때문이다.
프로세스는 생성, 종료, 준비, 실행, 대기를 반복하는데, 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태로 전환했다가 다시 실행 하기를 반복한다.
여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있다.
프로세스가 디스크에서 데이터를 읽어야 할 때 CPU를 OS에 반환하고 수면 상태에서 I/O가 완료되기를 기다린다.
(이때 OS 함수를 호출(I/O Call)하고 CPU를 반환 한 채 알람을 설정하고 대기 큐에서 기다림)
따라서 I/O가 많이 발생하면, 성능이 느릴 수 밖에 없다.
전반적으로 I/O 튜닝이 안 된 시스템이라면, 수많은 프로세스에 의해 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고, 대기 시간이 늘어난다. SQL이 느린 이유는 바로 여기에 있다!
2. 데이터베이스 저장 구조
테이블 스페이스 : 세그먼트를 담는 컨테이너 여러 개의 데이터파일(디스크 상의 물리적 파일)로 구성
세그먼트 :
- 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트 -> 테이블, 인덱스를 생성할 때 어떤 테이블스페이스에 저장할지를 지정한다.
- 여러 익스텐트로 구성된다.
익스텐트:
- 공간을 확장하는 단위
- 데이터를 입력하다가 공간이 부족해지면, 테이블스페이스로부터 익스텐트를 추가로 할당 받음
- 연속된 블록의 집합
- 하나의 테이블 데이터를 저장함
블록:
- 사용자가 입력한 레코드를 실제로 저장하는 공간
- 한 블록은 하나의 테이블이 독점한다. 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.
* 세그먼트에 할당된 모든 익스텐트가 같은 데이터 파일에 위치하지 않을 수 있다.
익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다!
DBA(Data Block Address)
모든 데이터 블록은 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값을 갖는다.
이를 DBA라고 부른다. 데이터를 읽고 쓰는 단위가 블록이므로 데이터를 읽으려면 먼저 DBA를 확인해야 한다.
인덱스의 ROWID가 DBA + 로우 번호(블록 내 순번)으로 구성된다.
테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다. 익스텐트 맵을 통해 각 익스텐트의 첫 블록 DBA를 알 수 있다.
3. 블록 단위 I/O
데이터베이스에서 데이터를 읽고 쓰는 단위는 블록 단위이다. 데이터 I/O 단위가 블록이므로, 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
show parameter block_size
-- 블록 사이즈를 확인할 수 있다. 대략 8KB
인덱스도 블록 단위로 데이터를 읽고 쓴다.
4. 시퀀셜 엑세스 VS 랜덤 엑세스
- 시퀀셜 엑세스
- 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
- 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 연결됨 -> 이를 순차적으로 스캔하는 방식
- 테이블은 논리적 연결고리가 없음으로, 익스텐트 맵을 통해 읽어야할 익스텐트를 찾고, 첫 블록 뒤를 연속해서 읽으면서 full scan함
- 랜덤 엑세스
- 논리적, 물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근 하는 방식
- INDEX ROWID를 통해 테이블에 접근할 때 랜덤 엑세스가 발생
5. 논리적 I/O vs 물리적 I/O
DB 버퍼 캐시
SGA의 핵심 구성 요소 중 하나로 디스크에서 읽어온 데이터를 캐싱하는 장소이다.
데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다.
논리적I/O
논리적 블록 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O를 의미한다.
보통 메모리 I/O를 곧 논리적 I/O라 생각해도 무방하다.
물리적I/O
물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 의미한다.
블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적 I/O로 한다.
*데이터 베이스에서 논리적 I/O는 조건절에 같은 변수 값을 입력하면 여러 번 실행해도 매번 읽는 블록 수가 같다.
물리적 I/O는 데이터 입력이나 삭제가 없어도, 매 SQL 실행마다 달라 질 수 있다 -> 읽은 데이터들이 DB 버퍼캐시에 캐싱되기 때문임
버퍼캐시 히트율
BCHR = (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 )*100
= ( (논리적 I/O - 물리적 I/O)/ 논리적 I/O) * 100
= (1- (물리적 I/O) / (논리적 I/O) ) *100
버퍼캐시 히트율은 전체 블록 중 물리적 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 의미한다.
BHCR 공식을 통해 중요한 성능 원리를 발견할 수 있다. 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O 횟수를 줄여야 한다.
물리적 I/O = 논리적 I/O *(100% -BHCR)
-논리적 I/O는 일정함 -> 물리적 I/O는 BHCR에 의해 결정됨 -> BHCR은 시스템 상황에 따라 달라짐 (외생변수) -> 따라서 논리적 I/O를 줄이는 것 밖에 할 수 없다.
논리적 I/O는 SQL 튜닝을 통해 줄일 수 있는 내생변수이므로, 논리적 I/O를 줄임으로써 물리적I/O를 줄이는 것이 곧 SQL 튜닝이다.
6. Single Block I/O vs Multiblock I/O
Single Block I/O
- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식 -> 소량의 데이터를 읽을 때 효율적
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소로 브랜치 블록을 읽을 때
- 브랜치 블록에서 리프 블록을 읽을 때
- 리프 블록에서 테이블 블록을 읽을 때
Multiblock I/O
- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
- 많은 데이터 블록을 읽을 때는 이것이 훨씬 효율적 -> 테이블 전체 스캔
- 캐시에서 찾지 못한 특정 블록을 읽기 위해 I/O Call을 수행할 때 인접 블록을 한꺼번에 적재한다. (보통 1MB 단위로 I/O 수행)
블록을 DB 버퍼캐시에서 찾지 못하면, 블록을 디스크에서 읽기 위해 I/O Call을 하고, 이때 프로세스는 대기 큐에서 기다린다.
대용량 테이블에 Single Block I/O라면, 그만큼 많은 대기 시간이 소모될 것이다. Multiblock를 사용한다면, 한번에 많은 데이터를 읽어오고 대기하면 되므로 훨씬 효율적일 것이다.
* db_file_multiblock_read_count 파라미터로 크기 확인 가능하다. 일반적으로 OS 레벨 I/O단위가 1MB, 오라클 레벨 I/O 단위가 8KB이므로, 파라미터를 128로 설정하면, 1MB가 되므로 딱 맞다.
*Multiblock I/O 중간에 Single Block I/O가 나타나는 이유
- 익스텐트 맵은 테이블 블록에 대한 인덱스, Multiblock I/O는 배치 I/O라고 생각하자
> Multiblock I/O 단위가 4일 때 익스텐트 맵을 통해 첫 번째 익스텐트에서 읽어야 할 블록 목록을 확인하니
1,2,3,4,5,6,7,8,9,10이었고, 그중 1번, 6번, 8번 블록이 현재 버퍼캐시에 캐싱 돼 있다.
1 -> 캐시버퍼 체인에서 찾음 (바로 읽기)
2,3,4,5 -> 디스크 I/O 보류했다가 5번일 때 Multiblock I/O로 실행
6 -> 캐시 버퍼 체인
7 -> 디스크 I/O 보류
8 -> 캐시 버퍼에서 찾음 이때, 7번 블록만을 위해 Single Block I/O 실행 -> 이와 같은 과정 반복
따라서 full scan 중간 중간 캐시 버퍼에서 찾고 못찾고를 반복하는 과정에서 Single Block I/O가 발생
7. Table Full Scan vs Index Range Scan
테이블 전체를 스캔해서 읽는 방식과 인덱스를 사용해서 읽는 방식 두 가지로 데이터를 읽는다.
Table Full Scan
- 테이블에 속한 블록 전체를 읽어서 데이터를 찾는다.
Index Range Scan
- 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아간다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지 가리키는 위치 정보다.
대량의 데이터를 처리하는 집계용 SQL과 배치 프로그램은 Table Full Scan 하는 편이 Index Range Scan보다 낫다.
이는 Index Scan이 Single Block I/O와 랜덤 엑세스 방식을 사용하기 때문이다.
따라서 읽을 데이터가 일정량을 넘어서면, 인덱스보다 Table Full Scan이 유리하다.
8. 캐시 탐색 매커니즘
Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.
- 인덱스 루트 블록 읽을 때
- 인덱스 루트 블록 -> 브랜치 -> 리프 -> 테이블 블록을 읽는 과정
- 테이블 블록을 Full Scan 할 때
DBMS는 위 그림과 같은 버퍼캐시 구조를 가지고 있다.
버퍼캐시에서 블록을 찾는 과정은 다음과 같다.
- 모듈러 함수를 이용해 블록 해시 체인을 만들어 블록을 연결한다. (같은 입력 값은 항상 동일한 해시 체인에 연결)
- 블럭을 찾기 위해 모든 버퍼를 탐색할 필요없이, 연결된 체인만 검사하면 된다. 이때 해당하는 블록이 체인에 없다면 물리적 I/O를 통해 연결한다.
- 해시 체인 내에서는 정렬이 보장되지 않는다.
버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다.
모두에게 권한이 있기 때문에 누구나 접근할 수 있다.
하나의 버퍼블록에 두 개 이상 프로세스가 동시에 접근하려할 때 블록 정합성 문제가 생길 수 있다.
따라서 순차적으로 접근할 수 있도록 내부적으로 직렬화 되어있다.
*캐시버퍼 체인 래치
대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색한다 -> 이때 체인 구조 변경이 발생하면 곤란하므로, 해시 체인 래치를 통해 체인에 Lock을 건다. -> Key를 가진 프로세스만 해당 체인에 접근할 수 있다.
읽고자하는 블록을 찾았으면 캐시버퍼 체인 래치를 곧바로 해제해야 한다. 이때 래치를 해제한 순간에 같은 블록에 접근해 데이터를 읽고 쓴다면 정합성 문제가 발생할 수 있다.
이를 방지하기 위해 오라클은 버퍼 Lock를 사용한다. 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정하여 블록버퍼 자체에 대한 직렬화 문제를 해결한다.