[SQLp]과목 3 - 1 데이터베이스 I/O 원리

 

SQLp - 국가공인 SQL 전문가

INDEX

• 과목 1 - 데이터 모델링의 이해

  • 데이터 모델링의 이해

  • 데이터 모델과 성능

  ​

• 과목 2 - SQL 기본 및 활용

  • SQL 기본

  • SQL 활용

  • SQL 최적화 기본 원리

  ​

• 과목 3 - SQL 고급 활용 튜닝

  • 아키텍처 기반 튜닝 원리

  • Lock 과 트랜잭션 동시성 제어

  • 옵티마이저 원리

  • 인덱스와 조인

  • 고급 SQL 튜닝

---

 

3과목은 튜닝에 대한 이해와 실습 병행이 필요할 것 같아 주제별로 정리를 할 계획입니다.

 

 

과목 3 - SQL 고급 활용 및 튜닝

1 . 아키텍처 기반 튜닝 원리

데이터베이스 성능 튜닝의 3대 핵심 요소로서 SQL 파싱 부하 해소, 데이터베이스 call 최소화, I/O 효율화를 뽑을 수 있다. 이번 포스팅에서는 성능튜닝의 핵심 요소들을 이해하기 위한 기본 지식과 요소들을 소개할 것 입니다.

• 데이터베이스 I/O 원리

  데이터베이스 I/O 튜닝을 위해서는 인덱스, 조인, 옵티마이저 원리, 소트 원리 등에 관한 종합적인 이해가 필요하다. 이번 포스팅에서는 데이터베이스 I/O 효율화 및 버퍼캐시 최적화 방법을 이해하는 데에 필요한 기본 개념과 원리를 소개하고자 한다.

1. 블록 단위 I/O

   DBMS 에서 I/O 는 블록(페이지) 단위로 이루어 진다. 즉, SQL 성능을 좌우하는 지표는 액세스 하는 블록 갯수 이며 옵티마이저의 판단에 가장 큰 영향을 미친다.

블록 단위 I/O는 버퍼 캐시와 데이터 파일 I/O 모두에 적용된다.

​ 1) 데이터 파일에서 DB 버퍼 캐시로 블록을 적재

​ 2) 데이터 파일에서 블록을 직접 읽고 쓸 때

​ 3) 버퍼 캐시에서 블록을 읽고 쓸 때

​ 4) 버퍼 캐시에서 변경된 블록을 다시 데이터 파일에 쓸 때

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

   디스크를 경유한 데이터 입출력은 디스크의 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느린 반면에 메모리를 통한 입출력은 전기적 신호이기 때문에 매우 빠르다. 때문에 DBMS 는 읽고자 하는 블록을 버퍼 캐시에서 찾아 본 후에 없을 경우에만 디스크에서 읽고 버퍼 캐시에 적재한 후에 읽기 쓰기 작업을 수행한다.

   • I/O 효율화 튜닝의 중요성

     물리적인 디스크 I/O가 필요할 때면 서버 프로세스는 시스템에 I/O Call을 하고 대기 상태에 빠진다. 디스크 I/O 경합이 심할수록 대기 시간도 길어진다.

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

   • 버퍼 캐시 히트율

     버퍼 캐시 효율을 측정하는 지표로서 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율이다.

     즉, 물리적인 디스크 읽기를 수반하지 않고 바로 메모리에서 블록을 찾은 비율이다. Direct Path Read 방식 이회의 블록읽기는 버퍼 캐시를 통해 이루어 진다.

      

     BCHR(버퍼 캐시 효율, Buffer cache hit ratio) = (버퍼캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) *100

      

     BHCR 은 주로 시스템 전체적인 관점에서 측정하지만, 개별 SQL 측면에서 구할 수도 있는데 이 비율이 낮은것이 SQL 성능을 떨어 뜨리는 주 원인이다.

      

     즉, SQL 성능을 높이기 위해서는 버퍼 캐시 히트율을 높이면 된다.

      

     디스크 항목은 디스크를 경유한 블록 수이며, 버퍼 캐시에서 읽은 블록수는 Query 와 Current 항목을 더해서 구한다.

     총 읽은 블록수는 디스크로부터 읽은 블록수를 의미하므로 주의해야 한다. 논리적인 I/O 요청을 줄이고 물리적으로 디스크에서 읽어야 할 블록수를 줄이는 것이 매우 중요하다.

     그러나 같은 블록을 반복적으로 액세스하는 형태의 SQL은 논리적인 I/O 요청이 많이 발생해도 BCHR은 높게 나타난다. 이는 성능 지표 로서의 BCHR이 갖는 한계점이다. (Ex- NL Join에서 작은 Inner 테이블을 반복적으로 룩업 하는 경우)

      

     NL Join에서 작은 Inner 테이블을 반복적으로 룩업 하는 경우

     작은 블록을 액세스 하면 모든 블록이 메모리에서 찾아져 BCHR은 높겠지만 일량이 작지 않고 블록을 찾는 과정에서 래치 경합과 버퍼 Lock 경합이 발생한다면 메모리 I/O 비용이 디스크 I/O 비용보다 커질 수 있다.

     따라서 논리적으로 읽어야 할 블록의 수가 절대량이 많다면 반드시 튜닝을 통해 논리적인 블록읽기를 최소화해야 한다.

    

   • 네트워크, 파일 시스템 캐시가 I/O 효율에 미치는 영향

     대용량 데이터를 읽고 쓰는데에는 NAS 서버나 SAN등의 다양한 네트워크 기술을 사용한다. 이에 따라 네트워크 속도는 SQL 성능에 많은 역할을 주고 있다. 네트워크 전송량을 줄여 성능을 높이기 위해서는 SQL을 작성할 때, 다양한 튜닝 기법을 사용해야 한다.

     RAC와 같은 클러스터링 데이터베이스 환경에선 인스턴스간에 캐시된 블록을 공유하므로, 메모리 I/O 성능에도 네트워크 속도가 지대한 영향을 준다. 같은 양의 디스크 I/O에서도 I/O 대기 시간은 크게 차이가 나는데 이 원인은 디스크 경합 혹은 파일 시스템 버퍼 캐시(OS) 와 SAN 캐시 차이 때문이다. SAN 캐시는 커도 문제가 되지 않지만 파일 시스템 버퍼 캐시는 최소화 해야 한다. 데이터베이스 자체적으로 캐시 영역을 갖고 있으므로 이 공간을 크게 할당하는 것이 효과적이다.

     I/O 성능에 대한 근본적인 해결책은 논리적인 블록 요청 횟수를 최소화 하는 것이다.

      

3. Sequential I/O vs Random I/O

   I/O 튜닝의 핵심은 Sequential I/O를 늘리고 Random I/O를 줄이는 데에 있다.

   • Sequential 액세스에 의한 선택 비중 높이기

     Sequential 액세스는 논리적 또는 물리적인 순서가 있으며 이 순서에 따라 차례대로 읽는 방식이다. 인텍스 리프 블록에 위치한 레코드는 포인터에 따라 논리적으로 연결되어 있고, 이를 따라 스캔하는 것이 Sequential 액세스 방식이다. 테이블 레코드 간에는 포인터로 연결되어 있지 않지만 테이블을 스캔할 때에 물리적으로 저장된 순서대로 읽는 것 또한 Sequential 액세스 방식이다.

     Sequential 액세스 효율을 높이기 위해서는 읽은 총 건수 중에서 결과 집합으로 선택되는 비중을 높여야 한다. 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽는지를 통해 효율성을 판단할 수 있다.

     • 테이블을 스캔하면서 읽은 레코드의 대부분이 필터링 된다면 인덱스를 이용해야 한다.

     • 인덱스를 활용할 때에는 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성을 반영해야한다.

        

   • Random 액세스 발생량 줄이기

     반면, Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근하는 방식이다. 블록 I/O를 하더라도 Sequential 방식으로 그 안에 저장된 레코드를 읽는다면 비효율은 없지만 하나의 레코드를 위해 한 블록씩 Random 액세스 한다면 비효율적이다.

     • 인덱스에 속하지 않은 칼럼을 참조한다면 테이블 액세스가 발생하고 테이블 Random 액세스가 일어난다.
     • 인덱스를 변경하여 테이블 Random 액세스를 줄여야 한다.

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

   Single Block I/O는 한번의 I/O Call 에 하나의 데이터 블록만 읽어 메모리에 적재하는 방식이다. 인덱스를 통해 테이블을 액세스 할 때 기본젓으로 인텍스와 테이블 블록 모두 Single Block I/O 방식을 사용한다.

    

   MultiBlock I/O는 I/O Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재한다. Table Full Scan 과 같이 물리적으로 저장된 순서에 따라 읽을 때에 인접한 블록(한 익스텐트 내에 속한 블록)들을 같이 있는 것이 유리하다. MultiBlock I/O방식이어도 익스텐트 범위를 넘어서 까지 읽지는 않는다.

   인덱스 스캔 시에는 Single Block I/O 가 효율적이다. 인덱스 블록간 논리적 순서(이중 연결리스트 구조로 연결된 순서)는 데이터 파일에 저장된 물리적인 순서와 다르기 때문이다. 물리적으로 한 익스텐트에 속한 블록들을 I/O Call 시점에 같이 메모리에 올렸는데 이들이 논리적 순서로는 한참 뒤쪽에 위치할 수 있다. 그러면 그 블록들은 사용되지 못하고 버퍼상에서 밀려날 수 있다. 하나의 블록을 캐싱하기 위해 다른 블록을 밀어내야 하기 때문에 이 현상이 자주 발생한다면 버퍼 캐시 효율이 떨어진다.

   대량의 데이터는 MultiBlock I/O 방식으로 읽는 것이 유리하다. 그 이유는 I/O Call 발생 횟수가 줄어들기 때문이다. 테이블 액세스 없이 인덱스만 읽을 때에는 Index Range Scan, Index Full Scan에서도 MultiBlock I/O 방식으로 읽는다. 그러나 인덱스를 스캔하면서 테이블을 랜덤 액세스 할때에는 테이블과 인덱스 블록을 모두 Single Block I/O 방식으로 읽는다.

   Single Block I/O 방식은 블록들을 LRU 리스트상 MRU 쪽으로 위치시키므로 한번 적재되면 버퍼 캐시에 비교적 오래 머무는 특징이 있으며, 반면에 Multi Block I/O일 경우 LRU 리스트상 LRU 쪽으로 연결되어 있으므로 적재된 지 얼마 지나지 않아 1순위로 버퍼캐시에서 밀려난다.
   

5. I/O 효율화 원리

   효과적인 I/O 효율화 방안은 SQL 튜닝을 통해 I/O 발생 횟수 자체를 줄여 근본적인 해결을 하는 것이다.

   • 필요한 최소 블록만 읽도록 SQL 작성

     동일한 데이터를 중복 액세스 하지 않고, 필요한 최소 블록만 읽도록 SQL 을 작성해야 한다. 최소 일량을 요구하는 형태로 논리적인 집합을 정의하고 효율적인 처리가 가능하도록 SQL 명령을 작성해야 한다. 즉 논리적인 집합 재구성을 통해 액세스해야 할 데이터 양을 최소화 해야 한다.

      

   • 최적의 옵티마이징 팩터 제공

     최적의 옵티마이징 팩터를 제공하는 것은 매우 중요하다.

     • 전략적인 인덱스(가장 기본) 구성
     • DBMS 가 제공하는 기능 활용 : 인덱스, 파티션, 클러스터, 윈도우 함수 등을 적극 활용
     • 옵티마이저 모드 설정 : 옵티마이저 모드(전체 처리 속도 최적화, 최초 응답 속도 최적화)와 일부 파라미터 변경

     • 통계정보 : 옵티마이저에게 정확한 정보 제공

        

   • 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

     옵티마이저가 최적의 실행계획을 수립하지 못하는 경우, 힌트를 사용한다. 힌트를 사용할 때에는 의도한 실행계획으로 수행되는지 반드시 확인해야 한다.

     인덱스, 조인, 옵티마이저의 기본원리에 대한 이해를 기반으로 최적의 액세스 경로로 유도하는 것이 중요하다.

     • Oracle

       ​x
       select /*+ leading(d) use_nl(e) index(d dept_loc_idx)*/ *
       from emp e, dept d
       where e.deptno=d.deptno
       and d.loc='CHICAGO'
       

        

     • Sql Sever

       xxxxxxxxxx
       select *
       from dept d with (index(dept_loc_idx)), emp e
       where e.deptno = d.deptno
       and d.loc = 'CHICAGO'
       option (force order, loop join)

 

 

 

1. 정리