MySQL Architecture

정윤호·2023년 10월 17일

MySQL 아키텍처

Real MySQL 8.0을 바탕으로 공부한 MySQL Architecture 관련된 내용을 정리해보려고 한다. 따라서 MySQL 8.0을 기준으로 내용을 정리할 예정이다.

MySQL 전체 아키텍처는 다음과 같다. 크게 클라이언트, MySQL 서버, 운영체제 및 하드웨어로 구성되어 있다.

먼저 MySQL 접속을 위한 클라이언트가 있으며 대부분의 프로그래밍 언어에 대해 접속 API를 제공한다. 그리고 MySQL 아키텍처에서 핵심에 해당되는 MySQL 서버가 있다. MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구성되어 있다. MySQL 엔진은 MySQL 서버의 두뇌에 해당되며 클라이언트 접속과 SQL 요청을 처리한다. (MySQL 엔진 설명). MySQL 스토리지 엔진은 MySQL 손발에 해당되며 MySQL 엔진에서 구성한 쿼리 실행 계획에 따라 실제로 데이터를 디스크에 저장하거나 디스크에 저장된 데이터를 읽어오는 역할을 담당한다.

MySQL 스레딩 구조

MySQL은 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분된다.

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

포그라운드 스레드는 주로 각 클라이언트가 요청하는 쿼리 문장을 처리하는 것이 주된 역할이며, 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다. 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하는 스레드는 스레드 캐시로 돌아간다. 만약 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있다면 스레드 캐시에 넣지 않고 해당 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 있도록 유지한다. 이 때 스레드 개수를 일정하게 만들어주는 파라미터는 thread_cache_size이다.

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다. 이 떄 데이터가 버퍼나 캐시에 없는 경우, 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 처리해야 한다. 디스크 쓰기 작업은 InnoDB 테이블의 경우에는 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지 버퍼로부터 디스크로 기록하는 작업은 백그라운드 스레그가 처리한다.(MyISAM의 경우에는 디스크 쓰기 작업까지 포그라운드 스레그가 작업한다.)

백그라운드 스레드

InnoDB의 경우, 다음과 같은 작업들이 백그라운드 스레드로 처리된다.

로그를 디스크로 기록하는 스레드
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
인서트 버퍼를 병합하는 스레드
데이터를 버퍼로 읽어 오는 스레드
잠금이나 데드락을 모니터링하는 스레드

이 중 가장 중요한 역할을 하는 스레드는 로그 스레드와 쓰기 스레드이다. 두 작업은 작업량도 많고 모두 백그라운드로 처리하기 떄문에 두 스레드의 경우 디스크를 최적으로 사용할 수 있도록 잘 설정하는 것이 중요하다.

InnoDB의 경우, 쓰기 작업을 버퍼링해서 일괄 처리하는 기능을 당연히 제공한다. 따라서 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우, 이를 디스크에 저장될 때까지 기다리지 않아도 된다.

메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬(세션) 메모리 영역으로 구분된다.

글로벌 메모리 영역

글로벌 메모리 영역은 MySQL 서버가 시작되면서 운영체제로부터 할당된다. 일반적으로 하나의 메모리 공간만 할당되며, 모든 스레드에 의해 공유된다. 대표적인 영역은 다음과 같다.

InnoDB 버퍼 풀
InnoDB 어댑티브 해시 인덱스
InnoDB 리두 로그 버퍼
테이블 캐시

로컬 메로리 영역

MySQL 서버에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데에 사용하는 메모리 영역으로 세션 메모리 영역이라고도 한다. 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 이러한 로컬 메모리 영역은 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다. 또한 각 쿼리의 용도별로 필요할 떄만 메모리 공간을 할당했다가 필요하지 않을 경우에는 해제하는 경우도 있는데 정렬 버퍼와 조인 버퍼가 그렇다. 대표적인 로컬 메모리 영역은 다음과 같다.

정렬 버퍼
조인 버퍼
바이너리 로그 캐시
네트워크 버퍼

플러그인 아키텍처와 컴포넌트 아키텍처

기존의 MySQL 아키텍처는 플러그인 모델이었으나 8.0 이후의 버전부터는 컴포넌트 아키텍처가 지원된다. 플러그인 아키텍처의 경우, 독립적인 배포 단위가 아니라 MySQL 서버 인터페이스에만 직접적으로 의존하기 때문에 다음과 같은 단점들이 있기 때문에 이를 극복하기 위해 컴포넌트 아키텍처가 등장하게 되었다.

플러그인들끼리 통신 불가능
MySQL에 직접 의존하여 MySQL 변수를 직접 호출하기 때문에 캡슐화 위반

쿼리 실행 구조

MySQL에서 쿼리는 크게 다음과 같은 단계를 거치며 실행된다.

쿼리 요청
(쿼리 캐시)
쿼리 파서
전처리기
옵티마이저
스토리지 엔진

1. 쿼리 요청

사용자가 MySQL 서버에 쿼리를 요청한다.

2. 쿼리 캐시

쿼리 캐시는 SQL 실행 결과를 메모리에 캐싱하는 역할을 한다. 동일 SQL 실행시 이전 결과를 즉시 반환함으로써 빠른 성능을 지원하는 것이 초기의 목적이었다. 하지만 테이블의 데이터가 변경되면 캐싱된 데이터도 변경해줘야 하는데 이 떄 캐시된 데이터에 변경이 일어날 때마다 쿼리 캐시에 접근하는 스레드에 락이 걸리기 때문에 동시 처리에 있어서 성능 저하를 유발하게 된다. 그에 따라 쿼리 캐시는 MySQL 8.0부터는 없어지게 되었다.

3. 쿼리 파서

쿼리 파서에서는 사용자가 요청한 쿼리 문장을 토큰으로 쪼개서 트리 형태의 구조로 만들어 내는데 이러한 트리 형태의 구조를 파서 트리라고 한다. 파서 트리를 만드는 파싱 과정에서 쿼리 문장의 기본적인 문법 오류 체크를 하게 된다.

4. 전처리기

전처리기는 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리의 문장 구조를 검사한다. 이 과정에서 파서 트리의 토큰이 유효한지 검증을 하고 접근 권한 등을 확인한다.

5. 옵티마이저

옵티마이저는 전처리된 파서 트리를 기반으로 SQL 실행을 최적화해서 실행 계획을 수립한다. 최적화 방법에는 크게 두 가지가 있다.

규칙 기반 최적화
- 옵티마이저에 내장된 우선 순위에 따라 실행 계획 수립
비용 기반 최적화
- 작업의 비용과 대상 테이블의 통계 정보를 활용해서 실행 계획 수립

이렇게 옵티마이저가 만든 실행 계획에 따라서 스트리지 엔진을 호출해 레코드를 읽고 쓰게 된다.

6. 스토리지 엔진

스토리지 엔진은 MySQL 엔진이 수립한 실행 계획에 따라 쿼리 실행 요청을 하게 되면 실제로 데이터를 디스크로 저장하고 읽는다. MySQL 엔진의 핸들러 API에 의해 동작하게 된다.

InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL 8.0 이후 버전에서 주로 사용하는 스토리지 엔진으로 레코드 기반의 잠금을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. InnoDB의 구조는 다음과 같다.

InnoDB는 다음과 같은 중요한 특징이 있다.

프라이머리 키에 의한 클러스터링
MVCC -> 잠금 없는 일관된 읽기 가능
Undo Log
Change Buffer
Redo Log
레코드 단위 잠금
InnoDB Buffer Pool
Double Write Buffer
Adaptive Hash Index
데드락 감지
자동화된 장애 복구

PK에 의한 클러스터링

PK에 의한 클러스터링은 PK를 기준으로 데이터를 묶어서 저장한다는 의미인데 쉽게 말해 PK 순서대로 레코드를 정렬해서 디스크에 저장하는 것을 의미한다. InnoDB에서는 PK를 통해 실제 데이터 파일에 접근하며 모든 세컨더리 인덱스는 레코드의 실제 주소 대신에 PK를 논리적인 주소로 사용한다. InnoDB는 PK에 대한 인덱스를 자동으로 생성해주기도 한다. PK 기준으로 순서대로 레코드가 정렬된 상태로 저장되기 때문에 이렇게 묶어서 저장된 데이터에 접근할 때 PK에 의한 레인지 스캔은 매우 빨라지게 된다. 그렇기 때문에 쿼리 실행 계획에서 다른 세컨더리 인덱스보다 PK가 선택될 확률이 높다.

하지만 쓰기 성능은 저하된다. PK값이 바뀌면 그에 따라 레코드의 물리적 순서도 하나씩 바꿔줘야 하기 때문이다.(해당 레코드를 DELETE 후 INSERT) 그럼에도 일반적인 웹서비스는 쓰기보다 읽기 요청이 훨씬 많이 있기 때문에 쓰기 성능을 희생하고 읽기 성능을 얻는 클러스터링을 하는 것이 합리적이다. InnoDB는 PK를 지정하지 않으면 내부적으로 PK를 자동 생성해서 클러스터링을 한다. 내부적으로 생성된 PK는 사용자가 직접 사용할 수는 없다. 따라서 InnoDB에서 테이블을 설계할 때에는 PK 직접 설정하는 것이 좋다.

MVCC(Multi Version Concurrency Control)

InnoDB는 기본적으로 Commit, Rollback과 같은 트랜잭션 처리 기능을 제공한다. 또한 MySQL의 중요한 특징 중 하나인 MVCC 기능을 제공한다. MVCC란 Multi Version Concurrency Control의 약자로, 데이터의 다양한 버전이 동시에 관리되어 트랜잭션 격리 수준에 따라 다르게 조회되는 것을 가능하게 해주는 기술을 말한다.

다음의 예시를 살펴보자. 아래 그림은 데이터베이스의 상태를 나타낸다.

InnoDB 버퍼 풀은 데이터의 상태가 변경됐을 때 빈번한 Disk I/O를 줄이기 위해 디스크에 반영하기 전까지 잠시 버퍼링하는 공간이다. 언두 로그는 변경되기 이전의 데이터를 백업해두는 공간이다. m_id가 '12', m_name이 '홍길동', m_area가 '서울'인 레코드가 있을 때, 이 레코드에 대해 m_area를 '서울'에서 '경기'로 변경하는 UPDATE 쿼리를 날리게 되면, 먼저 버퍼 풀에 있는 데이터를 변화된 상태가 곧바로 반영되며, 이전 값은 언두 로그에 복사된다. 이 때 다른 트랜잭션이 해당 id값을 조회하면 데이터베이스에 설정된 트랜잭션 격리 수준에 따라 일관되게 데이터를 조회할 수 있다.

READ_UNCOMMITTED: 해당 레코드가 버퍼 풀에서 조회
READ_COMMITTED: 언두 로그에 있는 해당 레코드가 조회
REPEATABLE_READ: 언두 로그에 있는 해당 레코드가 조회
SERIALIZABLE: 언두 로그에 있는 해당 레코드가 조회

이처럼 MySQL은 MVCC 기능을 이용해 트랜잭션 격리 수준에 따라 Lock이 필요없는 일관된 읽기가 가능하다.

Undo Log

언두 로그는 변경되기 이전의 데이터를 백업해두는 공간이다. Rollback시 언두 로그에 있는 백업된 데이터를 이용해서 복원이 가능하기 때문에 이를 통해 트랜잭션 보장이 가능하다. 또한 트랜잭션 격리 수준에 맞게 언두 로그에 있는 백업된 데이터를 반환할 수 있기 때문에 트랜잭션 격리 수준에 따른 일관된 읽기가 가능하다.

언두 로그의 사용 공간이 너무 늘어나게 되면 언두 로그가 많이 쌓이게 되어 디스크 사용량이 증가하게 되고, 변경된 데이터에 대해 조회할 때 그만큼 많은 양의 언두 로그를 스캔해야 되기 때문에 성능 저하를 유발하게 된다. 대용량 데이터를 처리하는 트랜잭션과 장기간 활성화된 트랜잭션이 언두 로그 사용 공간이 늘어나는 것을 유발한다. MySQL 8.0 버전부터는 이러한 언두 로그 공간의 문제점이 해결되어 필요한 시점에 언두 로그 사용 공간을 자동으로 줄여주기도 한다.

Change Buffer

체인지 버퍼는 인덱스를 업데이트하는 작업에 대한 버퍼링을 지원하는 공간이다. RDBMS에서 레코드가 INSERT되거나 UPDATE될 경우 데이터 파일을 변경할 뿐만 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다. 하지만 인덱스를 업데이트하는 작업은 랜덤 I/O 작업이 필요하므로 테이블에 인덱스가 많다면 상당한 자원이 소모되는 작업이다. 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하고 그렇지 않다면 인덱스 페이지들의 변화를 체인지 버퍼에만 저장하고 이후 읽기 작업에 의해 페이지가 버버풀에 로드될 때 체인지 버퍼 머지 스레드라는 백그라운드 스레드에 의해 버퍼 풀로 병합된다. 유니크 인덱스에 대해서는 체인지 버퍼를 사용할 수 없다.

Redo Log

리두 로그는 Commit이 완료된 번경된 데이터를 백업하는 공간이다. 이는 서버가 비정상 종료됐을 때 리두 로그 파일에 있는 백업된 데이터를 이용해 데이터 복구를 가능하게 해줌으로써 영속성을 보장해준다. 대부분의 DBMS와 마찬가지로 MySQL도 데이터 변경 내용을 로그로 먼저 기록한다. 그 이유는 보통의 DBMS의 데이터 파일의 경우 쓰기보다 읽기 성능을 고려한 자료 구조를 사용하기 때문에 변경된 데이터를 데이터 파일에 기록하려면 랜덤 I/O가 필요해 연산 비용이 크다. 따라서 쓰기 비용이 낮은 자료 구조를 갖는 리두 로그를 활용한다. 매번 디스크에 기록하면 Disk I/O로 인해 성능 이슈가 있기 때문에 체크 포인트 이벤트 발생 시점에 로그 버퍼에 있는 데이터를 디스크의 리두 로그 파일에 기록한다.

MySQL 서버가 비정상 종료되었을 경우, InnoDB 스토리지 엔진의 데이터 파일에서 일관되지 않은 데이터를 가지는 두 가지 경우 시나리오

커밋됐지만 데이터 파일에 기록되지 않은 데이터 -> 리두 로그 이용
롤백됐지만 데이터 파일에 이미 기록된 데이터 -> 리두 로그 참고, 언루 로그 이용

레코드 단위 잠금

InnoDB는 테이블 단위가 아니라 레코드 단위로 잠금을 걸기 때문에 동시 처리 성능이 좋다. 엄밀히 이야기하면 InnoDB 테이블의 레코드 자체를 잠그는 것이 아니라 B-tree 인덱스 리프 노드인 인덱스 테이블의 인덱스 레코드가 잠긴다.

다음과 같은 employees 테이블이 있는 상황을 가정해보자.

employees 테이블의 총 레코드 수는 1000개
first_name이 'Georgi'인 레코드는 100개 존재
first_name이 'Georgi'이고 last_name이 'Klassen'인 레코드는 1개 존재
first_name 컬럼에는 idx_firstname 인덱스가 걸려있음

> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+-----------+
|       100 |
+-----------+

> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+-----------+
|         1 |
+-----------+

이 때 first_name이 'Georgi'이고 last_name이 'Klassen'인 레코드의 hire_date를 현재 시간으로 UPDATE한다면 어떻게 될까?

> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

이 때 InnoDB 테이블은 해당 레코드 자체에 잠금을 거는 것이 아니라, B-tree 인덱스 리프 노드인 idx_firstname 테이블의 인덱스 레코드가 잠긴다. 따라서 UPDATE를 위해 검색에 사용된 'Georgi' 인덱스 레코드가 모두 잠긴다.

만약 first_name 인덱스가 아예 없었다면 해당 레코드를 찾기 위해 기본으로 생성된 PK 인덱스를 사용해서 테이블 풀 스캔을 하게 된다. 이 경우에도 검색에 사용된 모든 PK 인덱스가 잠기게 되며, 이는 곧 테이블 전체가 잠기게 되는 것을 의미한다.

만약, first_name과 last_name에 대한 복합 인덱스를 생성했다면, 하나의 레코드만 잠기게 된다.

인덱스를 어떻게 설정하느냐에 따라 레코드 잠금 범위가 달라질 수 있으니 InnoDB를 사용할 때는 인덱스를 신중하게 설정하는 것이 좋다.

InnoDB Buffer Pool

InnoDB 버퍼 풀은 InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱해두는 공간이다. 버퍼 풀은 쓰기 작업을 지연시켜 일괄적으로 작업을 처리해주기도 한다. INSERT, UPDATE, DELETE와 같이 데이터를 변경하는 쿼리는 디스크에 존재하는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤 I/O를 발생시키지만, 버퍼 풀이 이러한 변경된 데이터를 한 번에 모아서 처리하면 랜덤 디스크 작업의 횟수를 줄일 수 있다. 이처럼 InnoDB 버퍼 풀은 데이터 캐싱과 쓰기 지연 버퍼 두 가지 기능을 제공하여 MySQL 서버의 성능을 높인다. 버퍼 풀의 크기는 보통 전체 메모리의 50% 정도로 설정해서 조정하면서 최적점을 찾는 것이 좋다.

데이터 캐싱

인덱스 정보와 데이터 파일을 메모리에 캐싱
페이지 단위로 테이블 데이터를 관리
페이지 교체 알고리즘으로 LRU 사용
InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 할수록 데이터 캐시의 성능이 올라가면서 쿼리의 성능이 올라감(메모리 크기만으로 데이터 캐싱의 성능은 올릴 수 있지만 쓰기 지연 버퍼의 성능을 높일 순 없음)

버퍼 풀은 SQL 요청 결과를 일정한 크기의 페이지 단위로 캐싱한다. 운영 체제가 가상 메모리를 효율적으로 사용하기 위해 페이징을 하는 것처럼 InnoDB도 테이블 데이터에 대해 페이징을 한다.

버퍼 풀은 페이지를 관리하기 위해 다음 자료 구조들을 관리한다.

LRU 리스트: LRU 리스트는 LRU 알고리즘에 의해 교체된 페이지들의 목록을 관리한다. 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하기 위한 목적이다.
Flush 리스트: Flush 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록 관리한다. 데이터 변경과 관련된 상태 정보는 버퍼 풀의 플러시 리스트와 리두 로그에 반영되는데 InnoDB 스토리지 엔진은 체크포인트를 발생시켜서 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다.
Free 리스트: Free 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록으로 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용된다.

InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 다음과 같다.

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
- InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
- 해당 테이블의 B-tree 인덱스를 이용해 버퍼 풀에서 검색
- 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU(New 서브리스트) 쪽으로 승급
1. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU(Old 서브리스트) 헤더 부분에 추가
1. 버퍼 풀의 LRU(Old 서브리스트) 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU(New 서브리스트) 헤더 부분으로 이동
1. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했냐에 따라서 Age가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랜 기간 사용되지 않으면 데이터 페이지에 부여된 Age가 오래되고 결국 해당 페이지는 버퍼 풀에서 제거된다. 반대로 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 Age가 초기화되어 MRU(New 서브리스트) 헤더 부분으로 다시 이동
1. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

쓰기 지연 버퍼

변경된 데이터를 버퍼 풀에 모았다가, 한 번에 디스크에 기록
JPA 영속 컨텍스트의 쓰기 지연 SQL 저장소와 유사

Insert, Update, Delete 명령으로 변경된 페이지를 더티 페이지라고 한다. InnoDB는 이 더티 페이지들을 모았다가 주기적으로 이벤트를 발생시켜서 한 번에 디스크에 반영한다. 이렇게 변경된 데이터를 한 번에 모았다가 처리하는 이유는 랜덤 I/O를 줄이기 위해서다. 데이터 캐싱과는 달리 쓰기 지연 버퍼의 성능을 올리기 위해서는 버퍼 풀의 메모리 크기 뿐만 아니라 리두 로그 공간의 크기도 고려해야 한다.

InnoDB에는 클린 페이지와 더티 페이지가 있는데, INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가지고 있는 더티 페이지는 디스크의 실제 데이터와 데이터의 상태가 다르기 때문에 무한정 버퍼 풀에 머무를 수 없고 언젠가는 디스크에 기록이 되어야 한다. 리두 로그의 경우, 1개 이상의 로그 파일을 순환 고리처럼 연결해서 사용하는데 로그마다 LSN(Log Sequence Number)가 있고 이 값을 기준으로 오래되어 덮어써도 될 로그와 그러지 말아야 할 활성 로그를 구분한다. 버퍼 풀의 더티 페이지는 각 리두 로그 엔트리와 관계를 맺고, 체크 포인트 이벤트마다 더티 페이지와 리두 로그가 디스크로 동기화된다. 체크포인트 간의 간격을 체크포인트 Age라고 하는데 이는 리두 로그의 공간을 말한다. 결국 리두 로그 공간만큼 쓰기 지연 버퍼가 가능하다는 의미이다. 그렇기 때문에 버퍼 풀의 크기가 아무리 크더라도 리두 로그 파일의 크기가 너무 작으면 쓰기 지연 버퍼링의 효과를 거의 못 보게 된다. 그렇다고 리두 로그 공간이 무한정 크기가 크다면 버퍼 풀에 더티 페이지의 비율이 매우 높은 상태에서 갑자기 필요해지는 상황이 오면 InnoDB 스토리지 엔진은 엄청난 양의 더티 페이지를 한 번에 리두 로그에 기록해야 하는 상황이 올 수도 있기 때문에 리두 로그 파일의 크기도 적절히 선택하고 조정해주는 것이 중요하다.

버퍼 풀은 아직 디스크로 기록되지 않은 더티 페이지들을 성능 상의 문제가 없이 디스크에 동기화하기 위해 다음과 같이 두 가지 플러시 기능을 백그라운드 스레드를 통해 실행한다.
Buffer Pool Flush

Flush 리스트 플러시
- Flush 리스트에서 오래 전에 변경된 더티 페이지를 순서대로 디스크에 동기화한다.
- 얼마나 많은 더티 페이지를 한 번에 기록하느냐에 따라 성능에 영향을 미칠 수 있고 버퍼 풀에 더티 페이지가 너무 많아지면 Disk I/O Burst가 발생할 수 있다.
- 더티 페이지를 디스크로 동기화하는 스레드, 버퍼 풀에서 유지시키고 싶은 더티 페이지의 비율 등은 설정값을 통해 조정이 가능하다.
LRU 리스트 플러시
- LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 수 있는 공간을 만든다.
- LUR 리스트를 스캔하면서 더티 페이지는 동기화시키고, 클린 페이지는 프리 리스트로 옮긴다.

Double Write Buffer

InnoDB의 리두 로그는 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 더티 페이지를 디스크로 플러시할 때 하드웨어 오작동이나 시스템의 비정상 종료 등으로 인해 일부만 기록되는 문제가 발생하면 그 페이지 내용을 복구할 수 없을 수도 있다. 이러한 현상을 Partial-page, Torn-page라고 한다. 이러한 문제를 방지하기 위한 것이 Double Write Buffer이다.

InnoDB 스토리지 엔진은 더티 페이지를 실제 데이터 페이지 파일에 변경 내용을 기록하기 전에 더티 페이지들을 묶어서 한 번의 디스크 쓰기로 시스템 테이블 스페이스의 DoubleWrite 버퍼에 기록
InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤 쓰기 작업을 실행
하드웨어 오작동이나 시스템의 비정상 종료 등의 문제로 더티 페이지가 실제 데이터 파일에 정상적으로 기록되지 않았다면, InnoDB 스토리지 엔진이 재시작될 때 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사(정상적으로 기록될 경우, DoubleWrite 버퍼의 더티 페이지를 필요없어짐)

이처럼 Double Write Buffer는 데이터의 안정성을 위해 사용되지만 SDD처럼 랜덤 I/O나 순차 I/O의 비용이 비슷한 저장 시스템에서는 부담스럽다. 하지만 데이터의 무결성이 매우 중요한 서비스에서는 Double Write Buffer 활성화를 고려하는 것이 좋다. innodb_doublewrite 시스템 변수를 이용해 활성화 여부를 결정할 수 있다.

Adaptive Hash Index(리두 로그 및 로그 버퍼와 관련)

어댑티브 해시 인덱스는 버퍼 풀 내에서 데이터 페이지에 빠르게 접근하기 위한 해시 자료구조 기반 인덱스로 InnoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. B-tree 인덱스에서 특정 값을 찾기 위해서는 B-tree의 루트 노드를 거쳐서 브랜치 노드, 리프 노드까지 찾아가야 하는데 이런 작업이 동시에 수많은 스레드로 실행하면 컴퓨터의 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리의 성능이 떨어지게 된다. 어댑티브 해시 인덱스는 이러한 B-tree의 검색 시간을 줄여주기 위해 도입된 기능이다.

<인덱스 키, 페이지 주소 값> 쌍으로 구성된다.
- 여기서 인덱스 키는 B-tree 인덱스의 id와 B-tree의 실제 키 값의 조합이다.
버퍼 풀에 올려진 데이터 페이지에 대해서만 관리하고 버퍼 풀에서 데이터가 사라지면 해당 데이터에 대한 어댑티브 해시 인덱스도 같이 삭제된다.

이처럼 쿼리를 좀 더 빠르게 처리할 수 있도록 B-tree 검색 시간을 줄여주는 데에 도움을 주는 어댑티브 해시 인덱스는 특정 상황에서는 메모리 공간만 잡아먹고 크게 도움이 되지 않아 비활성화하는 경우도 있다.

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

해당 명령어를 통해 해시를 사용한 검색과 사용하지 못한 검색으 비율을 비교해서 어댑티브 해시 인덱스의 활성화 여부를 결정하는 것이 좋다.

SHOW ENGINE INNODB STATUS\G

그 밖에도 InnoDB는 자동 데드락 감지, 자동화된 장애 복구를 해주는 특징이 있다.

InnoDB vs MyISAM 비교

MySQL 5.5 부터는 스토리지 엔진 기본이 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택되었고 MySQL 8.0 부터는 시스템 테이블 포함 모든 테이블이 InnoDB 스토리지 엔진을 사용하게 되었다. 그에 따라 MyISAM에서만 지원하던 전문 검색 기능이나 공간 좌표 검색 기능을 InnoDB에서도 제공하게 되었다. MyISAM 스토리지 엔진의 특징을 간단하게 요약하면 다음과 같다.

클러스터링 지원하지 않음
트랜잭션 지원하지 않음
외래키 지원하지 않음
테이블 단위 잠금 -> 동시성 처리에 불리
키 캐시 사용(인덱스 정보만 버퍼링)
전문 검색, 공간 좌표 검색 기능 지원

Reference

백은빈, 이성욱. 『Real MySQL 8.0』. 위키북스, 2021.

정윤호

Grind Hard, Shine Hard

이전 포스트

Docker와 Kubernetes 기본 개념

다음 포스트