[DB] MYSQL 엔진 아키텍처

MYSQL의 내부 동작과 그 원리에 대해 궁금해져, Real MySQL 8.0 책을 읽었는데 정리를 안하니 계속 내용이 휘발되어서 내 방식으로 정리해보고자 글을 쓴다. 

---

 

1. MYSQL 엔진 아키텍쳐

MySQL 서버는 크게 아래 두 엔진으로 구분할 수 있다. 

1. MySQL 엔진: SQL 분석과 최적화를 담당한다. SQL 파서, SQL 옵티마이저, SQL 실행기 등이 포함된다. 

2. 스토리지 엔진: 디스크에 저장, 조회를 담당한다. 이때, 핸들러 API를 이용해 실제 처리(핸들러 요청)를 수행한다. 

핸들러는 말 그대로 MySQL 엔진이 스토리지 엔진을 조종하기 위해 사용되는 것이다. 

 

MySQL은 아래와 같이 구성되어 있다. 

REAL MYSQL 8.0 1권

 

프로그램은 커넥션 핸들러를 통해 DB와 연결된다. 프로그램이 SQL을 전송하면, MySQL 엔진이 파싱(분석)하고, 전처리 후, 옵티마이즈(최적화), 실행기로 전송한다. 최종적으로 핸들러를 통해 스토리지 엔진이 요청을 수행하게 된다. 

- 쿼리 파서: 쿼리 문장을 MySQL이 인식할 수 있는 최소 단위인 토큰으로 분리 -> 트리 형태의 구조로 생성

- 전처리기: 파서 트리를 기반으로 문장에 구조적인 문제가 있는지 확인, 토큰을 객체에 매핑하며 객체의 권한 등 확인

- 옵티마이저: 쿼리 변환, 비용 최적화, 실행 계획 수립 *InnoDB는 여러 방법 시뮬레이션 후 가장 저렴하고 빠른 계획 선택

- 실행 엔진: 핸들러에게 받은 응답으로 또 다시 요청하며 결과물 생성

 

 

[ 스레딩 구조 ]

MySQL은 스레드 기반으로 작동하는데, 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

*실행 중인 스레드의 목록은 performance_schema의 threads 테이블에서 확인 가능하다. 

 

포그라운드 스레드(클라이언트 스레드)

- 클라이언트와 1:1로 할당되어, 사용자가 요청하는 쿼리 문장 처리 (커넥션 종료시 스레드 캐시로 되돌아감)

- 데이터 버퍼나 캐시로부터 데이터를 가져옴

백그라운드 스레드

- 디스크나 인덱스 파일에서 데이터를 읽어옴

- 로그와 버퍼풀의 데이터를 디스크로 기록 (write thread)

- 잠금이나 데드락을 모니터링

- 인서트 버퍼를 병합

 

JVM의 Heap 메모리처럼 스레드간 공유할 수 있는 글로벌 메모리가 있고, Stack 메모리처럼 스레드 간 공유할 수 없는 세션(커넥션)메모리가 있다.

커넥션 메모리: 클라이언트가 쿼리를 처리하는 데에 사용하는 메모리 영역 

- 각 쿼리의 용도별로 필요할 때만 공간을 할당하고, 필요하지 않은 경우에는 메모리 할당하지 않는다. 

- 조인 버퍼, 정렬 버퍼는 쿼리 실행하는 순간에만 할당, 끝나면 해제된다. 

- 네트워크 버퍼, 리드 버퍼는 커넥션이 열려있는 동안 계속 할당된 상태로 존재한다. 

글로벌 메모리: MySQL 서버 시작시 전부 OS로부터 할당되는 메모리 영역

- InnoDB 버퍼풀, 바이너리 로그 버퍼, REDO 로그 버퍼, 테이블 캐시가 해당한다. 

 

2. InnoDB 스토리지 엔진 아키텍처

 

[ 버퍼 풀 : 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간 ]

DB의 대표적인 성능 지표는 쿼리 처리 소요 시간인데, 스토리지 엔진은 성능을 향상시키기 위해 InnoDB 버퍼풀을 사용한다. 대표적인 버퍼풀 사용 예는 쓰기 작업의 지연(버퍼링)이다. 변경된 데이터를 버퍼풀에 작성해두면, 충분히 데이터가 쌓인 뒤 한 번에 디스크의 데이터 파일에 저장한다. 이렇게 변경 쿼리가 디스크에 반영될때까지 기다리지 않아도 되고, 랜덤한 디스크 작업의 횟수 또한 줄일 수 있다. 

 

내부 락 경합을 줄이기 위해 버퍼 풀을 여러개로 쪼개어 관리하도록 개선되어 개별 버퍼 풀 전체(버퍼 풀 인스턴스)를 관리하는 잠금(세마포어) 자체에 경합이 분산되도록 했다. 

 

버퍼풀은 페이지 크기 조각을 관리하기 위해 세가지 자료구조를 사용한다. 

1. LRU(Least Recently Used) 리스트: Old 서브리스트 영역인 LRU과 New 서브 리스트 영역인 MRU(Most Recently Used)이 합쳐진 형태. 이를 통해 자주 읽히는 페이지를 최대한 오랫동안 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화한다. 

2. Flush 리스트: 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다. 즉, 데이터가 변경되면 InnoDB가 변경 내용을 Redo 로그에 기록하고, 버퍼 풀의 데이터 페이지에 변경 내용을 반영한다. 이때 Redo 로그에는 어떤 데이터 페이지에 대한 변경 내용인지 식별자를 함께 기입한다. 

3. Free 리스트: InnoDB 버퍼 풀에서 비어 있는 페이지의 목록. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용한다. 

 

버퍼풀의 데이터 조회 방법

필요한 레코드가 저장된 데이터 페이지가 버퍼풀에 있는지 확인

1. 어댑티브 해시 인덱스 이용해 페이지 검색

2. 없다면 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지 검색

있다면? -> 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급

없다면? -> 데이터가 버퍼 풀에 없었다면, 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재한 페이지에 대한 포인터를 LRU 헤더 부분에 추가

 

버퍼 풀에 상주하는 데이터 페이지는 사용자가 얼마나 최근에 접근했었는지에 따라 나이가 부여되고, MRU와 LRU를 이동한다. 

- 오랫동안 사용되지 않으면, 페이지에 부여한 나이가 오래되고, 결국 LRU의 끝으로 밀려나면 버퍼 풀에서 제거된다. 

- 적재된 데이터 페이지가 실제로 읽히면, MRU 헤더 부분으로 이동한다. 

 

InnoDB의 버퍼 풀은 디스크에서 읽은 상태에서 변경되지 않은 클린 페이지와 함께 변경된 데이터를 가진 더티 페이지를 가지고 있다. 더티 페이지는 디스크와 메모리(버퍼 풀)의 상태가 다르고, 버퍼 풀의 크기는 한정되어 있기에 InnoDB는 주기적으로 Checkpoint 이벤트를 발생시켜 버퍼 풀의 변경 내용(Redo 로그와 버퍼 풀의 더티 페이지)을 백그라운드 스레드를 통해 디스크에 기록한다. 그래서 Checkpoint는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 Redo 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점이 된다. 

 

Redo 로그 또한 크기가 한정되어 있는데, 디스크로 Flush되고 필요없어진 부분은 다른 로그가 재사용하게끔 한다. 즉, 재사용 불가능한 사용중인 공간이 활성 Redo 로그가 된다. Redo 로그 파일 공간은 순환되어 사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN(Log Sequence Number)라고 한다. 좀 더 나아가서 생각하면, 가장 최근 Checkpoint 지점의 LSN이 활성 Redo 로그 공간의 시작점이 된다. 가장 최근 Checkpoint의 LSN과 마지막 Redo 로그 엔트리의 LSN의 차이를 Checkpoint Age라 하는데, 이는 활성 Redo 로그 공간의 크기를 일컫게 된다. Checkpoint가 발생하면, Checkpoint LSN보다 작은 Redo 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화되는 것이다. 

 

Checkpoint 이벤트를 언제 발생시켜서 얼마나 많은 더티 페이지를 한 번에 디스크로 기록(Flush)하느냐가 사용자의 쿼리 처리가 악영향을 받지 않도록 하는 것에 중요하다. 

- Flush 리스트는 기본적으로 전체 버퍼 풀이 가진 페이지의 90%를 더티 페이지의 비율이 넘어서면 10%정도씩 디스크로 기록한다. 시점과 양은 시스템 변수로 조정할 수 있다. 만일 Adaptive flush 기능을 활성화한다면, 기존 설정값에 의존하지 않고 새로운 알고리즘을 사용한다. 리두 로그가 어느 속도로 증가하는지 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다. 

- LRU 리스트는 LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지를 스켄하고, 더티페이지는 디스크에 동기화하고 클린페이지는 Free 리스트로 페이지를 옮긴다. 

 

버퍼 풀이 쿼리의 성능에 매우 밀접하게 연결되어 있는 만큼, 서버를 셧다운했다가 다시 시작하면, 쿼리 처리 성능이 평상시의 1/10도 안나올 수 있다. 그래서 버퍼풀에 데이터를 미리 올려놓는(Warming Up) 과정이 필요한데, 5.6버전부터 버퍼 풀을 백업하고, MySQL 서버 실행 시 백업된 버퍼 풀의 상태를 복구하면 된다. 해당 과정을 자동화할 수도 있다. 

 

버퍼 풀의 메모리에 어떤 페이지들이 적재되어있는지 보기 위해서는 information_schema db의 innodb_cached_indexex 테이블을 사용하면 된다. 이를 통해 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다. 

 

 

[ 체인지 버퍼 ]

데이터 뿐만이 아니라 인덱스도 DB에 페이지 단위(기본 16KB)로 저장된다. 그 말은 즉, 인덱스 페이지도 조회 혹은 수정을 위해선 디스크에서 읽어와야 함을 뜻한다. 특히 Non-Clustering Key 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하기에 테이블에 인덱스가 많으면 해당 작업은 많은 자원을 소모한다. 

 

그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트하지만, 디스크로부터 읽어와서 업데이트해야 한다면(INSERT, DELETE, UPDATE), 즉시 실행 대신 임시 공간인 체인지 버퍼에 저장해두고, 사용자에게 바로 결과를 반환한다. 체인지 버퍼에 임시로 저장된 인덱스 조각은 이후 체인지 버퍼 머지 스레드라는 백그라운드 스레드에 의해 병합된다. 

 

*이런 과정이 비효율적이라고 느껴지면 체인지 버퍼를 사용하지 않게 설정할 수도 있고, 작업의 종류별로 체인지 버퍼를 활성화할 수도 있다. 대표적인 예로 유니크 인덱스 사용시 사용자에게 결과를 전달하기 전 엔덱스의 중복 여부를 체크해야 하기에 체인지 버퍼를 사용할 수 없다.  

 

[ 로그 버퍼와 언두 페이지 ]

로그 버퍼와 버퍼 풀의 언두 페이지는 Redo 로그를 버퍼링할 수 있는 자료구조다.

 

먼저 Redo와 Undo 로그에 대해 알아보자. Redo는 트랜잭션의 4가지 요소인 ACID 중에서 Durable에 해당하는 데이터 영속성을 위한 것으로 물리적 복구에 쓰인다. DBMS는 일반적으로 데이터 쓰기보다 읽기 성능을 고려한 자료구조를 갖는다(ex. 클러스터링 인덱스). 일반적으로 데이터 파일 쓰기에는 디스크의 랜덤 액세스가 필요하기에 쓰기에 상대적으로 큰 비용이 필요하다. 이를 위해 Redo 로그를 메모리에 버퍼링 해 놓은 후, Checkpoint 이벤트를 통해 디스크에 기록한다.

*시스템 변수를 통해 트랜잭션 Commit 시 즉시 기록되도록 설정할 수 있는데, 이를 통해 Commit 시점의 데이터의 영속성을 더욱 견고히 지킬 수 있다. 많은 부하를 유발하기에 innodb_flush_log_at_trx_commit 시스템 변수를 통해 디스크로 동기화하는 주기를 조정할 수 있지만, Commit 시점에 항상 동기화 되도록 설정함을 권장한다.

 

Undo는 데이터 일관성을 위해 어떤 트랜잭션이 어떤 버전의 데이터를 읽어야하는지 알려준다. 그래서 트랜잭션에 대응되도록 Undo가 존재하고, 이는 데이터가 변경되는 그 직후 실시간으로 디스크에 기록된다. 

 

[ 어댑티브 해시 인덱스 ]

어댑티브 해시 인덱스는 일반적인 의미인 사용자가 수동으로 테이블에 생성하는 B-Tree 인덱스가 아닌, InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터 대해 자동으로 생성하는 인덱스이다. 이를 통해 B-Tree의 검색 시간을 줄일 수 있다. 

 

일반적으로 B-Tree 인덱스에서 특정 값을 찾기 위해서는 B-Tree의 루트 노드를 거쳐서 브랜치 노드, 그리고 최종적으로 리프 노드까지 찾아가야 결국 레코드를 읽을 수 있다. 이런 작업을 동시에 몇천 개의 스레드로 실행하면 컴퓨터의 CPU는 엄청난 프로세스 스케줄링을 하게 되고, 쿼리의 성능은 떨어질 수 있다. 

 

InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 key값(B-Tree 인덱스의 고유 번호)와 해당 페이지의 key값이 저장된 데이터 페이지의 '메모리 주소'의 쌍을 이용해 해시 인덱스를 만든다. 이를 통해 필요할 때마다 인덱스 ID를 통해 어댑티브 해시 인덱스를 검색해서 페이지 주소를 통해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. 주의할 점은 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되기에 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보를 사라진다는 것이다. 

 

중요한 건, 데이터 페이지를 메모리 내에서 접근하는 것을 더 빠르게 만드는 기능이기에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 경우에는 아무런 도움이 되지 않는다. 또한, 데이터가 없을 때에도 조회해야 하고, 테이블을 삭제 혹은 변경하려고 하면 모든 페이지의 내용을 어댑티브 해시 인덱스에서도 제거해야 한다. 이러한 단점이 있으니 해시 인덱스 히트율과 어댑티브 해시 인덱스의 메모리 공간, 서버의 CPU 사용량을 종합해서 판단하자.