1. SQL 처리 과정과 I/O [3/3]

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

1.3.1 SQL이 느린 이유

대부분은 I/O 문제이다.

 

I/O란 입출력을 뜻하는 말로 입출력 동안에는 프로세스가 잠을 잔다.
프로세스가 일하지 않고 자는 이유는 여러가지가 있지만 가장 대표적인 이유는 I/O다.

 

프로세스는 '실행 중인 프로그램'을 나타내며 아래와 같은 생명주기를 갖는다.

생성 이후로 종료 전까지 준비와 실행, 대기 상태를 반복한다. 실행 중인 프로세스는 수시로 인터럽트로 인해 준비 상태로 전환 했다가 다시 실행 상태로 전환되고는 한다. 여러 프로세스가 한 CPU를 공유할 수는 있지만 특정 순간에는 한 프로세스만 사용할 수 있기 때문에 해당 메커니즘이 필요하다.

 

인터럽트가 없었던, 열심히 일하던 프로세스도 디스크에서 데이터를 잃어야 할 땐 CPU를 OS에 반환하고 잠시 대기 상태에서 I/O가 완료되길 기다린다. 

정해진 OS 함수를 호출해 CPU를 반환한 채 대기 큐에서 잠을 자는 것이다.

I/O가 많으면 필연적으로  성능이 느릴 수 밖에 없다.

I/O Call의 속도는 Single Block을 기준으로 평균 10ms 정도 된다. 초당 100 블록을 읽는 셈이다. (큰 캐시 스토리지는 좀 더 빠르다.) 스토리지 성능은 계속 빨라지고 있지만 아직 우리 기대에는 못 미친다. 

 

I/O 메커니즘을 알기전에 데이터베이스 저장 구조부터 확인해보자.

1.3.2 데이터베이스 저장 구조

데이터를 저장하기 위해서는 먼저 테이블 스페이스를 생성해야한다.

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

 

위처럼 테이블 스페이스를 생성했으면, 세그먼트를 생성한다. 세그먼트는 테이블이나 인덱스처럼 데이터 저장공간이 필요한 오브제그로 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 결정한다.

 

또한 세그먼트는 여러 익스텐트로 구성되며, 파티션 구조가 아닌 이상 테이블도 하나의 세그먼트요, 인덱스도 하나의 세그먼트다. 파티션 구조라면 각 파티션이 하나의 세그먼트가 된다.

 

익스텐트는 공간을 확장하는 단위로, 데이터를 입력하다가 공간이 부족해지면 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가로 할당받는다.

세그먼트와 익스텐트

 

세그먼트는 데이터베이스에서 논리적인 구조를 나타낸다. 이는 테이블이나 인덱스 등을 포함한다.

즉 하나의 테이블은 하나의 세그먼트로 표현할 수 있다.

 

익스텐트는 데이터베이스 세그먼트의 물리적인 공간을 의미한다. 하나의 익스텐트는 여러 블록으로 구성될 수 있다.

세그먼트에 데이터를 추가할 때 익스텐트를 할당하고 추가적인 공간이 필요할 때 익스텐트를 추가 할당하여 데이터 공간이 확장하는 식.

 

요약

세그먼트는 데이터베이스의 논리적 구조를 나타내며, 특정 객체(예: 테이블, 인덱스 등)를 포함합니다.
익스텐트는 세그먼트의 물리적 저장소 단위로, 연속된 블록의 집합입니다.

익스텐트 단위로 공간을 확장하게 되지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록 단위이다.

특정 DBMS에서는 블록 대신 페이지라는 용어를 사용하기도 한다.

한 블록은 하나의 테이블이 독점한다. 즉 한 블록의 레코드들은 모두 같은 테이블의 레코드이다.

한 익스텐트도 하나의 테이블이 독점한다.

 

세그먼트 공간이 부족해지면 테이블 스페이스로부터 익스텐트를 추가 할당받는데 한 세그먼트의 모든 익스텐트가 같은 데이터 파일에 위치하지 않을 수 있다. 하나의 테이블 스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해DBMS가 데이터를 가능한 여러 데이터파일로 분산 저장하기 때문이다.

파일 경합 : 여러 프로세스가 같은 파일에 동시에 접근하여 충돌하는 상황. 한 프로세스가 파일에 데이터를 쓰고 있는 동안 다른 프로세스가 같은 파일을 읽으려고 하면, 읽기 작업이 완료되지 않거나 잘못된 데이터를 읽을 수 있다.

익스텐트 내 블록은 인접한 연속 공간이지만, 익스텐트 끼리는 연속된 공간이 아니라는 걸 알 수 있다.

오라클 상의 세그먼트에 할당된 익스텐트 목록 조회.

SELECT segment_type, tablespace_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner = USER AND segment_name = 'MY_SEGMENT'
ORDER BY extent_id;

 

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

이들 간 관계 ERD

 

1.3.3. 블록 단위 I/0

클라우드에 위치한 문서는 파일 단위로 저장하고 파일 단위로 읽는다.

그렇다면 데이터베이스에서 데이터를 읽고 쓰는 단위는 무엇일까?

파일 단위나 테이블 세그먼트 단위로 읽는건 상상하기 어렵다. 데이터 I/O 단위가 블록이므로 블록이 바로 DBMS가 데이터를 읽고 쓰는 단위이다. 특정 레코드 하나를 읽으려 해도 블록을 통째로 읽는다. 심지어 1바이트짜리를 컬럼 하나만 읽으려해도 블록을 통째로 읽는다.

 

아래의 사진은 EMP 테이블에 데이터가 저장된 모습을 표현한다. 

 

오라클 기준으로 1바이트를 읽기 위해 8KB를 읽는 셈이다.

 

테이블 뿐만이 아니라 인덱스도 블록 단위로 읽고 쓴다. 

인덱스 구조에 대한 설명은 2장에서 다시 설명한다.

 

1.3.4 시퀀셜 엑세스 VS 랜덤 엑세스

테이블 혹은 인덱스 블록을 액세스하는 방식은 시퀀셜 엑세스와 랜덤 엑세스로 나뉜다.

1. 시퀀셜 엑세스

논리적 혹은 물리적으로 연결된 순서에 따라 차례대로 읽는 방식이다. 인덱스 리프 블록은 앞 뒤를 가리키는 주소 값을 통해 논리적 연결이 되어 있다. 이 주소 값을 이용해 순차적으로 스캔하는 방식이 시퀀셜 엑세스 되시겠다.

 

테이블 블록 간에는 서로 간 논리적 연결고리를 가지고 있지 않다. 어떻게 테이블은 시퀀셜 방식을 사용할까?

 

세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵 형태로 관리한다. 익스텐트 맵은 각 익스텐트 첫 블록 주소 값을 갖는다. 

즉, 읽어야할 익스텐트 목록을 익스텐트 맵에서 얻고 각 익스텐트 첫 블록 뒤에 연속해서 저정된 블록을 읽으면 그것이 Full Table Scan이다.

 

2. 랜덤 엑세스

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

 

1.3.5 논리적 I/O VS 물리적 I/O

- DB 버퍼 캐시

강조하자면, 디스크 I/O가 SQL의 성능을 결정한다. 자주 읽는 블록을 매번 디스크에서 읽는 건 매우 비효율적이다. DBMS에 데이터 캐싱 메커니즘이 필수인 이유이다.

데이터 버퍼 캐시로 SGA의 중요 구성요소 중 하나이다.

 

디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로서 같은 블록에 대한 반복적인 I/O를 줄이는 목적을 가진 것이 DB 버퍼 캐시 되시겠다.

 

위처럼 서버 프로세스와 데이터 파일 사이에 버퍼 캐시가 있음에 데이터 블록을 읽을 땐 버퍼 캐시부터 항상 확인한다.

오라클 SQL*PLUS 상에서 버퍼캐시 사이즈를 확인하는 쉬운 방법

SHOW sga

 

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

논리적 블록 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O를 말한다. 메모리상의 버퍼 캐시를 경유하므로 메모리 I/O 가 곧 논리적 I/O 라고 해도 무방하다.

 

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

 

왜 논리적 I/O인가?

데이터 베이스 세계에서 논리적 일량과 물리적 일량을 정의해보자. 

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

 

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

DB 버퍼캐시에서 블록을 못찾아 디스크에서 읽은 블록 I/O가 물리적 I/O이다. 이는 입력이나 삭제가 없더라도 SQL을 실행할 때마다 다르다. 연속 실행 시 DB 버퍼 캐시의 데이터 블록 점유율이 높아지기 때문이다. 반대로 한참 뒤에 실행하면 점유율은 내려간다.

 

버퍼캐시 히트율

가장 많이 사용하는 지표는 BCHR이다. (Buffer Cache Hit Ratio)

BCHR = 전체 블록 중 물리적인 디스크 I/O를 수반하지 않은, 메모리에서 찾은 비율.

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

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

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

 

위를 통해 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야함을 알 수 있다.

 

그렇다면 논리적 I/O는 어떻게 줄일 수 있을까? SQL 튜닝을 통해 읽는 블록 자체를 줄이면 된다.

논리적 I/O를 줄임으로 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

BHCR에는 주의해야할 함정이 있다. BHCR이 SQL 성능을 좌우하지만 높다고 해서 효율적인 SQL을 의미하진 않는다.

같은 블록을 비효율적으로 반복해서 읽으면 BHCR이 높아진다.

 

1.3.6 Single Block I/O VS Multi Block I/O

메모리 캐시가 클수록 좋지만, 모든 데이터를 캐시에 적재할 순 없다. 여러 한계 때문에 일부만 캐시에 적재해서 읽을 수 있다. 캐시에서 못 찾은 블록은 I/O CALL을 통해 디스크에서 버퍼캐시에 적재하고 읽는다. 한 번에 한 블록만 요청하기도 하고 여러 블록을 요청하기도 한다. 한 번에 한 블록만 요청해서 적재하는 방식을 Single Block I/O라고 한다. 반대로 여러 블록을 요청해서 적재하는 방식을 Multi Block I/O라고 한다. 

인덱스를 이용할 땐 보통 인덱스와 테이블 블록 모두 Single Block 방식을 이용한다.

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

 

반대로 많은 데이터 블록을 읽을 땐 Multi Block I/O 방식이 효율적이다. 그래서 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식이 좋다. 

읽고자 하는 블록을 DB 버퍼 캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call한다. 그 동안 프로세스는 대기 큐에서 잠을 잔다. 대용량이라면 수많은 블록을 읽는 동안 여러 번 잠을 잘텐데, 기왕에 대기 상태가 된다면 한 번에 많은 양을 요청해야 성능을 높일 수 있다.

 

정리하면 Multi Block I/O는 캐시에서 찾지 못한 특정 블록을 읽으려 I/O Call 할 때 디스크 상에서 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능이다. 

오라클 상에서 Multi Blcok I/O 설정량은 show db_file_multiblock_read_count 파라미터로 정한다.

 

좀더 부연하자면 인접한 블록이란 같은 익스텐트에 속한 블록을 의미한다. 

 

1.3.7 Table Full Scan VS Index Range Scan

두 방식은 테이블에 저장된 데이터를 읽는 방식이다. 

테이블 전체를 스캔해서 읽는 방식이 Table Full Scan, 인덱스를 이용해서 읽는 방식이 Index Range Scan이다.

말그대로 테이블에 속한 블록 전체를 읽어 데이터를 찾는 방식과 인덱스에서 일정량 스캔해서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다.

ROWID는 테이블 레코드가 디스크 상에서 어디 저장됐는지 가리키는 위치 정보.

 

많은 개발자가 실행 계획 분석시 Table Full Scan 찾기를 한다.

Table Full Scan 찾기식 실행 계획 분석은 실제 SQL 성능을 향상하는데 큰 도움이되지는 않는다.

인덱스를 이용해하는 상황에 Table Full Scan을 해서 해결해야하는 상황도 있어 의미가 없다고는 못하지만 인덱스가 SQL 성능을 떨어뜨리는 경우도 많다.(집계용 SQL과 배치 프로그램 등)

 

인덱스를 사용하는데 왜 성능이 더 느릴까? 

Table Full Scan은 시퀀셜 액세스와 Multi Block I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽고 캐시에서 못 찾으면 한 번의 I/O Call을 통해 인접한 수많은 블록을 한꺼번에 I/O한다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.

 

시퀀셜 액세스와 Multi Block가 아무리 좋아도 소량 데이터를 찾을 때 스캔하는건 비효율적이다. 

큰 테이블에서 소량 데이터를 검색할 땐 인덱스를 활용해야 한다.

 

Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.

캐시에서 못 찾으면 레코드 하나를 읽기 위해 매번 잠을 자는 I/O 매커니즘이다. 즉, 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다. 또한 읽은 블록을 반복 읽기하는 비효율도 있다. 

 

1.3.8 캐시 탐색 메커니즘

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

구체적으로는

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

버커 캐시 구조부터 보자. DBMS는 버퍼캐시를 위 그럼처럼 해시 구조로 관리한다.

위 그림은 해시 함수로 모듈러 함수를 사용하는 경우를 예로하고 있다. 만약 20번 블록을 찾고자한다면  블록번호를 5로 나누면 나머지가 0이다. 이 블록이 캐싱되어 있다면 버퍼 헤더가 첫 번째 헤시 체인에 연결되어 있을 것이고 찾을 때 첫 해시 체인만 탐색하면 된다. 

 

이처럼 버퍼 캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고 거기서 얻은 포인터로 버퍼 블록을 액세스한다.

• 같은 입력 값은 항상 동일한 해시 체인에 연결됨
• 다른 입력 값이 동일한 해시 체인에 연결될 수 있다.
• 해시 체인 내에서는 정렬이 보장되지 않는다.

 

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

버퍼 캐시는 SGA 구성요소이므로 캐싱된 버퍼블록은 모두 공유자원이다. 즉, 누구나 접근할 수 있다.

문제는 하나의 버퍼블록을 두 개 이상의 프로세스가 '동시에' 접근할 때이다.

동시 접근 시 블록 정합성에 문제가 생길 수 있다.

 

따라서 자원을 공유하더라도 한 프로세스씩 순차 접근하도록 구현해야하며, 직렬화 메커니즘이 필요하다.

공유캐시의 특정 자원이 두 개 이상의 프로세스가 같이 사용할 수 있나? 같이 사용하는 것처럼 보이지만, 특정 순간에는 한 프로세스만 사용할 수 있다. 그 순간 다른 프로세스는 기다려야한다. 이런 줄서기가 가능하게 지원하는 메커니즘이 래치(Latch)이다.

 

간단하게 위 사진에서 0부터 4까지 다섯 체인 앞쪽에 자물쇠가 있다고 생각하면 된다.  키를 가진 프로세스만 체인으로 진입할 수 있다.

 

SGA를 구성하는 서브 캐시마다 별도의 래치를 가지는데, 버퍼캐시엔 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다. 캐시버퍼 체인뿐 아니라 버퍼블록 자체에도 버퍼 Lock이라는 직렬화 메커니즘이 존재한다.

이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면 튜닝을 통한 쿼리 일량 자체를 줄여야한다.