데이터 저장 구조 및 I/O 메커니즘 (1)

◎ SQL이 느린 이유

십중팔구 디스크 I/O로 인한 것이다.

OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 잔다.

 

프로세스(Process)는 '실행 중인 프로그램'이다.

프로세스는 생성(new) 이후 종료(terminated) 전까지 준비(ready)와 실행(running)과 대기(wating) 상태를 반복한다.

실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태(Runnable Queue)로 전환했다가 다시 실행 상태로 전환한다. 여러 프로세스가 하나의 CPU를 공유할 수 있으나, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 이런 메터니즘이 필요하다.

 

interrupt 없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면(waiting) 상태에서 I/O가 완료되기를 기다린다.

정해진 OS 함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 자는 것이다.

열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.

따라서 디스크 I/O가 SQL 성능을 좌우한다고 볼 수 있다.

 

 

◎ 데이터베이스 저장 구조

데이터를 저장하려면 먼저 테이블스페이스를 생성해야 한다.

테이블스페이스는 세그먼트(하나의 독립된 개체 (테이블, 인덱스, 파티션, LOB 등))를 담는 콘테이너로서, 여러 개의 데이터파일(디스크 상의 물리적인 OS파일)로 구성된다.

 

 

테이블스페이스를 생성하면 세그먼트를 생성한다.

세그먼트는 테이블, 인덱스처럼 저장곤간이 필요한 오브젝트다. 그럼 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지 지정한다.

 

세그먼트는 여러 익스텐트(연속된 데이터 블록의 집합)로 구성되어있다.

파티션 구조가 아니라면 테이블도 하나의 세그먼트이며 인덱스도 하나의 세그먼트다. 테이블이나 인덱스가 파티션 구조라면 각 파티션이 하나의 세그먼트가 된다. LOB 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과는 별도 공간에 값을 저장한다.

 

익스텐트는 공간을 확장하는 단위이다.

테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.

 

익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다.

DB2, SQL Server 같은 DBMS는 블록 대신 페이지(page)라는 용어를 사용한다.

 

※ 파티션이란?

https://dakuo.tistory.com/60

파티션(Partition)의 개념

 

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

한 익스텐트도 하나의 테이블이 독점한다. 즉, 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다.

 

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

정확히는 서로 다른 데이터파일에 위치할 가능성이 더 높다.

 

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

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

 

위의 개념을 간단하게 정리하자면 아래와 같다.

• 블록 : 데이터를 읽고 쓰는 단위
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록의 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터파일 : 디스크 상의 물리적인 OS 파일

 

 

◎ 블록 단위 I/0

클라우드에 위치한 문서는 파일 단위로 저장하고, 파일 단위로 읽는다. 따라서 데이터망에 연결한 상태에서 저장 버튼을 자주 누르게 되면 요금 폭탄을 맞을 것이다.

그렇다면 데이터베이스에서 데이터를 읽고 쓰는 단위는 무엇일까? 2GB 짜리 파일을 파일 단위로 매번 데이터를 읽고 쓰는 건 상상하기 어렵다. 100MB 짜리 세그먼트 역시 마찬가지다. 익스텐트는 단위를 확장하는 단위다. 고로 블록이 바로 DBMS가 데이터를 읽고 쓰는 단위이다.

 

데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 1Byte 짜리 컬럼 하나만 읽고 싶어도 블록을 통째로 읽어야 한다. 오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해서는 8KB를 읽어야 하는 셈이다.

 

인덱스 역시 블록 단위로 데이터를 읽고 쓴다.

 

 

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

테이블 또는 인덱스 블록을 액세스(=읽는) 방식으로는 시퀀셜 액세스와 랜덤 액세스의 두 가지가 존재한다.

 

1. 시퀀셜 액세스

- 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다. 이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 엑세스다.

 

테이블 블록 간에는 서로 논리적인 연결고리를 갖고 있지 않다. 그렇다면 테이블은 어떻게 시퀀셜 방식으로 엑세스할까?

오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다.

 

익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다.

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

 

2. 랜덤 액세스

- 논리적, 물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식이다.

 

 

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

디스크 I/O가 SQL 성능을 결정한다.

SQL을 수행하는 과정에서 계속해서 데이터 블록을 읽는데, 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다. 이것이 DBMS에서 데이터 캐싱 메커니즘이 필수적인 이유이다.

 

SGA에서 라이브러리 캐시가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라고 한다면, DB 버퍼캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다.

 

서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다.

캐시에서 블록을 찾으면 프로세스가 I/O Call이 이루어지지 않아 잠들지 않는다.없더라도 처음엔 I/O Call이 이루어져 잠에 빠지겠으나, 같은 블록을 두 번째 읽을 때부터는 잠에 들지 않을 것이다.특히 버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.

 

 

1. 논리적 I/O

- 논리적 블록 I/O는 SQL문을 처리하는 과정에서 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다.

 

2. 물리적 I/O

- 물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다.SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적 I/O로 한다.

 

※ 메모리 I/O는 전기적 신호인 데 반해, 디스크 I/O는 액세스 암(Arm)을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해 상당히 느리다. 보통 10,000배쯤 느리다. 디스크 경합이 심할 때는 더 느리다.

 

 

◎ 왜 논리적 I/O인가?

ex) 바퀴를 7,500번가량 회전시켜야 갈 수 있는 거리 - 논리적인 일량 (변함없음)

ex) 페달을 750번가량 밟아야 갈 수 있는 거리 - 물리적인 일량 (매번 변화) 

 

SQL을 수행하려면 데이터가 담긴 블록을 읽어야한다.

SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 조건절에 같은 변수 값을 입력하면, 아무리 여러 번 실행해도 매번 읽는 블록 수는 같다. SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O다.

모든 블록은 DB 버퍼캐시를 경유해서 읽는다. 따라서 논리적 I/O 횟수는 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다.

 

DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O다.

데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL을 실행할 때마다 다르다. 첫번째 실행할 때보다 두번째 실행할 때 줄어들고, 세 번째 실행할 땐 더 줄어든다. 연속해서 실행하면 DB 버퍼캐시에서 해당 테이블 블록의 점유율이 점점 높아지기 때문이다.

반대로 한참 후에 다시 실행하면 물리적 I/O가 늘어난다. DB 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.

 

 

◎ 버퍼캐시 히트율 BCHR

버퍼캐시 효율을 측정하는 데 가장 많이 사용하는 지표는 버퍼캐시 히트율(BCHR)이다.

BCHR은 읽은 전체 블록에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다.

블록을 읽을 때는 해당 블록을 먼저 버퍼캐시에서 찾아보고 없을 때에만 디스크에서 읽는다. 이때도 바로 읽는 것이 아니라 먼저 버퍼캐시에 적재하고 읽는다.

물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.

 

그렇다면 어떻게 논리적 I/O를 줄일 수 있을까?

SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

물리적 I/O는 통제 불가능한 외생변수이다. 반면, 논리적 I/O는 통제 가능한 내생변수다.

 

그러나 BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다.

같은 블록을 비효율적으로 반복해서 읽으면 BCHR이 높아진다.