4.2 아키텍처 - InnoDB 스토리지 엔진 아키텍처 (2)

4.2.9 ~ 4.2.13

4.2.9 언두로그

언두 로그(Undo Log): InnoDB 스토리지 엔진이 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되지 이전 버전의 데이터를 별도로 백업하는데, 이 백업된 데이터를 의미함

• 트랜잭션 보장: 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데 이때 언두 로그에 백업해 둔 이전 버전 데이터로 복구함
• 격리 수준 보장: 특정 커넥션에서 데이터를 변경하는 도중 다른 커넥션에서 데이터를 조회할 때, 트랜잭션 격리 수준에 맞게 변경 중인 데이터 대신 언두 로그 백업 데이터를 읽어 반환하기도 함

그렇다면 이 언두 로그의 문제점과, 문제점을 해결하기 위해 InooDB는 무슨 기능을 제공할까?

4.2.9.1 언두 로그 모니터링

먼저 언두 로그의 데이터가 *어떻게 저장되고 어떤 목적8으로 사용되는지 알아보자

언두 영역: DML로 데이터를 변경했을 때 변경 전 데이터를 보관하는 공간
예시: 회원 이름을 "박계수"에서 "홍길동"으로 변경한 상황
1. UPDATE member SET name ='홍길동' WHERE member_id=1; 문장 실행
2. 실제 데이터 파일(데이터/인덱스 버퍼)는 '홍길동'으로 변경(커밋 전에도)
3. 언두 영역에 '박계수'라는 값이 백업
4. 커밋 -> 현재 상태 유지, 롤백 -> 언두 영역 백업 데이터를 복구
언두 로그 데이터 용도
1. 트랜잭션 롤백 대비용
2. 트랜잭션의 격리 수준 유지 + 높은 동시성 제공
- 트랜잭션의 격리 수준: 동시에 여러 트랜잭션이 데이터 변경 or 조회할 때 한 트랜잭션 내용이 다른 트랜잭션에 어떻게 보일지 결정하는 기분

이 언두 로그의 문제점? MySQL 5.5 이전에는 한 번 증가한 언두 로그 공간이 줄어들지 않았음

문제점 1: 계속 쌓이면 용량 폭발
문제점 2: 성능 저하 발생

• 언두 로그가 많이 쌓이면, 과거 데이터를 찾기 위해 스캔해야 할 양이 많아져 쿼리 성능 저하

• 언두 로그의 사용 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하는 방법 외에 줄일 방법 X

• 언두 로그가 늘어나면 디스크 사용량뿐만 아니라 백업할 때도 그만큼 더 복사해야 하는 문제

MySQL 8.0 이후부터는 언두 로그 문제점 해결

• 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간 줄이느 게 가능해짐
• MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여 주기도 함

그래도 서비스 중인 MySQL 서버에서 활성 상태 트랜잭션이 장시간 유지되는 건 성능상 좋지 않음
=> MySQL 서버의 언두 로그 증가량은 항상 모니터링하는 것이 좋음

언두 로그 건수 확인 방법

-- 방법 1 (모든 버전)
SHOW ENGINE INNODB STATUS;
-- History list length 값 확인

-- 방법 2 (MySQL 8.0+)
SELECT count 
FROM information_schema.innodb_metrics
WHERE NAME='trx_rseg_history_len';

주의
MySQL 서버에서 INSERT 문장 언두 로그와 UPDATE(DELETE 포함) 문장 언두 로그는 별도로 관리함
INSERT => 데이터 복구(롤백 포함)에만 사용
UPDATE, DELETE => MVCC, 데이터 복구(롤백 포함)에 모두 사용

4.2.9.2 언두 테이블 스페이스 관리

언두 테이블스페이스(Undo Tablespace): 언두 로그가 저장되는 공간
버전별 언두 테이블스페이스 변화

• MySQL 5.6 이전: 언두 로그가 모두 시스템 테이블스페이스에 저장
  -> 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되어 확장에 한계 존재 (왜 확장에 문제가 있는 건지 모르겠음)
• MySQL 5.6: innodb_undo_tablespaces 시스템 변수 도입되어, 2보다 큰 값을 설정하면 시스템 테이블스페이스가 아닌 별도의 언두 로그 파일을 사용
• MySQL 5.6 이후: innodb_undo_tablespaces 시스템 변수를 0으로 설정하면 언두 로그가 시스템 테이블스페이스에 저장됨
• MySQL 8.0: 언두 로그가 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되게 개선

언두 테이블스페이스 구조

언두 테이블스페이스

• 가장 큰 단위 (건물 전체)
• 실제 파일로 존재(ex:undo_001_ibu)

-- 언두 테이블스페이스 목록 확인
SELECT TABLESPACE_NAME, FILE_NAME 
FROM INFORMATION_SCHEMA.FILES 
WHERE FILE_TYPE LIKE 'UNDO LOG';

-- 결과 예시:
-- innodb_undo_001 | ./undo_001
-- innodb_undo_002 | ./undo_002

**기본적으로 2개 생성됨!** (MySQL 8.0 기준)

롤백 세그먼트

• 언두 테이블스페이스를 나눈 단위 (건물의 층)
• 하나의 언두 테이블스페이스는 최대 128개의 롤백 세그먼트를 가짐

만약 하나만 있다면?
트랜잭션 A: "언두 로그 쓸게요!" 🔒
트랜잭션 B: "저도요!" (대기...)
트랜잭션 C: "저도요!" (대기...)
→ 병목 현상! 😱

128개로 나누면?
트랜잭션 A: 세그먼트 1 사용 ✅
트랜잭션 B: 세그먼트 2 사용 ✅
트랜잭션 C: 세그먼트 3 사용 ✅
→ 동시성 향상! 🚀

-- 설정 확인
SHOW VARIABLES LIKE 'innodb_rollback_segments';
-- 기본값: 128

언두 슬롯

• 롤백 세그먼트를 다시 나눈 단위 (층의 각 방)
• 하나의 롤백 세크먼트는 1024개 정도의 슬롯을 가짐 (16KB 페이지 기준)
  예시
  • 16KB 페이지: 16384 / 16 = 1024개 슬롯
    - 32KB 페이지: 32768 / 16 = 2048개 슬롯
    트랜잭션이 슬롯 사용하는 방법
  • 트랜잭션이 시작되면
  1. 빈 롤백 세그먼트 하나 선택
  2. 그 안의 빈 슬롯 하나 할당 받음
  3. 슬롯에 언두 로그 기록 시작
  • 트랜잭션 하나당:
  1. 일반적으로 2개 슬롯 사용(INSERT용 1개 + UPDATE/DELETE용 1개)
  2. 임시 테이블 쓰면 최대 4개까지

언두 로그 레코드

• 실제 데이터 (방 안의 서류)

• 변경 전 값 저장

• MySQL 8.0부터 CREATE UNDO TABLESPACE나 DROP TABLESPACE 같은 명령으로 새로운 언두 테이블스페이스를 동적으로 추가 or 삭제가 가능해짐

Undo tablespcae truncate

• 언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것
  언두 테이블스페이스의 불필요한 공간 잘라내는 방법
  1.자동 모드: 언두 퍼지(Undo Purge) 작업 수행
  -> InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 깨어나 언두 로그 공간의 불필요해진 언두 로그를 주기적으로 삭제하는 행위
• innodb_undo_log_truncate 시스템 변수 ON으로 설정
  2.수동 모드: 자동 모드가 실행되지 않거나, 공간 반납이 부진한 경우 언두 테이블스페이스를 비활성화해 언두 테이블스페이스가 사용되지 않도록 설정 => 퍼지 스레드가 비활성 상태의 언두 테이블스페이스를 찾아 불필요한 공간을 잘라내고 운영체제로 해당 공간을 반납한 이후 다시 언두 테이블 스페이스를 활성화시킴(언두 테이블 스페이스가 최소 3개 이상은 되어야 작동함)

4.2.10 체인지 버퍼

Change Buffer는 왜 필요할까?

인덱스 = B+ Tree 구조
B+ Tree는 디스크 페이지 단위로 저장되는데, 특히 보조 인덱스(secondary index)는 페이지 접근이 매우 랜덤 I/O가 됨

       [Page A]
       /     \
   [Page B]  [Page C]
   /    \       ...
[Leaf1][Leaf2][Leaf3]...

• 트리의 노드 1개 = 페이지 1개
• 인덱스 페이지 안에 키 + 해당 레코드의 PK 값(논리적 주소)들이 묶여 있음
• 리프 페이지에는 실제 row 데이터 들어있음 (클러스터링 인덱스 기준)
• 페이지 단위로 저장 된다 = B+ Tree 노드 하나가 16kb 블록

디스트에서 데이터를 읽는 방식 2가지

• 순차 I/O: 옆에 붙어 있는 블록들을 쭉 읽는 방식
  - 배열 인덱스라면 -> 이 경우 검색은 빠르지만 쓰기가 너무 느려서 DB 인덱스 사용 불가
  • 해시 인덱스라면 -> 정렬이 안 되고 범위 스캔 안 되고 랜덤 I/O 최악
• 랜덤 I/O: 멀리 떨어져 있는 블록을 여기저기 뛰어다니며 읽는 방식

인덱스 탐색은 왜 랜덤 I/O일까?

• 인덱스는 트리의 각 노드가 디스크 파이지로 따로 저장됨
• 탐색하면서
  - 루트 페이지 읽고
  - 자식 페이지로 이동하고
  - 또 이동하고
  이런 식으로 매 단계마다 다른 페이지를 읽어야 함 => 랜덤 I/O
  그러면 왜 B+ Tree는 왜 인덱스로 빠를까?

1. B+ Tree는 이 랜덤 I/O 횟수 자체를 줄임
2. 상위 노드는 버퍼 풀에 거의 상주 => 실제 랜덤 I/O는 리프 페이지 몇 개만 디스크에서 읽어옴

문제
INSERT/UPDATE 과정에서 보조 인덱스 페이지가 디스크에 있어 메모리에 안 올라온 상태면, 그 한 페이지를 읽기 위해 랜럼 디스크 I/O 발생
=> 이 한 번의 랜덤 I/O가 쓰기 성능을 폭락시킴
그래서 InnoDB가
지금 당장 그 인덱스 페이지를 디스크에 읽어올 필요 X
=> 어떻게든 기록만 해 놓고, 나중에 그 페이지가 버퍼 풀에 올라올 때 밀어넣자 O

=> 이걸 해결하는 구조가 바로 체인지 버퍼(Change Buffer)

Change Buffer

• 보조 인덱스 페이지를 당장 읽어오지 않고, 변경 요청만 임시로 모아두는 버퍼
• 나중에 그 페이지가 메모리에 올라올 때 병합(merge)를 처리
• 디스크 읽기를 나중으로 미루는 기능 = 랜덤 IO 감소 = 쓰기 성능 향상

Change Buffer가 적용되는 연산

연산	의미
INSERT	보조 인덱스 키 추가
DELETE	보조 인덱스 키 삭제
UPDATE	인덱스 키 변경 시 INSERT+DELETE처럼 움직임

-> 보조 인덱스에 영향을 주는 쓰기 연산이 체인지 버퍼 대상

• 유니크 인덱스는 사용하지 않음
  - 새로운 값이 이미 존재하는지 즉시 검증해야 하는데 "디스크 읽기"가 무조건 필요함

4.2.11 리두 로그 및 버퍼

리두 로그

• 트랜잭션의 4가지 요소(ACID) 중 D(Durable, 영속성)와 가장 밀접
• HW, SW 문제로 MySQL 서버가 비정상 종료되었을 때, 데이터파일에 기록되지 못한 데이터를 잃지 않게 해 주는 안전 장치

리두 로그가 필요한 이유

데이터베이스의 딜레마

• 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료구조
• 데이터 파일 쓰기는 디스크의 랜덤 액세스 필요 -> 비용 큼
• 성능 저하를 막기 위해 쓰기 비용이 낮은 자료구조 필요 -> 리두 로그
  리두 로그 역할

1. 변경 내용을 리두 로그에 먼저 기록 (순차 쓰기, 빠름)
2. 비정상 종료 시 리두 로그 내용으로 데이터 파일 복구

리두 로그와 트랜잭션

1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터

• 리두 로그에 저장된 데이터를 데이터 파일로 복사
  2. 롤백됐지만 데이터 파일에 이미 기록된 데이터
• 리두 로그로 해결 X
• 변경 전 데이터를 가진 언두 로그 내용을 가져와 데이터 파일에 복사
• 단, 변경이 커밋되었는지 롤백됐는지 확인하기 위해서라도 리두 로그 필요

리두 로그와 버퍼링

리두 로그 버퍼링

• 변경 작업이 많은 DBMS 서버는 리두 로그 기록 작업이 큰 부하
• 이를 보완하기 위해 ACID 속성을 보장하는 수준에서 버퍼링
• 이 버퍼링에 사용되는 공간이 로그 버퍼
  로그 버퍼 크기 설정
• 기본값: 16MB 수준
• BLOB, TEXT처럼 큰 데이터를 자주 변경하는 경우 더 크게 설정 권장
  innodb_flush_log_at_trx_commit 설정
• 트랜잭션 커밋 시 리두 로그를 디스크에 동기화할지 결정하는 중요 파라미터

4.2.11.1 리두 로그 아카이빙

도입 배경(MySQL 8.0)

• MySQL 엔터프라이즈 백업, Xtrabackup 같은 백업 툴은 데이터 파일 복사 중 새로 추가되는 리두 로그 엔트리를 추적하며 복사
• 데이터 변경이 너무 많을 경우 => 리두 로그가 덮어쓰여져 백업 실패 가능
• 리두 로그 아카이빙으로 덮어쓰여져도 백업 가능하게 개선
  사용 방법

-- 1. 아카이빙 디렉터리 설정
SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive';

-- 2. 아카이빙 시작
DO innodb_redo_log_archive_start('backup', '20200722');

-- 3. 데이터 변경 작업 수행

-- 4. 아카이빙 종료
DO innodb_redo_log_archive_stop();

주의사항

• 아카이빙을 시작한 세션이 연결 끊어지면 자동을 아카이빙 중단되고 파일 삭제됨
• 작업 완료 후 반드시 'innodb_redo_log_archive_stop()' 호출 필요

4.2.11.2 리두 로그 활성화 및 비활성화

MySQL 8.0부터

• 수동으로 리두 로그 비활성화 가능
• 데이터를 복구 or 대용량 데이터 적재 시 시간 단축 가능
  사용 방법

-- 리두 로그 비활성화
ALTER INSTANCE DISABLE INNODB REDO_LOG;

-- 대량 데이터 적재
LOAD DATA ...

-- 리두 로그 활성화
ALTER INSTANCE ENABLE INNODB REDO_LOG;

-- 상태 확인
SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';

주의사항

• 리두 로그 비활성화 중 MySQL 서버 비정상 종료 시 복구 불가능
• 데이터가 일관되지 않은 상태가 될 수 있음
• 서비스 중에는 절대 비활성화하지 말 것
• 비활성화 후 재시작 실패 시 innodb_force_recovery=6 설정 후 시작

4.2.12 어댑티브 해시 인덱스

어댑티브 해시 인덱스(Adaptive Hash Index)

• 사용자가 수동으로 생성하는 인덱스가 아님
• InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
• innodb_adaptive_hash_index 시스템 변수를 이용해서 활성화/비활성화 가능

B+ Tree 검색 시간 줄이는 원리

B+ Tree 인덱스의 검색 과정

• 일반적인 인덱스는 테이블에 사용자가 생성해 놓은 B+ Tree 인덱스 의미
• B+ Tree 인덱스에서 특정 값을 찾는 과정은 매우 빠르게 처리됨
  그러나 상대적인 개념
• 빠르냐 느리냐의 기준은 상대적
• 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 B+ Tree 인덱스에서 값을 찾는 과정이 느려질 수도 빨라질 수도 있음
  B+ Tree 검색 과정

B-Tree의 루트 노드 → 브랜치 노드 → 리프 노드

• 적당한 사양의 컴퓨터에서 이런 작업을 동시에 몇 개 실행한다고 성능 저하가 보이지는 않음
• 하지만 이런 작업을 동시에 몇천 개의 스레드로 실행하면 컴퓨터 CPU는 엄청난 프로세스 스케줄링을 하게 되고 자연히 쿼리 성능은 떨어짐

어댑티브 해시 인덱스의 동작

• 어댑티브 해시 인덱스는 이런 B+ Tree 검색 시간을 줄여 주기 위해 도입된 기능
• InnoDB 스토리지 엔진이 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인데그를 만들고, 필요할 때마다 어댑티브 해시 인댁스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있음
  효과
• B+ Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어짐
• 그만큼 CPU는 적은 일을 하지만 쿼리 성능은 빨라짐
• 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 됨

해시 인덱스 구조

• "인덱스의 키 값"과 "해당 인덱스 키 값이 저장된 데이터 페이지 주소"의 쌍으로 관리
  인덱스 키 값
  구성

"B-Tree 인덱스의 고유번호(Id)" + "B-Tree 인덱스의 실제 키 값"

조합하는 이유

• 어댑티브 해시 인덱스의 키 값에 B+ Tree 인덱스의 고유 번호가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재(물론 파티션 되는 기능이 있긴 함)하기 때문임 (이게 뭔 말임?)
• 즉 모든 B+ Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문
  데이터 페이지 주소
• 실제 키 값이 저장된 데이터 페이지의 메모리(InnoDB 버퍼 풀) 주소를 가짐
  버퍼 풀과의 관계
  이는 InnoDB 버퍼 풀에 로딩된 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보가 사라짐

성능 향상에 도움이 되지 않는 경우

비효율적인 경우

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우 (조인이나 LIKE패턴 검색) (왜?)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  효율적인 경우
• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우 (데이터 읽기가 많지 않은 경우
• 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
• 쿼리가 데이터 중 일부 데이터에만 집중되는 경우

주의사항
1. 디스크 I/O가 많으면 무용지물
-> 메모리(버퍼 풀) 내에서 데이터 페이지 접근을 빠르게 만드는 기능
2. 비용 문제
- 메모리 사용
- 검색 오버헤드: 있든 없든 일단 검색해 봐야 함
- 테이블 삭제/변경 시 성능 저하

모니터링: 히트율 + CPU + 메모리 종합 판단

4.2.13 inooDB와 MyISAM, MEMORY 스토리지 엔진 비교

스토리지 엔진	특징	장점	단점	용도
InnoDB	트랜잭션, MVCC, 행 잠금	안정성·동시성·내구성 우수	구조 복잡	OLTP, 실서비스
MyISAM	테이블 잠금, 트랜잭션 없음	읽기 빠름, 단순	내구성 부족	읽기 중심, 분석용
MEMORY	모든 데이터 메모리 저장	매우 빠름	휘발성	세션, 임시 테이블

1) InnoDB - 현재의 MySQL 표준 엔진

주요 특징

• 트랜잭션(ACID) 지원
• MVCC
• 레코드(행) 단위 잠금
• 리두 로그/언두 로그 기반 복구 가능
• 클러스터링 인덱스 구조

용도

• OLTP 시스템 전부
• 회원/주문/결제 같은 변경이 많은 서비스

2) MyISAM - 예전 기본 엔진

주요 특징

• 트랜잭션 없음
• 테이블 단위 잠금
• 충돌이 잦으면 전체 테이블이 막힘
• 복구 기능 없음 (crash–unsafe)
• 단순 구조 → 읽기 위주 작업에 빠름

용도

• 읽기 비중이 매우 높거나
• 잠금 경합이 낮은 환경

최근 상황

• MySQL 8.0에서는 거의 모든 기능이 InnoDB로 통합

3) MEMORY - 메모리 기반 엔진

주요 특징

• 모든 데이터를 메모리에 저장
• 매우 빠르지만
• 서버 재시작 시 데이터 사라짐
• 테이블 잠금 기반
• 가변 길이 타입 지원 부족

용도

• 임시 테이블
• 세션 저장 등 비영속성 작업

최근 상황

MySQL 8.0부터 TempTable 스토리지 엔진 도입:

• MEMORY 스토리지 엔진 대체
• TempTable 스토리지 엔진이 내부 임시 테이블 용도로 사용됨