LLM WikiAccess-protected knowledge portal

WIKI

데이터베이스별 스토리지 튜닝 체크리스트

스토리지 튜닝은 DB마다 다른 이유가 있다 스토리지 벤치마크 7편 와 I/O 스케줄러, 파일시스템 3편 , 클라우드 블록 스토리지 4편 를 다뤘다면, 이번 편은 그 지식을 각 DB 엔진의 실제 파라미터에 연결하는 작업이다. 스토리지 튜닝이 DB마다 다른 이유는 각 엔진이 OS I/O를 다루는 방식이 근본적으로 다르기 때문이다. MySQL/InnoDB 자체 buffer pool을 유지하고, OS page cache를 우회 O D

경로human/study/content/storage-and-io/08-database-storage-tuning-checklist.md
카테고리Study
태그#checklist #database #mysql #storage #study #tuning

스토리지 튜닝은 DB마다 다른 이유가 있다

스토리지 벤치마크(7편)와 I/O 스케줄러, 파일시스템(3편), 클라우드 블록 스토리지(4편)를 다뤘다면, 이번 편은 그 지식을 각 DB 엔진의 실제 파라미터에 연결하는 작업이다.

스토리지 튜닝이 DB마다 다른 이유는 각 엔진이 OS I/O를 다루는 방식이 근본적으로 다르기 때문이다.

공통 OS 레이어를 먼저 맞추고, 그 위에 DB별 파라미터를 조정하는 순서가 가장 안전하다.

스토리지 튜닝 레이어: OS 공통 → DB 별 파라미터 OS / 하드웨어 공통 레이어 I/O 스케줄러: NVMe → none/mq-deadline | 파일시스템: XFS/ext4, mount noatime,nodiratime | readahead, swappiness=1 MySQL / InnoDB innodb_buffer_pool_size innodb_flush_method innodb_io_capacity innodb_log_file_size innodb_flush_log_at_trx_commit doublewrite 설정 innodb_read_io_threads innodb_write_io_threads PostgreSQL shared_buffers effective_io_concurrency wal_buffers checkpoint_completion_target max_wal_size synchronous_commit random_page_cost maintenance_work_mem ClickHouse merge_max_block_size background_pool_size max_bytes_to_merge mark_cache_size uncompressed_cache_size min_bytes_for_wide_part storage_configuration (tiered disk) Redis rdbcompression yes/no save 정책(snapshot 주기) appendfsync always/everysec no-appendfsync-on-rewrite aof-rewrite-incremental-fsync vm.overcommit_memory=1 transparent_hugepage disabled 모든 파라미터 변경 후 → fio + iostat + DB 내부 지표로 검증. 벤치마크 먼저, 변경 후 비교 측정.
데이터베이스별 스토리지 튜닝 레이어

OS 레이어: DB를 올리기 전에 맞춰야 할 공통 설정

데이터베이스는 OS 위에서 실행된다. DB 파라미터를 아무리 잘 조정해도 OS 레이어가 잘못 설정되어 있으면 효과가 제한된다.

I/O 스케줄러

NVMe SSD에서는 커널 I/O 스케줄러 역할이 줄어든다. NVMe는 내부적으로 큐를 수천 개 병렬로 처리하기 때문에, OS가 재정렬을 추가하면 오히려 latency가 올라갈 수 있다.

# NVMe 장치 확인
lsblk -d -o NAME,ROTA,SCHED
# ROTA=0 이면 SSD/NVMe

# NVMe에 none 설정
echo none | sudo tee /sys/block/nvme0n1/queue/scheduler

# SATA SSD에는 mq-deadline 권장
echo mq-deadline | sudo tee /sys/block/sda/queue/scheduler

스케줄러를 영구적으로 설정하려면 udev rule 또는 /etc/udev/rules.d/60-io-scheduler.rules에 device 타입별로 지정한다.

파일시스템 mount 옵션

DB 데이터 디렉터리에는 access time 업데이트(atime)가 필요하지 않다. 이를 비활성화하면 각 read마다 발생하는 inode update write를 줄일 수 있다.

/dev/nvme0n1p1 /var/lib/mysql xfs defaults,noatime,nodiratime 0 0

XFS는 대용량 파일 I/O와 DB workload에 잘 맞는다. ext4는 data=writeback과 함께 사용하면 일부 workload에서 성능이 개선된다. 파일시스템 3편에서 자세히 다뤘다.

swappiness

DB 프로세스가 스왑으로 밀리면 I/O 스파이크와 latency 폭발이 발생한다.

# 확인
cat /proc/sys/vm/swappiness

# DB 서버에서 권장: 1 (0은 OOM kill 위험이 있어 1이 안전하다)
echo 'vm.swappiness=1' | sudo tee -a /etc/sysctl.d/99-db.conf
sudo sysctl --system

readahead

readahead는 순차 읽기에 도움을 주지만, OLTP random I/O가 많은 DB에서는 불필요한 I/O를 유발할 수 있다.

# 현재 확인
blockdev --getra /dev/nvme0n1

# OLTP 계열에서 줄이는 경우 (단위: 512B 섹터)
# 예: 256 = 128KiB readahead
sudo blockdev --setra 256 /dev/nvme0n1

InnoDB는 자체 read-ahead 로직이 있어 OS readahead가 겹치면 불필요한 I/O가 늘 수 있다. 반대로 백업이나 데이터 로드 등 sequential 위주 작업이 많은 서버는 큰 readahead가 유리하다. 어느 쪽이든 변경 전후 iostat -x와 DB slow query 지표를 함께 봐야 판단할 수 있다.

MySQL / InnoDB 튜닝 체크리스트

1. buffer pool 크기

MySQL 8.4 레퍼런스 매뉴얼은 innodb_buffer_pool_size를 전체 RAM의 50~80% 수준으로 설정하고, 나머지는 OS, 연결별 메모리, 각종 캐시를 위해 남겨두라고 설명한다.

-- 현재 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- hit rate 계산
-- (1 - reads / read_requests) * 100
-- 99% 이상이 정상, 95% 미만이면 buffer pool이 부족하다

buffer pool이 충분해도 dirty page 비율이 높게 지속된다면 flush 속도가 쓰기 속도를 따라가지 못하는 것이다.

SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
-- dirty / total * 100 이 30% 이상 지속되면 innodb_io_capacity 확인

2. I/O 설정: flush method와 io_capacity

innodb_flush_method는 InnoDB가 데이터 파일과 로그 파일에 쓸 때 OS page cache를 어떻게 다루는지 결정한다.

값동작적합한 환경
O_DIRECT데이터 파일에 direct I/O, 로그는 기본로컬 SSD/NVMe, buffer pool과 OS cache 이중 버퍼링 방지
O_DIRECT_NO_FSYNCdirect I/O + redo log fsync 생략 (durability는 volume이 보장)하드웨어 write-back cache 또는 클라우드 durable volume
fsync기본값 (Linux), OS cache 경유구형 환경, 실험적 세팅

로컬 NVMe에서는 O_DIRECT가 표준이다. innodb_io_capacity는 InnoDB background 작업(flushing, change buffer merge, purge)이 사용할 IOPS를 설정한다.

[mysqld]
innodb_flush_method         = O_DIRECT
innodb_io_capacity          = 4000      # SATA SSD 2000, NVMe 4000~10000
innodb_io_capacity_max      = 8000      # io_capacity의 2~3배
innodb_read_io_threads      = 8         # 기본 4, NVMe에서 확대 가능
innodb_write_io_threads     = 8

innodb_io_capacity를 너무 낮게 설정하면 dirty page가 쌓이고 checkpoint가 강제 발동해 write stall이 생긴다. 너무 높게 설정하면 background I/O가 foreground workload를 압박한다. 측정 기반으로 조정해야 한다.

3. redo log와 commit durability

innodb_log_file_size        = 2G        # 8.0, 8.4에서는 innodb_redo_log_capacity
innodb_flush_log_at_trx_commit = 1      # 기본값, 가장 안전
# 1: 매 commit마다 redo log를 fsync → ACID 보장
# 2: 매 commit마다 OS buffer에만 씀, 초당 한 번 fsync → 1초치 데이터 유실 위험
# 0: 가장 빠름, 1초치 + crash 시 유실 위험

innodb_flush_log_at_trx_commit=2는 복제 환경에서 primary 성능을 높이는 용도로 쓰는 경우가 있지만, 장애 시 1초치 commit이 유실될 수 있어 설계 상 허용 가능한지 확인해야 한다.

4. doublewrite buffer

InnoDB는 기본적으로 doublewrite buffer를 사용해 partial write(16KiB page를 쓰다가 crash)를 방지한다. NVMe 또는 파일시스템 atomic write를 지원하는 환경에서는 불필요하다.

SHOW VARIABLES LIKE 'innodb_doublewrite';
-- NVMe + XFS atomic write 지원 환경에서는 OFF 검토 가능
-- MySQL 8.0.20+: innodb_doublewrite_dir, innodb_doublewrite_files 분리 가능

doublewrite를 끄면 write 성능이 소폭 개선되지만 crash 후 page 손상 위험이 있다. 신중하게 판단한다.

5. MySQL 스토리지 모니터링 핵심 지표

-- InnoDB I/O 상태
SHOW ENGINE INNODB STATUS\G

-- 핵심 상태 변수
SHOW STATUS LIKE 'Innodb_os_log_written';         -- redo log 쓰기량
SHOW STATUS LIKE 'Innodb_data_reads';             -- 데이터 파일 read
SHOW STATUS LIKE 'Innodb_data_writes';            -- 데이터 파일 write
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';  -- free buffer 부족 신호

Innodb_buffer_pool_wait_free가 꾸준히 증가하면 buffer pool에서 free page를 찾지 못해 대기하는 상황이다. buffer pool 크기 확대 또는 flush 속도 증가가 필요하다.

PostgreSQL 튜닝 체크리스트

PostgreSQL은 shared_buffers 외에도 OS page cache를 적극 활용하는 설계다. MySQL의 O_DIRECT 전략과 달리, PostgreSQL은 double-buffering을 일부 허용하는 대신 OS cache의 read-ahead 효과를 누린다.

1. shared_buffers와 page cache 배분

# postgresql.conf
shared_buffers = 8GB                 # 전체 RAM의 25% 수준이 출발점
effective_cache_size = 24GB          # 실제로 할당하는 값이 아니라 옵티마이저 힌트
                                     # = shared_buffers + OS free page cache 추정량

effective_cache_size는 플래너가 index scan vs sequential scan 비용을 계산할 때 참고한다. 실제 RAM 크기보다 크게 설정하면 안 된다.

2. effective_io_concurrency

이 파라미터는 PostgreSQL이 bitmap index scan 등에서 동시에 prefetch할 수 있는 I/O 요청 수다. SSD에서는 기본값(1)보다 높이는 것이 유리하다.

effective_io_concurrency = 200      # SATA SSD: 100~200, NVMe: 200~300
# 지나치게 높으면 context switching overhead가 생길 수 있다

PostgreSQL 16부터는 maintenance_io_concurrency도 별도로 설정할 수 있다. VACUUM, CREATE INDEX CONCURRENTLY 같은 유지보수 작업의 I/O 병렬성이다.

3. WAL과 checkpoint 튜닝

checkpoint는 dirty buffer를 디스크에 쓰는 작업이다. 짧은 checkpoint 간격은 안전하지만 I/O spike를 만든다. checkpoint_completion_target은 checkpoint 기간의 몇 분의 몇에 걸쳐 I/O를 분산할지 결정한다.

wal_buffers                  = 64MB      # 기본 자동(shared_buffers의 1/32), 고쓰기 환경은 명시 설정
max_wal_size                 = 4GB       # checkpoint 간격을 늘리는 효과
min_wal_size                 = 1GB
checkpoint_completion_target = 0.9       # 90% 지점까지 I/O를 분산
checkpoint_timeout           = 10min     # 기본 5min, 쓰기 집중 환경에서 늘림

max_wal_size를 늘리면 checkpoint가 덜 자주 발생해 spike I/O가 줄어들지만, 장애 후 recovery time이 길어진다. workload와 RTO 요건을 함께 고려한다.

4. synchronous_commit

synchronous_commit = on      # 기본값, WAL이 디스크에 flush되어야 commit 반환
# off: commit 직후 반환(성능 향상), 장애 시 마지막 commit 일부 유실 가능
# local: primary만 fsync, 복제 확인 안 함
# remote_write: standby에 write됐을 때 반환 (fsync 전)
# remote_apply: standby가 실제로 적용했을 때 반환 (가장 안전)

synchronous_commit=off는 개별 세션 단위로도 설정할 수 있어, 일부 쓰기가 많은 batch 세션에만 적용하는 방식도 유효하다.

5. random_page_cost와 스토리지 타입 일치

옵티마이저는 random_page_cost와 seq_page_cost 비율로 index scan vs sequential scan을 선택한다. SSD에서는 이 비율이 HDD와 크게 다르다.

# HDD 기본값
random_page_cost = 4.0
seq_page_cost    = 1.0

# SATA SSD
random_page_cost = 1.5
seq_page_cost    = 1.0

# NVMe
random_page_cost = 1.1
seq_page_cost    = 1.0

이 값이 스토리지 타입과 맞지 않으면 옵티마이저가 잘못된 실행계획을 선택한다. 인프라를 HDD에서 SSD로 마이그레이션한 후 플랜 회귀가 생기는 흔한 원인이다.

6. PostgreSQL 스토리지 모니터링

-- checkpoint 빈도
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;

-- checkpoint_req가 checkpoints_timed보다 자주 발생하면
-- max_wal_size 또는 checkpoint_timeout 확대 검토

-- 버퍼 히트율
SELECT round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS hit_ratio
FROM pg_stat_database WHERE datname = current_database();

-- VACUUM, autovacuum I/O
SELECT * FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

ClickHouse: 순차 I/O와 mergeTree 병합 중심

ClickHouse는 OLAP 설계로, 대량 sequential write + read가 주 workload다. page cache 활용이 중요하기 때문에 OS page cache를 키우는 전략이 MySQL과 반대다.

<!-- config.xml -->
<uncompressed_cache_size>8589934592</uncompressed_cache_size>  <!-- 8GiB, 자주 읽는 데이터 -->
<mark_cache_size>5368709120</mark_cache_size>                  <!-- 5GiB, primary index(mark file) 캐시 -->
<background_pool_size>16</background_pool_size>               <!-- mergeTree 병합 thread 수 -->
<max_bytes_to_merge_at_max_space_in_pool>161061273600</max_bytes_to_merge_at_max_space_in_pool>

병합이 지연되면 part 수가 너무 많아져 쿼리 성능이 급격히 저하된다. system.parts 테이블에서 active part 수를 모니터링한다.

-- part 수 모니터링
SELECT table, count() AS parts, sum(rows) AS rows, formatReadableSize(sum(bytes)) AS size
FROM system.parts
WHERE active = 1
GROUP BY table ORDER BY parts DESC;

part 수가 특정 테이블에서 수천 개 이상으로 늘어나면 병합이 따라가지 못하는 신호다. background_pool_size 확대 또는 insert batch 크기 증가가 도움이 된다.

ClickHouse는 멀티 디스크 tiered storage도 지원한다. 최신 데이터는 NVMe, 오래된 데이터는 HDD나 오브젝트 스토리지로 이동하는 설정을 storage_configuration에서 관리한다.

Redis: in-memory 이지만 스토리지 설정이 있다

Redis는 인메모리 스토어지만, 영속성(persistence) 설정에 따라 스토리지에 쓰는 방식이 달라진다.

RDB (snapshot)

save 3600 1       # 3600초마다 1개 이상 변경되면 snapshot
save 300 100      # 300초마다 100개 이상 변경
save 60 10000     # 60초마다 10000개 이상 변경
rdbcompression yes

BGSAVE는 fork 후 자식 프로세스가 디스크에 전체 dataset을 쓴다. 데이터셋이 크면 fork시 COW(Copy-on-Write) 때문에 메모리 사용량이 순간 2배에 달할 수 있다.

AOF (Append-Only File)

appendonly yes
appendfsync everysec      # 초당 1회 fsync (성능과 안전성 균형)
# appendfsync always      # 매 write마다 fsync (가장 안전, 성능 저하)
# appendfsync no          # OS에 맡김 (가장 빠름, 유실 가능)

no-appendfsync-on-rewrite yes   # AOF rewrite 중 기존 fsync 생략 (I/O 절약)
aof-rewrite-incremental-fsync yes

OS 설정: Redis가 요구하는 것

# Redis 공식 문서 권장
echo never > /sys/kernel/mm/transparent_hugepage/enabled
# THP는 Redis에서 latency spike를 만들 수 있다

sysctl vm.overcommit_memory=1
# 0(기본): commit 실패 가능, BGSAVE fork가 실패할 수 있음
# 1: 항상 허용, Redis BGSAVE/fork가 안전하게 메모리 요청 가능

검증: 변경 전후를 비교하는 방법

파라미터를 바꾼 후 "느낌이 좋아진 것 같다"로 끝내면 안 된다. 전후 비교 측정이 필요하다.

단계 1: baseline 먼저 측정한다

변경 전에 fio, sysbench, 또는 실제 DB query 지표를 저장한다.

# I/O baseline
fio --name=baseline --filename=/mnt/data/fio-test \
  --size=20G --direct=1 --ioengine=libaio \
  --rw=randrw --rwmixread=70 --bs=8k \
  --numjobs=4 --iodepth=32 \
  --runtime=300 --time_based --group_reporting \
  | tee baseline-$(date +%Y%m%dT%H%M).log

단계 2: DB 내부 지표 캡처

-- MySQL: 변경 전 InnoDB 상태 스냅샷
SELECT * FROM information_schema.INNODB_METRICS
WHERE STATUS = 'enabled'
INTO OUTFILE '/tmp/innodb-before.tsv';

-- PostgreSQL: 변경 전 bgwriter 지표
SELECT now(), * FROM pg_stat_bgwriter;

단계 3: 변경 적용 → 같은 측정 반복

파라미터를 바꾼 후 최소 30분 이상 안정화를 기다리고 같은 fio 명령을 실행한다. DB 내부 지표는 피크 시간대를 포함해 하루 이상 비교하는 것이 좋다.

단계 4: 되돌리기 플랜

DB 파라미터 변경은 대부분 동적으로 가능하다.

-- MySQL 동적 변경
SET GLOBAL innodb_io_capacity = 4000;

-- PostgreSQL 동적 변경
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

-- 재시작 필요 파라미터 확인
SELECT name, setting, pending_restart FROM pg_settings
WHERE pending_restart = true;

재시작이 필요한 파라미터는 maintenance window에서 변경하고, 변경 전 설정을 반드시 기록한다.

운영 체크리스트: 데이터베이스별 스토리지 설정 검토 순서

OS 공통

MySQL / InnoDB

PostgreSQL

ClickHouse

Redis

References