InnoDB 스토리지 엔진 분석
- InnoDB Buffer Pool의 내부구조와 동작 원리
- Redo Log와 WAL(Write-Ahead Logging) 매커니즘
- Undo Log와 MVCC(Multi-Version Concurrency Contorl)
- Write 프로세스 살펴보기
- 실습
InnoDB Buffer Pool
InnoDB의 메모리 캐시 시스템
- Buffer Pool 없는 경우
- 모든 쿼리마다 디스크 접근
- 디스크 I/O 속도 → ~10ms (10, 000, 000 나노초)
- 1000 TPS만 처리 가능
- 메모리 접근 속도 → ~100 ns (100 나노초)
- 한 번 읽은 데이터는 메모리에 캐싱
- 두 번째 부터는 메모리에서 읽기
- 10,000,000 TPS 처리 가능
Buffer Pool의 물리적 구조
InnoDB Buffer Pool (예시 : 8GB 설정)
┌────────────────────────────────────────────────┐
│ │
│ ┌────────────────────────────────────────┐ │
│ │ Data Pages (실제 테이블 데이터) │70% │
│ │ - users 테이블의 행들 │ │
│ │ - orders 테이블의 행들 │ │
│ └────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────┐ │
│ │ Index Pages (B+Tree 인덱스) │ 20%│
│ │ - Primary Key 인덱스 │ │
│ │ - Secondary 인덱스들 │ │
│ └────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────┐ │
│ │ Change Buffer (Insert/Update 버퍼) │ 5%│
│ └────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────┐ │
│ │ Adaptive Hash Index (자동 생성 해시) │ 3%│
│ └────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────┐ │
│ │ Lock Information (행 잠금 정보) │ 2%│
│ └────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────┘Page - Buffer Pool의 기본 단위
- InnoDB는 Page 모든 작업을 수행
- Page의 크기는 16KB - 고정
- 디스크는 블록 단위로 읽음 (보통 4KB, 8KB)
- 16KB ← 한 번의 I/O로 여러 행을 읽을 수 있는 최적 크기
- 너무 크게 되면 메모리 낭비, 너무 작으면 I/O 횟수가 증가하게 됨
- 1개 Page에 들어가는 행 수
- 행 크기가 100 bytes → 약 160개 행
- 행 크기가 1KB → 약 16개 행
- 같은 테이블의 페이지들은 링크드 리스트로 연결(Doubly Linked List)
- Full Table Scan 시 이 순서대로 읽음
Page 내부 구조
-
FIL Header ( 38 bytes)
- 체크섬 (데이터 무결성 검증)
- 페이지 타입 (데이터, 인덱스.. 등)
- 이전/ 다음 페이지 포인터 (링크)
- LSN (Log Sequence Number)
-
Page Header (56bytes)
- 레코드 개수
- Free Space 포인터
- 삭제된 레코드 리스트
-
Infimum Record (논리적 최솟값)
- 모든 레코드보다 작은 가상 레코드
-
User Records (실제 데이터)
- Record 1 : id = 1, name = ‘A’
- Record 2 : id = 2, name = ‘B’
- Record 3 : id = 3, name = ‘C’
-
Supremum Record(논리적 최대값)
- 모든 레코드보다 큰 가상 레코드
-
Free Space(빈공간)
새 레코드 삽입 가능영역
-
Page Dricetory(슬롯)
- 빠른 검색을 위한 포인터 배열
- 4~8개 레코드마다 1개 슬롯
-
FIL Trailer (8bytes)
- 체크섬 (중복 검증)
LRU List - 페이지 교체 알고리즘
- 전통적인 LRU (Least Recently Used)
- 가장 오래 사용하지 않은 페이지를 제거
- 간단하지만 치명적 문제 발생
- Full Table Scan 시 자주 쓰는 데이터가 밀려남
- 한번만 읽고 버릴 데이터가 캐시를 오염시킴
- Buffer Pool Pollution
- Buffer Pool에 자주 쓰는 100개 페이지 캐싱
- Full Table Scan 쿼리 실행(백만개 페이지 스캔)
- 자주 쓰는 100개가 모두 밀려남
- 이후 쿼리들이 캐시 미스 → 성능 급락)
- InnoDB의 개선된 LRU(Midpoint Insertion Strategy) BufferPool 영역을 두개로 분리
- 자주 접근하는 영역의 New SubList
- 첫 접근하는 영역의 Old SubList
- 동작방식
- 새 페이지는 Old Sublist의 Head에 삽입
- 1초 이내 재접근 → 무시 (Full Scan 필터링)
- 1초후 재접근 → New Sublist로 승격
- 메모리 부족 시 Old Sublist의 Tail부터 제거
실습
Buffer Pool의 페이지 타입별 분포
SELECT
page_type,
COUNT(*) as page_count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM
information_schema.INNODB_BUFFER_PAGE
GROUP BY
page_type
ORDER BY
page_count DESC;+-------------------+------------+------------+
| page_type | page_count | percentage |
+-------------------+------------+------------+
| UNKNOWN | 6977 | 85.18 |
| UNDO_LOG | 482 | 5.88 |
| SYSTEM | 387 | 4.72 |
| INDEX | 274 | 3.35 | <- B+Tree 인덱스
| FILE_SPACE_HEADER | 17 | 0.21 | <- 일반 데이터
| INODE | 15 | 0.18 |
| LOB_DATA | 15 | 0.18 |
| LOB_FIRST | 14 | 0.17 |
| IBUF_BITMAP | 3 | 0.04 |
| RSEG_ARRAY | 2 | 0.02 |
| SDI_INDEX | 2 | 0.02 |
| TRX_SYSTEM | 1 | 0.01 |
| IBUF_INDEX | 1 | 0.01 |
| LOB_INDEX | 1 | 0.01 |
+-------------------+------------+------------+
14 rows in set (0.058 sec)Buffer Pool Hit Ratio (캐시 효율)
mysql> SELECT
-> ROUND(
-> (1 - (r.Innodb_buffer_pool_reads / req.Innodb_buffer_pool_read_requests)) * 100,
-> 2
-> ) AS hit_ratio_percent
-> FROM
-> (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
-> FROM performance_schema.global_status
-> WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
-> (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
-> FROM performance_schema.global_status
-> WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') req;
+-------------------+
| hit_ratio_percent |
+-------------------+
| 98.64 |
+-------------------+
1 row in set (0.00 sec)
목표 Hit Ratio: 99% 이상
- 99.5% → 매우 우수
- 95-99% → 양호
- 90% 미만 → Buffer Pool 크기 증설 필요Buffer Pool 튜닝
-- 현재 Buffer Pool 크기 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 일반적 권장사항:
-- 전용 DB 서버: 물리 메모리의 70-80%
-- 공유 서버: 물리 메모리의 50-60%
-- 예: 16GB 서버 → 12GB Buffer Pool
-- my.cnf에 설정:
/*
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8 ← 병렬 처리 (8GB 이상 시)
innodb_buffer_pool_chunk_size = 128M
*/
-- 동적으로 크기 변경
SET GLOBAL innodb_buffer_pool_size = 12884901888; -- 12GBRedo Log - 크래시 복구의 핵
WAL(Write - Ahead Loggin)
데이터를 디스크에 쓰기전 로그를 먼저 작성
- 문제 상황
- Buffer Pool에서 잔액 수정(메모리)
- 아직 디스크에 쓰지 않음
- 정전 및 예기치 못한 사고 발생
- 메모리 데이터 손실 → 돈이 사라지게 됨
- WAL 해결책
- Buffer Pool 수정 전 “이런 변경을 할 것”을 Redo Log에 기록
- Redo Log는 순차쓰기 → Random I/O 를 Sequential I/O로 변환
- 크래시 후 재시작시 Redo Log를 재생하여 복구
Redo Log의 구조
Redo Log는 고정 크기의 순환 버퍼 구조
- 논리적 구조
┌─────────────────────────────────────────┐ │ Redo Log Ring │ │ │ │ Write Pos ─┐ │ │ ▼ │ │ ┌───────────────────────────────────┐ │ │ │ [Record 1] [Record 2] [Record 3] │ │ │ │ │ │ │ │ ... [Record N] ... │ │ │ │ ▲ │ │ │ │ └── Checkpoint Pos │ └───────────────────────────────────┘ │ │ │ │ Write Pos가 Checkpoint Pos를 따라잡으면? | │ → Checkpoint 강제 실행 (Flush) │ └─────────────────────────────────────────┘- Write Pos: 새 로그 기록 위치
- Checkpoint Pos: 디스크에 반영 완료된 위치
Redo Log Record 형식
┌─────────────────────────────────────────┐
│ [Type] [Space ID] [Page Number] │ ← 어떤 페이지인지
│ [Offset] [Length] [Data] │ ← 어떤 변경인지
└─────────────────────────────────────────┘
UPDATE users SET balance = 1000 WHERE id = 42Redo Log Record
- Type - MLOG_WRITING_STRING (데이터 수정)
- Space ID: 23 (테이블 공간 ID)
- Page NumberL 157 (페이지 번호)
- Offset: 894 (페이지 내 오프셋)
- Length: 4 (4바이트)
- Data: 0x00003E8 (1000을 16진수로)
Undo Log와 MVCC
Undo Log의 역할
- 트랜잭션 롤백 실행 취소를 위한 이전 버전 저장
- MVCC(Multi-Version Concurrency Control) 동시성 제어를 위한 스냅샷 제공
MVCC 동작원리
같은 데이터의 여러 버전을 동시에 유지
초기 상태:
┌────────────────────────────┐
│ users 테이블 │
│ id=1, balance=1000 │
│ (Transaction ID: 100) │
└────────────────────────────┘
트랜잭션 A (ID: 101) 시작:
UPDATE users SET balance = 1500 WHERE id = 1;
데이터 변화:
┌────────────────────────────┐
│ users 테이블 (최신 버전) │
│ id=1, balance=1500 │ ← 트랜잭션 101이 수정
│ (Transaction ID: 101) │
│ [Roll Pointer] ───────┐ │
└────────────────────────│────┘
│
▼
┌────────────────────────────┐
│ Undo Log (이전 버전) │
│ id=1, balance=1000 │ ← 원래 값
│ (Transaction ID: 100) │
└────────────────────────────┘
트랜잭션 B (ID: 102) 시작:
SELECT balance FROM users WHERE id = 1;-
트랜잭션 B가 보는 것
- 최신 버전 확인(balance = 1500, TxID = 101)
- TxID 101이 아직 커밋 안 됨 확인
- Roll Pointer 따라가기
- Undo Log에서 balance = 1000 읽기
- 결과: 1000반환
-
트랜잭션 A가 COMMIT 한 경우
- 트랜잭션 B는 여전히 1000을 봄 (REPEATABLE READ)
- 새로운 트랜잭션은 1500을 봄
Undo Log 레코드 체인
여러 트랜잭션이 동일 행을 수정하면 버전 체인 형성
최신 ──────────────────> 과거
┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐
│ V4 │───▶│ V3 │───▶│ V2 │───▶│ V1 │
│(1500)│ │(1200)│ │(1000)│ │(800) │
│TxID │ │TxID │ │TxID │ │TxID │
│ 104 │ │ 103 │ │ 102 │ │ 101 │
└──────┘ └──────┘ └──────┘ └──────┘
▲
│
최신 버전 (Buffer Pool & 디스크)→ 각 트랜잭션은 자신의 Read View에 따라 적절한 버전 선택
실습
MVCC 실습
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance INT,
version INT DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1, 1000, 0);
-- ===== 세션 1 (터미널 1) =====
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 1. 초기 값 확인
SELECT * FROM accounts WHERE id = 1;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 1000 | 0 |
+----+---------+---------+
1 row in set (0.000 sec)
-- 2. 10초 대기 (세션 2가 수정하도록)
SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.003 sec)
-- 3. 다시 조회 (세션 2가 수정했지만 격리됨)
SELECT * FROM accounts WHERE id = 1;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 1000 | 0 |
+----+---------+---------+
1 row in set (0.000 sec)
-- 4. 트랜잭션 커밋
COMMIT;
-- 5. 커밋 후 조회
SELECT * FROM accounts WHERE id = 1;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 1500 | 1 |
+----+---------+---------+
1 row in set (0.000 sec)
-- ===== 세션 2 (터미널 2) =====
START TRANSACTION;
-- 세션 1이 대기 중일 때 수정
UPDATE accounts SET balance = 1500, version = version + 1
WHERE id = 1;
-- 커밋
COMMIT;- 세션1이 커밋전에 세션 2의 Update 결과가 반영되지 않는 것을 볼 수 있음 → MVCC
Undo Log 모니터링
SELECT
TABLESPACE_NAME,
FILE_NAME,
ROUND(TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024, 2) as size_mb,
ROUND(FREE_EXTENTS * EXTENT_SIZE / 1024 / 1024, 2) as free_mb
FROM
information_schema.FILES
WHERE
TABLESPACE_NAME LIKE 'innodb_undo%';
+-----------------+------------+---------+---------+
| TABLESPACE_NAME | FILE_NAME | size_mb | free_mb |
+-----------------+------------+---------+---------+
| innodb_undo_001 | ./undo_001 | 16.00 | 2.00 |
| innodb_undo_002 | ./undo_002 | 16.00 | 2.00 |
+-----------------+------------+---------+---------+
2 rows in set (0.007 sec)Undo Log History List Length
SHOW ENGINE INNODB STATUS\G
------------
TRANSACTIONS
------------
Trx id counter 118953
Purge done for trx's n:o < 118953 undo n:o < 0 state: running but idle
**History list length 0**- History List Length 0
- 정리되지 않은 Undo 레코드 수
- 값이 크면 → 오래된 트랜잭션이 Undo를 점유
- 권장: 1000 미만
- 10, 000이상 → 성능 저하 위험
전체 Write 프로세스 - UPDATE 쿼리
Step1 - SQL 파싱 & 최적화
- Parser: SQL 문법 검증
UPDATE users SET balance = 1500 WHERE id = 1;→ Parse Tree 생성
-
Optimizer 실행 계획 수립
- id 칼럼에 PRIMARY KEY 존재 확인
- Index Scan 결정
- Cost: 1 (단일 행 접근)
-
Executor → InnoDB에 명령 전달
Step2 - 트랜잭션 시작
- InnoDB Storage Engine 트랜잭션 시작 → 트랜잭션 ID 할당 → 이 ID는 MVCC에서 버전 관리에 사용
- 트랜잭션 격리 수준 확인
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ (기본 값) ← 대부분 이것
- SERIALIZABLE
Step3 - Buffer Pool에서 페이지 검색
PRIMARY KEY (id = 1)사용하여 검색 Hash Table 체크 (빠른 검색) Key: (Space ID, Page Number)
- Case A: Buffer Pool Hit Page 157이 메모리에 있음 → 즉시 읽기 → LRU List 업데이트 (Old → Young , Young → Head)
- Case B: Buffer Pool Miss Page 157이 메모리에 없음 → 디스크에서 읽기 (느림) Buffer Pool에 적재 Old Sublist의 Midpoint에 삽입 Free Buffer 사용 없으면 LRU Tail 페이지 제거
Step4 - Undo Log 생성 (롤백 대비)
-
목적
- 트랜잭션 롤백 시 복구
- MVCC를 위한 이전 버전 유지
-
Undo Tablespace에 기록
┌─────────────────────────────────────────┐ │ Undo Record │ ├─────────────────────────────────────────┤ │ Type: UPDATE_EXISTING │ │ Transaction ID: 12345 │ │ Table ID: 23 │ │ Index: PRIMARY │ │ Key: id=1 │ │ │ │ Old Values (복구용): │ │ balance = 1000 │ │ │ │ Previous Version Pointer: NULL │ └─────────────────────────────────────────┘→ Undo Log의 주소를 Roll Pointer
Step 5: Redo Log 기록 (WAL - Write-Ahead Logging)
- Redo Log를 먼저쓰는 이유 (데이터 페이지보다 먼저 기록)
- Sequential Write (순차 쓰기) → 빠름
- 작은 크기 (변경 부분만)
- 크래시 복구 보장
Step 6: Buffer Pool에서 데이터 수정
┌─────────────────────────────────────────────────┐
│ Buffer Pool의 Page 157 수정 │
├─────────────────────────────────────────────────┤
│ │
│ 수정 전: │
│ ┌─────────────────────────────────────────┐ │
│ │ id=1, balance=1000 │ │
│ │ TxID=12340 │ │
│ │ Roll Pointer=NULL │ │
│ └─────────────────────────────────────────┘ │
│ │
│ 수정 후: │
│ ┌─────────────────────────────────────────┐ │
│ │ id=1, balance=1500 ← 값 변경! │ │
│ │ TxID=12345 ← 현재 트랜잭션 ID │ │
│ │ Roll Pointer=0x7F3A2B1C │ │
│ │ └─ Undo Log 주소 │ │
│ └─────────────────────────────────────────┘ │
│ │
│ 이 페이지는 이제 "Dirty Page" │
│ (메모리와 디스크가 불일치) │
│ │
│ Modified DB Pages 카운터 증가 │
│ │
└─────────────────────────────────────────────────┘Step 7 : 트랜잭션 커밋
- COMMIT 실행
-
Redo Log Buffer → Redo Log File 플러시 (디스크에 물리적 기록)
Log Buffer -(fsync 호출) > ib_logfile0 (디스크)
-
트랜잭션 상태 변경
ACTIVE → COMMITED
-
Lock 해제
id = 1 행의 잠금 해제
-
- innodb_flush_log_at_trx_commit 설정
- 0: 1초마다 플러시 (빠름, 위험)
- 1 : 매 커밋마다 플러시 (안전, 느림)
- 2: 매 커밋마다 OS 캐시에만 (중간)
Step 8: 백그라운드 Flush(비동기)
- Trigger 1: Checkpoint Redo Log 가득 차면 → Checkpoint 실행 → LSN 이전의 Dirty Page 모두 Flush
- Trigger 2: Buffer Pool 부족 Free Buffer가 부족하면 → LRU Tail의 Dirty Page Flush → 공간확보
- Trigger 3: 주기적 Flush Page Cleaner Thread가 1초마다 → 적당한 양의 Dirty Page Flush → Checkpoint Age 관리
- Flush 프로세스
- Dirty Page 목록 스캔
- 연속된 페이지 그룹화 (I/O 최적화)
- 디스크에 일괄 쓰기
- Flush List에서 제거
