데이터베이스 I/O 효율화 및 버퍼캐시 최적화 방법을 이해하는데 필요한 기본 개념과 원리를 알아보도록 하자.
블록 단위 I/O
Oracle을 포함한 모든 DBMS에서 I/O는 블록단위로 이뤄진다. 즉 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.
SQL성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록개수이다. 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록개수이다.
블록단위 I/O는 버퍼캐시와 데이터 파일 I/O 모두에 적용된다.
- 데이터 파일에서 DB버퍼캐시로 블록을 적재할때
- 데이터 파일에서 블록을 직접 읽고 쓸때
- 버퍼캐시에서 블록을 읽고 쓸 때
- 버퍼캐시에서 변경된 블록을 다시 데이터 파일에 쓸 때
메모리 I/O VS 디스크 I/O
1. I/O 효율화 튜닝의 중요성
- 모든 DBMS는 읽고자 하는 블록을 먼저 버퍼캐시에서 찾아보고, 없을 경우에만 디스크에서 읽어 버퍼 캐시에 적재한 후 읽기/쓰기 작업을 수행한다.
- 물리적인 디스크 I/O가 필요할 때면 서버 프로세스는 시스템에 I/O Call을 하고 잠시 대기상태에 빠진다. 디스크 I/O경합이 심할수록 대기 시간도 길어진다.
- 디스크 I/O를 최소화하고 버퍼캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표가 된다.
2. 버퍼 캐시 히트율
: 버퍼 캐시 효율을 측정하는 지표로서, 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율을 나타낸다.
-
버퍼캐시히트율(BCHR, Buffer Cache Hit Ratio)는 물리적인 디스크 읽기를 수반하지 않고 곧바로 메모리에서 블록을 찾은 비율을 말한다.
-
모든 블록 읽기는 버퍼 캐시를 통해 이뤄진다. 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 없을 때만 디스크로부터 버퍼캐시에 적재한 후 읽어 들인다.
BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) X 100
- BCHR은 주로 시스템 전체적인 관점에서 측정하지만, 개별 SQL측면에서 구해볼 수도 있는데 이 비율이 낮은 것이 SQL 성능을 떨어뜨리는 주원인이다.
<사진>
위의 샘플의 BCHR을 구해보자
버퍼캐시에서 읽은 블록 수는 Query와 Current항목을 더해서 구한다.
- 총 읽은 블록수 = 822+0 = 822
- 버퍼캐시에서 곧바로 찾은 블록수 = 822-18 =804
- BCHR = (822-18) / 822 = 97.8%
100개 블록읽기를 요청하면 98개는 메모리에서 찾고 나머지 2개는 디스크 I/O를 발생시켰다는 뜻이다.
-
논리적인 블록 요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O효율화 튜닝의 핵심원리이다.
-
논리적으로 읽어야 할 블록수의 절대량이 많다면 반드시 튜닝을 통해 논리적인 블록읽기를 최소화해야 한다.
3. 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향
: SQL을 작성할 때는 다양한 I/O 튜닝 기법을 사용해서 네트워크 전송량을 줄이려고 노력하는 것이 중요하다.
- RAC같은 클러스터링 데이터베이스 환경에선 인스턴스 간 캐시된 블록을 공유하므로 메모리 I/O 성능에도 네트워크 속도가 지대한 영향을 미친다.
*RAC (Real Application Cluster)
: 여러개의 인스턴스가 하나의 데이터베이스를 액세스 할 수 있는 것. 어플리케이션에서 접속할 수 있는 통로는 여러개이며 데이터베이스는 하나인 형태이다.Oracle RAC = N개의 인스턴스 + 1개의 데이터베이스
- 네트워크 문제이든 파일시스템 문제이든 I/O 성능에 관한 가장 확실하고 근본적인 해결책은 논리적인 블록 요청횟수를 최소화하는 것이다.
Sequential I/O VS Random I/O
<사진>
- Sequential 액세스
- 코드간 논리적 또는 물리적인 순서에 따라 차례대로 읽어 나가는 방식이다.
- 인덱스 리프블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결되어있고 이 포인터를 따라 스캔하는것은 Sequential 액세스 방식이다.
- Random 액세스
- 레코드간 논리적,물리적인 순서를 따르지 않고 한 건을 읽기위해 한 블록씩 접근하는 방식을 말한다.
- 하나의 레코드를 읽으려고 한 블록씩 랜덤액세스를 한다면 매우 비효율적이다.
여기서 I/O 튜닝의 핵심 원리 두가지를 발견할 수 있다.
- 시퀀셜 액세스에 의한 선택 비중을 높인다.
- 랜덤 액세스 발생량을 줄인다.
1. 시퀀셜 액세스에 의한 선택 비중 높이기
: 시퀀셜 액세스 효율성을 높이려면, 읽은 총 건수 중에서 결과 집합으로 선택되는 비중을 높여야 한다. 즉 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있다.
테이블을 스캔하면서 읽은 레코드중 대부분 필터링되고 일부만 선택된다면 인덱스를 이용하는 게 효과적이다.
하지만 인덱스를 사용할때도 비효율이 나타날 수 있다. 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 효율성이 결정된다.
2. 랜덤 액세스 발생량 줄이기
인덱스를 사용해 테이블 랜덤 액세스 발생량을 줄일 수 있다. 같은 쿼리를 수행했는데도 인덱스 구성에 따라 랜덤액세스가 다를 수 있으므로 인덱스 구성을 잘해야 한다.
Single Block I/O VS MultiBlock I/O
- Single Block I/O
- 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 방식이다.
- 인덱스를 통해 테이블을 액세스할때는 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용한다.
- MultiBlock I/O
- I/O Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 방식이다.
- Table Full Scan처럼 물리적으로 저장된 순서에 따라 읽을 때는 인접한 블록들을 같이 읽는 것이 유리하다.
- 인접한 블록이란 한 익스텐트내에 속한 블록을 말한다. 달리 말하면 MultiBlock I/O 방식으로 읽더라도 익스텐트 범위를 넘어서까지 읽지는 않는다.
-
인덱스 스캔 시에는 Single Block I/O 방식이 효율적이다. 인덱스 블록간 논리적 순서는 데이터 파일에 저장된 물리적인 순서와 다르기 때문이다.
-
대량의 데이터를 MultiBlock I/O 방식으로 읽을때 성능이 유리한 이유는 I/O Call 발생횟수를 줄여주기 때문이다.
-
Single Block I/O 방식으로 읽은 블록들은 LRU리스트 상 MRU 쪽(end)으로 위치하므로 한번 적재되면 버퍼캐시에 비교적 오래 머문다.
-
반대로 MultiBlock I/O 방식으로 읽은 블록들은 LRU리스트상 LRU쪽(end)으로 연결되므로 적재된지 얼마 지나지 않아 1순위로 버퍼캐시에서 밀려난다.
I/O 효율화 원리
- 논리적인 I/O 요청 횟수를 최소화 하는 것이 I/O 효율화 튜닝의 핵심원리다.
- I/O때문에 시스템 성능이 낮게 측정될 때 하드웨어적인 방법을 통해 I/O성능을 향상 시킬 수도 있다. 하지만 SQL 튜닝을 통해 I/O 발생 횟수 자체를 줄이는 것이 더 근본적이고 확실한 해결방안이다.
어플리케이션 측면에서의 I/O 효율화 원리는 다음과 같이 요약할 수 있다.
- 필요한 최소 블록만 읽도록 SQL 작성
- 최적의 옵티마이징 팩터 제공
- 필요하다면 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
1. 필요한 최소블록만 읽도록 SQL 작성
: 데이터베이스 성능은 I/O 효율에 달렸다. 이를 달성하려면 동일한 데이터를 중복 액세스하지않고 필요한 최소 블록만 읽도록 SQL을 작성해야 한다.
SQL명령을 사용자는 최소 일량을 요구하는 형태로 논리적인 집합을 정의하고, 효율적인 처리가 가능하도록 작성하는 것이 무엇보다 중요하다.
아래는 비효율적인 중복 액세스를 없애고 필요한 최소 블록만 액세스하도록 튜닝한 사례이다.
<사진>
위 SQL는 어제 거래가 있었던 카드에 대한 전일, 주간, 전월, 연중 거래 실적을 집계하고 있다. 논리적인 전체집합은 과거 1년치인데 전일,주간,전월데이터를 각각 액세스한후 조인한 것을 볼수 있다 .전일 데이터는 총 4번을 액세스한 셈이다.
SQL을 아래와 같이 작성하면 과거 1년치 데이터를 한번만 읽고 전일,주간,전월 결과를 구할 수 있다. 즉 논리적인 집합 재구성을 통해 액세스해야 할 데이터 양을 최소화할 수 있다.
<사진>
2. 최적의 옵티마이징 팩터 제공
- 옵티마이저가 블록 액세스를 최소화하면서 효율적으로 처리할 수 있도록 하려면 최적의 옵티마이징 팩터를 제공해주어야 한다.
- 전략적인 인덱스 구성
: 가장 기본적인 옵티마이징 팩터이다.- DBMS가 제공하는 기능 활용
: 인덱스 외에도 DBMS가 제공하는 다양한 기능을 적극적으로 활용한다.
인덱스, 파티션, 클러스터, 윈도우함수등을 적극활용해 옵티마이저가 최적으로 선택할 수 있도록 한다.- 옵티마이저 모드 설정
: 옵티마이저모드( 전체처리속도 최적화, 최초응답속도 최적화)와 그 외 옵티마이저 행동에 영향을 미치는 일부 파라미터를 변경해 주는 것이 도움이 될 수 있다.- 통계정보
: 옵티마이저에게 정확한 정보를 제공한다.
3. 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
- 옵티마이저 힌트를 사용할 때는 의도한 실행계획으로 수행되는 지 반드시 확인해야 한다.
참고자료: SQL 전문가 가이드
