친절한 SQL 튜닝 03

SQL의 성능을 결정하는 디스크 I/O

1. I/O를 처리하는 동안 프로세스는 잠을 잔다.

2. 즉 프로세스가 디스크에서 데이터를 읽어야 할 때, CPU는 OS에 반환하고 잠시 waiting state 상태로 진입해 I/O가 완료되길 기다린다. 즉 프로세스가 I/O가 많을수록 대기시간이 길어지고 성능이 느려진다.

3. 아무리 하드웨어 성능이 향상되고 있지만 여전히 느리다. 그러므로 디스크 I/O를 줄이기 위한 최적화가 필요하다.

 

SQL의 데이터 구조

1. 블록: 데이터를 읽고 쓰는 단위

2. 익스텐트: 공간을 확장하는 단위, 연속된 블록 집합

3. 세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)

4. 테이블 스페이스: 세그먼트를 담는 컨테이너

5. 데이터 파일: 디스크 상의 물리적인 OS파일

 

DBMS는 블록 단위로 데이터를 읽고 쓴다.

데이터베이스 저장 구조

1. 테이블 스페이스가 가장 외곽에 위치

2. 세그먼트는 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블 스페이스에 저장할지를 지정함

3. 세그먼트는 여러 익스텐트로 구성됨. 테이블, 인덱스도 하나의 세그먼트다.

4. LOB 칼럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장한다.

5. 익스텐트는 공간을 확장하는 단위다. 테이블이나 인덱스에 데이터가 추가되어 공간이 부족하면 해당 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가로 할당받는다.

6. 익스텐트는 연속된 블록의 집합이다.

7. 사용자가 레코드를 실제로 저장하는 공간은 데이터 블록이다. DB 종류에 따라 Page라고 부르기도 한다(SQL Server, DB2 등)

8. 한 블록은 하나의 테이블이 독점한다. 즉 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.

9. 한 익스텐트도 하나의 테이블이 독점한다. 즉 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다. (MY-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수도 있다.)

 

10. 세그먼트에 할당된 모든 익스텐트가 같은 데이터 파일에 위치하지 않을 수 있다. 그 반대일 가능성이 더 높다.

11. 하나의 테이블스페이스를 여러 데이터 파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 여러 데이터 파일로 분산해서 저장하기 때문이다.

12. 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다.

 

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

1. 시퀀셜 액세스

논리적, 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식

주소 값을 통해 앞 또는 뒤로 순차적으로 스캔하는 방식

 

테이블 블록 간에는 논리적인 연결고리가 없음

 

해결법

 

오라클의 경우 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵(Map)으로 관리한다.

익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다. 읽어야 할 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 Full Table Scan이다.

 

2. 랜덤 액세스

논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

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

1, DB 버퍼 캐시

SGA의 구성요소인 라이브러리 캐시와 비슷한 기능을 함

라이브러리 캐시는 SQL과 실행계획, 함수 및 프로시저 등을 캐싱하는 "코드 캐시"

DB 버퍼 캐시는 "데이터 캐시", 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O를 줄이기 위해서 사용한다.

즉 프로세스의 waiting을 줄여주어 성능을 향상한다.

 

2. 논리적 I/O

SQL을 수행하면서 읽은 총 블록 I/O

 

3. 물리적 I/O

디스크에서 발생한 I/O

 

Single Block I/O vs Multiblock I/O

1. 싱글

캐시에서 찾지 못한 데이터 블록을 디스크에서 요청할 때 한 번의 I/O에 하나의 블록을 요청하는 방식

일반적으로 인덱스를 이용할 땐 기본적으로 인덱스와 테이블 모두 싱글 방식을 사용한다. 소량의 데이터를 읽을 때 주로 사용하므로 효과적임

2. 멀티

캐시에서 찾지 못한 데이터 블록을 디스크에서 요청할 때 한 번의 I/O에 여러 개의 블록을 요청하는 방식

캐시에서 데이터 블록을 찾지 못한 경우 디스크상에 찾으려는 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능 ( 인접한 블록 == 같은 익스텐트에 속한 블록)

 

Table Full Scan vs Index Range Scan

항상 full scan이 성능이 나쁜 것은 아니다. 오히려 그 반대인 경우도 있다.

 

1. full scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽어드리고(시퀸셜 액세스) 캐시에서 못 찾으면 한 번의 I/O를 통해 인접한 수십~수백 개의 블록을 한꺼번에 읽어 들인다(Multiblock I/O). 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.

 

2. 하지만 소량의 데이터를 찾을 때 다량의 데이터를 스캔하는 것은 비효율적이어서 index range scan을 사용해야 한다.

 

3. Index Range Scan은 랜덤 액세스와 Single I/O 방식으로 디스크 블록을 읽는다. 즉 캐시에서 블록을 못 찾으면 레코드 "하나"를 읽기 위해 잠을 자는 I/O 메커니즘이다. 따라서 많은 데이터를 읽을 때 불리하고, 스토리지 성능이 수십 배 좋아져도 성능이 조금밖에 좋아지지 않는다.

 

4. 그리고 읽었던 블록을 반복해서 읽는 비효율이 있다. 많은 데이터를 읽을 때 물리적, 논리적 I/O 둘 다 불리하다. 한 블록에 500개의 레코드가 있다면 같은 블록을 최대 500번 읽게 된다. (반면에 table full scan은 한 번만 읽음)

 

5. 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안된다. 읽을 데이터가 일정량을 넘으면 table full scan이 유리하다.

 

출처 및 참고문헌

친절한 SQL 튜닝 국내도서 저자 : 조시형 출판 : 디비안(주) (DBian) 2018.06.01 상세보기