MySQL InnoDB Buffer Pool 튜닝

글쓴이 /

왜 Buffer Pool 튜닝이 중요한가

InnoDB는 디스크 I/O를 줄이기 위해 데이터 페이지와 인덱스 페이지를 메모리에 캐싱한다. 이 캐시 영역이 Buffer Pool이다. Buffer Pool이 제대로 설정되지 않으면 디스크 읽기가 급증하고, 쿼리 응답 시간이 수십 배 느려진다. MySQL 공식 문서는 “전용 데이터베이스 서버에서는 시스템 메모리의 80%까지 할당하라”고 권장한다.

이 글에서는 MySQL 8.0/8.4 공식 문서를 기반으로, innodb_buffer_pool_size 산정부터 LRU 알고리즘, 온라인 리사이즈, dump/load 워밍업, 그리고 모니터링 쿼리까지 운영에서 바로 쓸 수 있는 설정을 정리한다.

Buffer Pool 아키텍처: LRU와 Young/Old Sublist

InnoDB Buffer Pool은 변형된 LRU(Least Recently Used) 알고리즘으로 페이지를 관리한다. 리스트는 두 영역으로 나뉜다.

  • Young Sublist (head 쪽, 기본 63%): 최근에 자주 접근된 “뜨거운” 페이지
  • Old Sublist (tail 쪽, 기본 37%): 새로 읽힌 페이지가 처음 삽입되는 영역

새 페이지가 Buffer Pool에 올라오면 Old Sublist의 head(midpoint)에 삽입된다. 이후 해당 페이지에 다시 접근이 발생하면 Young Sublist로 승격된다. 이 설계의 핵심은 풀 스캔(full table scan) 방어다. 한 번만 읽히는 대량 페이지가 Young 영역의 핫 데이터를 밀어내는 것을 막는다.

innodb_old_blocks_pct와 innodb_old_blocks_time

변수 기본값 역할 튜닝 포인트
innodb_old_blocks_pct 37 Old Sublist 비율(%) 풀 스캔이 빈번하면 높이고, OLTP 전용이면 기본값 유지
innodb_old_blocks_time 1000 (ms) Old에서 Young으로 승격되려면 이 시간 이후에 재접근 필요 mysqldump 중 값을 높여 핫 데이터 보호

innodb_buffer_pool_size 산정: 공식과 실무 기준

기본 공식

-- MySQL 공식 문서 권장: 전용 서버 기준 시스템 RAM의 50~80%
-- 예: 64GB RAM 서버
SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;  -- 48GB (75%)

단, 이 값은 InnoDB만을 위한 것이다. OS 커널 캐시, MySQL 연결 버퍼(sort_buffer_size, join_buffer_size 등), 다른 프로세스가 필요로 하는 메모리를 반드시 빼야 한다.

실무 산정 체크리스트

-- 1단계: 현재 데이터+인덱스 크기 확인
SELECT 
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- 2단계: 현재 Buffer Pool 사용량 확인
SELECT 
  ROUND(@@innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_size_gb,
  ROUND(
    (SELECT variable_value FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_pages_total') * 16 / 1024 / 1024, 2
  ) AS actual_pool_gb;

-- 3단계: Free 페이지 비율로 여유 확인
SELECT 
  variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_buffer_pool_pages_total',
  'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_pages_data'
);
시나리오 권장 pool_size 이유
데이터 < RAM의 50% 데이터 크기 + 20% 여유 전부 캐싱 가능, 과잉 할당 불필요
데이터 > RAM RAM의 70~80% 워킹셋 최대한 캐싱, OS 여유 확보
공유 서버 (앱+DB 동거) RAM의 25~50% 앱 힙, OS 캐시, 기타 프로세스 고려
컨테이너 환경 (K8s) memory limit의 60~70% OOMKill 방지를 위해 반드시 limit 기준

innodb_buffer_pool_instances와 chunk_size: 병렬성과 리사이즈

Buffer Pool은 여러 인스턴스로 분할할 수 있다. 인스턴스별로 별도의 LRU 리스트와 뮤텍스를 가지므로, 동시 접근 경합(contention)이 줄어든다.

-- MySQL 공식 문서 권장: pool_size가 1GB 이상이면 instances 분할
-- 경험적 기준: 1GB당 1 instance, 최대 64
[mysqld]
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 128M  -- 기본값

핵심 제약: pool_size는 chunk_size × instances의 배수여야 한다

MySQL은 Buffer Pool 크기를 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances의 배수로 자동 조정한다. 원하는 크기와 실제 할당 크기가 다를 수 있으므로 반드시 확인해야 한다.

-- 예: chunk=128M, instances=16이면 단위는 128M × 16 = 2GB
-- pool_size를 33G로 설정하면 → 34G로 반올림
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS actual_gb;
변수 기본값 동적 변경 주의사항
innodb_buffer_pool_size 128MB ✅ SET GLOBAL chunk × instances 배수로 조정됨
innodb_buffer_pool_instances 8 (size≥1GB) ❌ 재시작 필요 size < 1GB면 자동으로 1
innodb_buffer_pool_chunk_size 128MB ❌ 재시작 필요 chunk × instances ≤ pool_size 이어야 함

온라인 리사이즈: 무중단으로 Buffer Pool 크기 변경

MySQL 5.7부터 innodb_buffer_pool_size는 동적 변수로, 서버 재시작 없이 변경할 수 있다. 리사이즈는 chunk 단위로 진행되며, 진행 상태를 모니터링할 수 있다.

-- 1. 현재 크기 확인
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS current_gb;

-- 2. 크기 변경 (비동기로 시작됨)
SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;

-- 3. 리사이즈 진행 상태 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

-- 출력 예: "Completed resizing buffer pool at 260220 04:00:00"
-- 또는: "Resizing buffer pool from 34359738368 to 51539607552 (increase 8 chunks)"

주의: 리사이즈 중에는 AHI(Adaptive Hash Index) 분할, 페이지 재배치 등의 내부 작업이 발생한다. 트래픽이 낮은 시간대에 수행하는 것이 안전하다. MySQL 에러 로그에 진행 과정이 기록된다.

Buffer Pool Dump/Load: 재시작 후 워밍업 전략

MySQL을 재시작하면 Buffer Pool이 비어 있어 초기 쿼리가 모두 디스크를 읽는다. 이를 콜드 스타트(cold start)라 한다. MySQL 5.6부터 도입된 dump/load 기능으로 이 문제를 해결할 수 있다.

-- my.cnf 설정
[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON   -- 종료 시 페이지 목록 저장 (기본 ON, 8.0+)
innodb_buffer_pool_load_at_startup = ON    -- 시작 시 페이지 목록 기반 로드 (기본 ON, 8.0+)
innodb_buffer_pool_dump_pct = 75           -- 가장 최근 사용된 페이지의 75%만 덤프 (기본 25)

덤프 파일은 ib_buffer_pool이라는 이름으로 데이터 디렉터리에 저장된다. 이 파일에는 실제 데이터가 아닌 (space_id, page_number) 쌍만 기록되므로 크기가 매우 작다.

-- 수동 덤프/로드 (온라인)
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 로드 진행률 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
-- 출력 예: "Buffer pool(s) load completed at 260220 04:01:23"

-- 로드 중단 (필요시)
SET GLOBAL innodb_buffer_pool_load_abort = ON;

dump_pct 튜닝 가이드

환경 dump_pct 이유
OLTP (워킹셋 고정) 75~100 핫 데이터 대부분 복원해 콜드 스타트 최소화
배치+OLTP 혼합 25~50 배치로 올라온 일시적 페이지 제외
대용량 Pool (64GB+) 25 로드 시간 단축, 핫 페이지만 복원

Buffer Pool Hit Ratio 모니터링

Buffer Pool의 핵심 지표는 Hit Ratio다. 이 값이 99% 미만이면 디스크 I/O가 과도하다는 신호다.

-- 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;

-- 해석:
-- Innodb_buffer_pool_read_requests: Buffer Pool에서 읽기를 시도한 횟수
-- Innodb_buffer_pool_reads: Buffer Pool에 없어서 디스크에서 읽은 횟수
-- 99% 이상이 정상, 95% 미만이면 pool_size 증가 검토

종합 모니터링 쿼리

-- Buffer Pool 상태 한눈에 보기
SELECT 
  FORMAT(@@innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_size_gb,
  @@innodb_buffer_pool_instances AS instances,
  s1.variable_value AS total_pages,
  s2.variable_value AS free_pages,
  s3.variable_value AS dirty_pages,
  ROUND((1 - s4.variable_value / s5.variable_value) * 100, 2) AS hit_ratio_pct,
  s6.variable_value AS pages_made_young,
  s7.variable_value AS pages_not_made_young
FROM 
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') s1,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_free') s2,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') s3,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') s4,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') s5,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_made_young') s6,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_not_made_young') s7;
지표 정상 범위 비정상 시 조치
Hit Ratio ≥ 99% pool_size 증가 또는 풀 스캔 쿼리 제거
Free Pages 전체의 5% 이상 0에 가까우면 pool_size 부족
Dirty Pages 비율 75% 미만 innodb_max_dirty_pages_pct 확인, I/O 용량 점검
pages_not_made_young 안정적 급증하면 old_blocks_time이 너무 높은 것

Kubernetes 환경에서의 Buffer Pool 설정

컨테이너 환경에서는 cgroup memory limit가 실제 가용 메모리를 결정한다. 호스트 RAM이 아닌 Pod의 resources.limits.memory 기준으로 산정해야 한다.

# Kubernetes StatefulSet 예시
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  template:
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        resources:
          requests:
            memory: "8Gi"
          limits:
            memory: "8Gi"    # 이 값 기준으로 buffer pool 산정
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        volumeMounts:
        - name: mysql-config
          mountPath: /etc/mysql/conf.d
      volumes:
      - name: mysql-config
        configMap:
          name: mysql-config
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  custom.cnf: |
    [mysqld]
    innodb_buffer_pool_size = 5G           # limits 8Gi의 약 62%
    innodb_buffer_pool_instances = 4
    innodb_buffer_pool_dump_at_shutdown = ON
    innodb_buffer_pool_load_at_startup = ON
    innodb_buffer_pool_dump_pct = 75

OOMKill 방지 핵심: Buffer Pool 외에도 MySQL은 연결당 메모리(sort_buffer, join_buffer, tmp_table 등), Performance Schema, InnoDB 로그 버퍼 등을 사용한다. limits의 60~70%를 넘기지 않는 것이 안전하다.

실전 체크리스트: Buffer Pool 튜닝 7단계

  1. 데이터 크기 측정 — information_schema.tables에서 data_length + index_length 합산
  2. 가용 메모리 확인 — 전용 서버: 시스템 RAM, K8s: Pod memory limit
  3. pool_size 결정 — 데이터가 메모리보다 작으면 데이터+20%, 크면 가용 메모리의 70%
  4. instances 설정 — pool_size(GB) ÷ 1~2 (최대 64), 재시작 필요
  5. chunk_size 확인 — pool_size = chunk_size × instances × N 이 되는지 검증
  6. dump/load 활성화 — dump_pct는 OLTP면 75, 혼합이면 25~50
  7. 모니터링 설정 — Hit Ratio, Free Pages, Dirty Pages를 Grafana/Prometheus에 등록

흔한 실수 4가지와 방지법

실수 1: pool_size를 시스템 RAM 전체로 설정

증상: OOM Killer가 mysqld를 죽이거나, OS 스왑이 발생해 전체 성능 급락.

방지: 전용 서버라도 80%를 넘기지 않는다. OS 파일시스템 캐시, MySQL 연결 버퍼, 다른 데몬을 위한 여유가 필요하다.

실수 2: chunk_size × instances 배수를 무시

증상: 원하는 크기를 설정했는데 실제 할당이 다르다. 에러 로그에 “InnoDB: Adjusted innodb_buffer_pool_size” 경고.

방지: 설정 후 SELECT @@innodb_buffer_pool_size로 실제 값을 반드시 확인한다.

실수 3: 온라인 리사이즈를 피크 시간에 실행

증상: 리사이즈 중 AHI 재구축, 페이지 이동으로 인한 뮤텍스 경합 → 쿼리 지연 스파이크.

방지: 트래픽이 낮은 새벽에 수행하고, SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status'로 완료를 확인한다.

실수 4: 재시작 후 워밍업 없이 트래픽 투입

증상: 콜드 Buffer Pool로 디스크 I/O 폭증 → 응답 시간 10배 이상 증가 → 연쇄 타임아웃.

방지: innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup을 반드시 ON으로 설정한다. 로드 완료 전 트래픽을 받지 않도록 Kubernetes readiness probe와 연계한다.

마무리

InnoDB Buffer Pool은 MySQL 성능의 가장 큰 단일 변수다. 올바른 크기 산정, LRU 동작 이해, 온라인 리사이즈 활용, dump/load 워밍업, 그리고 지속적인 모니터링이 조합되어야 운영 환경에서 안정적인 99%+ Hit Ratio를 유지할 수 있다. 이 글의 모든 설정과 쿼리는 MySQL 8.0/8.4 공식 문서(InnoDB Buffer Pool, Buffer Pool Resize)를 근거로 한다.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux