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

SQL이 느린 이유

• SQL이 느린 이유는 십중팔구 I/O 때문이라고 필자는 말한다.
• OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다.
• 프로세스의 생명주기
  • 실행중인 프로세스는 수시로 실행 준비 상태로 전환했다가 다시 실행 상태로 전환하는 것을 반복한다.
  • 여러 프로세스가 하나의 CPU를 공유할 수 있지만 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 이런 메커니즘이 필요하다.
  • Waiting : 프로세스 도중에 I/O 작업이 필요하여 I/O 작업을 수행하는 상태이다. 이 때 CPU는 I/O를 기다리며, 다른 프로세스를 수행한다. (필자는 이 과정을 잠을 잔다고 표현함.) Waiting 상태가 끝나면 프로세스는 다시 Ready 상태가 되고, 잠시 후 다시 Running 상태가 된다.
  • 시공유 시스템에서는 Running 상태에서 할당된 시간이 종료(Expired)되면, Waiting 상태를 거치지 않고 바로 Ready 상태로도 변할 수 있다.

• Waiting Queue (대기 큐) 에서 잠을 잔다? 그게 뭔데 
  • Job Queue : HDD에 있던 프로그램들이 메모리에 올라올 때, 만약 메인 메모리가 가득 찼거나 CPU가 다른 작업을 수행 중이라면 메모리에 올라오기 전에 어느정도 기다려야하는데 HDD의 프로그램들이 잠시 기다리는 곳을 Job Qußeue라고 한다.
  • Ready Queue : 메인메모리에 프로그램이 올라왔다고 하더라도 바로 CPU의 서비스를 받을 수 있는 것은 아니다. 다른 프로그램이 수행 중이면 기다렸다가, 해당 프로그램이 I/O를 시작하거나, 시공유시스템의 경우 기존 프로세스가 시간초과되면 실행된다. 이렇게 이미 메모리에 올라온 프로세스들이 실행을 위해 대기하는 곳을 Ready Queue라고 한다.
  • Device Queue : I/O장치 (e.g. 프린터, 키보드, 마우스 등)을 이용하려면 기존 I/O 작업이 끝나기를 기다렸다가 밀려있던 모든 작업이 끝나면 비로소 I/O장치를 사용할 수 있다. 프린터는 프린터큐, 마우스는 마우스큐, 키보드는 키보드큐와 같이 각각 I/O 장치들은 각자의 대기 큐를 가지고 있는데, 이러한 I/O 장치들의 큐들을 통틀어서 Device Queue라고 부른다.
  • +) 그럼 Terminated는? 어떠한 프로세스가 종료될 때는 기다릴 필요 없이 바로 종료된다. 즉 Terminated 상태를 위한 큐는 필요 없다. 
    

 

 

 

데이터베이스 저장 구조

• 데이터를 저장하려면 먼저 테이블스페이스를 생성해야 한다. 테이블 스페이스는 세그먼트를 담는 콘테이너로서, 여러 개의 데이터파일로 구성된다. 테이블, 인텍스를 생성할 때 어떤 테이블 스페이스에 저장할지를 지정한다.
• 세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트. 하나의 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드 이다. (다중 테이블 클러스터일때는 예외)
• 익스텐드 : 하나의 세그먼트에 할당된 공간. 하나의 세그먼트는 한 개 또는 다수의 익스텐트로 구성된다. 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다. (MS-SQL의 경우엔 여러 오브젝트가 같이 사용하기도 함)
• 세그먼트 공간이 부족해지면 테이블 스페이스로부터 익스텐드를 추가로 할당받게된다. 이때 세그먼트에 할당된 모든 익스텐드가 같은 데이터파일에 위치하지 않을 수 있다. (오히려 다른 데이터파일에 위치할 가능성이 더 높다.) 하나의 테이블 스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터파일로 분산해서 저장하기 때문이다.
• DBA : 데이터 블록이 디스크상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값. data block address
• 세그먼트 정보 조회
• SELECT extents, blocks, tablespace_name, segment_name FROM DBA_SEGMENTS
• 사용중인 인스텐트 정보 조회
• SELECT extent_id, file_id, block_id, blocks FROM DBA_EXTENTS

 

 

 

블록단위 I/O

• DBMS 가 데이터를 읽고 쓰는 단위는 블록이다. 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
• 테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

 

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

• 테이블 또는 인덱스 블록을 읽는 방식은 두가지가 있다.
• 시퀀셜 엑세스
  • 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 엑세스이다.
  • 오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫번째 블록 주소 값을 갖는다.
  • 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐드의 첫번째 블록 위에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 곧 full table scan 이다.
• 랜덤 액세스
  • 물리적, 논리적 순서에 따르지 않고, 한 블록씩 접근하는 방식.

 

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

• 버퍼캐시
  • SQl을 수행하는 과정에서 계속 데이터 블록을 읽는데, 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율 적임. 캐싱 메커니즘이 필요하다!
  • 라이브러리 캐시가 SQL 과 실행계획, 함수, 프로세저 등을 캐싱하는 ‘코드캐시' 라고 생각한다면, DB 버퍼캐시는 ‘데이터캐시'라고 할 수 있다.
  • 데이터 블록을 읽을때는 항상 버퍼캐시부터 탐색한다.
  • 버퍼캐시는 공유 메모리 영역이기 때문에 같은 블록을 읽는 다른 프로세스도 득을 본다.
• 논리적 블록 I/O
  • SQL을 처리하는 과정에 발생한 총 블록 I/O.
  • 메모리상의 버퍼캐시를 경유한다.
  • 일반적으로 버퍼캐시에서 블록을 읽은 횟수와 일치한다.
• 물리적 블록 I/O
  • 디스크에서 발생한 총 블록 I/O.
  • 버퍼캐시에서 읽어야 할 블록을 찾지 못할때만 디스크를 액세스하므로 논리적블록 I/O 의 일부를 물리적으로 한다.
  • 상당히 느림. 메모리 I/O에 비해 10000배쯤.
  • 하지만 첫번째 실행할 때 보다 두번째가 줄어들고, 세번째 실행할 땐 더 줄어든다. 버퍼캐시의 점유율이 점점 높아지기 때문이다.
  • 한참 후에 다시 실행하면 반대로 늘어난다. 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.
• 버퍼캐시 히트율
  • BCHR : 가장 전통적으로 많이 사용해 온 지표
  • (논리적 블록 읽기 - 물리적 블록 읽기) / 논리적 블록 읽기 * 100 = ( 1 - ( 물리적 I/O) / (논리적 I/O) ) * 100 물리적 I/O = 논리적 I/O * (100 - BCHR)
  • 물리적 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다.
  • 우리가 튜닝을 진행할 때 물리적 I/O를 줄이려고 노력하지만 사실 논리적 I/O를 줄여야 한다.
  • 변형식을 참고하면, 논리적 I/O는 일정하므로 물리적I/O는 BCHR에 의해 결정된다. BCHR은 시스템상황에 따라 달라지므로 결국 물리적I/O는 시스템상황에 따라 결정되는 통제 불가능한 외생변수이다.
  • 물리적 I/O를 늘리려면 결국 메모리 증설해서 DB 버퍼캐시를 늘리는 방법밖에 없다. 그러기 때문에 한정된 리소스내에서 성능 튜닝을 위해서 결국 논리적 I/O를 줄여야한다.
  • 그렇다면 논리적 I/O를 줄이기 위해서는 어떻게 해야하는가? 논리적 I/O를 줄이기 위해서는 메모리에서 읽는 총 블록의 개수를 줄여야 한다.