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
출처
- Exem - MySQL Architecture: SQL 처리 과정
- Exem - MySQL Architecture: MySQL Memory
- MySQL Blog - The New MySQL Thread Pool
- MySQL - Overview of MySQL Storate Engine Architecture