[Book] 친절한 SQL 튜닝 - SQL 처리 과정과 I/O - 데이터 저장 구조 및 I/O 메커니즘

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

I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다.

SQL이 느린 이유

SQL이 느린 이유는 십중팔구 I/O 때분이다. 구체적으로, 디스크 I/O 때문이다.

I/O 가 무엇일까 ?

I/O = 잠(SLEEP) 이라고 설명한다. OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다. 프로세스는 ‘실행 중인 프로그램’ 이며, 생명주기를 갖는다.

생성 이후 종료 전까지 준비와 실행과 대기 상태를 반복한다. 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 이런 메커니즘이 필요하다. interrupt없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면(waiting) 상태에서 I/O가 완료되기를 기다린다. 정해진 OS 함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 자는 것이다. 열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.

데이터베이스 저장 구조

데이터를 저장하려면 먼저 테이블스페이스를 생성해야 한다.

• 테이블 스페이스 : 세그먼트를 담는 콘테이너, 여러개의 데이터파일(디스크 상의 물리적인 OS)로 구성
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록들의 집합
• 블록 : 데이터를 읽고 쓰는 단위
• 데이터파일 : 디스크 상의 물리적인 OS 파일

💡 DBA(Data Block Address) 모든 데이터 블록이 가지는 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값을 의미한다.

• 데이터를 읽고쓰는 단위는 블록 이다.
• 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 이용한다. ROWID = DBA + 로우 번호(블록 내 순번) 로 구성되므로 이를 분해하면 읽어야할 DBA를 알 수 있다.
  
  

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

• 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다.
• 랜덤액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식이다.

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

DB 버퍼캐시

SQL을 수행하는 과정에 계속해서 데이터 블로을 읽는데, 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다. 모든 DBMS에 데이터 캐싱 메커니즘이 필수인 이유다.

서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다. 버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.

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

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

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

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

전체 읽은 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다.

💡 BCHR공식에서 중요한 성능 원리를 발견할 수 있다. 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다는 사실이다. BCHR 공식을 이루는 물리적 I/O는 통제 불가능한 외생변수다.(메모리증설외에는 없음). 반면, 논리적 I/O는 통제 가능한 내생변수다. SQL를 튜닝해서 논리적 I/O를 줄이면 물리적 I/O도 줄고, 그만큼 성능도 향상된다.

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 방식을 사용한다.

반대로, 많은 데이터 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다. 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다. 테이블이 클수록 Multiblock I/O 단위도 크면 좋다. 프로세스가 잠자는 횟수를 줄여주는 데 있다.

DBMS 세계로 돌아와서, 읽고자 하는 블록을 DB 버퍼캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call을 한다. 그동안 프로세스는 대기 큐(Wait Queue)에서 잠을 잔다. 대용량 테이블이면 수많은 블록을 디스크에서 읽는 동안 여러 차례 잠을 잘 텐데, 기왕에 잠을 자려면 한꺼번에 많은 양을 요청해야 잠자는 횟수를 줄이고 성능을 높일 수 있다. 대용량 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설정하면 성능이 좋아지는 이유다.

 

Table Full Scan vs Index Range Scan

Table Full Scan : 말 그대로 테이블에 속한 블록 ‘전체’를 읽어서 사용자가 원하는 데이터를 찾는 방식

Index Range Scan(인덱스를 이용한 테이블 액세스) : 인덱스에서 ‘일정량’을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보다.

💡 Table Full Scan은 피해야 한다는 많은 개발자의 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.

한번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램이 특히 그렇다. 그래서 이들 프로그램에서 사용하는 SQL은 온라인 SQL(온라인 트랜잭션 처리 시스템에서 사용하는 SQL)보다 튜닝하기가 비교적 쉽다. 다 그런 건 아니지만, 상당수가 Table Full Scan으로 유도하면 성능이 빨라진다. 조인을 포함한 SQL이면, 조인 메소드로 해시 조인을 선택해주면 된다.

💡 큰 테이블에서 소량 데이터를 검색할때는 반드시 인덱스를 이용해야 한다.

캐시 탐색 메커니즘

버퍼캐시 구조

DBMS는 버퍼캐시를 해시구조로 관리한다. 버퍼캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터(Pointer)로 버퍼 블록을 액세스하는 방식을 사용한다.

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

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

따라서 자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야함. 이를 위해 직렬화(serialization) 메커니즘이 필요하다(줄 세우기). 이런 줄서기가 가능하도록 지원하는 메커니즘이 **래치(Latch)**다.