DB 서버 메모리 32GB인데 innodb_buffer_pool_size가 2G로 잡혀 있었다. 전임자가 그냥 기본값에서 조금만 올려둔거 그대로 썼다가 느리다고 한참 까였는데 범인이 이거였음. my.cnf 튜닝 메모.
[mysqld] innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 8 innodb_log_file_size = 512M innodb_log_buffer_size = 16M innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_file_per_table = 1 innodb_io_capacity = 2000 innodb_read_io_threads = 4 innodb_write_io_threads = 4 query_cache_type = 0
buffer_pool_size = 24G. 전체 메모리의 70~75% 가이드라인. OS 파일 캐시(page cache)랑 mysqld 자체가 쓸 메모리를 빼고 나면 이 정도. 다만 O_DIRECT로 가면 page cache를 우회하기 때문에 OS쪽 캐시에 의존하지 않음. 그래도 백업/레플리카 IO, tmpfs, 커넥션별 버퍼(sort/read/join buffer × 동시 접속) 때문에 여유 있어야 함. 실측으로 mysqld RES 27GB + 시스템 기타 3GB 정도 되어서 안정됐다.
buffer_pool_instances = 8. 5.5에서 추가된 옵션. 풀을 여러 조각으로 쪼개서 LRU/flush mutex 경합을 줄인다. 가이드는 pool_size가 1GB 이상이면 인스턴스 수 × 1GB 이상 되도록. 24GB면 8~16 사이 적당. 16까지 가봤는데 우리 워크로드(OLTP 10:1 read:write)에서 8과 16 차이가 거의 없어서 8로 낮춤.
log_file_size = 512M. 쓰기 워크로드에서 redo log가 너무 작으면 dirty page를 더 자주 flush해야 해서 IO가 튄다. 5.5는 redo log 총 크기를 바꾸려면 안전 종료 → ib_logfile0, ib_logfile1 백업 후 삭제 → 재시작 순서가 필요. 5.6부터는 서버 살린 채 바꿀 수 있다는데 아직 5.5 쓰고 있어서 주말 점검창에 적용. 합계 1GB(512M × 2)면 우리 쓰기량 기준 redo가 한 바퀴 돌기까지 25분 정도 걸린다. 재해 발생 시 crash recovery 시간도 이 사이즈에 비례함.
flush_log_at_trx_commit = 2. 1(default, ACID 완전 준수)에서 2로 내림. 매 커밋마다 로그 버퍼를 OS 파일시스템 캐시에 write만 하고 fsync는 초당 한 번. 서버가 통째로 죽으면 마지막 1초 커밋이 날아갈 수 있음. 금융성 데이터가 아니고 서비스 특성상 재처리 가능한 이벤트라 감수. 실측 쓰기 TPS가 대략 1.7배쯤 뜀.
flush_method = O_DIRECT. 더블 버퍼링(OS page cache + innodb buffer pool) 피한다. 단 O_DIRECT_NO_FSYNC는 ext4에선 위험하니 그냥 O_DIRECT. XFS 파일시스템 쓰는 경우라면 fsync가 journal도 같이 밀어주는 이점이 있음.
query_cache_type = 0. 5.5에선 아직 있지만 멀티 코어 환경에선 global lock이 발목 잡는다. 읽기 쿼리도 invalidation 때문에 lock 경합이 남. 프로파일링으로 Waiting for query cache lock 대기가 눈에 띄길래 그냥 껐다. 6 이후로는 deprecate될 분위기.
설정 바꾸고 재시작 직후에는 오히려 체감 더 느림. 버퍼풀이 비어 있어서 모든 조회가 디스크 히트. 핫데이터 강제 예열 필요. 이런 식.
-- 매출/주문 테이블 선두 1M행 읽어 올리기 SELECT SQL_NO_CACHE COUNT(*) FROM orders FORCE INDEX (PRIMARY) WHERE id BETWEEN 1 AND 1000000; SELECT SQL_NO_CACHE /*+ */ AVG(price) FROM items;
스크립트로 자주 읽히는 테이블 top 10 정도를 연속으로 읽어 풀에 올려두면 10분 안에 안정권. 5.6부터는 innodb_buffer_pool_dump_at_shutdown / innodb_buffer_pool_load_at_startup 옵션으로 재시작해도 워밍업 저장/복원 가능한데 지금 버전은 수동.
한 시간쯤 지켜보니 애플리케이션 평균 응답 300ms → 60ms. slow query 빈도도 확 줄었다. Innodb_buffer_pool_read_requests 대비 Innodb_buffer_pool_reads 비율이 3% → 0.2% 수준이면 일단 만족. 내일은 innodb_io_capacity를 SSD 기준으로 더 올려 볼 예정.
