💻 본 포스팅은 [친절한 SQL 튜닝] 내용을 참고하여 작성하였습니다.
데이터 저장 구조 및 I/O 메커니즘
SQL을 실행하면 메모리 / 디스크에 있는 DB로부터 원하는 데이터를 찾아온다. 즉, SQL 튜닝은 곧 I/O 튜닝이라고 해도 과언이 아니다. 즉 SQL 튜닝 원리를 제대로 이해하려면 I/O를 이해해야 한다. SQL 튜닝에 앞서 데이터 저장구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘을 살펴보자.
SQL이 느린 이유는?
앞서 말했듯이 우리가 짠 SQL이 느리다면 십중팔구 I/O, 특히 디스크 I/O 때문이다. 알다시피 I/O를 하는중에 프로세스는 sleep 상태에 빠진다. 그만큼 우리가 원하는 로직을 수행하는 데 시간이 오래걸린다는 의미이다. 즉, I/O 시간이 길어지면 길어질수록 프로그램 실행이 더뎌지는데 디스크는 그 중에서도 I/O 속도가 매우 느린 저장장치이다. 그렇기 때문에 I/O를 어떻게 하냐에 따라서 속도가 좌지우지된다.
데이터베이스 저장 구조
그럼 우리의 디스크에 데이터는 어떻게 저장되어 있는가? 먼저 데이터를 저장하려면 테이블스페이스를 생성한다. 테이블스페이스는 세그먼트를 담는 컨테이너로서, 여러 개의 데이터파일(디스크 상의 물리적인 OS 파일)로 구성된다.
세그먼트는 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트다. 테이블, 인덱스를 생성할 때 어떤 테이블 스페이스에 저장할지를 지정한다.
세그먼트는 여러 익스텐트로 구성된다. 파티션 구조가 아니라면 테이블도 하나의 세그먼트, 인덱스도 하나의 세그먼트다. 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 된다. LOB(Large Object, Oracle의 데이터 타입) 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장한다.
익스텐트는 공간을 확장하는 단위다. 그 말은 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다. 익스텐트는 연속된 블록의 집합이기도 하다.
익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. SQL server 등에서는 페이지라는 단어를 쓰기도 한다. 한 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블의 레코드다.
한 익스텐트도 하나의 테이블이 독점한다. MS-SQL server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수도 있다.
추가로 데이터파일과 관계를 알아둘 필요가 있다. 세그먼트 공간이 부족하면 테이블스페이스로부터 익스텐트를 추가로 할당받는다고 했는데, 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다. 실제로 위치하지 않을 확률이 더 높다.
하나의 테이블스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 가능한 한 여러 데이터파일로 분산 저장하기 때문이다.
익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니라는 사실을 알 수 있다.
DBA(Data Block Address)
모든 데이터 블록은 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 주소값을 가진다. 이 주소값을 DBA라고 부른다. 데이터를 읽고 쓰는 단위가 블록이므로 데이터를 읽으려면 DBA부터 확인해야 한다.
인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 이용한다. ROWID는 DBA + 로우 번호 (블록 내 순번)로 구성되므로 이를 분해하면 읽어야 할 테이블 레코드가 저장된 DBA를 알 수 있다.
테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다. 익스텐트 맵을 통해 각 익스텐트의 첫 번째 블록 DBA를 알 수 있다.
마지막으로 정리하면 다음과 같다.
- 블록: 데이터를 읽고 쓰는 단위
- 익스텐트: 공간을 확장하는 단위, 연속된 블록의 집합
- 세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 파티션, 인덱스, LOB 등)
- 테이블스페이스: 세그먼트를 담는 콘테이너
- 데이터파일: 디스크 상의 물리적인 OS파일
블록 단위 I/O
DBMS에서 데이터를 읽고 쓰는 단위는 블록이다. 그렇기 때문에 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 오라클은 기본적으로 8KB 크기의 블록을 사용한다. 테이블 뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.
Sequential access vs Random access
테이블 또는 인덱스 블록을 읽는 방식은 시퀀셜 액세스와 랜덤 액세스로 나뉜다.
시퀀셜 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다.
인덱스의 경우에는 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다.
이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 액세스다.
그럼 테이블은 논리적인 연결고리가 없는데 어떻게 시퀀셜 액세스를 할까?
오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 map으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소를 가지고 있다. 그래서 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽는다. 이것이 Table Full Scan이다.
랜덤 액세스는 논리적, 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.
인덱스를 이용해 리프 노드를 찾고, 리프 노드에서 임의의 블록 주소에 따라 접근하는 것이 이 예이다.
논리적 I/O vs 물리적 I/O
가장 중요한 사실은 디스크 I/O가 SQL 성능을 결정한다는 것이다. 그래서 SQL을 수행할 때 자주 읽는 블록을 매번 디스크에서 읽어오는 것은 성능에 악영향을 끼친다. 그래서 모든 DBMS에는 데이터 캐싱 메커니즘이 필수적으로 필요하다.
데이터를 캐싱하는 역할은 DB 버퍼 캐시가 수행한다. 이것도 SGA의 중요한 구성요소 중 하나다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O 콜을 줄일 수 있다.
버퍼 캐시가 존재하므로 데이터 블록을 읽기전에 항상 버퍼 캐시부터 탐색한다. 별도의 설정으로 버퍼 캐시의 크기도 정해줄 수 있다.
I/O는 논리적과 물리적으로 나뉘는데, 논리적 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O이고, 모든 SQL은 버퍼 캐시를 경유하므로 사실상 메모리 I/O가 곧 논리적 I/O라고 생각해도 무방하다.
물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다.
이론적으로 물리적 I/O는 시스템 상황에 따라 달라진다. 당시의 버퍼 캐시가 어떠냐에 따라 물리적 I/O가 많이 일어날수도 있고 아닐수도 있기 때문이다. 그럼 우리가 통제할 수 있는 변수는 논리적 I/O다. 그러므로 핵심은 SQL 성능을 향상시키려면 논리적 I/O의 총량을 줄여야 한다.는 점이다. 당연히 논리적 I/O가 적으면 물리적 I/O도 적어질 확률이 높아지기 때문이다.
따라서 인덱스, 효율적인 조인등을 활용해서 효율적인 논리적 I/O를 해야한다.
Single block I/O vs MultiBlock I/O
메모리 캐시가 클수록 당연히 좋겠지만, 모두 캐싱하는 것은 불가능하다. 어쩔 수 없이 디스크로부터 필요한 블록을 가져와서 메모리에 적재해야 한다. 블록을 가져올 때 한 블록씩 가져오거나 여러 블록을 한 번에 가져올 수도 있다.
인덱스를 사용할 때는 인덱스 블록/테이블 블록 모두 Single Block I/O 방식을 사용한다. 구체적으로 아래 목록이 Single Block I/O 대상 오퍼레이션이다.
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
반대로, 많은 데이터 블록을 읽을 때는 Multiblock I/O가 효율적이다. 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 이용한다. 블록을 찾을 때 캐시에서 찾지 못한 특정 블록을 읽어오는 경우에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능이다. DBMS는 블록 사이즈가 얼마건 간에 OS 단에서 보통 1MB 단위로 I/O를 수행한다. 즉 1MB/(block size) 단위로 블록을 가져와 적재한다.
여기서 인접한 블록은 물리적인 개념이 아니라 같은 익스텐트의 블록을 의미한다. 이 때, 같은 익스텐트의 블록이 1MB보다 적더라도 동시에 한 익스텐트의 블록만 들고 올 수 있다.
Table Full Scan vs Index Range Scan
테이블에 저장된 데이터를 읽는 방식은 두 가지다. 테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용하는 방식이다.
Table Full Scan이 테이블 전체를 스캔해서 읽는 방식이고, 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식이다.
인덱스를 이용한 테이블 액세스는 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보다.
보통 인덱스를 이용한 테이블 액세스가 더 효율적이라는 인식이 있다. 그런데 보통 인덱스를 이용한 스캔은 랜덤 액세스와 Single block 방식을 이용한다. 그러므로 인덱스 스캔의 대상이 많아지면 모든 블록을 계속해서 한 블록씩 탐색을 해야한다. 반면에 테이블 풀 스캔은 시퀀셜 액세스와 Multiblock I/O 방식을 이용한다. 한 번에 여러 블록을 가져와서 탐색을 하므로 탐색할 대상이 많은 경우에는 오히려 효율적인 방식이다.
캐시 탐색 메커니즘
마지막으로 버퍼 캐시에서 블록은 어떻게 읽어올까?
우선 버퍼 캐시는 해시 구조로 관리되고 있다. 예를 들어 해시 함수를 5로 나눈 나머지 값으로 사용한다고 하자. 그리고 20번 블록을 찾으려고 하면 5로 나눈 나머지가 0인 0번째 해시 체인을 찾아간다. 그리고 27번 블록을 찾으면 2번 해시 체인을 찾아간다. 만약 2번 해시 체인에 없다면, 디스크를 읽어와서 2번 해시 체인에 연결한다.
해시 체인에서 찾은 버퍼 헤더를 찾고 거기서 찾은 포인터로 버퍼 블록을 액세스하는 방식을 사용한다.
추가적으로 SGA는 공유 메모리 영역이므로 동시성 이슈가 발생할 수 있다. 그래서 lock을 이용해 제어를 해줘야 하는데, 모든 해시 체인에 대해 lock을 거는 방식으로는 성능의 저하를 가져올 수 있다.
이 때 체인 래치라는 개념을 통해 해시 체인 별로 lock을 걸어서 관리할 수 있다.
출처
