실습 목표

innodb_buffer_pool_size를 조정하여
TPS (Transactions Per Second) 성능 변화를 직접 체험
메모리 설정이 실제 서버 성능에 미치는 영향을 이해

1. 기본 준비

항목	설정
MySQL 버전	8.0 이상 권장
부하 툴	sysbench 사용 추천 or 직접 SQL 스크립트 사용 가능
테스트 테이블	데이터가 100만건 이상 존재하는 테이블

테스트용 테이블 예시 (없으면 생성)

CREATE TABLE tps_test (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    col1 VARCHAR(255),
    col2 INT,
    col3 DATETIME
) ENGINE=InnoDB;

-- 데이터 대량 삽입 (100만건 정도)
INSERT INTO tps_test (col1, col2, col3)
SELECT REPEAT('A', 100), FLOOR(RAND()*1000), NOW()
FROM information_schema.columns a, information_schema.columns b LIMIT 1000000;

2. 시뮬레이션 스크립트 설계

Step 1: 현재 Buffer Pool Size 확인

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

(예: 128MB면 너무 작음. 기본은 512MB~1GB 이상 권장)

Step 2: 부하 테스트용 SQL 스크립트

-- TPS 측정을 위한 간단한 트랜잭션
DELIMITER $$

CREATE PROCEDURE simple_load_test()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 10000 DO
    INSERT INTO tps_test (col1, col2, col3)
    VALUES (REPEAT('B',100), FLOOR(RAND()*1000), NOW());
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

실행:

CALL simple_load_test();

(한번 수행하면 약 10,000건 Insert → TPS 측정)

Step 3: TPS 측정 쿼리

수동으로 시간 측정

mysql -u root -p -e "CALL simple_load_test();" > /dev/null

time 명령어를 함께 써서 걸린 시간 측정
TPS 계산: 10000건 / (걸린 초 수)

또는

SHOW GLOBAL STATUS LIKE 'Com_commit';

부하 전후의 커밋 수 차이를 시간으로 나누어 TPS 계산

3. Buffer Pool 크기 변경

변경 예시 (my.cnf 수정)

[mysqld]
innodb_buffer_pool_size=2G

(기존 512MB → 2GB로 변경)

변경 후 서버 재시작 필수

4. 부하 재실행 및 성능 비교

다시 실행

mysql -u root -p -e "CALL simple_load_test();" > /dev/null

다시 걸린 시간 측정 → TPS 재계산

5. 결과 기록 및 비교

변경사항	TPS (Before)	TPS (After)
Buffer Pool Size	예: 512MB	2GB
측정 TPS	예: 500 TPS	1200 TPS

예상 결과

Buffer Pool이 커지면 디스크 읽기(IOPS) 감소
Page Miss 감소 → TPS 상승
CPU Idle 비율 소폭 증가할 수 있음

전체 시나리오 요약

1. 기본 Buffer Pool 상태 확인
2. 대량 트랜잭션 부하 스크립트 실행 → TPS 측정 (Before)
3. Buffer Pool 크기 확대
4. 서버 재시작
5. 동일 부하 스크립트 실행 → TPS 측정 (After)
6. Before/After 비교 및 분석

추가

Buffer Pool을 너무 과하게 키워도 효과가 줄어드는 지점 찾기 (Diminishing Returns 실험)
동시에 Redo Log 크기도 조정하면 더 극적인 효과 관찰
TPS만이 아니라 Page Miss Rate, Buffer Pool Hit Rate도 함께 체크

Buffer Pool Hit Rate 확인

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

공식:

Buffer Pool Hit Rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

LIST