[Real MySQL 8.0] InnoDB 스토리지 엔진 알아보기 - 2편 (Buffer Pool - in memory structures)

🌱 들어가기 전

이번 포스팅에서는 InnoDB의 전반적인 아키텍처 중에서 메모리 영역에 대해서 알아보자.
상당히 복잡하기 때문에 대략적인 내용만 이해하고 넘어가도 충분하다고 생각한다.
 

---

 

🌱 InnoDB 버퍼 풀 (Buffer Pool)

위 그림은 MySQL 8.0 기준 InnoDB 구조를 나타낸 그림이다.
이중에서 버퍼 풀의 경우 InnoDB 스토리지 엔진의 가장 핵심적인 부분이다.
디스크의 데이터 파일, 인덱스 정보를 메모리에 캐시도 해둘 수 있고, 쓰기 작업을 지연시켜서 일괄 처리도 가능하다.

💡 그렇다면 크기는 어느 정도로 설정해야 할까?
보통 OS 전체 메모리 공간이 8기가 이하라면 50% 정도만 버퍼 풀로 설정하고, 나머지는 다른 프로그램이 사용할 공간으로 남겨두는 것이 좋다. 만약 메모리 공간이 더 크다면 조금씩 늘려가면서 최적점을 찾아나가는 것이 좋다.
50기가 이상이라면 대략 15~30기가 정도만 제외하고 나머지는 버퍼풀에게 양보해주자. 
공식 문서에서는 최대 80%가 할당된다고는 말하고 있다.

또한, 이미 늘어나있던 풀의 크기를 줄이는 건 영향이 매우 크기 때문에 주의해야 한다.
 
innoDB 버퍼 풀은 여러 개로 쪼개어 관리할 수 있게 되었는데, 쪼개진 각각을 '버퍼 풀 인스턴스'라고 부른다.
기본적으로 8개로 초기화되어 있지만, 만약 버퍼 풀의 크기가 1기가 미만이라면 1개만 생성된다.
보통 40기가 이하라면 8개, 그 이상이라면 인스턴스당 5기가 정도로 잡고 개수를 할당하는 것이 좋다.
 

show variables like '%innodb_buffer%'

궁금해서 나도 한 번 조회해봤는데 다음과 같았다.

위의 값을 1024로 2번 나눈 값이 메가바이트인데, 딱 128MB이다. (기본값)
참고로 이 값은 항상 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances과 같아야 한다.

확인해보면 현재 인스턴스가 1개여서 chunk_size 값이 그대로 size로 배정받은 것을 볼 수 있다.
 

---

 

🌱  버퍼 풀의 구조 - Free / Flush / LRU List

기본적으로 innoDB는 '버퍼 풀'이라는 거대한 메모리 공간을 페이지 크기 조각으로 쪼개서, 필요할 때 해당 데이터 페이지를 읽어 각 조각에 저장한다. 이때, 페이지 크기 조각을 관리하기 위해 LRU / Flush / Free List 3개의 자료 구조를 관리한다.

LRU 리스트는 LRU 정책에 의해서 교체된 페이지들의 목록을 관리하는데, 밑에서 자세하게 알아보자.
 
 

💬 Flush 리스트

Flush 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지 (dirty page)의 변경 시점 기준의 페이지 목록을 관리한다. 만약, 디스크에서 읽은 상태에서 변화가 없다면 Flush 리스트에서 관리되지 않지만 특정 시점이 지나게 되면 디스크로 기록이 되어야 한다.
기본적으로 데이터가 변경되면 InnoDB는 Redo 로그에 기록하면서, 버퍼 풀의 데이터 페이지에도 해당 내용을 반영한다. 하지만, 항상 Redo 로그가 기록되었다고 데이터 페이지에도 반영되었음을 보장할 수 없기 때문에 '체크 포인트'를 발생시켜 두 상태를 동기화한다. 이때 동기화를 위해서 사용하는 게 Flush 리스트라고 생각하면 된다.
 
 

💬 Free 리스트

Free 리스트는 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록으로, 사용자 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용된다.
 
 

💬 LRU 리스트

가장 중요한 LRU 리스트에 대해서 자세히 알아보자.

LRU 리스트의 경우 LRU + MRU 리스트가 결합된 형태로, 위 그림에서 Old 서브 리스트 영역은 LRU, New 서브 리스트 영역은 MRU라고 생각하면 된다. 자주 접근되는 데이터 페이지를 New 서브리스트에 적재하고, 자주 접근되지 않는 데이터 페이지를 Old 서브리스트에 저장하는 것이다. 이는 한 번 디스크로부터 읽어온 페이지는 최대한 오랫동안 버퍼 풀이 관리하도록 해서 디스크로부터 다시 읽어오는 작업을 최소화하기 위해서이다.

💡 LRU (Least Recently Used)
가장 오랜 시간 동안 참조되지 않은 페이지를 교체하는 정책

💡 MRU (Most Recently Used)
가장 최근에 사용된 페이지를 교체하는 정책

여기서 New 서브 리스트의 Tail과 Old 서브 리스트의 head가 만나는 지점이 버퍼 풀 전체의 'MidPoint'라고 할 수 있으며, InnoDB가 새로운 데이터 페이지를 버퍼 풀로 읽어올 때 midPoint에 삽입하게 된다. (Old 서브리스트의 Head에 삽입)
 
만약 Old 서브리스트에 존재하는 페이지를 접근하면 해당 데이터 페이지는 'Young'이라고 판단되며, New 서브리스트의 head로 이동한다. 이때, 사용자가 작성한 쿼리나 실제로 필요해서 접근한 페이지는 바로 young이 되지만, read-ahead에 의해서 자동으로 읽어오는 데이터의 경우 young으로 판단되지 않는다.

💡 Read-Ahead
사용될 것 같은 데이터 페이지에 대해 버퍼 풀로 동기화하는 I/O request를 의미한다. 이때, 비동기로 동작한다.
하나의 extent (64개의 페이지 그룹) 전체를 버퍼 풀에 prefetch 하게 되는데, 2개의 알고리즘을 사용한다.
- linear read-ahead / random read-ahead.
- 버퍼 풀 내에서 순차적으로 읽혀진 페이지의 개수로 판단하거나, 버퍼풀 내에 존재하는 페이지의 개수로 판단하거나.

이러한 과정을 반복하면서, 버퍼풀에 있는 데이터 중에서 자주 접근되지 않으면 young이 되는 페이지가 앞으로 이동하면서 자연스럽게 tail로 이동하게 된다. 또한, Old 서브리스트에 있는 페이지들은 새로운 페이지들이 계속 midPoint에 삽입되다 보니까 결국 tail 쪽으로 가까워지게 되고, 계속 접근되지 않으면 결국 삭제되는 것이다.
 
 

💬 InnoDB에서 데이터 찾는 과정

1. 필요한 레코드가 저장된 페이지가 버퍼 풀에 존재하는지 검사한다.
- 어댑티브 해시 인덱스 / 테이블의 인덱스를 활용하여 버퍼 풀에서 페이지를 검색한다.
- 버퍼 풀에 이미 데이터 페이지가 존재한다면 해당 페이지의 포인터를 New 서브리스트 쪽으로 승급시킨다.

💡 어댑티브 해시 인덱스 (Adaptive Hash Index)
자주 사용되는 컬럼을 해시로 정의하여 B-Tree를 타지 않고 바로 데이터로 접근할 수 있는 기능이다.
어댑티브 해시 인덱스의 경우 Innodb_buffer_pool_size의 1/64 정도의 크기로 초기화된다.

 
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 Old 서브리스트의 헤더 부분에 추가한다. (midPoint 삽입)
 
3. Old 서브리스트 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 New 서브리스트 헤더 부분으로 이동한다. 
- 이때, 대량의 읽기 작업의 경우 데이터 페이지가 버퍼 풀에 적재는 되지만, 실제 쿼리상으로는 사용되지 않을 수 있기 때문에 New 서브 리스트의 헤더 부분으로 이동하지는 않는다.
 
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자가 최근에 얼마나 접근했는지에 따라서 Age를 부여받으며, 쿼리에서 오랫동안 사용되지 않는다면 Age가 오래되기 때문에 (Aging) 버퍼 풀에서 제거된다. (Eviction)
- 만약, 버퍼 풀의 데이터 페이지가 쿼리에 의해서 사용되면 나이가 다시 초기화되기 때문에 New 서브리스트의 헤더 부분으로 옮겨진다.
- 결과적으로 최근에 얼마나 접근했는지에 따라서 Old / New 서브리스트로 이동하는 것이며, Old 서브리스트 끝으로 밀려난 데이터는 버퍼 풀에서 제거해서 새로운 데이터 페이지를 적재할 수 있는 빈 공간을 준비하게 된다.
 
5. 필요한 데이터가 자주 접근되는 데이터라면, 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.
 
결과적으로 처음에 한 번 읽힌 데이터 페이지가 자주 사용되면 New 서브리스트에서 계속 살아남게 되고, 거의 사용되지 않는다면 계속 밀려서 Old 서브리스트 끝으로 밀려나다가 버퍼 풀에서 제거된다. 
 

---

 

🌱 Adaptive Hash Index (어댑티브 해시 인덱스)

https://tech.kakao.com/2016/04/07/innodb-adaptive-hash-index/

 
어댑티브 해시 인덱스는 사용자가 자주 요청하는 데이터에 대해서 자동으로 생성하는 인덱스이다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들어서, 해당 인덱스를 기준으로 데이터 페이지를 바로 찾아가는 것이다.
 

해시 인덱스의 경우 인덱스 키 값과 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리되며, 인덱스 키 값은 B-Tree 인덱스의 고유 번호와 실제 값으로 이루어져 있다. 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스는 하나의 해시 인덱스에 저장되어야 하기 때문에 B-Tree의 고유 번호를 통해 겹치지 않도록 만든 것이다.
 
버퍼 풀에 로딩된 페이지 주소를 사용하기 때문에 버퍼 풀에 올라간 데이터에 대해서만 관리된다.
그래서, 버퍼 풀에서 삭제되면 인덱스 정보 역시 함께 제거된다.
= 즉, 어댑티브 해시 인덱스는 버퍼풀에 올라간 데이터에 대해 더 빠르게 접근할 수 있도록 만든 것이다.
 
다음과 같은 상황에서 어댑티브 해시 인덱스는 도움이 된다.

- 디스크의 데이터가 innoDB 버퍼 풀의 크기와 비슷한 경우 (읽기 작업이 많지 않음)
- 동등 비교와 IN 연산을 통한 비교 작업이 많은 경우
- 쿼리가 일부 데이터만 집중되는 경우

 
하지만, 다음과 같은 경우에는 크게 의미있지 않기 때문에 잘 고려해서 사용하자.

- 디스크 읽기 작업이 많은 경우
- 특정 패턴의 쿼리가 많은 경우 (JOIN, LIKE 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

⭐️ 어댑티브 해시 인덱스는 테이블을 삭제하거나 변경할 때 해당 테이블의 모든 데이터 페이지 내용을 어댑티브 해시 인덱스에서 제거해야 하다 보니 이럴 때는 CPU cost가 크다. 잘 고려해서 사용하자.
 

show engine innodb status;

위 사진은 innodb status를 확인했을 때의 결과이다. 현재는 별도의 작업을 하는 게 없어서 0으로 나오지만, 여기서 hash searches는 어댑티브 해시 인덱스를 사용한 결과를, non-hash searches는 사용하지 않은 결과이다. 
 
(hash searches) / (hash searches + non-hash searches) * 100을 했을 때 100%에 가깝다면 어댑티브 해시 인덱스를 잘 사용하는 것이고, 낮다면 비활성화하는 것도 고려해보는 것이 좋다. 비활성화를 하게 되면 버퍼 풀은 더 많은 메모리를 사용할 수 있을 것이다.

# 어댑티브 해시 인덱스의 메모리 사용량
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME='memory/innodb/adaptive hash index'

 

---

 

🌱 Change Buffer (체인지 버퍼)

체인지 버퍼는 특정 데이터 페이지가 버퍼 풀에 없을 때 secondary-index 페이지에 대한 변경 사항을 캐시하는 특수한 데이터 구조이다.
 
Secondary-index의 경우 유니크하지 않고, 임의의 순서로 삽입된다. insert, update 작업 시 데이터 파일뿐만 아니라 인덱스도 함께 업데이트하면서 랜덤하게 디스크를 읽어야 하기 때문에 비용이 많이 들게 된다. 이를 위해 변경해야 하는 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와서 업데이트해야 하다면 즉시 실행하지 않고 체인지 버퍼에 저장했다가 사용자에게 결과를 바로 반환하도록 최적화가 되어 있다. 
 
⭐️ 단, 유니크 인덱스의 경우 중복 체크를 해야 하므로 체인지 버퍼를 사용할 수 없다.
체인지 버퍼에 저장된 인덱스 레코드는 백그라운드 스레드 중 '체인지 버퍼 머지 스레드 (Merge Thread)'에 의해서 병합된다.
innodb_change_buffering이라는 변수를 통해서 어떤 작업인지에 따라 체인지 버퍼를 활성화할 수 있다.

- all: 모든 인덱스 관련 작업 (inserts, deletes, purges) 버퍼링
- none: 버퍼링 X
- inserts: 새로운 아이템을 추가하는 작업만 버퍼링
- deletes: 기존 아이템을 삭제하는 작업만 버퍼링 (영구 삭제가 아닌, 삭제됐다고 마킹만 함)
- changes: inserts + deletes만 버퍼링
- purges: purges - 영구 삭제 작업만 버퍼링

 
체인지 버퍼의 경우 버퍼 풀의 25%~50%까지 사용할 수 있으며, 어느 정도 사용 중인지는 아래와 같이 확인할 수 있다.

select EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
from performance_schema.memory_summary_global_by_event_name
where EVENT_NAME='memory/innodb/ibuf0ibuf';

---

 

🌱 Log Buffer (로그 버퍼)

로그 버퍼는 디스크의 로그 파일에 기록할 데이터를 보관하는 데이터 영역이다.
대부분의 데이터베이스 서버는 데이터의 변경 내용을 먼저 로그로 기록하는데, 이는 디스크에 바로 기록하는 것이 상당한 비용이 들기 때문이다. 또한, 리두 로그에 작성하면 비정상 종료 시 복구도 가능하기 때문에 상당히 중요하다.
 
흔히 비정상 종료된 이후, 일관되지 않은 데이터는 2종류로 나뉜다.
- 커밋은 됐지만 데이터 파일에 기록되지 않음
- 롤백은 됐지만 데이터 파일에 이미 기록이 되어버림
 
1번의 경우 리두 로그에 저장된 걸 기록하면 되고, 2번의 경우 변겨오디지 않은 데이터를 가져와서 기록해야 한다.
당연히 데이터의 일관성을 위해서라면 트랜잭션이 커밋될 때마다 리두 로그를 즉시 디스크에 기록하는 게 좋지만, 비용이 큰 디스크 작업 상 부하가 일어날 포인트이다. 이를 위해 innodb_flush_log_at_trx_commit 시스템 변수를 통해서 어느 정도 주기로 동기화할지 설정할 수 있다.

0: 1초에 한 번씩 리두 로그를 디스크로 기록 후 동기화 
1: 트랜잭션이 커밋될 때마다 디스크로 기록 후 동기화 (기본값)
2: 트랜잭션이 커밋될 때마다 디스트 기록은 되지만, 동기화는 1초에 한 번씩

 
Log Buffer의 경우 디스크 영역에 있는 Redo Log와 밀접한 관계를 가지기 때문에 자세한 건 다음 포스팅에서 알아보자.
 

---

 

🌱 버퍼 풀 플러시 

dirty page를 디스크에 동기화하는 것을 더티 페이지 플러시라고 한다.
InnoDB에서는 플러시 시 성능 문제를 고려하여 2가지의 방법을 제시하고 있는데, '플러시 리스트 플러시(Flush-List Flush)'와 'LRU 리스트 플러시 (LRU-List Flush)가 있다.

 
 
💬 플러시 리스트 플러시

InnoDB는 리두 공간의 재활용을 위해서, 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비운다. 이때, 디스크로 동기화되는 작업이 꼭 선행되어야 하는데 Flush_List 함수를 호출해서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 해당 작업은 '클리너 스레드 (Cleaner Thread)'를 통해서 이루어진다.
 
이를 위해 다양한 시스템 변수들을 제공하는데, 이는 다음과 같다.

- innodb_page_cleaners
: 클리너 스레드의 개수를 조정한다. 버퍼 풀의 개수보다 많으면 innodb_buffer_pool_instances 값으로 변경된다.
더 적다면 하나의 클리너 스레드가 여러 개의 버퍼 풀을 처리하게 된다. (웬만하면 동일하게 하자.) 기본값은 4이다.

- innodb_max_dirty_pages_pct
: 버퍼 풀이 더티 페이지를 몇 퍼센트나 가지고 있을지 비율 설정 (클수록 여러 번의 디스크 쓰기를 한 번으로 최적화 가능)
최대 90%까지 가지고 있을 수 있다.

- innodb_max_dirty_pages_pct_lwm
: 디스크로 작성되는 페이지보다 버퍼 풀에 더 많은 더티 페이지가 존재하게 되면, 더티 페이지를 모두 디스크 페이지에 작성하기 위해서 디스크 쓰기 작업이 폭발할 수 있다. (즉, pages_pct로 정의한 임계값까지 도달하지 않도록 만드는 변수) 이를 막기 위해 일정 수 이상의 더티 페이지가 생기면 조금씩 디스크로 기록하는 '사전 플러시' 비율을 조정한다. 기본 값은 10%이다. 

- innodb_io_capacity
: 일반적인 상황에서 어느 정도의 디스크 읽고 쓰기가 가능한지 설정. 여기서 말하는 디스크 작업은 '백그라운드 스레드'가 수행하는 작업이며, 대부분 더티 페이지의 쓰기 작업이 해당한다.

- innodb_io_capacity_max
: 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지 설정.

- innodb_flush_neighbors
: 디스크 기록 시 디스크에서 근접한 페이지에 더티 페이지가 있다면 함께 묶어서 기록하는 기능을 활성화할지 설정.
-이면 비활성화 (SSD 권장), 1이면 활성화 (HDD 권장), 2면 연속된 더티 페이지를 플러시하는 게 아닌, 그냥 하나만 플러시.

- innodb_lru_scan_depth
버퍼 풀 인스턴스마다 클리너 스레드가 더티 페이지 플러시를 위해 버퍼 풀 LRU 리스트를 얼마나 깊게 스캔할지 설정.
innodb_lru_scan_depth * innodb_buffer_pool_instances가 클리너 스레드가 1초에 수행하는 작업량을 정의한다.

- innodb_adaptive_flushing
: 어댑티브 플러시 활성화 여부 설정. 활성화 시 capacity, capacity_max 같은 시스템 변수 값을 의존하지 않고, 별도의 새로운 알고리즘을 사용하여 적절한 수의 더티 페이지가 버퍼 풀에 유지되도록 디스크 쓰기 작업을 수행한다.
버퍼 풀의 더티 페이지 수와 리두 로그 레코드의 생성 속도를 바탕으로 어느 정도의 더티 페이지를 플러시할지 알아서 결정한다.

- innodb_adaptive_flushing_lwm
: 활성 리두 공간이 n% 미만일 때 어댑티브 플러시를 활성화 할지 적용하는 값. 이 임계값을 넘게 되면 innodb_adaptive_flushing이 비활성화되어 있더라도 알아서 어댑티브 플러시를 적용하여 활성화한다.

 

 
💬 LRU 리스트 플러시

LRU 리스트에서 사용 빈도가 낮으면 Old 서브리스트에서 밀려나다가 플러시되는 게 기억날 것이다. 이때 사용하는 게 LRU 리스트 플러시이며, innodb_lru_scan_depth 변수에 설정된 개수만큼 페이지를 스캔하며 더티 페이지를 디스크에 동기화한다.
 
플러시된 더티 페이지들의 프레임을 비운 다음, free 페이지로 만들어서 Free List에 삽입한다. 이때, 버퍼 풀 인스턴스별로 최대 설정한 개수만큼 스캔하기 때문에 innodb_buffer_pool_instances * innodb_lru_scan_depth 수만큼 수행된다.
 

---

 

🌱 버퍼 풀 상태 백업 및 복구 

디스크의 데이터 버퍼 풀에 적재되어 있는 상태를 '워밍업'이라고 하는데, 잘 워밍업되어 있다면 당연히 쿼리 속도가 매우 증가한다.
하지만, 서버를 재시작하는 경우 적재되어 있는 게 없으니 처음에는 요청 속도가 매우 느리게 된다. 하지만, MySQL 5.6부터는 재시작 시 innodb_buffer_dump_now라는 변수를 통해서 현재의 버퍼 풀 상태를 백업할 수 있다. 백업 후 innodb_buffer_dump_load_now를 통해서 다시 복구가 가능하다!

# 백업하기
SET GLOBAL innodb_buffer_pool_dump_now = ON;
# 로드하기
SET GLOBAL innodb_buffer_pool_load_now = ON;

백업 시에는 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문에 실제 용량은 그렇게 크지 않으며 (ib_buffer_pool 파일 확인), 백업 속도도 빠르다. 하지만, 다시 로드할 때는 적재 작업을 재진행해야 하다 보니까 시간이 좀 걸릴 수 있다. 진행 상황을 확인하고 싶다면 아래와 같이 입력하자.

# 얼마나 복구되었는지 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G

만약 너무 오래 걸려서 참을 수 없다면 멈추자.

SET GLOBAL innodb_buffer_pool_load_abort = ON;

 
근데 이런 걸 수동으로 하는 건 쉽지 않다.
그래서 MySQL 서버가 셧다운 되기 전에 백업하고, 시작 시 자동으로 복구하도록 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup을 설정 파일에 넣어두자. (기본적으로 활성화가 되어 있긴 하다 ㅎㅎ)
 
그렇다면, 버퍼 풀에는 어떤 내용이 적재되어 있을까?
아래는 테이블의 인덱스별로 데이터 페이지가 얼마나 innoDB 버퍼 풀에 적재되어 있는지를 나타낸다.

select
    it.NAME AS table_name,
    ii.NAME AS index_name,
    ici.N_CACHED_PAGES AS 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;