MySQL엔진의 아키텍쳐를 알아보자

소프트웨어를 만들때 아키텍쳐가 정말 중요한 부분이라는 생각이 점점 커지는 요즘입니다!

그래서 Real MySQL 을 읽고 공부한 MySQL과 InnoDB의 엔진 아키텍쳐를 정리해보고자 합니다.

 

MySQL 아키텍쳐

 

MySQL은 MySQL 엔진 + 스토리지 엔진 두가지 엔진으로 이루어져 있습니다.

이 둘을 합하여 MySQL, MySQL서버라고 부릅니다.

 

MySQL 엔진

클라이언트로 부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리 최적화 실행을 위한 옵티마이저가 존재합니다.

데이터를 디스크에 저장하는 작업 외 전반적인 MySQL기능들은 대부분 MySQL엔진에서 이루어집니다. 

스토리지 엔진

요청된 SQL을 분석하거나 최적화 하는 중 DBMS의 핵심입니다.

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로 부터 데이터를 읽어옵니다.

-- ENGIND 파라미터로 스토리지 엔진을 지정 할 수 있습니다.
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

 

핸들러API

MySQL엔진은 스토리지 엔진에 읽기 또는 쓰기를 요청하게되는데 이를 핸들로 요청이라 합니다.

이때 사용되는 API를 핸들러 API라고 합니다.

-- 핸들러 API 확인 명령어
SHOW GLOBAL STATUS LIKE 'Handler%';

 

 

MySQL 스레딩 구조

 

MySQL은 스레드 기반으로 동작하며 포그라운드 스레드 백그라운드 스레드로 구분됩니다.

아래 명령어로 실행중인 스레드 목록을 확인 할 수 있습니다.

 

48스레드 thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드입니다.

동일한 이름의 스레드가 있는 이유는 동일한 작업을 병렬로 처리하기 떄문입니다.

SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads ORDER BY type, thread_id;

 

포그라운드 스레드 (클라이언트 스레드)

포그라운드 스레드는 최소 MySQL서버에 접속된 클라이언트 수만큼 존재하며 클라이언트가 요청하는 쿼리 문장을 처리합니다.

클라이언트와의 커넥션이 종료되면 해당 커넥션을 담당하는 스레드는 다시 스레드 캐시(Thread cache)로 돌아갑니다.

만약 스레드 캐시가 가득 차있다면 스레드는 종료됩니다. (thread_cache_size로 캐시 수를 설정합니다.)

 

백드라운드 스레드

InnoDB에서는 아래와 같은 일들이 백그라운드 스레드로 처리됩니다.

 

- 인서트 버퍼(Insert Buffer) 병합

- 디스크에 로그 기록 (Log Thread 사용)

- 디스크에 InnoDB 버퍼 풀의 데이터 기록 (Write Thread)

- 데이터를 버퍼로 읽기

- Lock, DeadLock 모니터링

 

InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리하지만 버퍼 풀의 데이터를 디스크에 기록하는 Write Thread는 아주 많고 중요한 작업을 처리하기 때문에 충분히 설정해야합니다. (내장 디스크 2 ~ 4 , DAS나 SAN의 경우 디스크 최적량으로)

 

InnoDB에서 쓰기 작업은 버퍼링하여 디스크에 일괄적으로 저장되도록 처리됩니다.

만약 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 InnoDB 버퍼풀에 존재하였다가 Write Thread를 통해 주기적으로 또는 특정 시점에 데이터가 디스크 파일로 완전히 저장 됩니다.

디스크에는 존재하지 않고 버퍼풀에만 데이터가  존재할 때 'Dirty Page'로 표시되며 읽기 요청이 들어오면 버퍼풀에서 즉시 제공됩니다.

 

메모리 할당 및 사용 구조

글로벌 메모리 영역, 로컬 커넥션 영역으로 구분됩니다.

글로벌 메모리 영역은 MySQL 서버가 시작되면서 OS로 부터 할당됩니다. (MySQL 시스템 변수로 설정해둔 만큼 할당받습니다.)

 

글로벌 메모리 영역

하나의 메모리 공간만 할당됩니다.

필요에 따라 2개 이상도 가능하지만 생성된 N개의 글로벌 메모리 영역은 모든 스레드(포그라운드 + 백그라운드 스레드)에 의해 공유됩니다.

버퍼링, 캐싱, 로그기록, 백그라운드 작업에 사용되는 영역입니다.

아래는 대표적인 글로벌 메모리 영역입니다.

 

- 테이블 캐시

- InnoDB 버퍼 풀 (InnoDB의 주요 캐싱 메커니즘으로, 데이터와 인덱스 페이지를 디스크에서 읽어와 메모리에 캐싱)

- InnoDB 어댑티브 해시 인덱스 (InnoDB 버퍼 풀에 저장된 데이터에서 자주 사용되는 인덱스를 해시 구조로 관리)

- InnoDB 리두 로그 버퍼 (데이터 변경 작업에 대한 로그를 기록)

 

로컬 메모리 영역

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다. 

클라이언트 메모리 영역, 세션 메모리 영역이라고도 말합니다.

 

클라이언트 별로 독립적으로 메모리가 할당되며 공유되지 않습니다.

쿼리의 용도별로 필요한 공간이 할당되고 필요하지 않은 경우에는 메모리 공간을 할당하지 않을수도 있습니다.

커넥션이 열려있는 동안에만 계속 할당되어 있는 공간 (커넥션 버퍼, 결과 버퍼), 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간 (소트 버퍼, 조인 버퍼)도 있습니다.

커넥션이 종료되면 할당된 모든 로컬 메모리는 해제됩니다.

 

대표적으로 아래와 같은 곳에서 사용됩니다.

 

- 커넥션 버퍼 (클라이언트와 서버 간의 데이터 통신을 위한 기본 네트워크 버퍼)

- 정렬 버퍼(Sort Buffer)

- 바이너리 로그 캐시 (트랜잭션이 커밋되기 전에 로그를 디스크에 기록하기 전 임시로 저장)

- 네트워크 버퍼 (쿼리를 처리할 때 클라이언트에서 받은 데이터가 먼저 네트워크 버퍼에 저장)

 

 

플러그인 아키텍쳐, 컴포넌트 아키텍쳐

사용자들의 요구를 만족시키기 위해 MySQL서버에 기능을 확장하기 위한 모델

컴포넌트는 플러그인 아키텍쳐의 단점 (플러그인간 통신 불가, 캡슐화 안됨 등)을 보안하고 나온 차세대 아키텍쳐입니다.

 

 

쿼리 실행 구조

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리형태의 구조로 만들어냅니다.

기본 문법 오류를 필터링하고 오류 메세지를 전달합니다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 구조적인 문제 확인

각 토큰을 테이블, 컬럼 이름 또는 내장 함수와 같은 객체를 매핑하여 각 존재여부와 객체의 접근 권한을 확인합니다.

존재하지 않는 테이블이나 컬럼, 함수에 접근한다면 전처리기에서 걸러집니다.

옵티마이저

들어온 쿼리 문장을 가장 효율적인 방법으로 처리하도록 도와줍니다.

실행 엔진

옵티마이저가 계획한 방법을 실행하기 위해 각 핸들러(스토리지 엔진)에게 필요한 작업들을 요청 후 최종 결과를 사용자나 모듈로 전달합니다. 

실제 DB내의 데이터를 얻기 위한 인터페이스라고 생각하시면 좋을 것 같습니다.

예를 들어 임시테이블 생성, WHERE조건에 일치하는 레코드 읽기, 읽어온 데이터를 임시테이블 저장, 최종결과 반환 과 같은 작업들을 각 핸들러에게 요청후 각 결과들을 취합하여 반환합니다.

헨들러 (스토리지 엔진)

실행엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크에서 불러오는 역할을 담당합니다.

위의 그림에서 쿼리 실행기에서 스토리지 엔진으로 나가는 화살표라고 생각하시면 좋을 것 같습니다.

복제 (Replication)

복제(Replication)는 1개 이상의 레플리카(replica) 저장소가 소스 저장소와 동기화를 자동으로 유지하는 과정입니다.
source-replica구조로 데이터의 백업이나 보호, 분산저장 등의 목적으로 사용되는 기능입니다.

스레드 풀

(엔터프라이즈 버전은 지원하지만 커뮤니티 버전 지원 X, 플러그인 형태로 추가하여 사용은 가능)

 

사용자의 요청을 처리하는 스레드 수를 줄여서 동시 처리하는 요청이 많아지더라고 MySQL서버의 CPU가 제한된 개수의 스레드 처리에만 집중 할 수 있게 서버의 자원을 줄이는 것이 목적입니다.

기본적으로 코어의 수만큼 스레드를 생성합니다. (thread_pool_size 시스템 변수로 관리)

thread_pool_size의 스레드가 모두 사용중일때에는 thread_pool_oversubscribe에 지정된 수(기본값3) 만큼 풀에 스레드를 추가로 받아들여 처리합니다.

 

스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크하여 thread_pool_stall_limit 변수에 지정된 밀리초만큼 작업 스레드가 지금 처리중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가합니다. (thread_pool_max_threads 크기를 넘을수는 없음)

즉 스레드의 새로운 요청이 들어온다면 무조건 thread_pool_stall_limit 만큼은 요청이 대기되기에 적절한 값을 설정하는것이 중요합니다.

 

트랜잭션 지원 메타데이터

테이블의 구조정보와 스토어드 프로그램(프로시저, 트리거, 함수 ,,, ) 등 의 정보를 메타데이터 라고합니다.

MySQL서버가 동작하기 위해 기본적으로 필요한 테이블을 시스템 테이블 이라고 하는데, MySQL 8.0이후 에서는 시스템 테이블과 메타데이터 정보를 모두 모아 InnoDB 스토리지 엔진의 mysql DB에 저장됩니다. (mysqlDB는 통째로 mysql.ibd라는 테이블 스페이스에 저장됩니다.)

그렇기에 테이블 구조가 변경되는 스키마 작업같은 경우에 MySQL서버가 비정상 종료가 되어도 완전한 성공, 완전한 실패로 정리됩니다.

 

추가로 mysqlDB의 시스템테이블과 메타데이터를 확인하려면 information_schema DB의 TABLES, COLUMN과 같은 VIEW를 통해 조회할 수 있습니다.

mysql DB에 실제로 존재하지만 해당 DB가 아닌 다른 DB의 view를 이용해 확인해야합니다.