[MySQL] MySQL엔진 아키텍처

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

MySQL 엔진 아키텍처 

---

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

1. MySQL 엔진 vs 스토리지 엔진

---

MySQL 엔진

• 요청된 SQL 문장을 분석하거나 최적화하는 등의 DBMS의 두뇌에 해당하는 처리
• 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저가 중심을 이룬다. (각 내용은 아래에서 설명)

스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당
• MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
• 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

CREATE TABLE test_table (id INT) ENGINE=InnoDB;

핸들러 API

• MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기 요청을 하는데, 이러한 요청을 핸들러 요청이라고 함.
• 여기서 사용되는 API를 핸들러 API라고 한다.

2. MySQL 스레딩 구조

---

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

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

• 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다.
  • 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면, 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.
    (스레드 캐시의 최대 스레드 수는 thread_cache_size 시스템 변수로 설정)
• 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
• MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB는 다음과 같은 작업들이 백그라운드로 처리된다.

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

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링 해서 일괄 처리하는 기능이 탑재되어 있다.

• InnoDB에서는 이러한 방식으로 처리하는 반면에, MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있다.
• 이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.

3. 메모리 할당 및 사용 구조

---

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

글로벌 메모리 영역

• MySQL 서버가 시작되면서 운영체제로부터 할당된다.
• 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. (설정에 따라 N개로 할당받을 수 있음)
• 글로벌 메모리 영역은 모든 스레드에 의해 공유된다.

역할

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

로컬 메모리 영역 (세션 메모리 영역)

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역.
• MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당해주는데, 클라이언트 스레드가 사용하는 메모리 공간
• 각 클라이언트 스레드 별로 독립적으로 할당되며, 절대 공유되어 사용되지 않는다.

역할

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

4. 쿼리 실행 구조

---

쿼리 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 수행
• 쿼리 문장의 기본 문법 오류는 이 과정에서 발견

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인.
• 실제 존재하지 않거나 권한상 사용할 수 없는 토큰은 이 단계에서 걸러진다.

옵티마이저

• 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할
• 일명 DBMS의 두뇌

실행 엔진

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

핸들러

• MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당.
• (상단의 핸들러 API 참고)

5. 쿼리 캐시

---

• MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거됐다.
• 실제로 큰 도움이 되는 경우는 흔치 않으며, 수많은 버그의 원인으로 지목되는 경우가 많았기 때문

6. 스레드 풀

---

MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만 MySQL 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다.

• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
  • 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도를 높이고, 운영체제 입장에서는 불필요한 컨텍스트 스위칭을 줄여서 오버헤드를 낮출 수 있다.

7. 트랜잭션 지원 메타데이터

---

MySQL 5.7 버전까지는 테이블 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리했다.

• 이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블 생성 또는 변경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 존재했음.

MySQL 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됐다. (이러한 테이블을 시스템 테이블이라고 한다)

• 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서 이제 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.