DBMS의 일반적인 아키텍처

DBMS 내부 역할

쿼리 평가 엔진

• 사용자로부터 입력 받은 SQL 구문을 분석
• 어떤 순서로 기억장치의 데이터에 접근할지 결정

이때 결정되는 계획을 실행 게획이라고 하고, 이 실행 계획을 기반으로, 데이터에 접근하는 방법을 접근 메서드라고 한다.

버퍼 매니저

• 버퍼를 관리하는 역할
• 디스크 용량 매니저와 함께 연동되어 작동

버퍼: DBMS가 특별한 용도로 사용하는 메모리 영역

디스크 용량 매니저

• 어디에 어떻게 데이터를 저장할지 관리
• 데이터의 읽고 쓰기 제어

웹 서버 또는 애플리케이션 서버와 다르게, 데이터베이스는 데이터를 영구적으로 저장해야 한다.

트랜잭션 매니저 & 락 매니저

데이터베이스는 동시에 수백 ~ 수천 명의 사용자가 접근한다. 이때 각 처리는 트랜잭션이라는 단위로 관리된다.

• 트랜잭션으로 데이터의 정합성 유지
• 필요한 경우에는 데이터에 락을 걸어 다른 사용자의 요청 대기시키기

리커버리 매니저

• 시스템 장애를 대비해 데이터 정기적으로 백업
• 문제 발생 시 데이터 복구

어떤 SQL 구문이 왜 느린지, 또는 빠른지는 버퍼 매니저와 쿼리 평가 엔진이 밀접한 관련을 가진다.

---

DBMS와 버퍼

DBMS와 기억장치

하드디스크

DBMS가 대부분의 데이터를 저장하는 저장소

기억장치 계층에서 그렇게 좋지도, 나쁘지도 않은 평범한 기억장치이다.

메모리

다른 기억 장치에 비해 기억 비용이 비싼 기억장치이다.

일반적인 데이터베이스 서버에는 아무리 많아도 100GB를 넘지 않는 메모리가 탑재된다.

당연히 모든 데이터를 메모리에 올리는 것은 불가능하다.

버퍼를 이용한 속도 향상

❗️여기서 말하는 버퍼 = 캐시

메모리를 사용하는 이유는, 성능 향상을 위함이다.

자주 사용하는 데이터는 메모리에 올려두어 디스크 접근을 감소시킨다면, 큰 폭의 성능 향상이 가능하다.

💡 SQL 구문의 실행 시간 대부분은 저장소 I/O 시간이다.

두 개의 버퍼

DBMS는 일반적으로, 데이터 캐시와 로그 버퍼 이렇게 두 개의 역할을 하는 메모리 영역을 가진다.

데이터 캐시

디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 메모리 영역

“디스크를 건드리는 자는 불행해진다”

로그 버퍼

DBMS는 갱신과 관련된 SQL 구문(INSERT, UPDATE, DELETE, MERGE)을 받으면, 바로 저장소에 있는 데이터를 변경하지 않는다.

데이터 베이스 갱신 처리

SQL 구문의 실행 시점과 저장소에 갱신하는 시점에 차이 존재 → 비동기 처리

로그 버퍼에 변경 정보 전송 후, 디스크(로그 파일)에 변경 수행

왜 시점 차이를 뒀을까? 바로 성능때문이다.

데이터를 갱신할 때에도 상당한 시간을 소모하기 때문

따라서, 로그 버퍼에 변경 정보 전송 후, 사용자에게 해당 SQL 구문 실행이 끝났다고 알린 후, 내부적으로 관련된 처리를 계속 수행한다.

메모리 & 트레이드 오프

휘발성의 문제점

데이터 캐시의 경우

메모리에 있던 데이터가 모두 사라져도, 디스크에 있는 원본 데이터를 한 번 더 읽어들이면 되므로, 시간은 걸려도 문제는 없다.

로그 버퍼의 경우

로그 버퍼의 변경 사항이 디스크에 반영되기 전에 장애가 발생해서 사라진다면?

사용자가 수행했던 갱신 정보가 사라진다는 의미이기 때문에, 심각한 문제이다.

로그 버퍼 문제 해결

DBMS가 다운 될 때, 로그 버퍼의 정보가 사라지는 현상은 DBMS가 갱신 처리를 비동기로 한다면 계속 일어날 현상이다.

이를 회피하고자, DBMS는 커밋 시점에 반드시 갱신 정보를 로그 파일에 씀 ⇒ 커밋된 데이터를 영속화

장애가 발생해도, 정합성을 유지할 수 있다.

COMMIT이란?
갱신처리를 확정하는 것

	데이터 정합성	성능
동기 처리	O	X
비동기 처리	X	O

시스템 특성 & 트레이드 오프

데이터베이스의 데이터 캐시와 로그 버퍼의 크기를 비교해보면, 로그 버퍼의 초깃값이 데이터 캐시에 비해 굉장히 작다.

이는 데이터베이스가 기본적으로 검색을 메인으로 처리한다고 가정하기 떄문

만약, 시스템이 검색에 비해 갱신이 많다면, 그럴 떄는 로그 버퍼의 크기를 늘려주는 튜닝 등을 고려해봐야 한다.

검색 vs 갱신

검색과 갱신 중 어떤 것을 더 우선시 해야 할까?

DBMS가 꽤 발전해서, 리소스를 자동으로 조절하는 기능도 있지만, 자동 설정에 의지하는 것은 위험하다.

따라서, 더 많이 실행되는 기능 위주로 생각하자.

갱신 처리가 많다면 ⇒ 로그 버퍼를 크게

검색 처리가 많다면 ⇒ 데이터 캐시를 크게

워킹 메모리 (추가적인 메모리 영역)

위에서 설명했던 메모리 외에도 정렬 또는 해시 관련 처리에 사용되는 작업용 메모리가 있다.

필요할 때만 사용되고, 종료되면 해제되는 임시 영역 ⇒ 따라서 다른 2개의 메모리와 따로 관리되고, 데이터양보다 작아 부족하다면, 추가적으로 저장소를 사용한다.

정렬: ORDER BY 구, 집합 연산, 윈도우 함수 등의 기능

해시: 테이블 등의ㅣ 결합에서 해시 결합이 사용 되는 때 실행

메모리가 부족해진다면?

워킹 메모리가 부족해진다면, 앞서 봤듯이 추가적으로 저장소를 사용한다.

⇒ 이에 따라 디스크에 접근하게 되어 당연히 전체적인 속도 또한 느려진다.

아무리 느려지더라도, SQL 구문에 오류를 절대 발생시키지 않도록 노력하여 처리 계속성을 담보하려 한다.

DBMS와 실행 계획

데이터에 접근하는 방법

파서

• SQL 구문이 항상 올바르다는 보증이 없으므로, 입력받은 SQL 구문 검사
• 내부에서 일어나는 후속 처리를 효율적으로 하기 위해, SQL 구문을 정형적인 형식으로 변환

옵티마이저

• DBMS 두뇌의 핵심
• 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건 고려 ⇒ 선택 가능한 많은 실행 계획 작성
• 이들의 비용을 연산
• 가장 낮은 비용을 가진 실행 계획 선택

카탈로그 매니저

카탈로그란?

DBMS의 내부 정보를 모아놓은 테이블(테이블 또는 인덱스의 통계 정보 저장)

플랜 평가

옵티마이저가 세운 여러 개의 실행 계획을 받아, 최적의 실행 결과를 선택하는 역할

옵티마이저와 통계 정보

플랜 선택을 옵티마이저에게 맡기는 경우, 최적의 플랜이 선택되지 않는 경우가 많다.

그 중, 통계 정보가 부족한 경우가 대표적인 원인이다.

아래는 통계 정보에 포함되는 것들이다.

- 각 테이블의 레코드 수
- 각 테이블의 필드 수와 필드 크기
- 필드의 카디널리티
- 필드 값의 히스토그램(어떤 값이 얼마나 분포되어 있는가)
- 필드 내부에 있는 NULL 수
- 인덱스 정보

❗️옵티마이저가 최적의 플랜을 선택하지 못하는 경우

카탈로그 정보가 테이블 또는 인덱스의 실제와 일치하지 않을 경우

⇒ 테이블에 갱신이 수행될 때, 카탈로그 정보 또한 같이 갱신되어야 한다. 그렇지 않으면 과거 정보를 이용해 잘못된 계획을 세우기 마련

최적의 실행 계획은 어떻게?

통계 정보 갱신은 대상 테이블 또는 인덱스 크기와 수에 따라서, 몇십 분~몇 시간이 소요되는 실행 비용이 높은 작업

따라서 매번 통계 정보를 갱신 할 수 없지만, 또 DBMS가 최적의 플랜을 선택하려면 필요한 조건이다.

실행 계획 & SQL 구문 성능

테이블 풀 스캔 실행 계획

아래는 사용할 예시 테이블이다.

shop_id(점포)	shop_name(점포 이름)	rating(평가)	area(지역)
00001	OO상점	3	서울
00002	XX상점	5	속초

PostgreSQL을 사용해 직접 실행해 보았다.

EXPLAIN SELECT * FROM Shops;

QUERY PLAN                       
-------------------------------------------------------
Seq Scan on shops  (cost=0.00..1.60 rows=60 width=25)
(1 row)

• 조작 대상 객체
• 객체에 대한 조작 종류
• 조작 대상이 되는 레코드 수

위와 같은 정보를 포함하고 있다.

조작 대상 객체

이 부분은 테이블(shops) 외에도, 인덱스, 파티션, 시퀀스처럼 SQL 구문으로 조작할 수 있는 객체라면 올 수 있다

객체에 대한 조작 종류

위 결과에서는 Seq Scan 으로 나타나고 있다.

⇒ ‘순차적인 접근’ 의 줄임말

조작 대상이 되는 레코드 수

결합 또는 집약이 포함되면 1개의 SQL 구문을 실행해도 여러 개의 조작이 수행된다.

→ 이때, 각 조작에서 얼마만큼의 레코드가 처리되는지, SQL 구문 전체의 실행 비용을 파악하는 데 중요한 지표가 된다.

이때, shops 테이블의 데이터를 모두 삭제하고 실행 계획을 다시 검색해본다면?
그대로 레코드 수에는 60 이라는 결과가 나온다 ⇒ 옵티마이저가 어디까지나 통계 정보를 믿기 때문(실제 테이블을 제대로 보지 않는다)

인덱스 스캔의 실행 계획

SELECT *
FROM Shops
WHERE shop_id = '00050';

접근 대상 객체와 조작

Index Scan 으로 바뀐 것을 볼 수 있다.

인덱스 스캔은 풀 스캔보다 왜 빠를까?
→ 풀 스캔이 모집합의 데이터양에 비례해서 처리 비용이 늘어나지만, B-tree가 모집합의 데이터양에 따라 대수 함수적으로 처리 비용이 늘어난다.

테이블 결합의 실행 계획

DBMS는 결합 할 때, 세 가지 종류의 알고리즘을 사용

Nested Loops

한 쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 테이블에서 찾는 방식

이 방식을 객체 지향 언어로 구현한다면, 중첩 반복이라 Nested Loop 라고 이름이 붙여졌다.

Sort Merge

결합 키(여기서는 점포 ID)로 레코드를 두 테이블 다 정렬 후에, 순차적으로 두 개의 테이블을 결합하는 방식

결합 전에 전처리(정렬)가 필요한데, 이때 바로 위에서 봤던 워킹 메모리 사용한다.

Hash

이름 그대로 결합 키값을 해시값으로 매핑하는 방법

이 또한, 해싱 테이블이 필요하므로 워킹 메모리 영역을 필요로 한다.

SELECT shop_name
FROM Shops S INNER JOIN Reservations R
ON S.shop_id = R.shop_id;

QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=2.35..17.42 rows=120 width=8)
   Hash Cond: (r.shop_id = s.shop_id)
   ->  Seq Scan on reservations r  (cost=0.00..14.00 rows=400 width=24)
   ->  Hash  (cost=1.60..1.60 rows=60 width=14)
         ->  Seq Scan on shops s  (cost=0.00..1.60 rows=60 width=14)
(5 rows)

책과는 다르게 Hash Join 결합 알고리즘을 사용하고 있다.

결합의 경우, 어떤 테이블에 먼저 접근하는 지가 굉장히 중요한 의미를 갖는다.

보면, 같은 중첩 단계에서는 위에서 아래로 실행하고, 중첩 단계가 다르다면, 더 깊을수록 먼저 실행된다.

위의 결과도 보면, Hash Join 전에 reservations에 대한 Seq Scan 과 shops에 대한 Seq Scan 및 Hash 작업이 더 깊은 중첩단계에 있기 때문에, 그 작업들이 실행된 후, Join이 진행될 것이다.

실행 계획의 중요성

요즘 옵티마이저는 꽤 우수하지만, 인덱스를 사용해야 빨라지는 부분인데 사용하지 않거나, 테이블 결합 순서를 이상하게 적는 실수를 할 수 있다.

따라서 이런 경우에는 최후의 튜닝 수단, 즉 수동으로 실행 계획을 변경해주어야 한다.

실행 계획을 변경하려면, 어떤 선택지가 있는지 알아야하기 때문에 어떤 테이블 설정이 효율적이며, 어떤 SQL 구문이 주어졌을 때, 어떤 실행 계획이 나올지 예측할 수 있어야 한다.