[MySQL] MySQL 엔진 아키텍처 파헤치기

전체적인 아키텍처

1. MYSQL 엔진

• 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러, SQL 파서, 옵티마이저 등 요청된 SQL 문장을 분석하거나 최적화하는 엔진이다. GROUP BY, ORDER BY 등 복잡한 처리는 해당 엔진의 처리 영역인 쿼리 실행기에서 실행된다.

2. 스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 읽어들이는 역할을 하는 엔진이다. MYSQl 엔진에서는 반드시 핸들러 API를 통해 스토리지 엔진과 데이터를 주고받는다. MyISAM, InnoDB 두 가지 종류가 존재한다.

SHOW GLOBAL STATUS LIKE 'Handler%';  // 핸들러 종류 보기

• MySQL 서버 (mysqld ) 에서는 다양한 스토리지 엔진을 지원하고 있다.

SHOW ENGINES;;  // 엔진 종류 보기

🔖 쿼리 캐시
이전에는 SQL 의 실행 결과를 메모리에 캐시하여 동일 SQL 쿼리가 실행되면 즉시 결과를 반환하도록 하여 높은 성능을 도모하였다. 하지만 테이블의 데이터에 변경이 일어나면 정합성을 위해 즉시 캐시 데이터를 모두 삭제하여야 했으니, 이는 심각한 동시 처리 성능 저하를 유발해 MySQL 8.0에서 쿼리 캐시 기능은 제거되었다.

☁️ MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동한다.

https://dung-beetle.tistory.com/66

정확히 어떤 스레드들이 돌아가고 있는지 보려면, performance_schema 데이터베이스의 threads 테이블을 통해 확인 가능하다.

SELECT .. FROM performance_schema.threads

1. 포그라운드 스레드 : 실제 사용자의 요청을 처리하는 스레드
2. 백그라운드 스레드 : thread/sql/one_connection 를 제외한 모든 41개의 스레드로, 여러 스레드가 동일 백그라운드 작업을 병렬로 처리하여 이름이 중복되어 나타난다.

전통적인 스레드 모델은 커넥션 별로 포그라운드 스레드 1:1 관계이지만, 엔터프라이즈 에디션에서는 스레드 풀을 사용해 하나의 스레드가 다수 커넥션 요청을 전담할 수 있다.

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

포그라운드 스레드는 MySQL 서버에 접속된 클라이언트 수만큼 존재하며, 각 요청 쿼리를 처리한다. 사용자가 커넥션을 종료하면, 해당 스레드는 스레드 캐시로 되돌아간다.

🔖 스레드 캐시란?
일종의 connection / Thread pool 역할이며, 이미 일정 개수 이상 대기 중인 스레가 있으면 넣지 않고 버려 스레드 캐시는 일정 개수를 유지할 수 있도록 한다. thread_cache_size 옵션을 통해 사이즈를 지정할 수 있다.
https://dung-beetle.tistory.com/70

데이터를 가져올때 1차적으로 MySQL 데이터 버퍼나 캐시를 검사하고, 없을 때만 디스크나 인덱스 파일로부터 데이터를 읽어오는 구조이다.

2. 백그라운드 스레드

InnoDB 에선, 다음과 같이 여러 작업이 백그라운드로 처리된다.

1. 인서트 버퍼를 병합하는 스레드
2. 로그를 디스크로 기록하는 스레드(Log Thread)
3. InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드(Write Thread)
4. 데이터를 버퍼로 읽어 오는 스레드
5. 잠금이나 데드락을 모니터링하는 스레드

InnoDB 에서는, 아래와 같은 차이점 때문에 읽기 작업 스레드와 쓰기 작업 스레드를 분리해놓고 있다.

1. 읽기 작업과 같은 경우 클라이언트 스레드(포그라운드)에서 처리하기 때문에 많은 스레드가 필요하지 않다.

2. 반면 쓰기 작업과 같은 경우 아주 많은 작업을 백그라운드로 처리한다. 또한 읽기 작업과 달리 지연되어 처리될 수 있기 때문에 데이터 일괄 처리 기능(모아두었다가 한꺼번에 쓰기)을 지원한다. 일반적인 내장 디스크를 사용한다면 2~4개 정도가 적당하다.

☁️ 메모리 할당 및 사용 구조

MySQL 서버의 메모리 구조이다. 글로벌 메모리 영역은, MySQL 서버가 시작되면서 운영체로부터 메모리를 할당받는다.
두 메모리 영역은, 여러 스레드들의 공유 여부에 따라 나누어진다.

1. 글로벌 메모리 영역

클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당되며, 모든 스레드에 의해 공유되는 전역 공간이다. 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼 영역이 존재한다.

해당 영역 크기를 크게 설정하면 종료되기 전까지 메모리에 남아있기 때문에 주의해서 설정이 필요하다.

2. 로컬 메모리 영역(세션 메모리 영역)

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이므로, 각 스레드마다 독립적으로 할당된다.

특이한 점은, 해당 로컬 메모리 영역의 커넥션 버퍼나 결과 버퍼는 커넥션이 살아 있는 동안 계속 할당되어 있는 영역이지만, 소트 버퍼나 조인 버퍼는 쿼리를 실행하는 순간에만 할당했다가 다시 해제된다. 이처럼 유동적으로 메모리가 관리된다.

☁️ 쿼리 실행 구조

1. 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어내는 작업을 의미한다. 기본 문법 오류를 여기에서 파악하고 사용자에게 오류 메시지를 전달한다.

🔖 토큰?
MySQL이 인식할 수 있는 최소 단위의 어휘나 기호

2. 전처리기

파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 존재하지 않거나, 권한 상 사용할 수 없는 개체의 토큰을 파악하고 걸러낸다.

3. 옵티마이저

쿼리 문장을 가장 저렴한 비용으로 빠르게 처리할지 결정하는 역할을 수행한다.

4. 실행 엔진

실행 엔진은 옵티마이저를 통해 만들어진 최적화된 계획대로, 각 핸들러에게 요청해서 받은 결과를 다시 핸들러 요청의 입력으로 연결하는 역할을 수행한다. 예를 들어, WHERE 절에 일치하는 레코드를 읽어오도록 요청한 이후 해당 데이터를 임시 테이블로 저장하도록 요청할 수 있다.

5. 핸들러(스토리지 엔진)

MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고, 디스크로부터 읽어 오는 역할을 담당한다.

☁️ 스레드 풀

MySQL 서버 엔터프라이즈 에디션만 스레드 풀 기능을 제공하지만, 여기서는 Percona Server 에서 제공하는 스레드 풀 기반으로 설명하고 있다.

스레드 풀은 사용자의 요청에 비례해 급격히 증가하는 스레드 개수를 줄여서, 항상 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 해 자원 소모를 줄일 수 있도록 한다.

스레드 풀 동작 과정

새로운 쿼리 요청이 들어왔을 때 만약 스레드 풀의 모든 스레드가 작업 중이라면, 스레드 풀의 타이머 스레드는 최대thread_pool_stall_limit 으로 설정된 시간만큼 기다리고, 처리가 완료되지 않으면 새로운 스레드를 생성해 그룹에 추가한다.

이때 thread_pool_max_threads 값을 넘을 수는 없기 때문에,해당 값을 0 에 가깝게 설정하면 기다리지 않고 매번 요청마다 스레드를 생성하게 되므로 풀을 생성하는 의미가 없어진다.

스레드 풀 주의사항

하지만, 스케줄링 과정에서 오히려 빈번한 컨텍스트 스위칭으로 인해 쿼리 처리가 더 느려질 수 있기 때문에 무작정 스레드 풀을 설정한다고 해서 성능이 2배 좋아지지 않는다.

그렇다면 어느 정도 스레드 개수가 성능을 올리는데 적합할까?

적절한 스레드 수를 설정하면, 프로세서 친화도를 높이고 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있다.

일반적으로 스레드 풀 개수 = CPU 코어 개수 가 프로세서 친화도를 높이는 데 좋다. 스레드마다 CPU 가 바인딩 되어 있으니, 랜덤으로 다른 스레드를 실행시키지 않아 자연스럽게 컨텍스트 스위칭 비용이 줄어들기 때문이다.

🔖 프로세서 친화도(Processor=Cache affinity)
프로세스를 지속적으로 동일 프로세서에서 스케줄링할 가능성을 의미한다.

지정된 프로세서에서 실행된 프로세스 실행에 필요한 데이터들이 캐시 메모리 데이터에 남아 있을 수 있다. 따라서, 동일한 프로세서에서 실행할 인터럽트가 거의 없는 CPU 집약적인 프로세스를 예약하면 캐시 미스와 같은 성능 저하 이벤트를 줄여 성능을 향상시킬 수 있다. 하지만 특정 CPU에서 큐에 존재하는 모든 프로세스가 끝나는 순간 CPU에 대기시간이 생긴다는 단점이 있다.

우선순위 큐

참고로, Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.

먼저 요청된 사용자의 트랜잭션 내부에 속한 쿼리를 먼저 처리해주면, 해당 트랜잭션이 가지고 있던 잠금이 빠르게 해제되어 잠금 경합을 낮춰 전체적인 성능을 향상시킬 수 있기 때문이다.

출처:
RealMySQL 8.0
https://en.wikipedia.org/wiki/Processor_affinity
https://icksw.tistory.com/127
https://velog.io/@bangseungho/멀티-프로세서-스케쥴링