백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. Architecture
MySQL Engine
- 클라이언트가 보내는 쿼리 요청을 처리하는 역할
Connection Handler
- 클라이언트와의 연결을 관리
- 요청을 SQL Parser로 전달
SQL Parser
- 클라이언트로부터 받은 SQL 쿼리를 파싱하여 내부적으로 이해할 수 있는 형태로 변환
- 문법을 검사하고 쿼리가 유효한지 확인
Optimizer
- SQL 쿼리의 실행 계획을 최적화
- 여러 가지 실행 계획 중에서 가장 효율적인 경로를 선택하여 성능을 최적화
Storage Engine
특징
- 성능 향상을 위한 기능 제공
- InnoDB: 버퍼 풀
- MyISAM: 키 캐시
사용법
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
- 여러 개 동시 사용 가능 (테이블 별 지정 가능)
Handler
- MySQL 엔진과 스토리지 엔진 사이의 인터페이스 역할을 하는 계층
- MySQL이 클라이언트의 SQL 쿼리를 실행할 때, 핸들러가 스토리지 엔진과 상호작용하여 데이터를 검색하고 변경하는 역할 수행
- 내부적으로 핸들러 함수를 사용하여 데이터에 접근함
Statue
SHOW GLOBAL STATUS LIKE 'Handler%';
- 각 스토리지 엔진에서 핸들러 API 별 사용 정보를 나타내는 명령어
2. 스레딩 구조
Foreground Thread
클라이언트와의 통신 담당 (SQL 수행)
- 클라이언트가 요청한 SQL을 처리하기 위한 스레드
- 최소 개수로 운영함 (최소 클라이언트 수만큼 존재)
- thread/sql/one_connection: 동적으로 생성되어 해당 요청을 처리하는 스레드
스레드 캐시
- 스레드 풀링 객체
- 스레드 생성을 최적화하고, 결과적으로 스레드 생성을 줄이는 역할
- 캐시된 스레드는 사용 후 재사용됨 (스레드 캐시로 되돌아감)
- 일정량의 스레드가 스레드 캐시에 존재 시, 사용되지 않는 스레드는 종료됨
- thread_cache_size: 스레드 캐시 크기 (최소한의 foreground thread 수)
Background Thread (InnoDB)
- 실제 데이터 처리 작업을 백그라운드에서 수행함
Disk Read
- 데이터 캐싱
- InnoDB Buffer Pool에 캐싱
Disk Write
- 쓰기 지연 방식
- 기록할 데이터를 모았다가 디스크에 한번에 반영
- InnoDB Buffer Pool에 모아둠
- redo log
- 트랜잭션 로그 (트랜잭션 실행 중에도 기록됨)
- 데이터 복구에 사용됨 (시스템 충돌시 원활한 복구를 위해 쓰임)
- Binlog
- SQL 실행 기록 로그 (트랜잭션 커밋 시 기록됨)
- 복제나 시점 복구에 사용됨
Merge Insert Buffer
- insert query를 버퍼링하는 공간
- 여러 개 쌓여 있다가 한 번에 디스크에 기록함
- 주로 secondary index에서 동작
Lock Monitoring
- Dead Lock 감지 시, 자동으로 교착상태 해결
- 트랜잭션 롤백 수행을 통해 교착 상태를 풀어줌
시스템 변수
- innodb_read_io_threads: read thread 수 설정
- innodb_write_io_threads: write thread 수 설정
3. 메모리
Global Memory
- shared memory (모든 스레드에 의해 공유됨)
- 운영체제로부터 할당받으며, 서버 전체에서 사용됨
Table Cache
- 테이블의 메타데이터를 캐싱
- 테이블을 열 때마다 발생하는 디스크 I/O를 줄여 성능을 향상시킴
InnoDB Buffer Pool
- 데이터와 인덱스를 캐싱하는 공간
- 자주 사용하는 데이터를 캐싱하여 성능 저하를 최소화합니다.
InnoDB Change Buffer
- secondary index에 대한 변경사항을 임시로 저장하는 공간
- 디스크에 기록하기 전, 메모리에 모아서 한 번에 처리함
- 백그라은드 스레드가 유휴 시간이거나 충분한 변경사항이 쌓이면, secondary index 페이지에 병합함
InnoDB Adaptive Hash Index
- 자주 사용되는 데이터의 컬럼을 해시 인덱스로 정의
- 빠른 인덱스 검색 속도를 위함
InnoDB Log Buffer
- Redo Log에 기록하기 전에 임시로 저장하는 공간
Local Memory
- 각 스레드는 독립적으로 쿼리를 실행하며, 쿼리 실행 시에만 사용되는 메모리 영역
- 스레드가 종료되면 해제되며, 다른 스레드들과 공유되지 않음
Read Buffer
- 데이터베이스에서 데이터를 읽을 때, 해당 데이터를 저장하는 임시 버퍼로 사용됨
Join Buffer
- 조인 작업을 위한 버퍼
- 조인 결과를 메모리에서 처리하여 성능을 높임
Sort Buffer
- 정렬 작업 버퍼 (Order By, Group By)
Binlog Cache
- 바이너리 로그 이벤트를 기록하기 전, 임시적으로 저장하는 공간
Connection Buffer
- 네트워크 통신 버퍼
- 클라이언트와 MySQL 서버 간의 데이터 전송중에 사용됨
4. 쿼리 실행
쿼리 파서
문법 체크
토큰화
파서 트리 생성
전처리기
파서 트리 구조 검사
개체 매핑
- 실제 존재하는 개체로 매핑하기 (테이블 이름, 칼럼 이름, 내장 함수 등)
권한 확인
옵티마이저
실행 계획 생성
최적의 방법 고려
실행 엔진
- 실행 계획을 기반으로 실제 SQL을 수행
- 실제 데이터를 핸들러에게 요청하여 가져옴
핸들러
- MySQL 엔진과 스토리지 엔진 사이에서 데이터 요청을 중개하는 인터페이스
- 실행 계획을 기반으로 스토리지 엔진에 데이터를 요청하고 결과를 반환
5. 스레드 풀
- 스레드 관리를 최적화하여 성능을 향상시키는 기능
- 자원 소모를 줄이기 위함 (제한된 수의 스레드 처리에만 집중해서 서비스하려는 것이 목적)
에디션
엔터프라이즈 에디션 전용
커뮤니티 에디션
- 스레드 풀 플러그인 라이브러리 사용 (Percona Server)
동작 방식
- 요청마다 스레드 그룹에 배정됨
- 스레드 그룹의 모든 스레드가 바쁠 경우, 새로운 스레드를 생성할지 또는 대기할지 결정
작업 스레드
시스템 변수
- thread_pool_max_threads: 전체 스레드의 수
- thread_pool_small_limit: 작업 스레드가 시간내로 작업을 마치지 못하면 새로운 작업 스레드를 생성
선순위 후순위 큐
- 특정 트랜잭션을 우선적으로 처리할 수 있는 기능
6. 트랜잭션 지원 메타데이터
메타데이터
- 테이블의 구조 정보와 스토어드 프로그램 정보 등을 저장하는 데이터
- MySQL 8.0~
시스템 테이블 (InnoDB)
mysql.tables
- 테이블 메타데이터 정보 저장
- 테이블 이름, 엔진 유형, 생성 시간 등
- 권한 필요
SDI (Serialized Dictionary Information)
- InnoDB 스토리지 이외의 스토리지 엔진을 사용하는 테이블들을 위한 파일
- SDI 파일을 생성하여 메타데이터를 관리함
7. 플러그인 스토리지 엔진
- 기본적으로 제공되는 스토리지 엔진 뿐만 아니라 직접 개발하여 사용할 수 있습니다.
8. 컴포넌트
- 플러그인의 단점을 보완하기 위해 등장했습니다.
- 플러그인끼리 통신 불가
- 변수나 함수가 캡슐화가 되지 않습니다.
- 의존 관계를 설정할 수 없습니다.
9. 쿼리 캐시
- SQL 실행 결과를 메모리에 캐시 (동일 SQL 쿼리 실행 시, 결과 즉시 반환)
- 무효화: 쿼리에 사용된 테이블에 데이터가 변경되면 모두 폐기
- MySQL 8.0~ X
출처