[대용량 이관] DB 성능 튜닝 (InnoDB Buffer Pool)

300만 데이터가 있는 테이블의 count만 했을 뿐인데 20초 이상 소요되는 문제가 발생했다.

[MariaDB 최신 버전으로 컨테이너 생성]만 하고 그 외 설정을 하지 않았기 때문에 설정을 추가해보려고 한다.

 

---

 

.cnf 설정

# InnoDB 설정
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_file_per_table = 1

 

항목	설명
innodb_buffer_pool_size	테이블, 인덱스, row 데이터를 메모리에 캐싱	데이터를 디스크에서 읽지 않고 메모리에서 처리 가능 → 조회 속도 대폭 향상
innodb_file_per_table=1	테이블별 독립적인 IBD 파일 사용	테이블 단위의 I/O 최적화와 관리 가능성 증가
innodb_io_capacity	InnoDB가 백그라운드 I/O 작업에 사용할 수 있는 IOPS 수	너무 낮으면 병목 생김. SSD 기반이라면 1000 이상 권장
table_open_cache, thread_cache_size	테이블/스레드 캐싱 설정	테이블 open/close 반복 줄이고, 스레드 재사용 성능 증가
tmp_table_size, max_heap_table_size	임시 테이블 크기 조절	쿼리 결과를 메모리 기반 임시 테이블에 유지해 디스크 접근 줄임

 

docker run -d \
  --name mariadb \
  -e MARIADB_ROOT_PASSWORD=admin \
  -v "[로컬경로]/my.cnf:/etc/mysql/my.cnf:ro" \
  -v mariadb_data:/var/lib/mysql \
  -p 3306:3306 \
  mariadb:11.4.5

수정한 cnf 적용 후 실행

 

 

 

innodb_buffer_pool_size 조정되어 다시 카운트해보니 10초가 줄었다.

 

 

---

 

InnoDB Buffer Pool

디스크의 데이터와 인덱스를 미리 읽어서 메모리에 저장해두는 InnoDB의 캐시 공간

• 데이터를 디스크에서 읽지 않고 메모리에서 처리할 수 있도록 함
• SELECT, INSERT, UPDATE, DELETE 등 모든 작업에서 사용됨
• 디스크 I/O를 최소화해서 성능을 극적으로 향상시킴
  
  

innodb_buffer_pool_size

• InnoDB 전체 성능에 가장 큰 영향을 미치는 변수
• 일반적으로 시스템 메모리의 60~80%까지 사용 가능
• 예: 8GB 메모리 → innodb_buffer_pool_size = 5G ~ 6G

 

버퍼풀 상태 확인

SHOW STATUS LIKE 'Innodb_buffer_pool%';

 

변수명	설명
Innodb_buffer_pool_read_requests	메모리에서 읽으려 한 요청 수
Innodb_buffer_pool_reads	디스크에서 실제 읽은 횟수 (이게 작을수록 좋음)
Innodb_buffer_pool_pages_total	전체 버퍼 페이지 수
Innodb_buffer_pool_pages_free	현재 남은 페이지 수
Innodb_buffer_pool_pages_dirty	변경되었지만 아직 디스크에 기록되지 않은 페이지 수

 

 

요약

항목	설명
Buffer Pool 역할	InnoDB의 데이터/인덱스 캐시
주요 이점	디스크 접근 최소화, 쿼리 속도 개선
크기 설정	시스템 메모리의 60~80%까지 설정 가능
성능 확인	SHOW STATUS 로 캐시 히트율 점검 가능
성능 튜닝 핵심	innodb_buffer_pool_size 및 flush 관련 설정 조정

 

 

---

 

 

Index 설정

하지만 12초라니.. 너무 심하지 않나;;

 

create index book_rating_id_index
    on book_rating (id);

[해당 글]을 보고 primary key인 id 컬럼의 인덱스를 하나 더 추가해봤다.

 

EXPLAIN SELECT COUNT(*) FROM book_rating;

기존 테이블에서 확인해보면 index를 전혀 사용하고 있지 않았다.

인덱스 추가 후 index를 사용해서 스캔하는 것을 볼 수 있다.

 

12s → 240ms 로 줄어들었다!