I/O 튜닝이 곧 SQL 튜닝!
SQL이 느린이유
디스크 I/O 하는동안 프로세스가 잠을 자기 때문! 잠자는 시간을 줄여야 한다!
데이터베이스 저장 구조
출처
- 테이블 스페이스 : 세그먼트를 담는 컨테이너
- 세그먼트 : 데이터 저장공간이 필요한 오브젝트들. (각 세그먼트는 테이블, 인덱스, 파티션, LOB 등을 담는다.)
- 익스텐트 : 공간을 확장하는 단위. 테이블 또는 인덱스에 데이터를 입력하다가 공간이 부족해지면 테이블 스페이스로부터 공간을 할당 받는다.
- 블록 (페이지) : 데이터를 읽고 쓰는 단.위 사용자가 입력한 레코드가 실제로 저장되는 공간.
한 플록에 저장된 레코드는 모두 같은 테이블 레코드이다.
세그먼트에 할당된 익스텐드들은 분산될 가능성이 아주 크다.
DBMS가 파일 경합을 줄이기 위해 데이터를 가능한 여러 데이터파일로 분산저장함.
아래는 각 요소들의 관계 ERD이다.
데이터를 읽고 쓰는 단위
DBMS는 블록 단위로 데이터를 읽고 쓴다. 레코드 하나만 읽는다고 해도 블록을 통째로 읽는다.
오라클은 기본 블록 단위가 8KB인데 1Byte 레코드를 읽기 위해 8KB를 읽는다.
시퀀셜 액세스 vs 랜덤 엑세스
시퀄셜 액세스
논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
오라클은 세그먼트에 할당된 익스텐드 목록을 세그먼트 헤더에 맵으로 관리함. 익스텐트 맵은 각 익스텐트의 첫번째 블록 주소를 가지고 있기 때문에, 익스텐트 첫번째 블록부터 하나하나 읽으면 Full Table Scan이 됨. Full Table Scan 하려면 시퀀셜 엑세스 해버렷!
랜덤 액세스
논리적, 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.
논리적 I/O vs 물리적 I/O
DB 버퍼캐시
캐시가 있는 이유. => RAM이 있는 이유와 비슷함.
매번 오래걸리는 디스크를 읽는 것은 비효율적이다. 그래서 데이터 캐싱 매커니즘은 필수이다.
- SGA 구성요소 중에 DB 버퍼캐시도 중요한 요소
- DB 버퍼캐시는 데이터 캐시이다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해서 같은 블록에 대한 반복 I/O Call을 줄일 수 있다.
- 버퍼 캐시는 공유 메모리 영역이므로 다른 프로세스도 좋다.
논리적 I/O vs 물리적 I/O
논리적 I/O
- SQL을 처리하는 과정중 발생하는 총 블록 I/O를 말한다.
- 메모리 I/O + Direct I/O = 논리적 I/O.
물리적 I/O
- 디스크에서 발생한 총 블록 I/O를 말한다.
- 버퍼캐시에서 못 찾은 블록만 디스크 액세스 하는 I/O이다.
- 메모리 I/O에 비해 1만배 느리다. 디스크 경합 생기면 더 느리다.
버퍼캐시 히트율
버퍼캐시 효율을 측정하는데 전통적인 방법 : BCHR
BCHR = (캐시에서 찾은 블록 수 / 총 읽은 블록 수) * 100
= ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
= (1 - 물리적 I/O / 논리적 I/O) * 100물리적 I/O가 성능을 결정하지만, 실제로 SQL 성능을 향상시키려면 논리적 I/O을 줄여야 한다.
물리적 I/O = 논리적 I/O * (100% - BCHR)물리적 I/O는 BCHR에 의해 결정되고 BCHR은 시스템 상황에 따라 달라지므로, 물리적 I/O는 결국 시스템 상황에 따라 결정됨.
즉 논리 I/O를 줄여야 성능을 높일 수 있다.
-- BCHR이 70%라고 가정
-- 첫번째 논리적 I/O는 10000개
물리적 I/O = 10000 * (100 - 70)% = 3000
-- 두번째 논리적 I/O는 100개
물리적 I/O = 100 * (100 - 70)% = 300논리적 I/O 줄이는 방법
SQL을 튜닝해서 읽는 총 블록 갯수를 줄이면 된다.
논리적 I/O을 줄임으로서 물리적 I/O를 줄이는 것이 SQL 튜닝이다.
- 물리적 I/O 를 줄이려면 메모리를 증설해서 db 버퍼캐시를 늘리는 방법도 있습니다.
BCHR을 높이는 방법은 같은 블록을 자주 사용하게 하면 버퍼캐시를 사용하기 때문에 높아진다.
같은 블록을 자주 사용하게 해서 BCHR을 높이고 논리적 I/O를 줄여 물리적 I/O도 함께 줄이는 것이 SQL 튜닝이다.
일단 내가 생각한 결론
- 논리적 I/O를 줄여야 물리적 I/O를 줄일 수 있는데, 논리적 I/O를 줄이려면 메모리에서 읽는 총 블록의 갯수를 줄여야한다.
- 총 블록의 갯수는 쓸데없고 무의미하게 부르는 블록의 갯수를 줄이는 것.
- BCHR이 높다고 좋은것은 아니다. 블록의 반복 호출이 많아질수록 BCHR이 높아지는데, 이것은 블록의 무의미한 반복 호출이 많다고 볼 수도 있다.
그러면 다른 유효한 블록의 호출이 느려질 수 있다. - 비효율적이고 반복적인 논리적 I/O를 줄이자
- 유효하게 자주 I/O되는 블록을 버퍼캐시에 적절히 점유율을 확보하여 속도를 올리자.
