InnoDB 메타데이터 확인 및 튜닝 기본 실습

실습 목표

• InnoDB 주요 내부 상태 및 메타데이터 직접 조회
• 기본적인 튜닝 항목 파악 및 실습 적용
• 서버 부하를 주지 않고 안전하게 수행 가능

---

1. 환경 준비

항목	설정값
MySQL 버전	8.0 이상 권장
권한	SUPER 권한 또는 PERFORMANCE_SCHEMA 활성화
실습용 데이터	기본 테이블 몇 개 존재하면 충분

---

2. InnoDB 메타데이터 확인

---

2.1 Buffer Pool 상태 확인

Buffer Pool 전체 상태 요약

SHOW ENGINE INNODB STATUS\G

• Buffer Pool Size
• Free Pages
• Dirty Pages
• Pages Read/Write 수

보다 세밀하게 보기

SELECT * 
FROM information_schema.INNODB_BUFFER_POOL_STATS;

 

주요 컬럼 설명

컬럼	의미
POOL_SIZE	총 페이지 수
FREE_BUFFERS	비어 있는 페이지 수
DATABASE_PAGES	데이터가 저장된 페이지 수
PAGES_READ	읽은 페이지 수
PAGES_WRITTEN	쓴 페이지 수

---

2.2 InnoDB 테이블/인덱스 메타정보 확인

SELECT 
    TABLE_NAME, 
    TABLE_ROWS, 
    DATA_LENGTH, 
    INDEX_LENGTH, 
    TABLE_TYPE
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = 'your_database_name'
    AND ENGINE = 'InnoDB';

 

주요 포인트

• DATA_LENGTH: 실제 데이터 크기
• INDEX_LENGTH: 인덱스 크기
• TABLE_ROWS: 대략적인 행 수 (정확치는 않음)

---

2.3 활성 트랜잭션 상태 확인

SELECT * 
FROM information_schema.INNODB_TRX;

• 실행 중인 트랜잭션 목록
• 트랜잭션 시작 시간, 락 걸린 대상 확인

중요

• trx_started가 오래된 트랜잭션은 Undo History List를 늘려서 부하를 줄 수 있음
• Long Transaction 감지 포인트

---

2.4 락 모니터링

SELECT * 
FROM information_schema.INNODB_LOCKS;
SELECT * 
FROM information_schema.INNODB_LOCK_WAITS;

• 데드락 상황이나 락 경합 상태를 확인 가능
• 락 대기시간이 긴 경우 시스템 튜닝 필요 가능성

---

3. InnoDB 기본 튜닝 실습

---

3.1 Buffer Pool 튜닝

현재 설정 확인

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

 

권장 가이드라인

• innodb_buffer_pool_size = 물리 RAM의 70~80% 수준
• 1GB 이상의 경우 innodb_buffer_pool_instances를 2개 이상 설정

예시: my.cnf 수정

[mysqld]
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8

 

반드시 재시작 필요

---

3.2 Redo Log 튜닝

현재 설정 확인

SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

 

권장 가이드라인

• innodb_log_file_size는 최소 512MB 이상 추천
• 트랜잭션 부하가 크면 innodb_log_buffer_size도 128MB 이상 추천

예시: my.cnf 수정

[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=256M

---

3.3 Adaptive Hash Index 튜닝 (선택)

현재 설정 확인

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

 

비활성화 테스트 (성능 경합 이슈 있을 경우)

[mysqld]
innodb_adaptive_hash_index=OFF

 

주의: AHI는 특정 환경에서는 성능을 향상시키기도, 경합을 유발하기도 함.

---

4. 실습 요약 플로우

1. InnoDB 메타데이터 조회 (Buffer Pool, 트랜잭션, 락 상태)
2. 현재 성능/구조 파악
3. 기본 튜닝 항목 설정 변경
4. 변경 후 상태 모니터링
5. Before / After 비교

---

실습 체크리스트

메타데이터 조회 쿼리 모두 실행

Buffer Pool/Redo Log/Undo 설정 점검

my.cnf 파일 백업 후 수정

변경 후 부하 테스트 및 상태 모니터링

튜닝 결과 기록 및 분석