[데이터베이스 첫걸음] 9-10장

디비디비딥 스터디 시리즈

[ 데이터베이스 첫걸음 ] 1장 2장 3장 4장 5장 6장 7장 8장 9-10장

[ SQL 첫걸음 ] 1장 2장 3-4장 5-6장 7-8장

9장. 백업과 복구 - 장애에 대비하는 구조

• 트랜잭션의 특성 중 Durability(지속성)은 시스템이 정상일 때뿐만 아니라 비정상적 종료 등의 시스템 장애에 견딜 수 있다는 것을 의미
• 데이터베이스의 쓰기는 기억장치의 임의 장소에 무작위로 액세스해서 쓰기를 수행 → 동기화 쓰기는 느려서 성능 면에서 실용적이지 X

DBMS의 3가지 구조

지속성과 성능의 양립을 위한 DBMS 구조

로그 선행 쓰기 (WAL. Write Ahead Log)

• 데이터베이스의 데이터 파일 변경을 직접 수행하지 않고 우선 로그 변경 내용을 기술한 로그 레코드를 작성하여 동기화하는 구조
• MySQL에서 InnoDB log
• Commit 시에는 WAL에 변경 내용을 write하므로 트랜잭션 커밋과 동시에 데이터 파일을 동기화할 필요 X

WAL의 이점

1. 디스크에 연속하여 write하므로 무작위로 쓰는 것보다 성능이 높음
2. 디스크에 쓰는 용량과 횟수를 줄일 수 있음
3. 데이터베이스 버퍼를 이용하므로 DB 데이터 파일로 변경 시 효율성이 높음

데이터베이스 버퍼

• 트랜잭션마다 비동기 write를 수행하면 로그와 데이터 파일 간의 일관성 유지가 어려움
• 데이터 파일의 입력을 데이터베이스 버퍼로 경유하여 단순화
• 효율적으로 데이터 일관성 유지 가능
• WAL과 버퍼 풀에 갱신이 반영되고, 이후 check point에서 데이터파일에 수정사항이 반영되는 것이 반복됨

MySQL 갱신 흐름

1. 갱신 대상의 데이터를 포함한 page가 버퍼 풀에 존재하는지 확인 후 없다면 데이터 파일로부터 읽기
2. 버퍼 풀의 해당 page에서 갱신 수행
3. 2번의 갱신 내용이 commit과 함께 log에 기록됨. 버퍼 풀에 갱신되었지만, 아직 데이터파일에 써지지 않은 page는 버퍼 풀 내에서 dirty page로 다룸
4. 데이터 page는 Check Point 때 데이터 파일로 써짐(= 체크포인트)
5. 4번의 Check Point 이전의 로그 파일은 불필요. 갱신과 함께 1번부터 다시 반복

크래시 복구 (Crash Recovery)

• Crash 발생 시 상태
  • WAL : 마지막으로 커밋된 트랜잭션의 갱신 정보를 가짐
  • 데이터베이스 버퍼 : 내용이 모두 소실됨
  • 데이터베이스 파일 : 마지막 체크 포인트까지의 갱신 정보를 가짐
• Crash 이후 MySQL 서버 재시작 시 Roll-Forward 수행
  • 데이터베이스 파일과 WAL의 check point 이후 갱신 정보를 사용하여 데이터베이스 파일을 Crash 이전까지 commit된 최신 상태로 수정 (= 롤 포워드)
• 논리적 파괴(DDL 문에 의한 테이블 파기 등)나 물리적 파손(디스크 장치 고장 등)에는 이와 같은 대응 불가능 → 주기적 백업 필요. 장애 발생 시 백업으로 복원

백업과 복구

PITR (Point-in-time Recovery)

• 백업을 통한 복구는 백업 이후 갱신이 반영되지 않음
• PITR: 데이터베이스에 실행된 갱신을 기록한 로그를 보존하여(Archive) 복원한 데이터베이스에 순차 반영해 백업 이후의 임의의 시점으로 복원하는 것

DBMS	Oracle	MySQL	PostgreSQL	DB2	SQL Server
로그 이름	REDO 로그	바이너리 로그	WAL 로그	트랜잭션 로그	트랜잭션 로그
Archive 지정	O	X (본문 참조)	O	O	O
Archive 시 이름	ARCHIVELOG	X	WAL Archive	Archive 로깅	완전 복구 모델
비 Archive 시 이름	NOARCHIVELOG	X	(없음)	순환 로깅	완전 복구 모델
- Archive 지정
- Crash 복구를 위해 check point 이전의 로그도 보존하는 모드
- 바이너리 로그
- PITR에는 MySQL 전체(InnoDB에 한정하지 않고)에서 이용하는 바이너리 로그 사용
- InnoDB 로그는 InnoDB 전용 crash 복구에만 이용

백업

핫 백업과 콜드 백업

• Hot Backup
  • 온라인 백업
  • 백업 대상의 데이터베이스를 정지하지 않고 가동한채로 백업 데이터를 얻음
  • MySQL Hot Backup
    • 트랜잭션 구조 이용, 특수 로그 지정, OS 또는 하드웨어의 스냅샷 등의 방법 존재
    • mysqldump 커맨드라인 클라이언트 유틸리티 이용
• Cold Backup
  • 오프라인 백업
  • 백업 대상의 데이터베이스를 정지한 후 백업 데이터를 얻음
  • MySQL Cold Backup
    • MySQL 서버를 셧다운하고 데이터 디렉터리 안의 디렉터리와 파일을 OS 명령어로 모두 복사
    • 주로 데이터베이스의 기능으로 백업 데이터를 얻음

논리 백업과 물리 백업

• Logical Backup
  • SQL 기반의 텍스트 형식으로 백업 데이터 기록
  • 오픈 소스 데이터베이스에서 주로 이용
• Physical Backup
  • 클로즈드 소스 데이터베이스에서 주로 이용
  • 데이터 영역을 그대로 dump하는 이미지로 바이너리 형식 기록

풀 백업과 부분(증분/차등) 백업

• Full Backup
  • 전체 백업
  • 데이터베이스 전체 데이터를 매일 백업
• Partial Backup
  • 풀 백업 이후 갱신된 데이터 백업
  • 차등(Differential) 백업
    • 풀 백업 이후 갱신된 데이터 백업
  • 증분(Incremental) 백업
    • 최근 백업(풀 백업이 아닐수도 있음) 이후 갱신된 데이터 백업
    • 차등 백업보다 데이터 양이 작지만 복원시 모든 증분 백업을 차례로 적용해야하므로 절차가 복잡함

롤 포워드 리커버리

• 바이너리 로그를 증분 백업으로 보존하고 이를 사용해 풀 백업 시점 이후 임의 시점까지 복원하는 것
• 현재의 데이터베이스 = 풀 백업한 데이터 + 풀 백업 후 얻은 모든 증분 백업

데이터베이스 관리 시 주의점

• 백업 파일들을 물리적으로 떨어진 장치에 각각 보관
• 장치를 지리적으로 떨어진 장소에 보관하면 재해로부터 데이터를 지킬 수 있음
• 장애는 언제든지 일어날 수 있다는 것을 전제로 대책을 세워야 함
• 백업과 복구에 걸리는 시간과 부하를 측정하여 차질 없이 운영 가능해야 함
• 24시간 가동이 필요하다면 핫 백업 필요
• 일정 시간만 운영해도 된다면 콜드 백업을 1일 1회 실행
• VLDB(Very Large Data Base) 운용 시 논리 백업에는 너무 많은 시간이 소요되므로 물리 백업을 사용

10장. 성능을 생각하자 - 성능 향상을 위해

성능을 측정하는 2가지 지표

처리 시간 (Processing Time) 또는 응답 시간 (Response Time)

• 특정 처리의 시작부터 종료까지 걸린 시간
• 사용자에 대한 영향이 가시적이다.

처리율 (Throughput)

• 단위 시간당 처리 가능한 트랜잭션. 시간 단위의 지표
  • 초당 50건의 트랜잭션 처리 = 50 TPS(Transaction Per Second)
• 시스템의 자원 용량(Resource Capacity)을 결정하는 요인
  • 필요한 자원의 양은 동시에 실행된 처리량에 비례함

정점과 한계점

• 처리율이 중요한 이유: 시스템의 자원 용량을 결정하는 요인
• 동시에 실행하는 사용자 수가 많아지면 필요한 물리 자원도 증가한다. → 시스템도 마찬가지.

병목 (Bottleneck)

• 한 가지 자원이라도 한계에 이른 시점부터 성능 저하 발생 → 병목 발생. 응답 시간 상승, 처리율 하락
• 동시에 실행되는 처리가 많아지는 순간에 대비하여 자원을 준비해두지 않으면 Peak에서 극단적인 지연 발생
  • Bottleneck Point: 최초로 한계에 이른 자원
  • 한계점 (Breaking Point): 처리율과 응답시간이 극단적으로 나빠지기 시작하는 처리량
• Sizing 또는 Capacity Planning: 정점을 상정한 자원을 확보하는 것

주기형과 돌발형

• 업무 시스템과 같은 주기형의 경우 과거 실적을 조사하여 성장률을 추가하면 어느정도 액세스 집중도 파악 가능
  • ex) 아침에 직원이 일제히 로그인
• 온라인 상거래 사이트와 같은 돌발형의 경우 사용자가 정해져있지 않고 세일이나 이벤트에 따라 다르므로 액세스 집중 발생 예측이 어려움
  • 정점이 아닌 경우와 정점인 경우에 필요한 자원량 차이가 크므로 자원 낭비 발생 가능
• 돌발형 액세스 집중에 대응하기 위해 클라우드(Cloud)를 통해 동적 자원 관리 가능
  • 가상화를 기반으로 자원량을 유연하게 변동할 수 있는 기술. 물리 자원의 임대 모델
  • Scale Up(고성능 장비 도입), Scale Out(장비 추가 도입)이 쉬우며 짧은 시간 내에 실시할 수 있다.

데이터베이스와 병목

데이터베이스는 왜 병목이 되는가

• 취급하는 데이터양이 가장 많다.
  • 최근 데이터가 폭발적으로 증가
  • 저장소 자원에서 병목이 많이 발생
  • SQL문의 응답시간 증가, 데이터베이스 처리율 감소(= 대부분의 SQL문을 처리할 수 없다.) 등의 문제 발생
• 자원 증가를 통한 해결이 어렵다.
  • 데이터베이스의 경우 동적 자원 관리가 어려움
  • 데이터베이스의 병목 지점은 CPU나 메모리가 아닌 저장소. 즉, 하드디스크. → Scale Out이 어려움
  • 주어진 자원 범위 내에서 융통성 있게 처리하기 위해 튜닝 기술 사용
    • 튜닝: 애플리케이션을 효율화하여 같은 양의 자원이라도 성능을 향상하게 하는 기술

성능을 결정하는 요인

SQL 실행 과정

Parse → Execution Plan 작성 (통계 정보 사용) → Execution Plan 평가 → 데이터 액세스

구문 오류가 없는지를 보는 파스(parse)

• Parser 프로그램이 데이터베이스가 받은 SQL문에 문법적으로 잘못된 부분이 없는지 점검
• 구문 오류 발견 시 SQL문과 오류메세지를 사용자에게 반환

실행 계획(Execution Plan)과 Optimizer

• Parse 후 SQL문에 필요한 데이터에 어떤 경로로 접근할 지 플랜을 세움
• Optimizer 프로그램이 통계정보를 참고하여 여러 가능한 플랜 중 어떤 플랜이 가장 효율적인지 계산하여 Execution Plan을 결정
  • 실행 계획을 세운다는 프로세스를 데이터베이스 자신이 수행하는 것이 관계형 데이터베이스의 큰 특징임
• 프로그램에 실행계획을 만들게 하는 이유는 데이터베이스가 더 효율적인 실행계획을 만들 수 있다고 판단하기 때문.

옵티마이저가 참조하는 통계 정보(Statistics)

• 통계정보에 포함되는 내용
  • 테이블의 (대략적인) 행수, 열수
  • 각 열의 길이와 데이터 타입
  • 테이블의 크기
  • 열에 대한 기본키나 NOT NULL 제약 정보
  • 열 값의 분산과 편향 등
• 정확한 정보가 아닌 데이터를 샘플링 추출하여 계산한 결과
  • 정확한 정보 참조를 위해 모든 데이터를 분석하면 너무 많은 시간이 소요되어 Execution Plan을 세우는 의미가 없어짐
• 통계 정보 확인
• mysql> show table status; mysql> show index from 테이블명;
• 통계정보는 대부분 자동으로 수집되며 대부분 대량의 데이터가 변경될 때 수집됨
• 필요한 경우 정기적으로 통계정보를 수집하도록 수동 설정 명령어 사용 가능
• mysql> analyze table 테이블명;

실행 계획은 어떻게 세워지는가

실행 계획을 표시한다

• SQL문 앞에 EXPLAIN 명령어를 사용
  • table은 데이터를 취득하려는 대상인 테이블
  • type은 테이블에 대한 액세스 방법
  • rows는 select 문이 액세스한 레코드의 행 수
• mysql> EXPLAIN SELECT ~ ;

Full Scan과 Range Scan

• 테이블 액세스 방법으로 Full Scan(ALL)과 Range Scan(range)이 있음
• Full Scan
  • 테이블에 포함된 레코드를 처음부터 끝까지 전부 읽어 들이는 방법
• Range Scan
  • 테이블의 일부 레코드에만 액세스하는 방법
  • SQL 문에 WHERE 절로 검색 범위를 제한하는 경우 Range Scan이 선택됨

인덱스의 중요성

• 실행 계획에서 possible_keys와 key의 PRIMARY는 Primary Key의 Index를 사용하였음을 나타냄
• 기본키 구성 열에는 반드시 인덱스가 생성됨
• Range Scan을 위해서는 Index가 필요
• 적절한 Index가 없다면 Full Scan을 하여야 함
• 인덱스 목록 확인
  • Primary Key인 id에 대한 인덱스와 Unique Key인 email에 대한 인덱스로 총 2개의 인덱스가 존재함
• mysql> show index from 테이블명;

인덱스는 SQL에서 만든다

• 풀 스캔시 대량의 테이블일 경우 엄청난 지연 문제 발생 → 이를 해결하기 위한 인덱스
• 인덱스 생성하기
• mysql> create index 인덱스명 on 테이블명(열명);

인덱스의 구조

• 인덱스는 데이터베이스의 성능 향상 수단의 가장 일반적인 방법
  • SQL문 변경 없이 성능 개선 가능
  • 테이블의 데이터에 영향을 주지 않음
  • 일정한 (때로는 극적인) 효과 기대 가능
• 인덱스를 적절히 생성하여 풀 스캔을 회피하고 일부 레코드만 스캔하여 성능을 향상시킬 수 있음
  • 인덱스를 적절히 생성해두면 인덱스 사용을 지시하지 않아도 옵티마이저가 인덱스를 사용하도록 설정함
  • 옵티마이저가 인덱스 사용보다 풀 스캔이 더 빠르다고 판단하는 경우에는 인덱스를 사용하지 않음

트리구조와 B-tree

• 트리 구조는 데이터베이스에 한정하지 않고 시스템 세계에서는 데이터를 유지하기 위해 자주 사용하는 구조 → 특정 데이터 찾는 것에 매우 효율 / 단기간 실행 가능
• B-tree 인덱스 외에도 비트맵 인덱스, REVERSE KEY 인덱스 등이 존재하지만, 대부분 B-tree 인덱스가 사용됨
• B-tree 인덱스의 구조
  • 각각의 데이터는 Node
  • 가장 상위의 노드는 Root Node, 가장 아래의 노드는 Leaf Node, 중간 노드는 Branch Node
  • 리프 노드 아래의 숫자는 데이터가 포함된 테이블의 페이지 수
• B-tree 인덱스는 반드시 데이터를 정렬된 상태로 유지
• 특정 데이터를 찾을 때 루트 노드에서 탐색을 시작하여 데이터의 대소를 비교하고 한 단계씩 아래로 진행해나가며 리프 노드에 도달

B-tree가 빠른 이유

• 이진 탐색을 수행하는 균형 트리이므로 성능이 우수하며 데이터양이 커질수록 성능 개선 효과가 큼
  • 균형 트리(Balanced-tree): 루트로부터 리프까지의 거리가 일정
    • 다만 처음 B-tree 생성 시에는 균형 트리지만, 갱신이 반복될수록 균형이 서서히 깨져가므로 정기적으로 인덱스 재구성 필요

SQL 뒤편에서 일어나는 일

• 실제 SQL 문 실행 시 백그라운드로 임시 영역에 정렬을 수행하는 경우 존재 → 성능 저하의 원인이 될 수 있음
  • GROUP BY, 집약 함수(COUNT/SUM/AVG 등), 집합 연산(UNION/INTERSECT/EXCEPT)
• 해당 키 열에 인덱스가 존재하면 옵티마이저가 정렬 과정을 건너뛰도록 효율화 작업 수행

Index 작성이 역효과가 나는 예

• 과한 인덱스 생성은 성능 개선에 효과가 없을 뿐더러 악영향을 미칠 수 있음
• 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다
  • 기존 데이터에 대한 갱신, 제거가 실행되면 자동으로 인덱스도 갱신됨
  • 인덱스 갱신으로 인한 오버헤드로 갱신 처리의 성능이 떨어지게 됨
  • SELECT 문을 고속화하는 것은 갱신 SQL을 늦추는 Trade off를 가짐
• 의도와 다른 인덱스가 사용된다
  • 한 개의 테이블에 복수의 인덱스를 작성한 경우에 발생 가능
  • 옵티마이저가 만능은 아니기 때문에 때때로 예측을 빗나가기도 함
• 인덱스를 생성하는 만큼 저장소의 용량을 소비
• 인덱스가 백업 대상에 포함되는 경우 백업 시간이 길어짐

인덱스를 만들 때 기준

• 크기가 큰 테이블만 만든다
  • 크기가 작은 테이블은 풀 스캔과 별 차이가 나지 않음
• 기본키 제약이나 유일성 제약이 있는 열에는 불필요하다
  • PK 제약과 Unique 제약이 있는 열에는 암묵적으로 인덱스가 생성됨
  • 값의 중복 체크를 위한 데이터 정렬이 발생하는데, 이를 줄이기 위해 인덱스가 자동 생성됨
• Cardinality가 높은 열에 만든다
  • Cardinality: 값의 분산도. Cardinality가 높을수록 값의 종류가 많음
  • Cardinality가 낮은 열에서는 인덱스 트리를 따라가는 조작이 증가할수록 오버헤드 증가

성능의 안티 패턴

• 아무리 옵티마이저가 플랜을 잘 세워주더라도 갱신되지 않은 낮은 정밀도의 통계 정보가 사용된다면 최적의 액세스 경로를 선택할 수 없음
• 결과 정보 갱신이 OFF로 설정되어 있다
  • 통계 정보의 갱신 방법을 OFF로 설정한 경우 테이블의 데이터가 아무리 변경되어도 통계 정보가 갱신되지 않음
• 정기 갱신을 설정하고 데이터양이 급격히 변화했다
  • 정기 갱신 이후 데이터가 급격하게 변한 경우 다음 갱신 시점까지 이전의 통계 정보가 사용됨
  • 이를 위해 통계 정보 자동 수집 설정이 가능하지만 이는 오히려 인덱스 갱신 오버헤드와 유사한 문제를 발생시키기도 함
• 통계 정보를 올바르게 수집하도록 명심하여야 함
• 예외적인 경우에는 옵티마이저 힌트, DBMS 파라미터를 이용한 실행 계획 명시적 제한, 통계 정보 처리 일시 보류 등의 옵티마이저를 신용하지 않는 대책을 채용해야. but 비상시 예외 수단이므로 우선 통계정보를 올바르게 수집할 것.