Database/Mysql

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

noahkim_ 2023. 11. 23. 22:18

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

1. Architecture

MySQL Engine

  • 클라이언트가 보낸 쿼리를 받아서 실행하는 역할
구성 요소 역할 설명
Connection Handler
클라이언트와의 연결을 관리하며, 클라이언트로부터 받은 요청을 SQL Parser에 전달
SQL Parser
SQL 쿼리를 파싱하여 내부적으로 처리 가능한 형태로 변환하고, 문법 유효성을 검사
Optimizer
실행 계획을 분석하고, 가능한 실행 경로 중 가장 효율적인 계획을 선택하여 성능 최적화

 

Storage Engine

  • 실제 데이터 읽기와 쓰기를 담당하는 엔진
  • 성능 향상을 위한 기능 제공 (InnoDB: 버퍼 풀, MyISAM: 키 캐시)

 

예시) 사용법

더보기 
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
  • 여러 개 동시 사용 가능 (테이블 별 지정 가능)

 

Handler

  • MySQL 엔진과 스토리지 엔진 사이의 인터페이스 역할을 하는 계층 (다양한 스토리지 엔진을 교체할 수 있도록 함)
  • MySQL 엔진과 스토리지 엔진이 상호작용하여 데이터를 검색하고 변경하는 역할 담당
  • 내부적으로 핸들러 함수를 사용하여 데이터에 접근함

 

명령어

더보기

Statue: 각 스토리지 엔진에서 핸들러 API 별 사용 정보를 나타내는 명령어

SHOW GLOBAL STATUS LIKE 'Handler%';

 

2. 스레딩 구조

  • 각 스레드가 클라이언트 요청을 처리함

 

Foreground Thread

  • 클라이언트가 요청한 SQL을 처리하기 위한 스레드 (Receiver Thread, Query Worker Thread 등)
  • 최소 개수로 운영함 (최소 클라이언트 수만큼 존재)
  • thread/sql/one_connection: 동적으로 생성되어 해당 요청을 처리하는 스레드

 

스레드 캐시
  • 생성된 스레드를 재사용하기 위한 스레드 풀링 객체
항목 설명
목적
스레드 생성/제거에 따른 오버헤드를 줄여 성능을 최적화
동작 방식
- 클라이언트 요청 처리 후 사용된 스레드는 캐시에 보관됨
- 새 요청 시 캐시된 스레드를 재사용
스레드 종료 조건
- 일정량 이상의 스레드가 캐시에 존재하고 일정 시간 동안 사용되지 않으면 종료됨
시스템 변수
thread_cache_size: 스레드 캐시 크기 지정(최소한의 foreground thread 수로도 사용됨)

 

Background Thread (InnoDB)

  • 실제 데이터 처리 작업을 백그라운드에서 수행함 (InnoDB Background Thread)
항목 설명
Disk Read
- 디스크에서 데이터를 읽고 InnoDB Buffer Pool에 캐싱
Disk Write
- 쓰기 지연 방식으로 Buffer Pool에 모은 후 일괄적으로 디스크에 기록
- Merge Insert Buffer
- Insert 쿼리 결과를 버퍼에 모아두고, 나중에 디스크에 일괄 반영
- 주로 secondary index에 대해 동작
- Redo Log
- 트랜잭션 실행 중에도 기록되는 트랜잭션 로그
- 시스템 장애 시 복구에 사용
- Binlog
- 트랜잭션 커밋 시 기록되는 SQL 실행 이력 로그
- MySQL 복제 및 시점 복구(Point-in-Time Recovery)에 사용
- Lock Monitoring
- Deadlock(교착 상태) 감지 시 자동으로 롤백 수행하여 문제를 해결
시스템 변수
- innodb_read_io_threads: 디스크 읽기 스레드 수 설정
- innodb_write_io_threads: 디스크 쓰기 스레드 수 설정

 

3. 메모리

Global Memory

  • 운영체제로부터 할당받은 공유 메모리 영역
  • 모든 스레드가 접근 가능하며 서버 전체에서 사용됨 (shared memory)
항목 설명
Table Cache
- 테이블의 메타데이터(열 정보 등)를 캐싱
- 디스크 I/O를 줄여 테이블 오픈 성능 향상
InnoDB Buffer Pool
- 데이터 페이지와 인덱스 페이지를 캐싱
- 디스크 접근을 줄여 전반적인 쿼리 성능을 향상
InnoDB Change Buffer
- Secondary Index에 대한 변경 내용을 메모리에 임시 저장
- 나중에 백그라운드에서 병합 처리
InnoDB Adaptive Hash Index
- 자주 조회되는 페이지를 해시 기반 인덱스로 전환
- 빠른 검색 속도 제공 (InnoDB 내부 최적화)
InnoDB Log Buffer
- Redo Log에 기록되기 전 임시 저장소
- 트랜잭션 커밋 전까지 로그 데이터를 모아서 처리

 

Local Memory

  • 특정 쿼리를 실행하는 동안 해당 스레드가 단독으로 사용하는 임시 메모리 공간
  • 다른 스레드와 공유하지 않고, 쿼리 실행이 끝나면 바로 해제됨
항목 설명
Read Buffer
- 쿼리 실행 시 데이터를 읽어올 때 사용하는 임시 버퍼
- 주로 테이블에서 데이터를 스캔할 때 활용됨
Join Buffer
- 조인 연산 시 결과를 저장하는 메모리 버퍼
- 인덱스가 없는 조인에서 성능 향상을 위해 사용됨
Sort Buffer
- 정렬 연산 시 사용하는 버퍼 (ORDER BY, GROUP BY 등)
Binlog Cache
- 트랜잭션 동안 생성되는 바이너리 로그 이벤트를 임시로 저장
- 커밋 시 실제 binlog로 기록됨
Connection Buffer
- 클라이언트 ↔ MySQL 서버 간의 데이터 통신에 사용되는 네트워크 버퍼

 

4. 쿼리 실행

단계 주요 역할
쿼리 파서
- SQL 문장 파싱
- 문법 오류 체크
- 토큰화
- 파서 트리 생성 (쿼리를 트리 구조로 분석)
전처리기
- 파서 트리의 구조 검증
- 테이블/컬럼/함수 등의 개체를 실제 스키마와 매핑
권한 확인
- 사용자가 해당 개체(테이블, 컬럼 등)에 접근 가능한지 권한을 확인
옵티마이저
여러 실행 계획 중 최적의 실행 경로 선택
- 인덱스 사용 여부 결정
- 조인 순서 및 방법 결정
실행 엔진
- 옵티마이저가 만든 실행 계획을 따라 쿼리를 실제 수행
- 필요한 데이터를 핸들러를 통해 요청
핸들러
- MySQL 엔진과 스토리지 엔진 사이의 인터페이스 계층
- 실행 엔진의 요청에 따라 스토리지에서 데이터를 읽고 반환

 

5. 스레드 풀

  • 스레드 수를 제한하고, 관리 가능한 범위 내에서 쿼리를 처리함으로써 자원 소모를 줄이고 성능을 향상시키는 기능
항목 설명
동작 방식
- 클라이언트 요청이 스레드 그룹에 배정됨
- 그룹의 모든 스레드가 바쁘면: 신규 스레드 생성 or 큐에서 대기 결정
우선순위 큐
- 선순위/후순위 큐 시스템으로 특정 트랜잭션을 우선 처리 가능 (긴급 쿼리 우선 등)
에디션
- Enterprise Edition 전용 기능
- Community Edition에서는 Percona Server 등에서 플러그인 형태로 사용 가능
시스템 변수
- thread_pool_max_threads: 전체 풀 내 최대 스레드 수
- thread_pool_stall_limit 또는 thread_pool_small_limit: 응답 지연 시 새 스레드 생성 조건

 

6. 트랜잭션 지원

항목 설명
메타데이터
- 테이블 구조, 인덱스, 제약 조건, 스토어드 프로그램 등 객체 정의 정보를 저장하는 데이터
- MySQL 8.0부터는 트랜잭션 방식으로 메타데이터 관리
시스템 테이블 (InnoDB)
- mysql.tables 등 InnoDB 기반의 시스템 테이블에서 메타데이터 저장
- 테이블 이름, 엔진 유형, 생성 시간 등의 정보 포함
- 접근에는 적절한 권한 필요
SDI (Serialized Dictionary Information)
- InnoDB 외 스토리지 엔진에서 사용하는 메타데이터 관리 파일
- .sdi 확장자를 가진 JSON 형태의 파일로 존재- 테이블 단위로 생성되며, 별도 관리됨

 

7. 플러그인 스토리지 엔진

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

 

8. 컴포넌트

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

 

9. 쿼리 캐시

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

 

출처
 
저작자표시 (새창열림)