1.3.1 SQL이 느린 이유
- SQL이 느린 이유는 I/O 때문이다 → 디스크 I/O 때문이다
- I/O란?
- 잠 (sleep)
- blocking 때문에 기다려야 하기 때문에
- 프로세스 (Process)는 실행 중인 프로그램이다
- 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있다
- 그래서 프로세스는 interrupt에 의해서 수시로 실행 준비 상태(Runnable Queue)로 전환 했다가 다시 실행 상태로 전환한다
- 디스크에서 데이터를 읽어야 할 땐(I/O) CPU를 OS에 반환하고 수면(waiting)상태에서 I/O가 완료되기를 기다린다
- 열심히 일해야 할 프로세스가 한가하게 기다리고 있으니 I/O가 많으면 성능이 느릴 수 밖에 없다
1.3.2 데이터베이스 저장 구조
- 테이블 스페이스 : 세그먼트를 담는 콘테이너, 여러 개의 데이터 파일(디스크 상의 물리적인 OS파일)로 구성된다
- 세그먼트 : 데이터 저장공간이 필요한 오브젝트, 여러 익스텐트로 구성
- 익스텐트 : 공간을 확장하는 단위, 공간이 부족하면 테이블스페이스로부터 익스텐트를 추가로 할당받는다, 연속된 블록들의 집합, 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다
- 한 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다
- 한 익스텐트도 하나의 테이블이 독점한다. 즉, 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다
| 이름 | 간단한 정의 |
| 블록 | 데이터를 읽고 쓰는 단위 |
| 익스텐트 | 공간을 확장하는 단위, 연속된 블록 집합 |
| 세그먼트 | 데이터 저장공간이 필요한 오브젝트 |
| 테이블스페이스 | 세그먼트를 담는 컨테이너 |
| 데이터파일 | 디스크 상의 물리적인 OS 파일 |
DBA (Data Block Address)
- 데이터 블록이 가지고 있는 자신만의 고유 주소값
- 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 이용
- DBA + 로우 번호
- 테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다 → 각 익스텐트의 첫 번째 블록 DBA를 알 수 있다 → 익스텐트는 연속된 블록 집합이므로 첫번째 블록 뒤에 연속해서 저장된 블록을 읽으면 된다
1.3.3 블록 단위 I/O
- DBMS가 데이터를 읽고 쓰는 단위 : 블록
- 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다
- 테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다
1.3.4 시퀀셜 액세스 vs 랜덤 액세스
테이블 또는 인덱스 블록을 액세스하는 (읽는) 방식으로 두가지가 있다
- 시퀀셜(Sequential) 액세스
- 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
- 인덱스 리프 블록 (<인덱스 key 값, RowId> 로 구성) 은 서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식
- 굵은 실선 화살표
- *Full Table Scan : 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽는것
- 랜덤 (Random) 액세스
- 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(touch)하는 방식이다
- 점선 화살표
1.3.5 논리적 I/O vs 물리적 I/O
DB 버퍼캐시
- DBMS에 데이터 캐싱 메커니즘이 필수인 이유
- 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이기 때문에
- 라이브러리 캐시
- SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'
- DB 버퍼캐시
- '데이터 캐시'
- 디스크에서 읽은 데이터 블록을 해싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 것이 목표
논리적 I/O vs 물리적 I/O
논리적 블록 I/O 중 일부를 물리적으로 I/O 한다
논리적 블록
- SQL을 처리하는 과정에 발생한 총 블록 I/O
물리적 블록
- 디스크에서 발생한 총 블록 I/O
1.3.6 Single Block I/O vs Multiblock I/O
- Single Block I/O
- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
- 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O를 사용한다
- 인덱스는 소량 데이터를 읽을 때
- Multi Block I/O
- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
- 많은 테이블 블록을 읽을 때
- 인덱스를 이용하지 않고 테이블 전체를 스캔할 때
- 대용량 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설명하면 성능이 좋아진다
- 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재한는 기능
- '인접한 블록' : 같은 익스텐트에 속한 블록
1.3.7 Table Full Scan vs Index Range Scan
- Table Full Scan
- 테이블 전체를 스캔해서 읽는 방식
- 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다
- 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 '한번의 수면(I/O Call)'을 통해 한꺼번에 I/O 하는 메거니즘이다
- SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다
- Index Range Scan
- 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다
- 인덱스에서 '일정량' 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
- 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다
- 캐시에서 블록을 못 찾으면, 레코드 하나를 읽기 위해 매번 잠을 자는 I/O 메커니즘이다
- 랜덤이기 때문에 읽었던 블록을 반복해서 읽는 비효율이 있다
인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐, 모든 성능 문제를 인덱스로 해결하려 해선 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan 이 유리하다
1.3.8 캐시 탐색 메커니즘
- 버퍼캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터(Pointer)로 버퍼 블록을 액세스하는 방식을 사용한다
메모리 공유자원에 대한 액세스 직렬화
- 버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다
- 문제 : 하나의 버퍼블록을 두 개 이상의 프로세스가 '동시에' 접근하려고 할 때 발생한다
- 동시 접근 시 블록 정합성에 문제 발생 가능
- 자원을 공유하는 것처럼 보여도 내부에선 한 프로레스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화 (Serialization) 메커니즘이 필요하다 → 래치(Latch)
- SGA를 구성하는 서브 캐시마다 별도의 래치가 존재한다
- 빠른 데이터베이스를 구현하려면 버퍼캐시 히트율을 높여야 하지만, 캐시 I/O도 생각보다 빠르지 않을 수 있다 → 래치에 의한 경합 가능성
- 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야한다
직렬화 문제
- 래치를 해제한 상태로 데이터를 사용하는데 하필 후행 프로세스가 같은 블록 접근 시 데이터 정합성의 문제 발생 가능
- → 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 문제를 해결한다
'SQL 튜닝 > Ch01 SQL 처리 과정과 IO' 카테고리의 다른 글
| SQL 공유 및 재사용 (2) (0) | 2024.11.13 |
|---|---|
| SQL 파싱과 최적화 (1) (0) | 2024.11.11 |