[SQL 튜닝] 데이터베이스 저장 구조와 I/O 메커니즘

SQL이 느린 이유

- SQL이 느린 이유는 디스크 I/O 때문임

- OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 멈춰지고 기다림 (동기방식)

- 인터럽트 없이 실행되던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 WAITING상태가 되어 I/O완료를 기다림 -> I/O가 오래 걸리면 프로세스가 느려짐

- I/O튜닝이 안 된 시스템에서는 수많은 프로세스들이 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고 그만큼 대기 시간도 길어짐 -> SQL이 느려짐 -> 디스크 I/O가 SQL 성능을 좌우함

 

 

데이터베이스 저장 구조

- 테이블스페이스 > 세그먼트 > 익스텐트 > 블록(페이지) > 데이터 레코드

(데이터베이스 > 테이블스페이스 > 데이터파일)

테이블스페이스 > 세그먼트 > 익스텐트 > 블록(페이지) > 데이터레코드

 

- 데이터를 저장하려면 테이블 스페이스를 생성해야 함

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

 

세그먼트

- 데이터 저장공간이 필요한 오브젝트 (테이블, 인덱스, 파티션, LOB 등)

- 파티션 구조가 아니라면 테이블도 하나의 세그먼트이고, 인덱스도 세그먼트

- 여러 익스텐트로 구성됨

 

익스텐트

- 공간을 확장하는 단위

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

- 하나의 익스텐트는 하나의 테이블이 독점, 한 익스텐트에 있는 블록은 모두 같은 테이블의 블록

- 연속된 블록의 집합체

- 익스텐트 내 블록은 연속적이지만, 익스텐트 끼리는 연속된 공간이 아님

 

블록

- 실제 사용자가 입력한 레코드 데이터를 저장하는 공간

- 하나의 블록은 하나의 테이블이 독점, 한 블록에 있는 레코드들은 모두 같은 테이블의 레코드

- 데이터베이스에서 데이터를 읽고 쓰는 단위

- 데이터 I/O의 단위가 블록이므로, 테이블의 레코드 하나, 칼럼 하나만 읽고 싶어도 전체 블록을 통째로 읽음

 

데이터 파일

- 세그먼트에 공간이 부족하면 테이블 스페이스로부터 익스텐트를 부여받는데, 하나의 세그먼트를 구성하는 익스텐트들은 모두 같은 데이터 파일에 존재하지 않을 수 있고, 그럴 가능성이 더 높음

- 하나의 테이블 스페이스를 파일 경합을 줄이기 위해 여러 개의 데이터 파일로 구성됨

 

데이터 파일

 

테이블(세그먼트) > 익스텐트 > 블록 접근 방법

- 모든 데이터 블록은 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지를 나타내는 고유 주소 값(DBA)을 가짐

- 데이터를 읽고 쓰는 단위가 블록이므로 데이터에 접근하려면 DBA부터 확인해야 함

- 인덱스를 사용하여 테이블 레코드를 읽을 때는 인덱스 ROWID을 이용함, ROWID는 DBA+로우 번호(블록 내 순번)로 구성됨

- 테이블을 스캔할 때는 테이블 세그먼트의 익스텐트 맵(세그먼트에 할당된 익스텐트 목록)을 이용

- 익스텐트 맵에서 각 익스텐트의 첫 번째 블록의 DBA를 알 수 있음

- 익스텐트는 연속된 블록의 집합이므로 첫 번째 블록을 읽고 뒤이어서 연속된 블록을 읽으면 됨

- 데이터 I/O의 단위가 블록이므로, 테이블의 레코드 하나, 칼럼 하나만 읽고 싶어도 전체 블록을 통째로 읽음

 

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

- 테이블 또는 인덱스 블록을 액세스 하는 방식에는 시퀀셜 액세스와 랜덤 액세스가 있음

- 시퀀셜 : 논리적/물리적으로 연결된 순서에 따라 차례로 블록을 읽음, 주소 값에 따라 앞 또는 뒤로 순차적으로 읽음

- 랜덤 : 논리적/물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해서 한 블록씩 접근

 

DB 버퍼 캐시

- DB의 성능은 디스크 I/O가 중요

- SQL을 수행하는 과정에서 계속해서 데이터 블록을 읽어오는데, 자주 읽는 블록을 매번 디스크에서 읽는 것을 비효율적

- 데이터 캐싱을 통해 디스크 I/O를 줄임

- DB 버퍼 캐시도 라이브러리 캐시(코드캐시) 처럼 SGA의 구성요소임

- 라이브러리 캐시가 SQL, 실행계획, 함수, 프로시저를 캐싱하는 코드 캐시라면, DB 버퍼 캐시는 데이터 블록을 캐싱해서 동일한 데이터 블록에 대한 반복적인 I/O를 줄여주는 데이터 캐시의 역할을 함

- 데이터 블록을 읽을 때는 항상 버퍼 캐시부터 탐색함

 

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

- 논리적 I/O는 SQL를 처리하는 과정에서 버퍼 캐시에서 블록을 읽는 경우(메모리 버퍼 캐시에서 읽어 들임)

- 물리적 I/O는 원하는 블록이 버퍼 캐시에 없으므로 디스크에 액세스 하여 블록을 읽어옴(디스크에서 읽음 -> 느림)

- 물리적 I/O는 디스크에 접근하므로 메모리에 접근하는 논리적 I/O에 비해 매우 느림

- 블록을 읽을 때는 해당 블록을 먼저 버퍼 캐시에서 찾아보고 없을 때만 디스크에서 읽는데, 이때도 디스크에서 곧바로 읽는 게 아니라 먼저 버퍼 캐시에 적재하고서 읽음

- SQL 성능을 높이기 위해서는 논리적 I/O를 줄여야 함, SQL 튜닝을 해서 논리적 I/O를 줄여야 물리적 I/O도 줄어듬

- 논리적 I/O을 줄이는 것은, SQL을 튜닝해서 읽어 들이는 총 블록의 개수를 줄이면 됨

 

Single Block I/O VS Multi Block I/O

- 모든 데이터를 전부 버퍼 캐시에 적재할 수는 없으므로, 전체 데이터 중에서 일부만 캐시에 적재

- 캐시에서 찾지 못한 블록은 디스크 I/O를 통해 디스크에서 버퍼 캐시로 적재하고 읽음

- Single Block I/O : 메모리 적재 시 한 번에 한 블록씩 요청

- Multi Block I/O : 메모리 적재 시 한 번에 여러 블록씩 요청

- 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O를 사용

- 인덱스는 소량의 데이터를 읽을 때 주로 사용(Single Block I/O) 반대로, 많은 데이터 블록을 읽을 때는 Multi Block I/O 가 효율적

- 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 Multi Block I/O을 사용

- Multi Block I/O는 캐시에 찾지 못한 특정 블록을 읽으려고 디스크 상에 해당 블록과 인접하며 같은 익스텐트에 속한 블록들을 한꺼번에 읽어서 미리 적재