dev.syw

InnoDB의 페이지·버퍼풀·로그 구조로 MySQL이 데이터를 다루는 원리를 파헤친다.

InnoDB 스토리지 엔진 내부 구조

MySQL을 입문 과정에서 배울 때는 "인덱스를 만들면 조회가 빨라진다", "트랜잭션으로 데이터 무결성을 보장한다"는 사실을 학습합니다. 그런데 실무에서 성능 문제나 장애를 마주치면, 단순히 그 사실을 아는 것만으로는 원인을 파악하거나 해결책을 찾기 어렵습니다. InnoDB가 데이터를 디스크에 어떻게 저장하고, 메모리에서 어떻게 관리하며, 장애 상황에서 어떻게 복구하는지를 이해해야 비로소 성능 튜닝과 장애 대응이 가능해집니다.

이 레슨에서는 InnoDB의 핵심 내부 구조를 페이지와 버퍼풀, 로그 시스템, 그리고 MVCC까지 단계적으로 살펴봅니다. 입문편에서 인덱스와 트랜잭션의 사용법을 익혔다면, 이번 레슨에서는 그것들이 내부적으로 어떻게 동작하는지를 파헤칩니다.

학습 목표

  • InnoDB의 페이지(Page), 익스텐트(Extent), 세그먼트(Segment) 단위 저장 구조를 설명할 수 있다.
  • 버퍼풀(Buffer Pool) 의 LRU 리스트 동작과 적중률(Hit Ratio) 을 측정하고 해석할 수 있다.
  • 클러스터형 인덱스와 보조 인덱스의 물리적 저장 방식 차이를 이해한다.
  • 리두 로그, 언두 로그, 더블라이트 버퍼의 역할과 상호 작용을 설명할 수 있다.
  • MVCC가 언두 로그를 이용해 스냅샷을 구성하는 메커니즘과 체크포인트 및 더티 페이지 플러시 흐름을 이해한다.

InnoDB 페이지 구조와 저장 단위

16KB 페이지 — InnoDB의 기본 I/O 단위

InnoDB는 데이터를 페이지(Page) 단위로 읽고 씁니다. 기본 크기는 16KB이며, MySQL 5.7부터는 innodb_page_size 파라미터로 4KB, 8KB, 32KB, 64KB 중 하나를 선택할 수도 있습니다. 다만 한 번 초기화된 인스턴스의 페이지 크기는 변경할 수 없습니다.

페이지 내부는 크게 세 영역으로 나뉩니다.

영역크기설명
File Header38 bytes페이지 번호, 이전/다음 페이지 포인터, 체크섬, LSN 등
Body가변실제 레코드 또는 인덱스 노드 데이터
File Trailer8 bytes무결성 검증용 체크섬

파일 헤더의 이전/다음 페이지 포인터는 동일 레벨의 B+Tree 리프 노드들을 이중 연결 리스트로 연결하는 데 사용됩니다. 덕분에 범위 스캔(Range Scan)이 페이지 간에 순서대로 이동하면서 효율적으로 처리됩니다.

-- 현재 인스턴스의 페이지 크기 확인
SHOW VARIABLES LIKE 'innodb_page_size';

-- 결과 예시:
-- +------------------+-------+
-- | Variable_name    | Value |
-- +------------------+-------+
-- | innodb_page_size | 16384 |
-- +------------------+-------+

익스텐트와 세그먼트 — 페이지 위의 상위 단위

페이지들은 익스텐트(Extent) 로 묶입니다. 기본 페이지 크기(16KB) 기준으로 하나의 익스텐트는 64개의 연속된 페이지, 즉 1MB를 차지합니다. InnoDB는 공간을 할당할 때 페이지 하나씩이 아니라 익스텐트 단위로 확보하여 물리적 연속성을 높이고 디스크 탐색 비용을 줄입니다.

여러 익스텐트의 집합은 세그먼트(Segment) 를 이룹니다. 하나의 인덱스는 리프 노드 세그먼트와 내부 노드 세그먼트로 구성되어 있으며, 테이블스페이스 내에서 관리됩니다.

-- information_schema를 통해 테이블 세그먼트 정보 확인
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    DATA_LENGTH / 1024 / 1024  AS data_mb,
    INDEX_LENGTH / 1024 / 1024 AS index_mb,
    DATA_FREE  / 1024 / 1024   AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME   = 'orders'
ORDER BY data_mb DESC;

💡 TIP DATA_FREE가 지속적으로 크다면 대규모 DELETE 이후 세그먼트 공간이 반환되지 않은 것입니다. OPTIMIZE TABLE 또는 ALTER TABLE ... ENGINE=InnoDB로 재구성하면 공간을 회수할 수 있습니다.

버퍼풀(Buffer Pool) 동작과 LRU 리스트

버퍼풀의 역할

버퍼풀은 InnoDB가 디스크 I/O를 줄이기 위해 페이지를 메모리에 캐시하는 핵심 공간입니다. 쿼리가 특정 페이지를 필요로 할 때, InnoDB는 먼저 버퍼풀에 해당 페이지가 있는지 확인합니다. 있으면 메모리에서 바로 읽고(버퍼풀 히트), 없으면 디스크에서 읽어 버퍼풀에 적재한 뒤 반환합니다(디스크 읽기). 디스크 접근은 메모리보다 수백~수천 배 느리므로, 버퍼풀의 크기와 적중률은 전체 성능에 결대적인 영향을 줍니다.

LRU 리스트 — 미드포인트 삽입 전략

일반적인 LRU(Least Recently Used) 알고리즘은 가장 최근에 사용된 항목을 리스트 앞에 놓고, 공간이 부족하면 뒤쪽(가장 오래 사용되지 않은 항목)을 제거합니다. 그런데 이 단순한 방식은 풀 테이블 스캔 시 문제가 됩니다. 대용량 테이블을 한 번 스캔하면 기존에 자주 사용하던 페이지들이 전부 밀려나 버립니다.

InnoDB는 이를 방지하기 위해 미드포인트 삽입(Midpoint Insertion) 전략을 사용합니다. LRU 리스트를 New 서브리스트(앞 5/8)와 Old 서브리스트(뒤 3/8)로 나누고, 디스크에서 새로 읽어온 페이지는 Old 서브리스트의 앞(미드포인트)에 먼저 삽입합니다. 이후 해당 페이지에 실제로 다시 접근이 발생해야 New 서브리스트로 승격됩니다.

버퍼풀 LRU 리스트 구조

[ New 서브리스트 (5/8) | Old 서브리스트 (3/8) ]
  ← 자주 사용됨         ← 새로 적재, 검증 중  →
                        ^
                  미드포인트 (새 페이지 삽입 위치)

-- innodb_old_blocks_pct: Old 서브리스트 비율 (기본값 37%)
-- innodb_old_blocks_time: Old에서 New로 승격되기 위한 최소 체류 시간 (ms)
SHOW VARIABLES LIKE 'innodb_old_blocks%';

-- 풀 테이블 스캔이 잦은 분석 쿼리 서버에서는 시간을 늘려 기존 캐시를 보호
SET GLOBAL innodb_old_blocks_time = 1000; -- 1초 후에만 승격 허용

버퍼풀 적중률 확인

-- 버퍼풀 적중률(Hit Ratio) 계산
SELECT
    (1 - (
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_reads')
        /
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS hit_ratio_pct;

일반적으로 운영 환경에서는 99% 이상의 적중률을 목표로 합니다. 95% 미만이라면 버퍼풀 크기(innodb_buffer_pool_size) 증설을 검토해야 합니다.

-- 버퍼풀 크기 확인 및 조정 (MySQL 5.7.5 이상: 온라인 변경 가능)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 물리 메모리의 50~75%를 권장 (예: 물리 16GB 서버라면 8~12GB)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024; -- 8GB

-- 버퍼풀 인스턴스 수 (버퍼풀이 8GB 이상이면 병렬성을 위해 여러 개 설정)
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

⚠️ 주의 버퍼풀 크기를 물리 메모리의 90% 이상으로 설정하면 OS 레벨의 페이지 캐시와 스왑 공간이 부족해져 오히려 성능이 급격히 저하될 수 있습니다. 운영 체제와 다른 프로세스를 위한 여유 메모리를 반드시 남겨 두어야 합니다.

클러스터형 인덱스와 보조 인덱스의 물리적 저장 방식

클러스터형 인덱스 — 테이블 자체가 B+Tree

InnoDB에서 테이블의 실제 데이터는 클러스터형 인덱스(Clustered Index) 의 리프 노드에 직접 저장됩니다. 기본 키(PRIMARY KEY)가 클러스터형 인덱스의 키가 되며, 리프 노드는 기본 키 순서대로 정렬된 전체 레코드 데이터를 담고 있습니다. 즉, InnoDB에서는 "테이블 = B+Tree" 라고 볼 수 있습니다.

클러스터형 인덱스 B+Tree 구조

           [루트 노드]
          /     |      \
   [내부 노드]  ...  [내부 노드]
   /    \                /    \
[리프]  [리프]  ...  [리프]  [리프]
 ↑ 리프에 실제 레코드 전체가 PK 순서대로 저장됨

기본 키가 정의되지 않으면, InnoDB는 다음 순서로 클러스터형 인덱스 키를 자동 선택합니다.

  1. NOT NULL 제약이 있는 첫 번째 UNIQUE 인덱스
  2. 내부적으로 관리하는 숨겨진 6바이트 row_id 컬럼
-- ✅ 좋은 예: 단조 증가하는 INT/BIGINT를 기본 키로 사용
CREATE TABLE orders (
    order_id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL,
    created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- ...
    PRIMARY KEY (order_id)
);

-- ❌ 나쁜 예: UUID를 기본 키로 사용 — 랜덤 삽입으로 페이지 분할 빈발
CREATE TABLE orders_bad (
    order_uuid CHAR(36) NOT NULL,
    -- ...
    PRIMARY KEY (order_uuid)
);

UUID를 기본 키로 사용하면 새 레코드가 항상 리스트의 끝에 삽입되지 않고 중간 어딘가에 삽입됩니다. 그 결과 페이지 분할(Page Split) 이 자주 발생하여 단편화가 심해지고 쓰기 성능이 저하됩니다.

보조 인덱스 — 리프에 기본 키 값만 저장

보조 인덱스(Secondary Index) 의 리프 노드에는 전체 레코드가 아닌 인덱스 컬럼 값 + 기본 키 값만 저장됩니다. 보조 인덱스로 검색하면 먼저 보조 인덱스 B+Tree를 탐색해 기본 키를 얻고, 그 기본 키로 다시 클러스터형 인덱스를 탐색합니다. 이 두 번의 인덱스 탐색을 더블 룩업(Double Lookup) 또는 북마크 조회라고 합니다.

-- 보조 인덱스 생성 예시
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- 아래 쿼리는 idx_user_created로 user_id, created_at 조건을 처리하고
-- order_id(PK)로 클러스터형 인덱스를 다시 탐색하여 status를 가져온다.
SELECT order_id, status
FROM orders
WHERE user_id = 100
  AND created_at >= '2025-01-01';

-- 커버링 인덱스(Covering Index)를 활용하면 더블 룩업을 피할 수 있다.
-- ✅ status 컬럼을 인덱스에 포함시켜 클러스터형 인덱스 재탐색 제거
ALTER TABLE orders ADD INDEX idx_user_created_status (user_id, created_at, status);

💡 TIP EXPLAIN 결과의 Extra 컬럼에 Using index가 표시되면 커버링 인덱스가 작동 중이라는 의미입니다. 이 경우 클러스터형 인덱스를 추가로 탐색하지 않아 I/O가 크게 줄어듭니다.

리두 로그, 언두 로그, 더블라이트 버퍼

리두 로그(Redo Log) — 내구성 보장

트랜잭션이 커밋될 때, InnoDB는 변경 내용을 리두 로그(Redo Log) 에 먼저 기록합니다. 실제 데이터 파일(.ibd)의 페이지는 바로 수정되지 않고 버퍼풀에서만 변경된 상태(더티 페이지)로 존재합니다. 나중에 체크포인트 시점에 더티 페이지가 디스크에 플러시됩니다.

서버가 갑자기 종료되더라도 리두 로그에 커밋된 기록이 남아 있으므로, 재시작 시 리두 로그를 재적용해 데이터를 복구할 수 있습니다. 이 원칙을 WAL(Write-Ahead Logging) 이라고 합니다.

-- 리두 로그 설정 확인 (MySQL 8.0.30+에서는 innodb_redo_log_capacity 사용)
SHOW VARIABLES LIKE 'innodb_log%';
-- innodb_log_buffer_size: 로그 버퍼 크기 (기본 16MB)
-- innodb_log_file_size  : 개별 로그 파일 크기 (구버전)
-- innodb_redo_log_capacity: 전체 리두 로그 용량 (MySQL 8.0.30+, 기본 100MB)

-- 리두 로그 쓰기 동기화 정책 (내구성과 성능의 트레이드오프)
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 1 (기본): 커밋마다 디스크에 fsync → 가장 안전, 가장 느림
-- 2       : 커밋마다 OS 버퍼에 쓰기, 초당 fsync → OS 크래시 시 최대 1초 손실
-- 0       : 초당 OS 버퍼 쓰기 + fsync → MySQL 크래시 시 최대 1초 손실

⚠️ 주의 innodb_flush_log_at_trx_commit=0 또는 2로 설정하면 MySQL 프로세스나 OS 크래시 시 최근 데이터가 손실될 수 있습니다. 금융·결제 시스템처럼 데이터 손실이 허용되지 않는 환경에서는 반드시 1로 유지해야 합니다.

언두 로그(Undo Log) — 롤백과 MVCC의 기반

언두 로그(Undo Log) 는 변경 이전의 데이터 이미지를 저장합니다. 두 가지 목적으로 사용됩니다.

  1. 롤백: 트랜잭션이 ROLLBACK되면 언두 로그의 이전 이미지를 이용해 변경을 되돌립니다.
  2. MVCC 스냅샷: 다른 트랜잭션이 일관된 읽기를 수행할 때, 언두 로그를 따라가며 해당 트랜잭션 시점의 데이터 버전을 재구성합니다.
-- 언두 테이블스페이스 확인 (MySQL 8.0)
SELECT NAME, STATE, SIZE
FROM information_schema.INNODB_TABLESPACES
WHERE NAME LIKE 'undo%';

-- 언두 로그 삭제 설정 (purge 스레드가 불필요한 언두 로그를 제거)
SHOW VARIABLES LIKE 'innodb_purge_threads';
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';

더블라이트 버퍼(Doublewrite Buffer) — 부분 쓰기 방지

디스크가 16KB 페이지를 쓰는 도중 OS 크래시가 발생하면 부분 쓰기(Partial Write) 가 일어날 수 있습니다. 리두 로그는 페이지가 온전하다는 전제 하에 변경 사항만 기록하므로, 페이지 자체가 깨지면 리두 로그만으로는 복구할 수 없습니다.

더블라이트 버퍼(Doublewrite Buffer) 는 이 문제를 해결합니다. 더티 페이지를 데이터 파일에 쓰기 전에, 먼저 시스템 테이블스페이스 내의 연속된 더블라이트 영역에 순차 쓰기로 저장합니다. 크래시 복구 시 더블라이트 버퍼의 온전한 복사본을 사용해 깨진 페이지를 복원한 뒤 리두 로그를 적용합니다.

-- 더블라이트 버퍼 활성화 여부 확인
SHOW VARIABLES LIKE 'innodb_doublewrite';

-- MySQL 8.0.20+: 더블라이트 버퍼를 별도 파일로 분리 가능 (성능 향상)
SHOW VARIABLES LIKE 'innodb_doublewrite_dir';
SHOW VARIABLES LIKE 'innodb_doublewrite_files';

-- 더블라이트 통계 확인
SHOW GLOBAL STATUS LIKE 'Innodb_dblwr%';
-- Innodb_dblwr_pages_written: 더블라이트 버퍼에 쓴 페이지 수
-- Innodb_dblwr_writes       : 실제 디스크 쓰기 횟수

MVCC와 스냅샷 읽기 내부 메커니즘

MVCC란 무엇인가

MVCC(Multi-Version Concurrency Control) 는 읽기와 쓰기가 서로를 차단하지 않도록 여러 버전의 데이터를 동시에 유지하는 기법입니다. InnoDB는 언두 로그를 이용해 MVCC를 구현합니다.

트랜잭션 ID와 버전 체인

모든 InnoDB 행에는 내부적으로 두 개의 숨겨진 컬럼이 있습니다.

숨겨진 컬럼설명
DB_TRX_ID해당 행을 마지막으로 수정한 트랜잭션 ID
DB_ROLL_PTR이전 버전을 가리키는 언두 로그 포인터

행이 수정될 때마다 이전 버전은 언두 로그에 보존되고, DB_ROLL_PTR이 그것을 가리킵니다. 이렇게 언두 로그 버전들이 체인을 이루어 버전 체인(Version Chain) 을 형성합니다.

버전 체인 예시 (orders 테이블의 order_id=1 행)

현재 데이터 파일:
  [order_id=1, status='shipped', DB_TRX_ID=1005, DB_ROLL_PTR → 언두로그A]

언두 로그 A (TRX 1003이 변경 전 이미지):
  [order_id=1, status='processing', DB_TRX_ID=1003, DB_ROLL_PTR → 언두로그B]

언두 로그 B (TRX 1001이 변경 전 이미지):
  [order_id=1, status='pending', DB_TRX_ID=1001, DB_ROLL_PTR → NULL]

일관된 읽기(Consistent Read) 동작 원리

REPEATABLE READ 격리 수준에서는 트랜잭션 시작 시점이 아니라 트랜잭션 내 첫 일관된 읽기(첫 SELECT) 시점에 InnoDB가 읽기 뷰(Read View) 를 생성합니다. 읽기 뷰에는 그 시점에 활성 중인 트랜잭션 ID 목록이 담겨 있습니다. 행을 읽을 때 DB_TRX_ID가 읽기 뷰보다 최신이거나 활성 트랜잭션에 속하면, DB_ROLL_PTR을 따라 언두 로그를 거슬러 올라가 자신의 뷰에서 보여야 할 버전을 찾습니다. 만약 트랜잭션 시작 시점에 스냅샷을 고정하고 싶다면 START TRANSACTION WITH CONSISTENT SNAPSHOT을 사용하면 됩니다. 이 경우 첫 SELECT를 기다리지 않고 BEGIN 시점에 즉시 읽기 뷰가 생성됩니다.

-- MVCC 스냅샷 읽기 동작 확인 예시
-- 세션 A
START TRANSACTION;
SELECT status FROM orders WHERE order_id = 1;
-- 결과: 'processing' (트랜잭션 시작 시점의 스냅샷)

-- 세션 B (동시에)
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- 세션 A (계속)
SELECT status FROM orders WHERE order_id = 1;
-- 결과: 'processing' (REPEATABLE READ: 스냅샷이 유지됨, 세션 B의 변경이 보이지 않음)
COMMIT;

-- 세션 A를 READ COMMITTED로 바꾸면:
-- 두 번째 SELECT에서 'shipped'가 보임 (매 읽기마다 새 Read View 생성)

💡 TIP 장시간 실행되는 트랜잭션은 그 시점 이후의 언두 로그가 purge되지 못하게 막습니다. 이로 인해 언두 로그가 무한 증가해 디스크를 채울 수 있습니다. 배치 작업이나 분석 쿼리는 트랜잭션을 최대한 짧게 유지하거나, 읽기 전용 트랜잭션(START TRANSACTION READ ONLY)을 사용하세요.

체크포인트와 더티 페이지 플러시

더티 페이지와 체크포인트

버퍼풀에서 수정되었으나 아직 디스크에 기록되지 않은 페이지를 더티 페이지(Dirty Page) 라고 합니다. 서버가 정상 종료될 때는 모든 더티 페이지를 디스크에 플러시합니다. 그러나 비정상 종료 시에는 리두 로그를 이용해 마지막 체크포인트 이후의 변경 사항을 재적용합니다.

체크포인트(Checkpoint) 는 "이 LSN(Log Sequence Number)까지의 변경 사항은 모두 데이터 파일에 반영되었다"는 표식입니다. 체크포인트가 오래될수록 크래시 복구 시간이 길어지고, 리두 로그가 가득 찰 위험도 높아집니다.

플러시 알고리즘

InnoDB의 페이지 클리너 스레드(page_cleaner)는 다음 두 가지 상황에서 더티 페이지를 플러시합니다.

  1. 샤프 체크포인트(Sharp Checkpoint): 리두 로그 파일이 꽉 차기 전에 강제로 더티 페이지를 내보내는 긴급 플러시.
  2. 퍼지 체크포인트(Fuzzy Checkpoint): 백그라운드에서 지속적으로 소량씩 더티 페이지를 플러시하는 일상적인 방식.
-- 더티 페이지 비율과 플러시 관련 상태 확인
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_pages_dirty',
    'Innodb_buffer_pool_pages_total',
    'Innodb_buffer_pool_pages_flushed',
    'Innodb_os_log_written'
);

-- 더티 페이지 비율 계산
SELECT
    ROUND(
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_pages_dirty')
        /
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_pages_total')
        * 100, 2
    ) AS dirty_ratio_pct;

-- 플러시 관련 주요 설정
SHOW VARIABLES LIKE 'innodb_io_capacity';        -- 초당 최대 I/O 작업 수 (기본 200)
SHOW VARIABLES LIKE 'innodb_io_capacity_max';    -- 긴급 플러시 시 최대 I/O (기본 2000)
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'; -- 더티 페이지 허용 비율 (기본 90%)
SHOW VARIABLES LIKE 'innodb_flush_method';        -- 디스크 쓰기 방식 (O_DIRECT 권장)

💡 TIP SSD를 사용하는 환경에서는 innodb_io_capacity를 2000~10000으로 높여 페이지 클리너가 더 공격적으로 플러시하도록 설정하면 리두 로그 부족으로 인한 순간적인 성능 저하를 예방할 수 있습니다.

-- SSD 환경을 위한 권장 설정 예시
SET GLOBAL innodb_io_capacity     = 4000;
SET GLOBAL innodb_io_capacity_max = 8000;
-- 영구 적용은 my.cnf에 기록
-- [mysqld]
-- innodb_io_capacity     = 4000
-- innodb_io_capacity_max = 8000
-- innodb_flush_method    = O_DIRECT

요약

  • InnoDB는 16KB 페이지 → 익스텐트(64페이지, 1MB) → 세그먼트 순서의 계층적 저장 구조를 사용하며, B+Tree 리프 노드들은 이중 연결 리스트로 연결되어 범위 스캔을 지원한다.
  • 버퍼풀은 미드포인트 삽입 전략의 LRU 리스트로 페이지를 캐시하며, 적중률 99% 이상을 목표로 크기를 물리 메모리의 50~75% 수준으로 설정하는 것이 일반적이다.
  • 클러스터형 인덱스 리프에는 전체 레코드가 기본 키 순서대로 저장되고, 보조 인덱스 리프에는 인덱스 컬럼과 기본 키만 저장되어 더블 룩업이 발생한다.
  • 리두 로그는 WAL 원칙으로 내구성을 보장하고, 언두 로그는 롤백과 MVCC 스냅샷을 지원하며, 더블라이트 버퍼는 부분 쓰기로 인한 페이지 손상을 방지한다.
  • MVCC는 각 행에 숨겨진 DB_TRX_ID와 DB_ROLL_PTR로 버전 체인을 구성하고, 읽기 뷰를 통해 격리 수준에 맞는 스냅샷을 재구성한다.
  • 체크포인트는 LSN 기준으로 더티 페이지 플러시 진행 상황을 추적하며, 리두 로그 공간 부족이나 더티 페이지 비율 초과 시 샤프 체크포인트가 발동해 I/O 스파이크를 유발할 수 있다.

연습문제

  1. 운영 중인 MySQL 서버에서 버퍼풀 적중률이 낮을 때(95% 미만), 즉각적으로 취할 수 있는 조치와 근본적인 해결 방법을 각각 설명하고, 적중률을 계산하는 SQL을 작성하시오.

  2. 다음 두 테이블 설계 중 InnoDB 성능 측면에서 어느 것이 더 유리한지 이유를 설명하고, 불리한 설계가 야기하는 내부 문제(페이지 분할, 단편화 등)를 구체적으로 서술하시오.

-- 설계 A
CREATE TABLE users_a (
    id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- 설계 B
CREATE TABLE users_b (
    uuid CHAR(36) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (uuid)
);

  1. MVCC에서 장시간 실행되는 트랜잭션이 언두 로그에 미치는 영향을 설명하고, 이를 확인할 수 있는 SQL과 완화 방법을 서술하시오.

  2. innodb_flush_log_at_trx_commit을 0, 1, 2로 각각 설정했을 때 MySQL 프로세스 크래시와 OS 크래시 상황에서 데이터 손실 가능성을 비교하는 표를 작성하고, 실무에서 어떤 값을 선택해야 하는지 이유와 함께 설명하시오.

힌트 각 설정값이 "언제 OS 버퍼에 쓰는지"와 "언제 fsync를 하는지"를 구분해서 생각해 보세요.

💡 연습문제 풀이

불러오는 중…

함께 보면 좋은 자료

📑 치트시트

댓글 0

“MySQL 심화” 강좌에 대한 댓글입니다.

댓글을 작성하려면 로그인이 필요합니다.