[Real MySQL] 4-1. 아키텍쳐: MySQL 엔진 아키텍쳐

"Real MySQL" 책을 정리한 포스팅 입니다.

1. Architecture

MySQL Engine

• SQL 분석 (파싱, 최적화)

 

Component

• Connection Handler
• SQL Parser
• Optimizer

 

Storage Engine

• 데이터 읽기 + 쓰기 (from Disk)

 

특징

• 성능 향상을 위한 기능 제공
  • InnoDB: 버퍼 풀
  • MyISAM: 키 캐시
• 여러 개 동시 사용 가능
• 테이블 별 지정 가능

CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

 

Handler

• 데이터 읽기 + 쓰기
• 쿼리 실행기에서 호출됨
• STATUS: 핸들러 API 별 사용 정보

SHOW GLOBAL STATUS LIKE 'Handler%';

 

2. 스레딩 구조

• 스레드 기반으로 동작 (Foreground or Background)

 

Foreground Thread

클라이언트 요청 처리 (SQL 수행)

• 최소한 클라이언트 수만큼 존재
• thread/sql/one_connection: 현재 사용자의 요청을 처리하는 스레드

 

스레드 캐시

• 스레드 풀링 객체 (스레드 생성을 줄이기 위함)
• thread_cache_size: 스레드 캐시 크기 (최소한의 foreground thread 수)

 

요청 처리 후

• 스레드 캐시로 되돌아감
• 스레드 캐시에 일정량의 스레드 존재 시, 스레드 종료

 

Background Thread (InnoDB)

역할

• Disk Read
  • 데이터 캐싱: 자주 접근하는 디스크 데이터를 InnoDB Buffer Pool에 올림
• Disk Write
  • InnoDB Buffer Pool: 쓰기 지연 방식 (기록할 데이터를 모았다가 디스크에 한번에 반영)
  • Log: 디스크에 기록하기 전, 먼저 redo log에 기록하여 데이터 복구 (시스템 충돌시에도 원활한 복구 가능)
• Merge Insert Buffer
  • insert query를 버퍼링해두었다가 한번에 기록
  • 주로 secondary index에서 동작 (효율적)
• Monitoring
  • Lock 감시: Dead Lock 감지 시, 자동으로 교착상태 해결 (트랜잭션 롤백 수행)

 

시스템 변수

• innodb_read_io_threads: read thread 수 설정
• innodb_write_io_threads: write thread 수 설정

 

3. 메모리

Global Memory

• shared memory (모든 스레드에 의해 공유됨)
• 운영체제로부터 할당됨

 

구성

• Table Cache: 테이블 메타데이터 캐싱
• InnoDB Buffer Pool: 데이터, 인덱스 캐싱
• InnoDB Change Buffer: 변경사항 쿼리 캐싱 (secondary index) 
• InnoDB Adaptive Hash Index
  
  • 자주 사용되는 데이터의 컬럼을 해시 인덱스로 정의 (InnoDB Buffer Pool 기반)
  • 빠른 인덱스 검색 속도를 위함
• InnoDB Log Buffer: 로그 버퍼
• InnoDB Redo Log Buffer: 로그 버퍼 (redo log에 기록하기 전 임시적으로 저장하는 공간)

 

Local Memory

• 스레드 독립적 memory
• 쿼리 실행 시, 사용하는 메모리 영역

 

구성 

• Read Buffer: 읽기 작업 버퍼
• Join Buffer: 조인 작업 버퍼
• Sort Buffer: 정렬 작업 버퍼
• Binlog Cache: 바이너리 로그 이벤트 캐시 (기록 전, 임시적으로 저장하는 공간)
• Connection Buffer: 네트워크 통신 버퍼

 

쿼리 실행

쿼리 파서

• 문법 체크
• 파서 트리 생성
  
  • 쿼리를 트리 형태로
  • 토큰 단위

 

전처리기

• 파서 트리 구조 체크
• 개체 매핑 (테이블 이름, 칼럼 이름, 내장 함수 등)
  • 존재 여부
  • 권한 확인

 

옵티마이저

• 실행 계획 생성 (최적의 방법 고려)

 

실행 엔진

• 실행 계획 수행
• 각 핸들러에게 요청하는 방식

 

핸들러 (스토리지 엔진)

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

 

스레드 풀

• 자원 소모를 줄이기 위함 (제한된 수의 스레드 처리에만 집중해서 서비스하려는 것이 목적)
• 엔터프라이즈 에디션 전용
• 커뮤니티 에디션: 스레드 풀 플러그인 라이브러리 사용 (Percona Server)

 

작업 스레드

• 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 해당 스레드 그룹에 새로운 작업 스레드를 추가할지 or 기다릴지 판단
• thread_pool_max_threads: 전체 스레드의 수
• thread_pool_small_limit: 작업 스레드가 시간내로 작업을 마치지 못하면 새로운 작업 스레드를 생성

 

선순위 후순위 큐

• 특정 트랜잭션을 우선적으로 처리할 수 있는 기능

 

트랜잭션 지원 메타데이터

메타데이터

• 테이블의 구조 정보 + 스토어드 프로그램
• MySQL 8.0~

 

시스템 테이블 (InnoDB)

• 시스템 정보 (기본)
• mysql.tables
• 권한 필요

 

SDI (Serialized Dictionary Information)

• InnoDB 스토리지 이외의 스토리지 엔진을 사용하는 테이블들을 위한 파일

 

플러그인 스토리지 엔진

• 기본적으로 제공되는 스토리지 엔진 뿐만 아니라 직접 개발하여 사용할 수 있습니다.

 

컴포넌트

• 플러그인의 단점을 보완하기 위해 등장했습니다.
  • 플러그인끼리 통신 불가
  • 변수나 함수가 캡슐화가 되지 않습니다.
  • 의존 관계를 설정할 수 없습니다.

 

쿼리 캐시

• SQL 실행 결과를 메모리에 캐시 (동일 SQL 쿼리 실행 시, 결과 즉시 반환)
• 무효화: 쿼리에 사용된 테이블에 데이터가 변경되면 모두 폐기
• MySQL 8.0~ X

 

출처

• Exem - MySQL Architecture: SQL 처리 과정
• Exem - MySQL Architecture: MySQL Memory
• MySQL Blog - The New MySQL Thread Pool
• MySQL - Overview of MySQL Storate Engine Architecture