✅ MySQL 엔진 아키텍처

MySQL 서버의 아키텍처는 크게 MySQL 엔진과 MySQL 스토리지 엔진으로 구분된다.

각 엔진에는 다양한 주제가 존재하지만, 그중 제일 중요한 주제는 아래와 같다고 생각한다.

• MySQL 엔진의 "SQL 옵티마이저"
• MySQL 스토리지 엔진의 "InnoDB"

📌 MySQL 엔진

▶︎ MySQL 엔진

클라이언트로부터 접속 및 쿼리 요청을 처리한다. SQL 파싱, 최적화, 캐싱 등의 기능을 담당한다.

▶︎ MySQL 스토리지 엔진

MySQL 엔진으로부터 요청 받아 물리적 데이터의 읽기/쓰기를 담당한다.

▶︎ 핸들러 API

쿼리를 실행 하기 위해 MySQL 엔진은 스토리지 엔진에 읽기/쓰기를 요청한다. 이때 요청을 "핸들러(Handler) 요청"이라 부르며, 여기서 사용되는 API를 "핸들러 API"라 부른다.

이러한 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 아래 명령문을 통해 확인할 수 있다.

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

📌 MySQL 스레딩 구조

MySQL은 프로세스 기반이 아닌 스레드 기반으로 작동되며, 이러한 스레드는 크게 포그라운드(클라이언트) 스레드와 백그라운드 스레드로 구분된다.

※ PostgreSQL은 프로세스 기반으로 동작.

MySQL에서 실행 중인 스레드는 performance_schema DB의 threads 테이블에서 확인할 수 있다.

mysql> select * from performance_schema.threads;

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

포그라운드 스레드는 MySQL에 접속한 클라이언트 사용자가 요청하는 쿼리문을 처리한다.

클라이언트의 작업을 마치고 종료될 때 해당 작업을 담당한 스레드는 스레드 캐시로 반환(되돌아)된다.

이러한 스레드 캐시의 최대 개수를 설정하는 시스템 변수가 thread_cache_size이다.

포그라운드 스레드는 데이터를 버퍼나 캐시로부터 가져온다. 데이터가 없는 경우 직접 디스크나 인덱스 파일에서 읽어온다.

▶︎ 백그라운드 스레드

InnoDB 기준 다양한 작업을 백그라운드 스레드가 처리된다. 그중 중요한 백그라운드 스레드 작업은 "로그 스레드(Log thread)"와 "쓰기 스레드(Write thread)"다.

InnoDB에서 읽기(read) 작업은 주로 클라이언트 스레드에서 처리하지만, 쓰기 작업의 경우 백그라운드 스레드에서 처리하기 때문에 적절한 설정과 확인이 필요하다.

쓰기 스레드의 시스템 변수는 innodb_write_io_threads이다.

📌 쿼리 실행 구조

클라이언트(사용자)가 MySQL 서버에 쿼리문을 전송하면 위 그림처럼 동작한다.

▶︎ 쿼리 파서

쿼리 문장을 토큰 단위로 분리하여 트리 형태로 만든다.
이때 쿼리문의 기본 문법 오류를 검출하고 클라이언트에게 오류 메시지를 전달한다.

토큰 단위 : MySQL이 인식할 수 있는 가장 작은 단위

▶︎ 전처리기

파서 트리에 구조적 문제가 있는지 확인한다.
이때 테이블, 컬럼, 함수 등 각 개체의 이름과 접근 권한, 실제 존재 여부 등을 확인한다.

▶︎ 옵티마이저

요청받은 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할 수 있게 실행 계획을 만든다.

옵티마이저는 일반적으로 적은 비용으로 최고의 성능(속도)이 나오게 실행 계획을 만든다.

하지만, 비용보다 속도를 최우선으로 설정할 수도 있다.

▶︎ 실행 엔진

옵티마이저가 만든 실행 계획에 따라 실행 엔진은 핸들러 API로 스토리지 엔진(InnoDB 등)에 행 단위 읽기/쓰기를 요청하고, 결과 집합을 클라이언트로 반환한다.

📌 스레드 풀(Thread Pool)

MySQL은 기본적으로 사용자 연결(요청 작업)과 스레드가 1대1로 이루어져 있다.

이러한 이유로 동시 연결(요청 작업)이 발생하면 그만큼 스레드도 늘어나게 된다. 즉, 동시에 실행되는 작업이 많을 경우 CPU 사용 또한 늘어난 스레드만큼 사용되게 된다.

단순 연결(요청 작업 X)인 경우는 대기 중인 상태이기 때문에 별도의 CPU 자원을 사용하지 않는다.

스레드 풀(Thread Pool)은 동시에 처리되는 요청 작업이 많아도 처리하는 스레드 개수를 제한하여 CPU가 제한된 개수의 스레드 처리에 집중할 수 있게 한다. 즉, IT Infra 자원 소모를 줄이는 목적으로 사용한다.

스레드 풀(Thread Pool)은 MySQL Enterprise Edition에서 제공되는 기능이지만 Percona Server에서 제공하는 오픈소스 스레드 풀 플러그인 라이브러리를 설치/사용할 수 있다.

※ Percona Server에서 제공하는 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다.

이러한 스레드 풀은 스레드 그룹으로 이루어져 있으며, 스레드 그룹의 개수는 thread_pool_size 시스템 변수로 조절할 수 있다.

스레드 그룹의 개수는 일반적으로 CPU 코어 개수와 맞춰 설정한다.

▶︎ 스레드 풀 동작

MySQL 서버에서 사용자 연결(요청)이 생기면 스레드 풀로 처리를 이관한다.

이때 스레드 풀 내 그룹 스레드의 모든 스레드(워커 스레드)가 작업 중이라면 스레드 풀은 스레드 그룹에 새로운 스레드를 추가할지 또는 기존 스레드 작업의 완료를 기다릴지 판단한다.

이러한 판단은 thread_pool_stall_limit 시스템 변수에 의해 결정된다. 해당 변수에 설정된 시간을 넘기게 되면 그룹 스레드에 새로운 스레드를 추가한다.

• 그룹 스레드는 시스템 변수에 설정된 스레드 수를 넘길 수 없다.

  그룹 스레드의 스레드 수를 경정하는 시스템 변수는 배포판, 플러그인에따라 thread_pool_max_threads 또는 thread_pool_max_active_query_threads를 사용한다.

• 만약 응답 시간에 민감한 서비스라면 thread_pool_stall_limit 시스템 변수값을 낮춰 사용하는 게 좋다.

  하지만, 0에 가까운 값으로 설정하는 것은 권장하지 않는다.
  (0에 가까운 값을 설정하면 스레드 풀을 사용하는 이유가 없음)