[SQL 레벨업] 1장. DBMS 아키텍쳐

1강. DBMS 아키텍쳐 개요

1. 쿼리 평가 엔진
   • 실행 계획을 세우고 실행하는 DBMS의 핵심 기능을 담당하는 모듈
2. 버퍼 매니저
   • 버퍼라는 메모리 영역을 관리하는 모듈
3. 디스크 용량 매니저
   • 데이터를 어떻게 저장할지, 데이터의 읽고 쓰기를 제어
4. 트랜잭션 매니저와 락 매니저
   • 트랜잭션의 정합성을 유지하면서 실행시키고, 필요한 경우 데이터에 락을 걸어 다른 사람의 요청을 대기시키는 모듈
5. 리커버러 매니저
   • 데이터를 정기적으로 백업하고, 문제가 일어났을 때 복구해주는 모듈

2강. DBMS와 버퍼

• 기억 비용과 접근 속도에 따라 기억장치 분류됨
  • 1차 기억장치 : 메모리, 레지스터 등 (접근 속도 빠르고, 기억 비용 높음)
  • 2차 기억장치 : 하드디스크, DC, DVD 등
  • 3차 기억장치 : 테이프 등
• HDD
  • DBMS가 데이터를 저장하는 매체는 대부분 HDD
• 메모리
  • DBMS가 일부 데이터를 메모리에 올림 (성능 향상 목적 = 버퍼or캐시)
  • SQL 구문은 대부분의 실행 시간을 저장소 I/O(입출력)에 사용하기 때문에 접근 속도가 빠른 메모리에 저장하면 성능이 좋음
    • 매우 적은 양의 데이터에 접근하는 SQL구문은 I/O보다 CPU 연산에 시간 잡아먹음
  • 메모리 위에 두 개의 버퍼가 존재
    • 데이터 캐시 : 디스크에 있는 데이터의 일부를 메모리에 유지
    • 로그 버퍼 : 갱신 처리 (insert, delete, update, merge)와 관련
      • 갱신처리는 비동기로 이루어짐 (commit)
  • 메모리 성질이 초래하는 트레이드오프 (Trade-off)
    • 휘발성 → 동기 처리/비동기 처리 : 데이터 정합성, 성능이름 데이터 정합성 성능

      동기 처리	O	X
      비동기 처리	X	O

  • 시스템 특성에 따른 트레이드오프 (Trade-off)
    • 데이터베이스는 검색을 중시한 메모리 배분이 기본
      • 기본 : 데이터 캐시 > 로그 버퍼
    • 검색에 비해 갱신이 잦다면, 로그 버퍼의 크기를 늘려주는 튜닝(최적화)를 고려한다.
    • 로그 버퍼와 데이터 캐시의 크기로 검색 처리 위주인지, 갱신 처리 부하를 고려한 설계인지는 알 수 있다.
  • 워킹 메모리 (Working Memory)
    • 정렬, 해시 관련 처리에 사용되는 작업용 영역
    • 다루려는 데이터 양보다 작아 부족해지는 경우가 생기면 저장소를 디스크 영역을 사용함
    • DBMS는 메모리가 부족하더라도 무언가를 처리하려고 계속 노력하는 미들웨어다.

3강.DBMS와 실행 계획

SQL은 일반 프로그래밍 언어와 다르게 어디에 있는 데이터를 어떻게 찾고 처리할지 등의 절차적인 세부사항을 작성해줄 필요가 없다. 모든 것은 DBMS에 맡긴다.

하지만 How를 의식하지 않고 사용하면 성능 문제로 고생하게 된다. 이때문에 RDB가 숨기고 있는 내부 절차를 들여다봐야한다.

• 파서 (parser)
  • 파스(구문 분석) 역할, 일반 프로그래밍 언어의 컴파일 역할 (문법 검사 등)
• 옵티마이저 (optimizer)
  • 데이터 접근법 (실행 계획) 최적화, DBMS 두뇌의 핵심
  • 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건을 고려해서 선택 가능한 많은 실행 계획을 작성하고, 이들의 비용을 연산하고, 가장 낮은 비용을 가진 실행 계획을 선택한다.
• 카탈로그 매니저 (catalog manager)
  • 카탈로그란 DBMS의 내부 정보를 모아놓은 테이블들로, 테이블 또는 인덱스의 통계 정보가 저장되어 있음
  • 옵티마이저가 실행 계획을 세울 때 옵티마이저에 중요한 정보를 제공
• 플랜 평가 (plan evaluation)
  • 옵티마이저가 SQL 구문에서 여러 개의 실행 계획을 세운 뒤 그것을 받아 최적의 실행 결과를 선택하는 것
• 카탈로그에 포함된 통계 정보
  • 각 테이블의 레코드 수
  • 각 테이블의 필드 수와 필드의 크기
  • 필드의 카디널리티 cardinality (값의 개수)
  • 필드값의 히스토그램 (어떤 값이 얼마나 분포되어 있는가)
  • 필드 내부에 있는 NULL 수
  • 인덱스 정보
• 테이블의 데이터가 많이 바뀌면 카탈로그의 통계 정보도 함께 갱신해야 한다. 하지만 통계 정보 갱신은 실행 비용이 굉장히 높은 작업이다. DBMS가 최적의 플랜을 선택하려면 필요한 조건이므로 갱신 시점을 확실하게 검토해야 한다.

4강. 실행계획이 SQL 구문의 성능을 결정

• 일반적인 DBMS 실행계획
  • 조작 대상 객체
  • 객체에 대한 조작의 종류
  • 조작 대상이 되는 레코드 수
    • SQL 구문 전체의 실행 비용을 파악하는 데 중요한 지표
    • 카탈로그 매니저로부터 얻은 값, 통계 정보 최신이 아닐 수 있음
• 모집합의 데이터가 많을수록 인덱스 스캔이 좋다

• 간단한 테이블 결합의 실행 계획
  • 가장 간단한 결합 알고리즘 Nested Loops
    • 한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식
  • Sort Merge
    • 결합 키로 레코드를 정렬하고, 순차적으로 두 개의 테이블을 결합하는 방법, 결합 전에 전처리로 정렬을 수행 (워킹 메모리 사용)
  • Hash
    • 결합 키 값을 해시값으로 맵핑, 해시 테이블을 만들어 작업용 메모리 영역 필요로함.

5강. 실행 계획의 중요성

• 옵티마이저도 완벽하지 못하다. 예를 들어 인덱스를 사용해야 빨라지는 부분인데 사용하지 않거나, 테이블 결합 순서를 이상하게 적는 실수를 할 수도 있다.
• 최후의 튜닝 수단으로 힌트를 사용할 수 있다.

정리

• 데이터베이스는 다양한 트레이드오프의 균형을 잡으려는 미들웨어
• 특히 성능적인 관점에서는 데이터를 저속의 저장소(디스크)와 고속의 메모리 중에 어디에 위치시킬지의 트레이드오프가 중요
• 데이터베이스는 갱신보다도 검색과 관련된 것에 비중을 두도록 기본 설정되어 있지만, 실제 시스템에서도 그럴지는 판단이 필요
• 데이터베이스틑 SQL을 실행 가능한 절차로 변환하고자 실행 계획을 만듦
• 사용자가 실행 계획을 읽는다는 것은 데이터베이스의 이상을 어기는 일이지만, 실행 계획을 수동적으로 변경해야하는 경우도 있음.

References

SQL 레벨업 - DB 성능 최적화를 위한 SQL 실전 가이드