실습 목표

디스크 IO(읽기/쓰기 부하)를 감소시키는 최적화 실습
MySQL의 Buffer Pool, Redo Log, Checkpoint 정책 튜닝 체험
TPS 향상 + 디스크 IOPS 절감 효과 분석

1. 실습 준비

항목	설정
MySQL 버전	8.0 이상
모니터링	iostat, SHOW STATUS, SHOW ENGINE INNODB STATUS
부하툴	sysbench 또는 수제 SQL 스크립트

2. 현재 IO 상태 및 성능 Baseline 측정

IO 모니터링 (OS 레벨)

iostat -dx 1

디스크당 IOPS, await(latency) 확인
중요한 항목: r/s, w/s, await, util

MySQL 상태

SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

Buffer Pool 미스율(Buffer Miss Rate)
Redo 쓰기량 확인

3. 대량 부하 시나리오 실행 (Before 튜닝)

대량 쓰기/읽기 부하를 발생시켜 디스크 IO 부하를 유발합니다.

예제 부하 SQL

DELIMITER $$

CREATE PROCEDURE heavy_load()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 50000 DO
    UPDATE tps_test
    SET col2 = FLOOR(RAND()*1000)
    WHERE id = FLOOR(RAND()*1000000);
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

실행

CALL heavy_load();

→ TPS, IOPS, Buffer Miss율 기록

4. 디스크 IO 최적화 적용

4.1 Buffer Pool 최적화

문제: Buffer Pool이 너무 작으면 매번 디스크 읽기 발생

조치:

innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=8

서버 메모리의 70~80%까지 Buffer Pool 할당
Instance 분할로 락 경합 최소화

4.2 Redo Log 최적화

문제: Redo Log 파일이 작으면 Checkpoint가 자주 발생 → 디스크 쓰기 부하 증가

조치:

innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_log_buffer_size=512M

Redo Log 파일을 키워서 Checkpoint 빈도를 낮춤

4.3 Checkpoint 정책 튜닝

문제: 트랜잭션 커밋마다 Redo 플러시 → 디스크 IO 과다

조치 (타협형 Durability)

innodb_flush_log_at_trx_commit=2

매 커밋마다 fsync 대신 1초에 1번만 플러시
약간의 데이터 유실 리스크(1초 이내)가 있지만 TPS와 IO는 극적으로 개선

5. 서버 재시작 및 튜닝 적용

MySQL 재시작 필수 (Redo Log 크기 변경 포함)

6. 최적화 후 부하 재실행 (After 튜닝)

다시 CALL heavy_load(); 실행
iostat, SHOW STATUS로 IO, TPS 비교

7. 결과 비교

항목	Before	After
Buffer Pool Size	512MB	6GB
Redo Log Size	256MB	2GB
TPS	예: 800 TPS	2200 TPS
Disk Read (r/s)	2000 r/s	400 r/s
Disk Write (w/s)	1800 w/s	900 w/s
Buffer Pool Miss Rate	10%	0.5%

효과 요약

TPS 2배 이상 증가
디스크 읽기/쓰기 IOPS 50% 이상 감소
Buffer Pool Hit Rate 개선

전체 실습 플로우 요약

1. 현재 IO 및 TPS 측정 (Baseline)
2. 대량 부하 발생 → 성능 기록
3. Buffer Pool / Redo Log / Checkpoint 튜닝 적용
4. 서버 재시작
5. 동일 부하 재실행 → 성능 기록
6. Before vs After 비교 분석

LIST