List
Search
Preface
•
InnoDB 스토리지 엔진 아키텍처 구성
◦
인메모리 스트럭처
▪
버퍼 풀
▪
체인지 버퍼
▪
어댑티브 해시 인덱스
▪
(리두) 로그 버퍼
1. 버퍼 풀의 역할
•
디스크의 데이터 파일 / 인덱스 정보를 메모리에 캐시해두는 공간
◦
데이터가 메모리에 직접 접근 → 처리 속도 향상
•
쓰기 작업을 지연시켜 일괄작업을 처리할 수 있게 하는 버퍼 역할도 수행
◦
랜덤한 디스크 작업을 일괄 처리하여 랜덤 디스크 작업 횟수를 줄임
2. 버퍼 풀의 크기 설정
2.1. 크기 설정
•
운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해 설정 필요
•
적절히 작은 값을 설정하여 조금씩 상황을 봐가면서 증가시키는 방법이 최적
1.
운영체제의 전체 메모리 < 8GB
•
50%를 버퍼 풀로 설정
•
나머지 메모리 공간은 MySQL 서버, 운영체제, 다른 프로그램 사용 공간으로 확보
2.
운영체제의 전체 메모리 > 8GB
•
버퍼 풀 크기를 메모리 50% 로 시작하여 조금씩 올리며 최적점 찾음
3.
운영체제 전체 메모리 > 50GB
•
15~30GB를 제외한 나머지를 버퍼풀로 할당
•
크기 설정
◦
innodb_buffer_pool_size 시스템 변수로 동적으로 설정 가능
◦
버퍼풀은 128MB 청크 단위로 쪼개서 관리됨
2.2. 버퍼 풀 인스턴스
•
버퍼풀을 여러개로 분리하여 관리
◦
버퍼풀 전체를 관리하는 잠금(세마포어)로 인한 내부 잠금 경합 유발 이슈 개선
•
innodb_buffer_pool_instance 로 설정 가능
•
기본적으로 8개로 설정
•
메모리 여유가 있다면 버퍼풀 인스턴스당 5GB 정도가 되게 인스턴스 갯수 설정
3. 버퍼 풀의 구조
•
버퍼풀 메모리 공간을 페이지 크기(innodb_page_size )의 조각으로 쪼개어 관리
•
버퍼풀 페이지 크기 조각 관리를 위한 자료 구조
◦
LRU(Least Recently Used) 리스트
◦
플러시(Flush) 리스트
◦
프리(Free) 리스트
3.1. LRU(Least Recently Used) 리스트
•
LRU(Least Recently Used) 리스트
◦
LRU와 MRU 리스트가 결합된 형태
◦
Old 서브리스트 영역: LRU
◦
New 서브리스트 영역: MRU
3.2. 플러시(Flush) 리스트
•
정의
◦
디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지) 목록
◦
데이터 변경이 가해진 데이터는 플러시 리스트에 관리 & 특정 시점에 디스크 기록
•
특징
◦
데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록
◦
버퍼풀의 더티 페이지에도 변경 내용 반영 → 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결되어있음
◦
리두 로그가 디스크로 기록되었다고해서 더티 페이지가 디스크로 기록됐다는 것을 항상 보장하지 않음 (반대경우도 동일)
•
cf) 체크포인트
◦
MySQL 서버가 시작될때, 스토리지 엔진이 리두 로그의 어느부분부터 복구를 실행 해야할지 판단하는 기준점
◦
리두 로그와 더티 페이지의 상태를 동기화
◦
체크 포인트 발생 시, 더티페이지는 디스크에 기록되며 버퍼풀에서 제거
3.3. 프리(Free) 리스트
•
InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 비어 있는 페이지 목록
4. LRU 알고리즘
4.1. 구성과 기본 매커니즘
•
구성
◦
New 서브리스트 영역 (MRU)
◦
Old 서브리스트 영역 (LRU)
•
기본 매커니즘
◦
새로운 페이지를 버퍼풀에 추가할때 가장 오래된 페이지가 제거되며, 새 페이지가 리스트 중간에 추가됨
◦
각 서브리스트 영역은 Head와 Tail을 가짐
◦
디스크로부터 한번 읽어온 페이지를 최대한 오랫동안 버퍼풀의 메모리에 유지하여 디스크 읽기 최소화 하는 것이 목적
4.2. 알고리즘 동작 원리
•
버퍼 풀의 3/8이 Old 서브리스트에 할당
•
LRU 리스트의 중간은 New 서브리스트의 Tail 과 Old 서브 리스트의 Head의 경계
•
InnoDB가 페이지를 버퍼 풀에 읽어들일 때, 처음에는 리스트의 중간(오래된 하위 리스트의 시작)에 삽입
•
오래된 하위 리스트의 페이지에 접근하면 그 페이지는 young 상태가 되어 새 Old 서브리스트의 맨 앞으로 이동
4.3. InnoDB 스토리지 엔진 내 데이터를 찾는 과정
1.
필요한 데이터 페이지가 버퍼 풀에 있는지 검색
a.
InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
b.
해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지 검색
c.
버퍼 풀에 이미 데이터 페이지가 있다면 해당 페이지의 포인터를 MRU 방향으로 승급
2.
디스크에서 필요한 데이터 페이지를 버퍼풀에 적재 → 적재된 페이지의 포인터를 LRU 헤더 부분에 추가
3.
버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4.
버퍼풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여
a.
오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고 버퍼풀에서 제거
b.
버퍼풀에서 페이지가 쿼리에 위해 사용되면 나이는 초기화 되고 다시 MRU 헤더 부분으로 이동
5.
필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
5. 버퍼 풀과 리두(Redo) 로그
5.1. 버퍼 풀과 리두 로그와의 관계
•
InnoDB 버퍼 풀의 용도
◦
데이터 캐시
◦
버퍼링
•
버퍼풀과 리두 로그는 매우 밀접한 관계를 띔
◦
버퍼 풀의 크기를 늘리는 것은 데이터 캐시 기능만 향상
◦
버퍼링 기능 향상을 위해서는 버퍼풀과 리두 로그의 관계 이해 필요
5.2. 데이터 변경의 관점에서 버퍼풀 내 페이지 종류
•
클린 페이지 (Clean Page)
◦
디스크에서 읽은 상태로, 전혀 변경되지 않은 페이지
•
더티 페이지 (Dirty Page)
◦
INSERT / UPDATE / DELETE 명령으로 변경된 데이터를 가진 페이지
5.3. 리두 로그
•
InnoDB 스토리지 엔진에서 리두 로그는 1개이상의 고정 파일을 순환 로그 파일(Circular Log File)로 구성되어 일정 크기로 도달하면 처음부터 다시 리고하는 방식으로 동작
•
전체 리두 로그 파일의 공간을 아래와 같이 구분하여 관리 필요
◦
재사용 가능한 공간
◦
재사용 불가능한 공간
•
활성 리두 로그 (Active Redo Log)
◦
재사용 불가능한 공간
•
LSN (Log Sequence Number)
◦
매번 기록될 때 마다 증가된 로그 포지션
5.4. 체크포인트와 동기화
•
동기화 이슈
◦
더티 페이지는 디스크와 버퍼 풀의 데이터 상태가 다르므로 디스크 기록 필요
◦
더티 페이지는 무한정 버퍼 풀에 남을 수 없음
•
체크 포인트
◦
InnoDB는 주기적으로 체크포인트 이벤트를 발생
◦
리두 로그와 버퍼 풀 더티 페이지를 디스크로 동기화 시킴
◦
발생한 체크포인트 중 가장 최근 포인트 지점의 LSN이 활성 리두 로그 공간의 시작점
◦
체크 포인트가 발생하면 체크 포인트 LSN 보다 작은 리두 로그엔트리와 관련된 더티 페이지는 모두 디스크 동기화 필요
•
체크 포인트 에이지 (Checkpoint Age)
◦
가장 최근의 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이
◦
활성 리두 공간의 크기를 일컫음
5.5. 버퍼 풀과 로그파일 크기 예시
•
기본 전제 조건
◦
리두 로그 파일의 크기: 100MB
◦
평균 리두 로그 엔트리 크기: 4KB
◦
평균 데이터 페이지 크기: 16KB
•
예시 1) InnoDB 버퍼 풀 100GB & 리두 로그 파일 전체 크기 100MB
◦
최대 체크포인트 에이지 100MB
◦
리두 로그 파일 크기 / 평균 리두 로그 엔트리 크기(100MB/4KB)
= 25,600개의 데이터 변경 작업 기록 가능
◦
실제로 데이터 페이지의 일부분만 변경될 수 있기 때문에 이를 고려하면 허용 가능한 전체 더티 페이지의 크기는 약 400MB 수준밖에 안됨
→ 쓰기 버퍼링 효과는 거의 못봄
•
예시 2) InnoDB 버퍼 풀 100MB & 리두 로그 파일 전체 크기 100GB
◦
위 예시와 유사
◦
버퍼풀 크기가 100MB이므로 최대 허용 가능한 더티 페이지 크기는 약 100MB에 불과함
•
최적의 조합은?
◦
버퍼 풀의 크기가 100GB 이하인 경우
▪
리두 로그 파일 전체 크기를 5~10GB 수준으로 설정하여 조금씩 늘려가며 최적값 선택
6. 버퍼 풀 플러시
•
더티 페이지를 디스크에 동기화하는 것
•
MySQL 5.6 버전까지는 쓰기 증폭 현상 발생하였으나, MySQL 8.0 버전 이후로는 발생 X
•
성능상 악영향 없이 디스크 동기화를 위해 2개의 플러시 기능을 백그라운드로 실행
◦
플러시 리스트 (Flush_list) 플러시
◦
LRU 리스트 (LRU_list) 플러시
6.1. 플러시 리스트 플러시
•
InnoDB 는 주기적으로 플러시 함수를 호출해서 동기화 작업 진행
•
InnoDB 버퍼 풀에 더티 페이지가 많으면 디스크 쓰기 폭발 현상이 발생할 수도 있음
•
클리너 스레드
◦
더티 페이지를 디스크로 동기화하는 스레드
시스템 변수 | 설명 |
innodb_page_cleaners | InnoDB의 페이지 클리너 스레드 수를 설정합니다. 페이지 클리너는 버퍼 풀에서 더티 페이지를 디스크로 플러시합니다. 기본값은 4이며, InnoDB 버퍼 풀 인스턴스 수와 동일하게 설정하는 것이 일반적입니다. |
innodb_max_dirty_pages_pct_lwm | InnoDB의 Low Water Mark(LWM)로, 더티 페이지 비율이 이 값보다 낮아질 때까지 백그라운드 플러시를 유도합니다. 디스크 I/O가 발생하기 전에 더티 페이지의 비율을 낮추는 역할을 합니다.
기본값은 10%입니다. |
innodb_max_dirty_pages_pct | 버퍼 풀에서 허용되는 최대 더티 페이지의 비율을 설정합니다. 이 값에 도달하면 더티 페이지가 플러시되어 디스크에 기록됩니다. 기본값은 75%입니다. |
innodb_io_capacity | InnoDB가 디스크 I/O 작업을 처리할 때 사용할 수 있는 최대 I/O 작업 수를 설정합니다. 이 값은 더티 페이지를 플러시할 때 얼마나 많은 I/O를 사용할 수 있는지를 제어합니다. 기본값은 200입니다. |
innodb_io_capacity_max | InnoDB의 최대 I/O 용량을 설정합니다. I/O 부하가 높은 상황에서 InnoDB가 최대한 사용할 수 있는 I/O 작업 수를 제한합니다. 기본값은 2000입니다. |
innodb_adaptive_flushing | InnoDB의 적응형 플러시를 활성화하여, 워크로드에 따라 동적으로 더티 페이지 플러시 속도를 조정합니다. 이 기능은 급격한 워크로드 변동으로 인한 디스크 I/O 병목 현상을 줄이는 데 도움이 됩니다. |
innodb_flush_neighbors | 페이지 플러시 시 이웃한 페이지도 함께 플러시할지 여부를 결정합니다. 1로 설정하면 이웃한 페이지도 함께 플러시하여 디스크 I/O 성능을 최적화합니다. 0으로 설정하면 해당 페이지만 플러시합니다. |
6.2. LRU 리스트 플러시
•
LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 생성
7. 기타
7.1. 버퍼 풀 상태 백업 및 복구
•
MySQL 서버 재시작을 위해 서버 셧다운 전에 버퍼 풀 상태 백업 가능
- // MySQL 서버 섯다운 전에 버퍼 풀의 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;
// MySQL 서버 재시작 후/ 백업된 버퍼 풀의 상태 복구
SET GLOBAL innodb_buffer_pool_load_now=ON;
// 버퍼풀 복구 중지
SET GLOBAL innodb_buffer_pool_load_abort=ON;
SQL
복사
•
버퍼 풀 백업 및 복구 자동화
◦
MySQL 서버 설정 파일에 설정
◦
innodb_buffer_pool_dump_at_shutdown
◦
innodb_buffer_pool_load_at_startup
7.2. 버퍼 풀의 적재 내용 확인
•
information_schema 데이터베이스
◦
innodb_buffer_page
◦
innodb_buffer_page_lru
◦
innodb_cached_indexes
▪
테이블 인덱스 별로 데이터 페이지가 얼마나 InnoDB 버퍼풀에 적재되었는지 확인 가능
SELECT it.name table_name,
ii.name index_name,
ici.n_cached_pages n_cached_pages
FROM information_schema.innodb_tables it
INNER JOIN information_schema.innodb_indexes ii
ON ii.table_id = it.table_id
INNER JOIN information_schema.innodb_cached_indexes ici
ON ici.index_id = ii.index_id
SQL
복사