[SQLP] 1-3. 데이터 저장 구조 및 I/O 메커니즘

데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘을 통해 I/O에 대한 이해

 

1. SQL이 느린 이유 : 디스크 I/O 때문

 

데이터베이스 서버와 스토리지 서버 간의 I/O 병목

 

I/O처리 하는 동안 프로세스가 잠을 자고있다.

프로세스가 갖는 생명주기에서 실행중인 프로세스는 인터럽트에 의해 수시로 실행 준비로 전환된다.

 

[여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에]

 

인터럽트 없이 실행중인 프로세스도 디스크에서 데이터를 읽어야 할 때 CPU를 OS에 반환하고 수면(waiting) [대기] 상태에서 I/O가 완료되기를 기다린다.

 

[정해진 OS 함수를 호출 (I/O CALL)하고 CPU반환한 채 알람을 설정하고 대기 큐에서 대기]

 

-> 실행중이어야 할 프로세스가 대기하고 있기 때문에, I/O가 많으면 성능이 느리다.

 

 

스토리지의 성능으로 인해 I/O 튜닝이 안된 시스템에서는, 수많은 프로세스에 의해 동시다발적으로 일어나는 I/O CALL 때문에 디스크 경합이 심해지고, 그만큼 대기 시간이 늘어난다.

 

-> SQL이 느린 이유도 디스크 I/O 때문.

 

---

 

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

 

테이블스페이스, 세그먼트, 익스텐트, 블록 간 관계

: 데이터를 저장하기 위해 테이블스페이스를 생성하는데, 테이블스페이스는 세그먼트를 담는 컨테이너로, 여러 개의 데이터 파일로 구성된다.

 

생성한 테이블스페이스에 세그먼트를 생성한다.

[세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트로 테이블, 인덱스 생성시 데이터를 어떤 테이블스페이스에 저장할지 지정]

 

 

파티션 구조에서 세그먼트는 여러 익스텐트로 구성되는데, 파티션 구조가 아니라면 테이블과 인덱스 모두 하나의 세그먼트로 구성될 수 있다.

 

-> 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트로 구성되고, LOB 칼럼의 경우 그 자체가 하나의 세그먼트를 구성하므로, 속한 테이블이 아닌 다른 공간에 값을 저장한다.

 

[익스텐트는 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면 테이블스페이스로부터 익스텐트를 추가로 할당받는다.]

 

[익스텐트 : 공간을 확장하는 단위 / 데이터 블록 (페이지) : 사용자가 입력한 레코드를 실제로 저장하는 공간]

 

[한 블록은 하나의 테이블이 독점하기 때문에, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드이다.

한 익스텐트도 하나의 테이블이 독점하기 때문에, 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다.]

 

[익스텐트 내 블록은 서로 인접한 연속된 공간, 익스텐트 끼리는 불연속된 공간]

 

데이터 파일 간의 관계

: 세그먼트의 모든 익스텐트는 같은 데이터파일에 위치하지 않아도 되기 때문에, 하나의 테이블 스페이스를 여러 데이터 파일에 구성시켜서 파일 경합을 줄이기 위해 데이터를 가능하면 여러 데이터파일로 분산 저장한다.

 

분류	설명
블록	데이터를 읽고 쓰는 단위
익스텐트	공간을 확장하는 단위. 연속된 블록 집합
세그먼트	데이터 저장공간이 필요한 오브젝트
테이블스페이스	세그먼트를 담는 컨테이너
데이터파일	디스크 상의 물리적인 OS 파일

 

---

 

3. 블록 단위 I/O

 

데이터베이스에서 데이터를 읽고 쓰는 단위 : 블록

 

-> 특정 레코드를 읽고 싶다고 해도 블록 단위로 읽어야 한다. [테이블, 인덱스]

 

 

---

4. 시퀀셜 액세스 vs 랜덤 액세스

 

: 테이블 또는 인덱스 블록을 액세스하는 방식

 

1) 시퀀셜 엑세스 : 순서에 따라 차례대로 블록을 읽는 방식

 

인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있어서, 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식

 

테이블 블록은 오라클의 경우 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리해, 각 인스텐트의 첫 번쨰 익스텐트의 첫 번째 블록 주소 값을 갖는다.

 

즉, 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, Full Table Scan이다.

 

 

2) 랜덤 액세스 : 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

---

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

DB 버퍼캐시

: SGA의 구성요소로, 자주 읽은 데이터 블록을 저장하는 데이터 캐시

 

[라이브러리 캐시의 경우 SQL과 실행계획, DB 저장형 함수/프로시저 등 저장하는 코드 캐시]

 

-> 디스크에서 어렵게 읽은 데이터 블록을 캐싱해둬서 같은 블록에 대한 반복적인 I/O Call을 줄인다.

 

 

데이터 블록을 읽을 때 버퍼캐시부터 탐색하고, 없다면 I/O Call을 한다.

[버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스에도 영향을 준다]

 

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

논리적 블록 I/O : SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O

-> 일반적으로 논리적 I/O와 메모리I/O는 같은 의미로 사용한다. [Direct Path Read 방식 제외]

 

물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O

-> SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때 디스크를 액세스하므로 논리적 블록 I/O 중 일부

 

[메모리 I/O는 전기적 신호고, 디스크 I/O는 물리적으로 작동하기 때문에 속도가 더 느리기 때문에 병목 발생]

 

분류	설명
논리적 I/O	DB 버퍼캐시에서 블록을 읽은 횟수로, 즉 SQL을 수행하면서 읽은 총 블록 I/O수
물리적 I/O	DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O [여러번 수행할 수록 DB 버퍼캐시에서의 점유율이 늘어나 점점 줄어든다.] [실행 후 시간이 지날수록 다른 테이블블록으로 채워져 점점 늘어난다.]

 

버퍼캐시 히트율

: Buffer Cache Hit Ratio [BCHR]

[읽은 전체 블록 중 물리적 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율]

 

물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 논리적 I/O를 줄여야 한다

[물리적 I/O는 줄일 수 없는 외생적 변수이기 때문에]

 

SQL 튜닝 : 읽는 총 블록 개수를 줄여서 논리적I/O를 줄임으로써 물리적  I/O를 줄이는 것]

 

BCHR은 SQL 성능을 좌우하지만 BCHR이 높다고해서 효율적인 SQL은 아니다.

[같은 블록을 반복해서 읽는 비효율 -> 인덱스와 NL 조인]

 

---

6. 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 단위도 큰게 효율적 [프로세스가 잠자는 횟수를 줄이기 위해서]

 

잠자는 프로세스

읽고자 하는 블록을 DB 버퍼캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call을 하는 동안

-> 프로세스는 대기 큐에서 잠을 잔다.

 

대용량 테이블인 경우 수많은 블록을 디스크에서 읽는 동안 여러 차례 잠을 자는데, 잠을 자는걸 피할 수 없다면

-> 한꺼번에 많은 양을 요청해서 잠자는 횟수를 줄여서 성능을 높인다.

[대용량 테이블 Full Scan시 Multiblock I/O단위를 크게 하는 이유]

 

Multiblock I/O

:캐시에서 찾지 못한 특정 블록을 읽기위해 I/O Call을 할 때, 디스크 상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능

 

[블록 사이즈에 상관없이 OS 단에서는 일반적으로 1MB 단위로 I/O를 수행 -> 한번 I/O를 할때 1MB 크기의 손수레]

-> 테이블 전체 블록 읽을 때는 손수레에 한 번에 담을 수 있는 만큼 최대한 많이 담는게 유리하다.

 

 

일반적으로 OS 레벨 I/O

OS 레벨 I/O단위	1MB
오라클 레벨 I/O 단위	8KB
파라미터를 128로 설정하면 담을 수 있는 만큼 최대한 담게 된다. [8KB X 128 = 1MB] [오라클 레벨에서 그렇게 설정할 수 있지만, OS는 자신의 I/O단위만큼 읽는다]

 

 

인접한 블록

: 같은 익스텐트에 속한 블록으로 Multiblock I/O 방식으로 읽어도 익스텐트 경계를 넘지 못한다.

 

---

7. Table Full Scan vs Index Range Scan

 

테이블에 저장된 데이터를 읽는 방식 -> 테이블 전체 스캔, 인덱스 이용 방식 [인덱스를 이용한 테이블 액세스]

 

Table Full Scan

: 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식

 

Index Range Scan

: 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식

[ ROWID는 테이블레코드가 디스크 상에 어디 저장되었는지 가리키는 위치 정보]

-> 테이블 전체 스캔의 방식은 일반적으로 SQL 성능에 도움이 되지 않지만, 인덱스가 SQL 성능을 떨어뜨리는 경우도 존재하기 때문에 단언할 수 없다.

 

한 번에 많은 데이터를 처리하는 집계용 SQL이나 배치 프로그램에선 상당수가 테이블 전체 스캔으로 유도하면 성능이 빨라진다.

[조인을 포함한 경우, 조인 메소드로 해시 조인을 선택하면 된다.]

 

인덱스를 이용하는데 성능이 느려지는 경우

테이블 전체 스캔은 : 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다.

[한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 한 번의 수면(I/O Call)을 통해 인접한 많은 블록을 한꺼번에 I/O하는 매커니즘이기 때문에 스토리지 스캔 성능이 좋아질수록 성능이 좋아진다.] 

 

따라서 큰 테이블에서 소량 데이터를 검색할 경우에는 비효율적이므로 반드시 인덱스를 사용

 

Index Range Scan은 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽기 때문에, 캐시에서 블록을 못 찾으면, '레코드 하나를 읽기 위해 매번 잠을 자는 I/O 매커니즘'이다.

[많은 데이터를 읽을 때에는 비효율적이기 때문에 스토리지 스캔 성능이 아무리 좋아져도 성능 향상은 미미]

-> 또한 읽었던 블록을 반복해서 읽는 비효율적인 측면이 있으므로, 많은 데이터를 읽을 때 논리적 블록 I/O에서도 불리하다.

 

따라서 Index Range Scan은 큰 테이블에서 일부 데이터를 찾기 위한 도구로, 읽을 데이터가 일정량을 넘으면 Table Full Scan이 유리하다.

 

 

---

8. 캐시 탐색 메커니즘

: Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.

 

버퍼캐시 탐색 과정을 거치는 오퍼레이션

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

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

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

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

:테이블 블록을 Full Scan 할 때

 

버퍼캐시 구조

: 해시 구조로 관리하며, 해시 함수 알고리즘을 이용해 캐시 탐색 매커니즘을 구성한다.

 

버퍼캐시에서 블록을 찾을 때

해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식 사용

 

해시 구조 특징

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

: 다른 입력 값이 동일한 해시 체임에 연결 가능

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

 

 

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

: 버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원

[공유자원은 모두에게 권한이 있기 때문에 누구나 접근 가능]

 

하지만 하나의 버퍼블록을 두 개 이상의 프로세스가 동시 접근시 문제 발생.

-> 블록 정합성이 깨진다.

 

따라서, 자원을 공유하는 것처럼 동작하지만, 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 한다.

-> 직렬화 매커니즘

 

직렬화 매커니즘

 

래치

:공유 캐시의 특정 자원을 두 개 이상의 프로세스가 같이 사용할 수 없기 때문에

특정 순간에 한 프로세스만 사용할 수 있도록 다른 프로세스가 줄을 서게 하는 매커니즘

 

SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데, 버퍼캐시에는 캐치버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다.

 

캐시버퍼 체인 래치

:대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색하는데, DBA를 해시 함수에 입력하고 반환된 값으로 스캔할 해시 체인을 찾는다.

[해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경할 수 없도록 해시 체인 래치 존재하고 키를 획득한 프로세스 만이 체인으로 진입 가능]

 

DB 효율을 위해 버퍼캐시 히트율을 높히는데, 래치에 의한 경합에 의해 캐시 I/O도 빠르지 않을 수 있다.

 

버퍼 Lock

:버퍼 블록의 직렬화 메커니즘으로, 캐시버퍼 체인 래치를 해제하기 전에 먼저 버퍼 헤더에 버퍼Lock을 설정해 버퍼블록 자체에 대한 직렬화 문제를 해결한다. [오라클]

 

-> 직렬화 매커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄인다.