MySQL Server 아키텍처와 스레딩

이 글은 책 Real Mysql 8.0 4장, 업무에 바로 쓰는 SQL 튜닝 2장과 하단 Reference 참고했습니다. 잘못된 내용이 있다면 편하게 말씀해주세요 🙏🏻

간단하게 MySQL Server의 아키텍처와 스레딩을 살펴보고자 한다.

MySQL Server 아키텍처

MySQL 서버 아키텍처는 어떻게 되어 있을까?

두 가지 관점(SQL 쿼리 실행했을 때, MySQL 서버 구성요소)으로 살펴보자.

1. SQL 쿼리 실행 구조의 아키텍처

클라이언트가 SELECT * FROM 학생; 쿼리를 실행했다고 가정하자. 해당 SQL 문은 아래와 같은 구조로 실행된다. 

쿼리 실행 관점 MySQL 구조

1. 파서(parser) : 사용자 요청한 SQL를 쪼개 토큰으로 분리하고 트리 형태의 구조로 만든다. 이때 기본 문법 오류를 확인한다
2. 전처리기(preprocessor) : 파서에서 만들어진 트리를 토대로 SQL 문에 구조적인 문제가 없는지 확인한다. 테이블 이름, 컬럼 이름, 내장 함수와 같은 객체 존재 여부, 접근 권한을 확인한다.
3. 옵티마이저(optimizer) : MySQL의 핵심 엔진 중 하나로 SQL를 어떻게 실행할지 실행 계획을 결정한다. (어떤 순서로 테이블을 접근할지, 인덱스 사용 여부, 정렬할 때 인덱스 사용할지 임시 테이블 사용할지 등) 옵티마이저의 실행계획이 항상 최상의 실행 계획이 아닐 수 있다.
4. 엔진/쿼리 실행기(engin executor) : 실행 계획을 참고하여 스토리지 엔진에서 데이터를 가져온다. 데이터를 가져온 후 MySQL 엔진은 추가 작업을 한다(데이터 정렬, 조인, 불필요한 데이터 필터링 등), 이때 MySQL 엔진의 부하를 줄이기 위해선 스토리지 엔진에서 가져오는 데이터 양을 줄이는 게 매우 중요하다. 

2. MySQL 서버 구성요소의 아키텍처

아래 사진은 MySQL 공식 문서 중 MySQL 서버 아키텍처 사진이다.

클라이언트(MySQL Connectors, MySQL Shell)가 MySQL Server에게 접속하여 SQL를 사용하고 결과를 받고있다.

MySQL 서버 아키텍처

MySQl 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

 

MySQL 엔진

MySQL 엔진은 클라이언트의 접속부터 클라이언트가 요청한 SQL에 대한 결과까지 관여한다. (시작부터 끝까지)

커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저, 엔진 실행기 등이 있다.

 

스토리지 엔진

스토리지 엔진은 사용자가 요청한 SQL문을 토대로 DB에 저장된 디스크, 메모리로부터 데이터를 읽거나 저장한다.

읽는 경우 데이터를 가져온 후 해당 데이터를 MySQL 엔진으로 보낸다.

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 특정 테이블마다 스토리지 엔진을 지정할 수 있다.

아래 코드는 테이블 별 엔진을 다르게 설정한 예이다.

SQL> create table 학생테이블1(학번 int, 이름 varchar(100)) ENGINE=InnoDB;
SQL> create table 학생테이블2(학번 int, 이름 varchar(100)) ENGINE=MYISAM;

학생테이블1은 InnoDB 스토리지 엔진이 적용된 테이블이고 학생테이블2는 MyISAM 스토리지 엔진이 적용된 테이블이다.

 

+a) 스토리지 엔진 규약인 핸들러 API가 존재하는데 핸들러 API를 구현한 외부 플러그인이나 직접 구현하여 커스텀 스토리지 엔진 추가 할 수도 있다. 아래 코드는 통해 핸들러 API 몇 가지를 살펴볼 수 있다.

SQL> show global status like '%Handler%'; 

결과 
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1532    |
| Handler_delete             | 0       |
생략
| Handler_write              | 4579170 |
+----------------------------+---------+

---

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작한다. (리눅스는 스레드도 커널 입장에서 모두 프로세스로 처리하지만)

 

실행 중인 스레드 목록은 performance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.

SQL> 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             |
|         3 | thread/innodb/io_ibuf_thread                | BACKGROUND | NULL             | NULL             |

생략

|        30 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        31 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |

생략

|        42 | thread/sql/event_scheduler                  | FOREGROUND | event_scheduler  | localhost        |
|        46 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|        50 | thread/sql/one_connection                   | FOREGROUND | root             | localhost        |
+-----------+---------------------------------------------+------------+------------------+------------------+

조회한 MySQL 서버의 스레드 type은 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

위 예는 약 50개의 스레드 중 47개가 백그라운드 스레드이고 3개만 포그라운드 스레드이다.

특별한 점으로는 마지막 스레드 thread/sql/one_connection(id 50) 은 실제 사용자의 요청을 처리하는 포그라운드 스레드(클라이언트 스레드)이다. 백그라운드 스레드 개수는 MySQL 설정에 따라 내용과 수는 가변적이고, 동일한 스레드 이름은 병렬 처리하는 경우(id 30, 31)이다.

 

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

포그라운드 스레드는 각 클라이언트의 SQL 문장을 처리한다. 클라이언트가 MySQL 서버에 접속하면 커넥션이 생성되고(포그라운드 스레드) 커넥션이 종료되면 해당 커넥션 담당하는 스레드가 다시 스레드 캐시(Thread cache)로 돌아간다.

보통 포그라운드 스레드는 데이터를 MySQL의 버퍼나 캐시로부터 가져오며 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 읽어와 처리한다. 자세하게는 스토리지 엔진마다 다르게 작동한다

MyISAM 테이블은 디스크 읽기 쓰기 모두 포그라운드 스레드가 처리한다.

InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드가 처리하고 나머지 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB 테이블은 아래 작업을 백그라운드로 처리한다.(MyISAM 해당 X)

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

앞에서 살펴봤지만 InnoDB의 MyISAM와 다르게 쓰기 작업을 백그라운드에서 실행된다 이는 쓰기 작업을 지연(버퍼링) 처리할 수 있는 장점을 가지고 있다. (읽기 작업은 지연되면 안 된다. 예를 들어 select 쿼리 결과를 10분 뒤에 알려주면 큰 문제가 발생한다.) MyISAM은 쓰기 버퍼링 기능이 없다

---

⛓ Reference

Real MySQL 8.0 8.0 1권 - 백은빈, 이성욱 저

업무에 바로 쓰는 SQL 튜닝 - 양바른 저

https://dev.mysql.com/doc/internals/en/custom-engine.html

https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

[일일 회고] 22.03.29 - "프로세스와 LWP, Green - Native Thread, 싱글톤 레지스트리"