MYSQL 엔진 아키텍처

출처: Real-MySQL 8.0

1. MySQL 전체 구조

MySQL 서버 전체구조

MySQL 엔진

접속을 처리하고 요청한 SQL 분석, 최적화, 실행 수행

• 클라이언트로부터 접속 및 쿼리 요청을 처리하는 '커넥션 핸들러'
• 'SQL 파서' 및 '전처리기'
• 쿼리의 최적화된 실행을 위한 '옵티마이저'
• 한 MySQL 서버에서 한 엔진

스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 데이터를 불러오는 역할 수행
• 한 MySQL 서버에 여러 개의 스토리지 엔진을 동시 사용 가능
• CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
• 성능 향상을 위한 키 캐시(MyISAM 스토리지 엔진), InnoDB 버퍼 풀(InnoDB 스토리지 엔진) 기능 제공
• MySQL 에서 대체 가능한 Storage Engines

SHOW ENGINES 실행 결과(MySQL v8.0.31)

핸들러 API

• MySQL 엔진의 쿼리 실행기에서 스토리지 엔진에 쓰기 또는 읽기 요청을 핸들러(Handler) 요청
• 요청에 사용되는 API를 핸들러 API
• SHOW GLOBAL STATUS LIKE 'Handler%'; 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있는지 확인 가능.

핸들러 API 작업들

2. MySQL 스레딩 구조

MySQL 스레딩 모델

• MySQL 서버는 프로세스 기반이 아닌 스레드 기반임
• 포그라운드(Foreground)와 백그라운드(Background) 스레드로 구분
• SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id; 를 통해 현재 실행 중인 스레드들을 확인 가능

• 

  현재 실행 중인 스레드들

• 동일한 이름의 스레드가 2개 이상씩 있는 것은 MySQL 설정에 따라 여러 스레드가 동일 작업을 병렬로 처리하는 경우
• MySQL 엔터프라이즈 에디션과 Percona MySQL 서버에서는 추가 기능으로 스레드풀(Thread Pool) 모델을 사용 가능하다.
  • 스레드풀이 있으면 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 한 스레드에 여러 개의 컨넥션 요청을 전담할 수 있다.

포그라운드 스레드(클라이언트 스레드, 사용자 스레드)

• 서버에 접속된 클라이언트의 수만큼 존재. 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리
• 클라이언트가 작업을 마치고 컨넥션을 종료하면 스레드는 스레드 캐시(Thread cache)로 되돌아감.
  • 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 이미 있으면, 스레드를 바로 종료함.
• 포그라운드 스레드는 데이터를 불어올 때, 먼저 데이터 버퍼나 캐시에서 찾고 없으면 디스크 데이터나 인덱스 파일에서 읽어와서 처리함.
  • MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리. 지연된쓰기 방식이 있지만 일반적 방식 X
  • InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리. 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

백그라운드 스레드

InnoDB 의 백그라운드 스레드들. MyISAM 경우는 포어그라운드에서 디스크 작업까지 처리함으로 아래 항목에 해당 사항이 없다.

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

로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread)가 중요.

• MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있다.
  • innodb_write_io_threads 와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정 가능.
  • InnoDB 에서도 데이터를 읽는 작업은 주로 클라이언트 스레드. 읽기 스레드는 스레드수를 많이 설정할 필요가 없지만
  • 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정도 설정.
  • DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정.
• 데이터 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만, 데이터 읽기 작업은 절대 지연될 수 없다. 상용 DBMS는 버퍼링해서 일괄 처리하는 기능이 탑재되어 있음. InnoDB도 포함. MyISAM은 포함이 안되며 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계.
  • InnoDB에선 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크 파일로 저장될 때까지 기다리지 않아도 됨.
  • MyISAM 에서는 일반적인 쿼리는 쓰기 버퍼링을 사용할 수 없음.

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

MySQL 의 메모리 사용 및 할당 구조

글로벌 메모리 영역

• 글로벌 메모리 영역은 서버가 시작되면 운영체제로부터 할당. 운영체제마다 요청 메모리는 100% 한 번에 할당해주는 경우도 있고 필요할 때 조금씩 할당해주는 경우가 있음.
• 스레드수와 무관하게 보통 1개 영역이 할당되며, 영역이 N개라 하더라도 모든 스레드에 공유됨.
• 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼

로컬 메모리 영역

• 세션 메모리 영역. 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역.
• 각 스레드별로 독립적으로 할당되며 절대 공유되지 않는 특성
• 로컬 메모리 공간은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않을 경우에는 MySQL이 메모리 공간을 할당하지 않을 수 있음.
  • 계속 할당된 채로 남아 있는 공간은 커넥션 버퍼나 결과 버퍼
  • 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간은 소트 버퍼나 조인 버퍼

대표적인 로컬 메모리 영역

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

4. 쿼리 실행 구조

쿼리 실행 구조

쿼리 파서

• 요청에 있는 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위 어휘나 기호) 단위로 파싱해 트리 형태의 구조로 만드는 작업 수행.
• 쿼리 문장 기본 문법 체크하고 사용자에게 오류 메시지 전달

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 '구조적인 문제점이 있는지 확인' 작업 수행
• 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 객체 존재 여부와 객체 접근 권한 체크
• 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러짐

옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할 수행. DBMS의 두뇌.
• 어떻게 하면 옵티마이저 더 나은 선택을 할 수 있게 유도하는가가 핵심.

실행엔진

• 실행엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할 수행. 비유하자면 회사의 중간관리자 역할. 옵티마이저는 실행 계획을 구성하는 경영진.

핸들러(스토리지 엔진)

• 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 저장하고 디스크로부터 읽어 오는 역할을 담당.
• 핸들러는 결국 스토리지 엔진을 의미. MyISAM 테이블을 조작하는 경우 핸들러가 MyISAM 스토리지 엔진이 되고 InnoDB 테이블은 조작하는 경우에는 핸들럭가 InnoDB 스토리지 엔진이 됨.

5. 쿼리 캐시

• 쿼리 결과를 캐시했다가 데이터가 변경되면 캐시를 삭제(invalidate)함.
• 하지만 데이터 쓰기가 많은 서비스일 경우, 쿼리 캐시가 캐시 이점을 가지기 보단 캐시를 삭제하는 작업 부하나 수많은 버그 원인으로 지적된 사례가 많았음.
• MySQL 8.0 이상부터 기능이 제거됨. 데이터를 쓰기보다 읽기만 하는 서비스 유형이 그렇게 많지 않기 때문에 제거됨.

6. 스레드 풀

• MySQL Enterprise 가 아닌 Percona Server 의 쓰레드풀 기준으로 설명. MySQL 엔터프라이즈의 쓰레드풀은 내장되어 있지만 페르코나 서버는 플러그인 형태로 제공됨.
• 스레드풀은 내부적으로 사용자 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 서버 자원의 소모를 줄이는 것이 목적.
• 스레드 풀은 동시에 실행 중인 스레드들을 CPU 가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하기 위한 기능.
• 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처리가 더 느려지는 사례도 발생할 수 있음.
• 페르코나 서버 쓰레드 풀 기본 설정은 CPU 코어 개수만큼 스레드 그룹을 성생험. 스레드 그룹 개수는 thread_pool_size 시스템 변수를 변경해서 조정할 수 있음. 일반적으로 CPU 코어 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋음.
• 스레드 풀이 처리 중인 작업이 있는 경우에는 thread_pool_oversubscribe 시스템 변수(기본값은 3)에 설정된 개수만큼 추가로 더 받아들여서 처리함. 이 값이 너무 크면 비효율적으로 작동할 수 있으니 유의.
• 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부 를 판단. 스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하 면 새로운 스레드를 생성해서 스레드 그룹에 추가
• 페르코나 서버의 스레드풀 플러그인에는 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공함. 이렇게 되면, 먼저 시작된 트랜잭션 내에 있는 SQL을 빨리 처리해주면 해당 트랜잭션이 가진 잠금을 빨리 해제할 수 있어서, 잠금경합을 낮추어 전체적인 처리 성능이 향상시킬 수 있음.

페르코나 서버의 선순위 후순위 배치 기능

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

• DB 내 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 함. MySQL 5.7 버전까지는 테이블 구조를 FRM 파일에, 스토어드 프로그램은 *.TRN, *.TRG, *.PA 등과 같은 파일 기반으로 관리했음. 하지만 파일 기반 방식으로 메타데이터를 생성 또는 수정할 때 트랜잭션 지원이 되지 않기 때문에, 메타데이터 생성수정 작업 도중에 DB 서버가 비정상적으로 종료되면 메타데이터가 깨지는 문제가 발생했음.
• MySQL 8.0 부터는 테이블 구조 정보, 프로시저 프로그램 정보, 사용자 인증과 권한을 포함한 시스템 테이블 모두 InnoDB에 저장하도록 함.
• mysql.ibd 라는 테이블스페이스에 저장됨으로 주의해야 함.
• 참고로 이러한 메타데이터와 시스템 테이블 정보는 view 형태로 일부 정보를 볼 수 있거나 숨겨져서 접그을 하지 못함.
• InnoDB 를 사용하지 않는 테이블들은 SDI(Serialized Dictionary Information) 파일 기반 형식을 사용함.