MySQL Architecture, 제대로 이해하기

 

 

MySQL Server의 구조를 파악하는 것이 본 포스팅의 목적입니다.

 

 

MySQL Architecture

TLDR;

MySQL 서버는 크게 MySQL Engine과 Storage Enigne으로 구분할 수 있습니다.

 

✔️ MySQL Engine: 요청된 SQL 문장을 분석하거나 최적화 등 처리. 논리적인 면에서 두뇌의 역할

✔️ Storage Engine: 손발 역할. 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어옴

 

여기서 중요한 점은 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데,

각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 점입니다.

 

 

 

Big Picture

 

Real MySQL 8.0

 

 

위의 그림을 위에서 부터 살펴보면 Client와 통신을 하는 MySQL 서버와

그 MySQL 서버가 MySQL엔진, 스토리지 엔진으로 나누어지는 것을 확인할 수 있습니다.

그리고 운영체제 하드웨어의 디스크가 스토리지 엔진을 통해 실질적으로 저장되는 공간임을 확인할 수 있습니다.

 

다양한 클라이언트에서 MySQL를 사용할 수 있는데요.

그 이유는 MySQL 서버를 통해 모든 언어에서 MySQL 서버로 쿼리를 사용할 수 있도록 지원하기 때문입니다.

 

 

MySQL Engine

요청된 SQL 문장을 분석하거나 최적화 등 처리합니다.

사람의 두뇌와 같이 논리적인 절차를 통해 효율적인 결정을 내리도록 합니다.

 

MySQL 엔진은 커넥션 핸들러, SQL 파서 및 전처리기, 옵티마이저 중심으로 구성되어 있습니다.

커넥션 핸들러는 클라이언트로부터의 접속 및 쿼리 요청을 처리하고,

SQL 파서 및 전처리기는 쿼리문을 읽어서 가공하고,

옵티마이저는 쿼리의 최적화된 실행을 담당합니다.

 

MySQL은 표준 SQL(ANSI SQL) 문법을 지원합니다.

때문에 표준 문법에 따라 작성된 쿼리는 다른 DBMS와 호환되어 실행할 수 있습니다.

 

 

Storage Engine

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

MySQL 엔진이 두뇌였다면, 스토리지 엔진은 사람의 손발과 같이 실제 데이터를 가져오고 저장하는 역할을 합니다.

 

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다.

아래와 같이 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리합니다.

 

CREATE TABLE test_table (fd1 INT, td2 INT) ENGINE=INNODB;

 

위 쿼리를 통해 test_table 테이블이 InnoDB 스토리지 엔진을 사용하도록 지정했는데요.

test_table 테이블에 INSERT, UPDATE, DELETE, SELECT, ... 등의 작업이 발생하면 InnoDB 엔진이 처리합니다.

각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM Storage Enigne)나 InnoDB 버퍼 풀(InnoDB Storage Enigne)과 같은 기능을 내장합니다.

 

 

Handler API

: MySQL 엔진이 스토리지 엔진에 쓰기 또는 읽기 시 요청 시 사용하는 API

 

MySQL 엔진의 쿼리 실행기에서 최적화를 한 후, 실제 작업을 위해 데이터를 쓰거나 읽어야 하겠죠.

MySQL 엔진이 DB의 스토리지 엔진으로 요청을 보낼텐데,

이 때 보내는 요청을 핸들러 요청이라고 하며 이 요청에 사용하는 API를 핸들러 API라고 합니다.

 

MySQL 엔진 ---[Handler Request(Read, Write Request)]-->  스토리지 엔진 :: Handler API

 

가령 InnoDB 스토리지 엔진은 이 Handler API를 통해 MySQL 엔진과 데이터를 주고받습니다.

 

Handler API를 통한 데이터(레코드) 작업이 어느정도 이루어졌는지 확인하고 싶다면,

아래와 같은 쿼리를 통해 확인할 수 있습니다.

 

mysql> show global status like 'Handler%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Handler_commit             | 606730    |
| Handler_delete             | 5308      |
| Handler_discover           | 0         |
 ...
| Handler_update             | 2965355   |
| Handler_write              | 126536909 |
+----------------------------+-----------+
18 rows in set (0.46 sec)

 

 

 

Threading Architecture

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

MySQL 서버의 스레드는 Foreground Thread, Background Thread 로 구분할 수 있습니다.

 

MySQL 서버에서 실행 중인 스레드 목록은 다음과 같이 performance_schema DB의 threads 테이블을 통해 확인할 수 있습니다.

 

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             |
...
|        43 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        45 | thread/sql/signal_handler                   | BACKGROUND | NULL             | NULL             |
|        47 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        44 | thread/sql/event_scheduler                  | FOREGROUND | NULL             | NULL             |
|        46 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|     15763 | thread/sql/one_connection                   | FOREGROUND | root             | localhost        |
+-----------+---------------------------------------------+------------+------------------+------------------+
44 rows in set (0.11 sec)

 

필자가 확인했을 때에는 44개의 스레드가 실행 중이며

41개의 백그라운드 스레드와 3개의 포그라운드 스레드가 실행되고 있음을 확인할 수 있습니다.

예제 코드의 마지막(thread_id 15763)의 thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드입니다.

 

백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있습니다.

동일한 이름의 스레드가 2개 이상씩 보일 수 있는데, 이는 여러 스레드가 동일한 작업을 병렬로 처리하는 경우입니다.

 

 

Foreground Thread

= Client Thread

 

Foreground Thread는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 

주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리합니다.

 

또한, Foreground Thread는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져옵니다.

만약 버퍼나 캐시에 없는 경우, 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리합니다.

 

MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용됩니다.
클라이언트가 MySQL 서버에 접속하면, MySQL 서버는 그 요청을 처리해 줄 스레드를 생성해 그 클라이언트들에게 할당합니다.

이 스레드는 DBMS의 앞단에서 사용자(클라이언트)와 통신하기 때문에 포그라운드 스레드라고 하며, 사용자가 요청한 작업을 처리하기 때문에 사용자 스레드라고도 합니다.

 

포그라운드 스레드는 아래와 같이 스토리지 엔진에 따라 역할의 범위에 차이가 있습니다.

 

MyISAM: 디스크 쓰기 작업까지 포그라운드 스레드가 처리 (지연된 쓰기가 있지만 일반적인 방식은 아님)

InnoDB: 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

 

 

✔️ Thread cache

클라이언트가 커넥션을 종료하면 해당 커넥션을 담당하던 Thread는 다시 Thread cache로 되돌아갑니다.

이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 합니다.

 

이때, 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정합니다.

 

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

 

 

 

Background Thread

InnoDB는 아래 같이 여러 가지 작업이 백그라운드로 처리됩니다. 

MyISAM은 해당 사항이 별로 없습니다.

 

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

 

모두 중요한 역할을 하지만 그중에서도 가장 중요한 것은 로그 스레드(Log thread)와 쓰기 스레드(Write thread)입니다.

여기서 쓰기 스레드는 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리합니다.

 

InnoDB에서 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되는 반면, 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리합니다.

그래서 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 충분한 값으로 설정해야합니다.

쓰기 스레드는 일반적인 내장 디스크를 사용할 때는 2~4 정도,

DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋습니다.

MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며, 

innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정합니다.

 

⚠️ 스레드가 너무 많아지면 오히려 성능이 저하됩니다.

스레드는 적정선의 충분한 값으로 설정하세요.
아래의 두 가지 이유로 스레드가 너무 많아지면 오히려 성능이 저하될 수 있습니다.

1. 너무 많은 스레드로 작업을 분할하게 되면, 각 스레드의 작업 양이 너무 적어서 스레드의 작업 양에 비해 스레드의 시작과 종료의 오버헤드로 생기는 비효율이 커집니다.
2. 너무 많은 스레드가 실행되면 한정된 하드웨어 리소스를 공유하는 방식으로 인해 오버헤드가 발생합니다.

 

사용자 요청 처리 중 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만,

데이터의 읽기 작업은 절대 지연되지 않기 때문에 InnoDB를 포함한 일반적인 상용 DBMS에는

대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 처리됩니다.

 

그래서 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리를 작업하기 위해 데이터 변경이 필요한 경우,

데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됩니다.

 

반면, MyISAM는 사용자 스레드가 쓰기 작업까지 함께 처리되도록 설계되어 있으며

일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없습니다.

 

 

 

 

 

그럼 지금까지 MySQL의 구조에 대해 알아보았습니다.

오타나 잘못된 내용을 댓글로 남겨주세요!

감사합니다 ☺️ 

 

 

 

 

| 참고 |

Real MySQL 8.0