[Database] 데이터베이스 I/O 원리 및 최적화

1. Database Read

Database는 데이터를 블록(Block) 단위로 읽고 저장한다. 오라클의 경우는 기본 블록 사이즈가 8kb이다. 즉, database가 아주 작은 데이터를 가져온다고 하더라도 최소한 8kb의 블록을 읽는다. Database의 튜닝에서 가장 중요한 것은 바로 이 블록 단위 I/O를 줄이는 것이다.

 

2. 메모리 I/O vs 디스크 I/O

디스크 I/O : 디스크의 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고 쓴다.
메모리 I/O : 전기적 신호

 

디스크 I/O를 통한 입출력은 메모리를 통한 입출력보다 평균적으로 10,000배 이상 느리다.

 

메모리는 물리적으로 한정된 자원이므로, 디스크 I/O를 최소화하고 버퍼 캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표가 된다.

 

3. 버퍼 캐시

DB 버퍼 캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.

 

4. 논리적 I/O와 물리적 I/O

논리적 I/O는 SQL을 처리하는 과정에서 Buffer Cache에서 발생한 총 블록 I/O를 말한다. 물리적 I/O는 디스크에서 발생한 I/O를 말한다. SQL을 처리하면서, 찾고자 하는 데이터가 없을 경우, 디스크를 액세스하는 경우가 물리적 I/O이다. 

 

5. 버퍼 캐시 히트율

버퍼 캐시 히트율은 읽은 블록 중에서 얼마만큼을 메모리 버퍼 캐시에서 찾았는지를 나타내는 것 이다.

공식 : BCHR

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

         = ((논리적 블록 읽기 - 물리적 블록 읽기)논리적 블록읽기)*100

         = (1 - (물리적 블록 읽기)/(논리적 블록읽기)*100)

 

 

논리적 I/O는 데이터의 변경이 없다면 같은 SQL을 몇 번 수행해도 같지만 물리적 I/O는 같은 SQL을 여러 번 수행할수록 줄어든다. 실행할수록 DB 버퍼 캐시에서 해당 테이블 블록의 점유율이 높아지기 때문이다.

논리적인 블록 요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리이다.

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

2. 버퍼캐시 히트 비율이 낮으면 SQL 성능이 떨어진다.
3. BCHR가 100%라고 하더라도 논리적으로 읽어야 할 블록 수의 절대량이 많다면 반드시 SQL 튜닝을 실시해야 한다.

 

 

애플리케이션 특성에 따라 다르지만, 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다. 

 

6. Sequential I/O vs Random I/O

Sequential 액세스 : 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어나가는 방식
Random 액세스 : 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식

I/O 튜닝의 핵심 원리 - Sequential 액세스에 의한 선택 비중을 높이고 Random 액세스 발생량을 줄인다.

7. Single Block I/O vs MultiBlock I/O

캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽는다. I/O Call할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다.

 

Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식. 인덱스를 통해 테이블을 액세스할 때, 인덱스와 테이블 블록 모두 이 방식을 사용한다.

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

 

Multiblock I/O : 한 번에 여러 블록(인접한 블록. 한 익스텐트 내에 속한 블록)씩 요청해서 메모리에 적재하는 방식. 테이블 전체를 스캔할 때 사용한다. (FULL SCAN)

 

Single Block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU 쪽으로 위치하므로 한 번 적재되면 버퍼 캐시에 비교적 오래 머무는 반면, MultiBlock I/O 방식으로 읽은 블록들은 LRU 리스트 상 LRU쪽으로 연결되므로 적재된지 얼마 지나지 않아 1순위로 버퍼캐시에서 밀려나게 된다.

 

 

8. I/O 효율화 원리

논리적인 I/O 요청 횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리다.

• 필요한 최소 블록만 읽도록 SQL 작성 (필요한 컬럼만 SELECT)
• 최적의 옵티마이징 팩터 제공
  • 1) 전략적인 인덱스 구성
    2) DBMS가 제공하는 기능 활용(인덱스, 파티션, 클러스터, 윈도우 함수 등)
    3) 옵티마이저 모드(전체 처리속도 최적화, 최초 응답속도 최적화) 설정
    4) 통계정보
• 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도 (힌트 사용)

 

REFERENCE

https://velog.io/@jduck1024/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-IO-%EC%9B%90%EB%A6%AC

데이터베이스 I/O 원리

https://augustines.tistory.com/95

Database I/O

https://12bme.tistory.com/330

Database I/O