MySQL 아키텍처 자세히 알아보기

MySQL 은 관계형 데이터베이스 관리 시스템(DBMS)으로 오픈 소스이다.
5.5부터 5.7 버전까지는 안정성과 성능 개선에 집중되었다면 8.0 부터는 상용 DBMS가 가지고 있는 기능들이 추가되었다.
MySQL 의 구조에 대해 자세히 알아본다.

MySQL 엔진 아키텍처

MySQL 구조는 다른 DBMS 에 비해 구조가 독특하다. 이러한 구조 때문에 혜택을 누릴 수도 있고 문제가 될 수도 있다.

MySQL 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

MySQL 엔진

커넥션 핸들러
- 클라이언트로부터 접속 및 쿼리 요청을 처리
SQL 파서 및 전처리기
옵티마이저
- 쿼리의 최적화

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 역할을 전담
MySQL 은 하나지만 스토리지 엔진은 여러 개를 동시에 사용 가능

스레딩 구조

mysql threading architecture (출처: Real MySQL 8.0)

MySQL 는 스레드 기반으로 동작
다음 명령어로 실행 중인 스레드 확인 가능
크게 포그라운드(Foreground)와 백그라운드(Background) 스레드로 구분

SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads
ORDER BY type, thread_id;

포그라운드 스레드 (Foreground Thread)

최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재 (요청하는 쿼리 문장 처리)
- 커넥션이 종료되면 담당하던 스레드는 스레드 캐시(Thread cache)로 되돌아감
- 스레드 캐시에 일정 개수 이상의 스레드가 대기 중이라면 종료시켜 일정 개수만 유지 (thread_cache_size 변수로 설정)
데이터를 데이터 버퍼나 캐시에서 가져옴
- 없으면 직접 디스크의 데이터나 인덱스 파일로부터 읽어와서 처리
- MyISAM 테이블: 디스크 쓰기 작업까지 포그라운드 스레드가 처리
- InnoDB 테이블: 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리 (디스크까지 기록하는 작업은 백그라운드 스레드)

백그라운드 스레드 (Background Thread)

InnoDB 의 백그라운드에서 처리되는 작업

인서트 버퍼(Insert Buffer)를 병합하는 스레드
로그를 디스크로 기록하는 스레드
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 쓰기 작업을 버퍼링해서 일괄 처리하는 기능 탑재 (MyISAM 의 경우 사용자 스레드에서 함께 처리)
데이터를 버퍼로 읽어오는 스레드
잠금이나 데드락을 모니터링하는 스레드

메모리 할당 및 사용 구조

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

글로벌 메모리 영역

클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당한다. (필요에 따라 2개 이상도 가능)
모든 스레드에 의해 공유

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

로컬 메모리 영역

세션 메모리 영역이라고도 하며 클라이언트 스레드가 쿼리를 처리하는 데 사용되는 영역
클라이언트 커넥션을 처리하기 위한 스레드에서 사용하는 메모리 공간(독립적으로 할당)

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

쿼리 실행 구조

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리하여 트리 형태의 구조로 형성
기본 문법 오류 발견하고 사용자에게 오류메세지 전달

전처리기

파서 트리를 기반으로 쿼리 문장에 구조적인 문제 검증
- 테이블 이름, 칼럼 이름, 객체 존재 여부, 접근 권한 등 검증

옵티마이저

저렴한 비용으로 빠르게 처리하는 방법을 결정하는 역할 (DBMS 의 두뇌 역할)
- 중요한 역할이며 영향 범위 또한 넓음

실행엔진

만들어진 계획대로 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할

GROUP BY 처리를 위해 임시 테이블을 사용하는 실행 엔진 예시

실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 요청
결과를 사용자나 다른 모듈로 넘김

핸들러 (스토리지 엔진)

데이터를 디스크로 저장하고 읽어오는 역할

InnoDB 스토리지 엔진 아키텍처

innoDB storage engine architecture (출처: Real MySQL 8.0)

프라이머리 키에 의한 클러스터링

InnoDB 의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 순서대로 클러스터링되어 저장
세컨더리 인덱스는 레코드의 주소대신 프라이머리 키의 값을 주소로 이용
- 다른 보조 인덱스에 비해 비중이 높음

외래 키 지원

InnoDB 스토리지 엔진 레벨에서 외래 키에 대해 지원 (MyISAM, MEMORY 는 불가능)
외래 키로 인해 잠금이 여러 테이블로 전파되고 데드락이 발생될 수 있기 때문에 주의
- SET foreign_key_check 로 활성 여부 선택 가능

MVCC(Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 DBMS 기능
주로 잠금을 사용하지 않는 일관된 읽기를 제공하기 위해 사용
UPDATE 문장이 실행되면 커밋 실행 여부와 관계없이 버퍼 풀은 새로운 값으로 변경
- 디스크의 데이터 파일에는 백그라운드 스레드에 의해 업데이트 됐을 수도 있고 아닐 수도 있음
격리 수준에 따라 버퍼 풀이나 데이터 파일에서 읽어올지, 언두 영역에서 읽어올지 다름
롤백하면 언두 영역에 있는 데이터를 버퍼 풀로 복구하고 언두 영역의 내용을 삭제
커밋되면 언두 영역이 바로 삭제되지 않고 트랜잭션이 없을 때 백업 데이터가 삭제 됨

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 기술로 잠금을 걸지 않고 읽기 작업 수행 (잠금 없는 일관된 읽기)
- 다른 트랜잭션의 잠금을 기다리지 않고 읽기 가능
격리 수준이 SERIALIZABLE 아니라면 읽기 작업은 작믐을 대기하지 않고 바로 실행

자동 데드락 감지

InnoDB 스토리지 엔진은 잠금이 교착 상태를 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리
데드락 감지 스레드가 있어서 잠금 대기 그래프를 검사해 교착 상태의 트랜잭션을 찾아 그 중 하나를 강제 종료
- 언두 로그 레코드가 더 적은 트랜잭션을 강제 종료 (처리할 내용, 부하가 적음)
동시 처리 스레드가 많거나 잠금의 개수가 많아지면 데드락 감지 스레드가 느려짐
- 서비스 쿼리를 처리하는 스레드가 작업하지 못하고 대기하게 됨
- innodb_deadlock_detect 로 활성 여부 지정 가능

자동화된 장애 복구

InnoDB 엔진에는 데이터를 보호하기 위한 자동으로 복구를 진행하는 메커니즘 존재
- MySQL 서버가 시작될 때 자동 복구 실행 (완료하지 못한 트랜잭션, 디스크에 일부만 기록된(Partial write) 데이터 페이지 등 복구)
견고해서 데이터 파일이 손상되거나 서버가 시작되지 못하는 경우는 거의 발생되지 않음
디스크나 하드웨어 이슈로 문제가 생기변 복구가 쉽지 않음 (복구될 수 없는 손상이라면 복구를 멈추고 MySQL 서버 종료)
- 이 경우 innodb_force_recovery 시스템 변수 설정 필요 (복구 모드에서는 SELECT만 가능)
  - 1(SRV_FORCE_IGNORE_CORRUPT)
  - 2(SRV_FORCE_NO_BACKGROUND)
  - 3(SRV_FORCE_NO_TRX_UNDO)
  - 4(SRV_FORCE_NO_IBUF_MERGE)
  - 5(SRV_FORCE_NO_UNDO_LOG_SCAN)
  - 6(SRV_FORCE_NO_LOG_REDO)

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시
쓰기 작업을 지연시켜 일괄 작업으로 처리하는 버퍼 역할 (디스크 작업 횟수 감소)

버퍼 풀의 크기 설정

운영체제와 클라이언트 스레드가 사용할 메모리를 고려하여 설정 필요
MySQL 5.7 버전부터 버퍼 풀의 크기를 동적으로 조절 가능
- 버퍼 풀의 크기를 작은 값으로 설정하여 상황을 보며 증가하는 것이 좋음
  - 메모리 공간이 8GB 미만이라면 50%
  - 그 이상이라면 50%에서 시작하여 조금씩 올리면서 최적점 찾기
  - 메모리 공간이 50GB 이상이라면 20~35GB
innodb_buffer_pool_size 변수로 버퍼풀 크기 설정 가능
- 버퍼 풀 크기 변경은 크리티컬하므로 한가한 시점에 진행
- 내부적으로 128MB 청크 단위로 쪼개어 관리하므로 128MB 단위로 처리됨

버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 메모리 공간을 페이지 크기의 조각으로 쪼개어 데이터 페이지를 읽어서 각 저장한다.

페이지 크기 조각을 관리하기 위해 크게 3개 자료 구조를 관리한다.

LRU(Least Recently Used) 리스트
- 디스크로부터 한 번 읽어온 페이지를 오랫동안 버퍼풀의 메모리에 유지하여 디스크 읽기를 최소화
- LRU(Least Recently Used, Old 서브리스트) 와 MRU(Most Recently Used, New 서브리스트) 리스트가 결합된 형태
- 데이터 페이지가 자주 사용되면 MRU 영역에 계속 살아남고, 사용되지 않으면 LRU 끝으로 밀려나면서 버퍼풀에서 제거
플러시(Flush) 리스트
- 디스크로 동기화 되지 않은 데이터를 가진 데이터 페이지 변경 시점 기준의 페이지 목록 관리
- 데이터가 변경되면 리두 로그에 기록, 데이터 페이지에도 변경 내용 반영
- 디스크의 리두 로그와 데이터 페이지의 상태를 동기화 하기 위해 엔진은 체크포인트 발생
프리(Free) 리스트
- 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
- 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용

버퍼 풀과 리두 로그

innoDB buffer pool and redo log (출처: Real MySQL 8.0)

InnoDB 버퍼 풀은 서버 성능 향상을 위한 데이터 캐시와 쓰기 버퍼링 용도다.
쓰기 버퍼링 기능을 향상 시키기 위해서는 리두 로그와의 관계 이해가 필요

버퍼 풀은 읽은 데이터를 가진 클린 페이지(Clean Page)와 변경된 데이터를 가진 더티 페이지(Dirty Page)를 가짐
- 더티 페이지는 버퍼 풀에 무한정 머무를 수 없음
- 리두 로그는 1개 이상의 고정 파일을 연결하여 순환 고리처럼 사용(새로운 로그 엔트리로 덮어쓰임)
- 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log) 라고 함 (위 그림에서 화살표를 가진 엔트리)
리두 로그 파일의 공간에는 기록될 때마다 로그 포지션(LSN, Log Sequence Number)이 증가
엔진에서 주기적으로 체크포인트 이벤트로 리두 로그와 더티 페이지를 디스크로 동기화
일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 리두 로그는 작은 공간만 있으면 됨

버퍼 풀 플러시(Buffer Pool Flush)

InnoDB 스토리지 엔진은 더티 페이지들을 디스크에 성능의 악영향 없이 동기화하기 위해 2개의 플러시 기능을 백그라운드에로 실행한다.

플러시 리스트 플러시

리두 로그 공간을 재활용하기 위해 주기적으로 리두 로그 엔트리 공간을 비워야 한다.
리두 로그 공간을 비우기 위해 디스크 동기화가 필요하다.
이 동기화를 위해 InnoDB 는 주기적으로 플러시 리스트(Flush_list) 함수를 호출하여 동기화 작업을 수행한다.
이렇게 디스크로 동기화하는 스레드를 클리너 스레드(Cleaner Thread)라고 한다.

LRU 리스트 플러시

새로운 페이지를 읽어오는 공간을 확보하기 위해 사용 빈도가 낮은 데이터 페이지들을 제거한다. 이러한 작업은 LRU 리스트(LRU_list) 플러시 함수를 사용한다.

버퍼 풀 상태 백업 및 복구

서버를 셧다운하고 다시 시작하면 쿼리 처리 성능이 낮은 경우가 많다.
버퍼 풀에 쿼리들이 사용할 데이터가 적재(워밍업, Warming Up)되어 있기 때문에 디스크 데이터를 읽지 않아도 되기 때문이다.
MySQL 5.5 버전에서는 강제 워밍업을 위해 서비스 오픈전에 풀 스캔을 했었다.
MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능(innodb_buffer_pool_dump_now, innodb_buffer_pool_load_now 시스템 변수)이 도입되었다.

버퍼 풀의 적재 내용 확인

MySQL 5.6 버전 부터 information_schema 데이터베이스의 innodb_buffer_page 테이블에서 버퍼 풀에 적재된 페이지들을 확인할 수 있다.
하지만 버퍼 풀이 큰 경우 이 조회는 큰 부하를 일으킬 수 있으니 주의해야 한다.

MySQL 8.0 버전 부터는 information_schema 데이터베이스의 innodb_cached_indexes 테이블에서 인덱스별로 데이터 페이지가 얼마나 버퍼 풀에 적재됐는지 확인할 수 있다.

Double Write Buffer

InnoDB 스토리지 엔진의 리두 로그는 공간 낭비를 막기 위해 페이지의 변경 내용만 기록한다.
그렇기 때문에 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되면 복구가 어렵다.
이러한 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 하는데 이 문제를 막기 위해 Double-Write 기법을 이용한다.

위 그림은 Double-Write 작동 방식을 표현한다.
더티 페이지를 디스크로 플러시하고 변경 내용을 기록하기 전에 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.
그리고 각 더티 페이지를 하나씩 랜덤으로 쓰기를 실행한다.
DoubleWrite 기록된 내용은 더티 페이지의 내용이 디스크에 기록이 실패할 때만 사용된다.
정상적으로 기록되면 필요 없어진다.

언두로그

언두 로그(Undo Log)는 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전의 백업된 데이터다.

트랜잭션 보장
- 트랜잭션이 롤백되면 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구
격리 수준 보장
- 특정 커넥션에서 데이터를 변경하는 도중 다른 커넥션에서 조회하면 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 읽어서 반환

언두 로그 레코드 모니터링

INSERT, UPDATE, DELETE 로 데이터를 변경하면 트랜잭션이 커밋하지 않아도 데이터 파일 내용은 변경된다.
커밋하면 그 상태가 유지되고 롤백되면 언두 영역의 백업된 데이터를 복구한다.

언두 로그의 용도는 크게 두 가지다.

트랜잭션 롤백 대비용
트랜잭션 격리 수준을 유지하면서 높은 동시성 제공

하지만 활성 상태의 트랜잭션이 장시간 유지되면 성능에 좋지 않다.
그래서 언두 로그 레코드를 항상 모니터링 하는 것이 좋다.

SHOW ENGINE INNODB STATUS \G

언두 테이블스페이스 관리

언두 테이블스페이스(Undo Tablespace)는 언두 로그가 저장되는 공간이다.
하지만 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장에 한계가 있다.
그래서 innodb_undo_tablesapces 시스템 변수가 도입되었고 이 변수를 이용해 별도의 언두 로그 파일을 사용할 수 있다.

체인지 버퍼

레코드가 INSERT, UPDATE 될 때는 데이터 파일 뿐 아니라 테이블의 인덱스 업데이트 작업도 필요하다.
만약 테이블에 인덱스가 많다면 상당히 많은 자원을 소모하게 된다.
인덱스 페이지가 버퍼 풀에 있다면 바로 업데이트를 한다.
하지만 디스크로부터 읽어와야 한다면 즉시 실행하지 않는다.
임시 공간에 저장해두고 결과를 반환하여 성능을 향상 시키는데 이 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 한다. 하지만 중복 여부를 체크하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

임시로 저장된 인덱스 레코드 조각은 백그라운드 스레드에서 병합되는데 이 스레드를 체인지 버퍼 머지 스레드(Merge Thread)라고 한다. innodb_change_buffering 시스템 변수를 통해 작업 종류별로 체인지 버퍼를 활성화할 수 있다.

all: 모든 인덱스 관련 작업(inserts + deletes + purges)을 버퍼링
none: 버퍼링 안함
inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
changes: 인덱스에 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)

체인지 버퍼는 기본적으로 버퍼 풀 메모리의 25% 공간까지 사용할 수 있으며, 50% 까지 사용 설정이 가능하다.

리두 로그 및 로그 버퍼

리두 로그는 트랜잭션의 요소 ACID 중 D(Durable) 에 속한다.
서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 유지하는 안정장치다.

비정상 종료되는 경우 다음 두가지의 일관되지 않은 데이터를 가질 수 있다.

커밋됐지만 데이터 파일에 기록되지 않은 데이터
- 리두 로그에 저장된 데이터를 다시 복사해서 해결
롤백됐지만 데이터 파일에 이미 기록된 데이터
- 언두 로그의 내용으로 데이터 파일에 복사해서 해결
- 커밋, 롤백, 트랜잭션 실행 중간 상태인지 확인하기 위해 리두 로그는 필요

어댑티브 해시 인덱스

인덱스라고 하면 이는 테이블에 사용자가 생성해둔 B-Tree 인덱스이다.
하지만 어댑티브 해시 인덱스(Adaptive Hash Index)는 사용자가 생성한 것이 아닌 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

B-Tree 인덱스는 루트 노드, 브랜치 노드, 리프 노드까지 찾아가야 레코드를 읽을 수 있는데 스레드가 많아지면 쿼리 성능이 떨어진다.
그래서 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 어탭티브 해시 인덱스를 검색해서 저장된 데이터 페이지를 즉시 찾는다.
해시 인덱스는 인덱스 키 값(B-Tree 인덱스의 고유 번호 ID와 실제 키 값의 조합)과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리된다.

다음과 같은 경우에는 성능 향상에 도움이 된다

디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

다음과 같이 도움이 되지 않는 경우 의도적으로 비활성화하는 경우도 많다.

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

참고 자료

Real MySQL 8.0 (1권)