MySQL 아키텍처

MySQL의 전체 구조 

---

MySQL 서버는 크게 MySQL엔진과 스토리지 엔진으로 구분할 수 있다. 쿼리 & 파서 & 옵티마이저 등과 같은 기능을 스토리지 엔진과 구분해서 이해하고자 다음과 같이 구분하였으며, 이 둘을 모두 합쳐서 MySQL(서버)라고 호칭한다.

 

 

1. MySQL 엔진

---

MySQL엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와, SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다. 즉 DBMS의 두뇌에 해당하는 처리를 수행한다.

 

 

2. 스토리지 엔진

---

실제 데이터를 디스크 스토리지에 WRITE & READ하는 과정은 스토리지 엔진에서 전담하게 된다. 스토리지 엔진은 여러 개를 동시에 사용할 수 있으며, 테이블 정의 시 지정해서 사용할 수 있다. 각 스토리지 엔진은 성능 향상을 위한 특화된 기능을 내장하고 있다.

 

• MyISAM : 키 캐시
• InnoDB : 버퍼 풀

참고로 위 기능들은 MySQL 서버의 글로벌 메모리 영역에 존재하며, 메인 메모리 내에서 데이터 또는 인덱스 데이터가 접근될 때 해당 데이터를 캐시하는 영역이다. 이를 활용하면 자주 접근되는 데이터를 메모리에서 바로 획득할 수 있으며 전체 작업의 수행 속도를 증가 시킬 수 있다. MySQL을 위한 서버에서는 물리 메모리의 최대 80%까지를 InnoDB의 버퍼 풀로 할당하여 사용하는 경우가 많다.

 

MySQL 엔진의 쿼리 실행기에서 데이터를 W/R 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데 이러한 요청을 핸들러(Handler) 요청이라고 하고, 여기에서 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받게 된다. 아래 명령을 통해 API가 호출된 횟수를 확인할 수 있다.

mysql> SHOW GLOBAL STATUS LIKE 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 588   |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 6121  |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 40    |
| Handler_read_key           | 1701  |
| Handler_read_last          | 0     |
| Handler_read_next          | 4011  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 796   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 315   |
| Handler_write              | 3     |
+----------------------------+-------+

 

3. MySQL 스레딩 구조

---

MySQL 서버는 프로세스가 아닌 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다. 

다음 명령을 통해 서버에서 실행 중인 스레드의 목록을 확인할 수 있다.

mysql> SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id;
+-----------+---------------------------------------------+------------+------------------+------------------+
| thread_id | name                                        | type       | processlist_user | processlist_host |
+-----------+---------------------------------------------+------------+------------------+------------------+
|         1 | thread/sql/main                             | BACKGROUND | NULL             | NULL             |
|         2 | thread/mysys/thread_timer_notifier          | BACKGROUND | NULL             | NULL             |
|         4 | thread/innodb/io_ibuf_thread                | BACKGROUND | NULL             | NULL             |
|         5 | thread/innodb/io_log_thread                 | BACKGROUND | NULL             | NULL             |
|         6 | thread/innodb/io_read_thread                | BACKGROUND | NULL             | NULL             |
|         7 | thread/innodb/io_read_thread                | BACKGROUND | NULL             | NULL             |
// ... 
|        44 | thread/sql/event_scheduler                  | FOREGROUND | event_scheduler  | localhost        |
|        48 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|        49 | thread/sql/one_connection                   | FOREGROUND | root             | localhost        |
+-----------+---------------------------------------------+------------+------------------+------------------+

위에서, 마지막 스레드만 실제 사용자의 요청을 처리하는 Foreground 스레드이다. 

백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있으며, 동일 이름 스레드가 2개 이상씩 보이는 것은 여러 스레드가 동일 작업을 병렬로 처리하는 경우이다.

 

참고로, 위 스레드 모델은 전통적으로 MySQL이 가진 스레드 모델이며, MySQL 커뮤니케이션 모델에 해당한다. MySQL 엔터프라이즈 에디션 또는 플러그인 설치(Percona)를 통해 스레드 풀(Thread Pool) 모델을 사용할 수도 있다. 둘의 차이는 포그라운드 스레드와 커넥션간의 관계이다.

- 전통 모델 : 커넥션 별로 포그라운드 스레드가 하나씩 생성되고 할당된다.(스레드 : 커넥션 = 1:1)
- 스레드 풀 : 하나의 포그라운드 스레드가 여러 개의 커넥션 요청을 전담한다.(스레드 : 커넥션 = 1 : N)

 

 

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

최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread Cache)로 되돌아가게 된다. 이 때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있다면 스레드 캐시에 넣지 않고 종료시킨다. 이 개수는 thread_cache_size 시스템 변수로 설정할 수 있다. 뒤에 쓰레드 풀에서 캐싱과 함께 비교해서 알아볼 예정이므로, 우선 넘어가자.

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+

특징으로는, 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

 

MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리한다.
InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드에서 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드에서 처리하게 된다.

 

2) 백그라운드 스레드

InnoDB에만 해당사항이 있는 경우이며, 다음 작업이 백그라운드 스레드에서 처리된다.

 

• 인서트 버퍼(Insert Buffer)를 병합하는 스레드
• Log Thread : 로그를 디스크로 기록하는 스레드 -> 중요
• Write Thread : InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 -> 중요
• Read Thread : 데이터를 버퍼로 읽어 오는 스레드
• 잠금, 데드락을 모니터링하는 스레드

 

MySQL 5.5 버전부터 아래 시스템 변수를 통해 데이터 읽기, 쓰기 스레드의 수를 지정할 수 있게 되었다.

mysql> show variables like '%io_thread%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+

InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리하기 때문에 읽기 스레드를 많이 할당할 필요는 없지만, 쓰기 스레드는 대부분의 작업을 백그라운드 스레드에서 처리하기 때문에 충분히 설정하는 것이 좋다.

 

 

쓰기 지연

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 일괄적으로 처리해도 무방하다.(읽기는 지연되서는 안된다.)

여기에서도 스토리지 엔진별로 가능 여부가 나뉘게 된다.

 

• InnoDB : 쓰기 지연(버퍼링) 처리가 가능하다.
• MyISAM : 클라이언트 스레드가 쓰기 작업도 함께 수행한다. 따라서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

 

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

---

MySQL에서 사용되는 메모리 공간은 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분되며, 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 

 

 

1) 글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당되기 때문에, 모든 스레드에 의해 공유된다. 

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

 

2) 로컬 메모리 영역(= 클라이언트 메모리 영역, 세션 메모리 영역)

세션 메모리 영역이라고도 표현하며, MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.

각 클라이언트 스레드별로 독립 할당되어 절대 공유하지 않는다는 특징이 있다.

각 쿼리의 용도별로 필요할 때만 공간이 할당되는 특징이 있는데, 예시로 소트 버퍼, 조인 버퍼가 있다. 

커넥션이 열려있는 동안 계속 할당된 상태로 남아있는 공간도 있으며, 예시로 커넥션 버퍼, 결과 버퍼가 있다.

 

 

5. 쿼리 실행 구조

---

실행 구조에 앞서, MySQL 엔진과 스토리지 엔진의 처리 영역에 대해서 간단히 살펴보자.

MySQL은 "플러그인 모델"이라는 개념을 통해 부가적인 기능을 플러그인해서 사용할 수 있는 특징을 가지고 있다. 대표적인 플러그인 대상으로 스토리지 엔진이 존재한다.

 

MySQL 엔진과 스토리지 엔진의 처리 영역

위와 같이 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막의 데이터 읽기/쓰기 작업만 스토리지 엔진에서 처리된다. MySQL 엔진은 각 스토리지 엔진에게 데이터 읽기/쓰기 명령을 요청하려면 반드시 핸들러 API를 통해서 해야 하며, 'Handler_'로 시작되는 상태변수로 호출 횟수가 기록된다. 실질적인 GROUP BY, ORDER BY 등 복잡한 처리는 MySQL 엔진의 영역인 SQL 실행 엔진(쿼리 실행기)에서 처리된다.

핵심은, '하나의 쿼리 작업이 여러 하위 작업으로 나뉘며, 각 하위 작업이 어떤 엔진에서 처리되는지를 구분할 수 있어야 한다'는 것이다.

 

 

위의 처리영역을 좀 더 자세히 알아보면 다음과 같다.

 

쿼리 실행 구조

1) 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위 어휘 또는 기호)으로 분리해서 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류가 여기에서 발견되며, 사용자에게 오류 메시지를 전달한다.

 

2) 전처리기

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

 

3) 옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다. 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는가를 학습할 필요가 있다.

 

4) 실행 엔진

옵티마이저가 두뇌(경영진)라면, 실행 엔진은 중간 관리자, 핸들러(스토리지 엔진)는 실무자로 비유할 수 있다. 만들어진 실행 계획 대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

 

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

핸들러는 MySQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미한다.

 

 

6. 스레드 풀

---

앞서, MySQL 기본 스레드 전략과 스레드 풀의 가장 큰 차이는 (포그라운드)스레드 당 처리할 수 있는 커넥션 수라고 언급했다.

mysql> show variables like 'thread_%';
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| thread_cache_size | 9                         |
| thread_handling   | one-thread-per-connection |
| thread_stack      | 1048576                   |
+-------------------+---------------------------+

위는 MySQL 커뮤니티 버전의 기본 전략으로, one-thread-per-connection라고 작성되어있음을 알 수 있다. 

Variable_name	Comments
thread_cache_size	스레드 풀에서 캐싱한 최대 스레드 개수
thread_handling	서버에서 사용하는 스레드 처리 모델 - one-thred-per-connection : 커넥션 마다 스레드 할당 - pool-of-threads : 스레드 풀 사용(여러 커넥션 처리 가능)
thread_stack	스레드 스택의 크기

 

이때 앞서 언급되었던 스레드 캐시와 스레드 풀의 차이가 헷갈려서 조금 더 알아봤다.

 

스레드 캐시

MySQL 서버에서는 스레드 할당으로 인한 리소스 낭비 및 성능 저하를 막기 위해 특정 개수의 스레드를 메모리에 캐싱해서 사용한다. 

 

 

 

 

스레드 풀의 목적은, 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 하여 서버의 자원 소모를 줄이는 것이 관건이다.

앞서 간단히 스레드 풀 기능은 엔터프라이즈 에디션에서만 사용이 가능하다고 했다. 커뮤니티 에디션에서는 Percona Server 플러그인을 추가 설치해서 스레드 풀 기능을 활용할 수 있다.

 

동작 원리

서버는 요청이 들어올 때마다 새 스레드를 생성하는 대신, 스레드 풀에게 작업을 위임한다.

스레드 풀은 놀고있는 스레드가 생기면 작업을 스레드에 할당하여 실행되도록 한다.

작업은 내부적으로 큐에 저장되며, 쓰레드 풀의 쓰레드들은 여기서 작업을 빼서 실행한다. 큐에 새로운 작업이 들어오면 놀고 있는 쓰레드가 큐에서 작업을 빼서 실행한다. 이를 두고 쓰레드에 작업이 할당되었다고 하며, 작업이 할당되지 않은 쓰레드들은 큐에 새 작업이 들어올 때까지 대기 상태로 유지된다.

 

사용되는 몇 가지 환경 변수를 알아보자.

 

1) thread_pool_size

스레드 그룹의 개수를 조정할 수 있다. 일반적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하며, 보통 한 번에 한 개의 active Thread를 갖는다.

 

2) thread_pool_oversubscribe

한 개의 스레드 그룹 안에서 몇 개의 스레드까지 동시에 active 상태일 수 있게할 것인지를 나타내며, 디폴트 값은 3이다.

스레드 그룹은 보통 한 번에 한 개의 active 스레드를 갖는데, 만약 타이머 스레드가 지연(stall)을 감지하면 스레드 그룹에 active 스레드를 추가할 수 있다. thread_pool_oversubscribe 값이 너무 크면 스케줄링해야 할 스레드가 많아져서 스레드풀이 비효율적으로 작동할 수도 있다.

 

3) thread_pool_stall_limit

주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다.

 

4) thread_pool_max_threads

전체 스레드 풀에 있는 스레드의 개수를 의미하며, 이 값을 제한한다.

 

 

추가로, Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능을 제공한다. 이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가진 잠금이 빨리 해제되고 잠금 경합을 낮춰 전체적인 처리 성능을 향상시킬 수 있다.

 

 

7. 트랜잭션을 지원하는 메타데이터(데이터 딕셔너리)

---

테이블의 구조 정보와 스토어드 프로그램(스토어드 프로시져, 함수 등) 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다. 기존 MySQL(5.7까지) 서버는 테이블 구조를 FRM 파일에 저장하고 스토어드 프로그램 또한 파일 기반으로 관리했었다. 하지만 이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.

 

예를 들어 테이블의 컬럼이 변경된 상태에서 MySQL 서버가 비정상적으로 종료된다면, 파일 기반이기 때문에 구조가 변경된 상태로 남지만 실제로는 비정상적으로 종료되었기 때문에 구조 변경 사항이 롤백되어야 하는게 맞다. 

 

MySQL 8.0 버전 부터는 이러한 메타데이터 및 시스템 데이터(MySQL 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블이 있다.)를 모두 MySQL DB에 저장하고 있다.(mysql.ibd)

 

이러한 변경 사항은 InnoDB 스토리지 엔진을 사용하는 테이블의 경우에만 적용되므로, MyISAM, CSV와 같은 스토리지 엔진의 메타 정보는 여전히 저장 공간이 필요하다. 이를 위해 SDI(Serialized Dictionary Information) 파일을 사용하며, 이름 그대로 직렬화를 위한 포맷이므로 InnoDB 테이블의 구조도 SDI로 변환할 수 있다.

또한 ibd2sdi 유틸리티를 통해 InnoDB 테이블로부터 스키마 정보를 추출할 수 있다.