[database] MySQL 엔진 아키텍처...1편
MySQL에서 서버는 머리에 해당하는 MySQL 엔진과 손발에 해당하는 스토리지 엔진으로 구분할 수 있다.
만약 스토리지 엔진은 핸들러 API를 만족한다면 누구든 스토리지 엔진이 구현 가능하여 MySQL 서버에 추가해서 사용이 가능하다. 그래서 이번 블로그에서는 직접 스토리지 엔진을 구현하기 전에 MySQL 서버에서 기본적으로 제공되는 InnoDB 스토리지 엔진과 MyISAM 스토리지 엔진을 구분하며 MySQL의 아키텍처에 대해 살펴보자.
MySQL 엔진 아키텍처
MySQL의 쿼리를 작성하고 더 좋게 튜닝하기 위해서는 기본적인 MySQL 엔진의 구조를 알아야 한다.
MySQL의 서버는 다른 RDBS에 비해 독특하다. 사용자 입장에서는 모를 수도 있지만 독특해 다른 RDBS에서 얻을 수 없는 장점이 존재하기도 하고 반대로 독특하여 단점도 존재한다.
MySQL은 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다. 그래서 사용 난이도가 어렵지 않은 DB 중 하나이고 C API부터 JDBC, ODBC 그리고. NET에서 표준 드라이버를 지원하며 이러한 드라이버로 모든 언어로 MySQL 서버에서 쿼리를 보낼 수 있다.
또한, 위 구조를 보면 스토리지 엔진 와 인터페이스와 옵티마이저 등을 구분하고자 나눠놓았지만 이후 설명할 때는 합쳐서 MySQL(서버)라고 이야기할 예정이니 참고하길 바란다.
MySQL 엔진
MySQL 엔진에는 여러 언어를 지원하는 커넥션 API로부터 오는 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서, 요청한 쿼리의 최적화된 실행을 위해 존재하는 SQL 옵티마이저가 존재한다. 또한, MySQL은 SQLD를 공부하였다면 한 번쯤 들어봤을 ANSI SQL 문법을 지원하기 때문에 만약 저 표준 문법에 맞춰 작성하였다면 다른 RDBS에도 호환되어 실행될 수 있다.
스토리지 엔진
위에서 설명한 MySQL 엔진은 설명한 그대로 접속 및 쿼리 요청을 전처리기를 거쳐 쿼리를 최적화하여 실행 등을 하는 머리에 해당하는 역할을 주로 하고 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 등의 부분을 스토리지 엔진에서 담당한다. 마치 머리가 있다면 손과 발이 있어야 움직이는 것과 같다. 위에 MySQL 서버의 구조를 보면 MySQL의 엔진은 하나인데 스토리지 엔진은 여러 개가 존재하는 것을 알 수 있다.
그리하여 MySQL의 엔진은 하나지만 테이블마다 스토리지 엔진을 다르게 설정하여 사용이 가능하다.
mysql> CREATE TABLE jsback_table (js1 INT, js2 INT) ENGINE=INNODB;
위와 같이 어떤 스토리지 엔진을 사용할지 지정하면 이후에 발생하는 모든 읽기와 변경 작업 등은 지정한 스토리지 엔진으로 처리가 된다.
또한, 각각의 스토리지 엔진은 성능 향상을 위해 키 캐시인 MyISAM 스토리지 엔진 또는 InnoDB 버퍼 풀과 같은 기능을 내장하고 있다.
핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰기 혹은 읽기를 해야 할 때는 각 스토리지 엔진에게 요청을 한다. 이때 하는 요청을 핸들러 요청이라고 부르는데, 여기서 사용되는 API를 핸들러 API라고 한다.
위에서 지정한 InnoDB 또한 핸들러 API를 사용하여 MySQL 엔진에게 요청하고 데이터를 주고받는다. 만약 핸들러 API를 이용하여 얼마나 많이 MySQL 엔진과 스토리지 엔진에게 요청을 하고 데이터를 주고받았는지 보고 싶다면
mysql> SHOW GLOBAL STATUS LIKE 'Handler%';
이런 식으로 쿼리문을 보내게 된다면 예시로 블로그를 쓰는 글쓴이의 현재 로컬 DB로 사용하는 MySQL의 핸들러 API에서 얼마나 데이터 작업이 있었는지 보자.
mysql> SHOW GLOBAL STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 732 | 트랜잭션에서 커밋이 발생한 횟수.
| Handler_delete | 8 | DELETE 쿼리가 실행된 횟수.
| Handler_discover | 0 | 커넥션에서 테이블을 발견하는 횟수.
| Handler_external_lock | 6747 | 외부 락 요청 횟수.
| Handler_mrr_init | 0 | Multi-Range Read (MRR) 초기화 작업 횟수.
| Handler_prepare | 0 | Prepared statement가 준비된 횟수.
| Handler_read_first | 40 | 테이블의 첫 번째 행을 읽은 횟수.
| Handler_read_key | 1844 | 특정 키로 데이터를 읽은 횟수 (인덱스를 사용한 조회).
| Handler_read_last | 0 | 테이블의 마지막 행을 읽은 횟수.
| Handler_read_next | 4261 | 다음 행을 읽은 횟수 (주로 범위 조회에서 사용).
| Handler_read_prev | 0 | 이전 행을 읽은 횟수.
| Handler_read_rnd | 0 | 랜덤 액세스로 데이터를 읽은 횟수. (인덱스를 사용하지 않고 조회)
| Handler_read_rnd_next | 234 | 랜덤 액세스를 사용하여 다음 행을 읽은 횟수.
| Handler_rollback | 0 | 트랜잭션에서 롤백이 발생한 횟수.
| Handler_savepoint | 0 | 세이브포인트를 설정한 횟수.
| Handler_savepoint_rollback | 0 | 세이브포인트 롤백이 발생한 횟수.
| Handler_update | 329 | UPDATE 쿼리가 실행된 횟수.
| Handler_write | 8 | INSERT 쿼리가 실행된 횟수.
+----------------------------+-------+
18 rows in set (0.04 sec)
직접 쿼리를 날려 핸들러 API에서 어떤 작업이 얼마나 일어났는지 이런 식으로 볼 수 있다.
MySQL 스레딩 구조
MySQL은 위 사진과 같이 스레딩 구조를 가진다. MySQL 서버는 PostgreSQL와 같이 프로세스 기반이 아닌 스레드 기반으로 작동하며, 포그라운드와 백그라운드로 나눌 수 있다. 만약 현재 MySQL의 서버에서 실행 중인 스레드 목록을 보고 싶다면 핸들러 API와 같이 쿼리를 날리면 된다.
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_read_thread | BACKGROUND | NULL | NULL |
| 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 7 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 8 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 9 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 10 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 11 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 12 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 13 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL | NULL |
| 14 | thread/innodb/log_checkpointer_thread | BACKGROUND | NULL | NULL |
| 15 | thread/innodb/log_flush_notifier_thread | BACKGROUND | NULL | NULL |
| 16 | thread/innodb/log_flusher_thread | BACKGROUND | NULL | NULL |
| 17 | thread/innodb/log_write_notifier_thread | BACKGROUND | NULL | NULL |
| 18 | thread/innodb/log_writer_thread | BACKGROUND | NULL | NULL |
| 19 | thread/innodb/log_files_governor_thread | BACKGROUND | NULL | NULL |
| 24 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | NULL |
| 25 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | NULL |
| 26 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | NULL |
| 27 | thread/innodb/buf_resize_thread | BACKGROUND | NULL | NULL |
| 28 | thread/innodb/srv_master_thread | BACKGROUND | NULL | NULL |
| 29 | thread/innodb/dict_stats_thread | BACKGROUND | NULL | NULL |
| 30 | thread/innodb/fts_optimize_thread | BACKGROUND | NULL | NULL |
| 31 | thread/mysqlx/worker | BACKGROUND | NULL | NULL |
| 32 | thread/mysqlx/worker | BACKGROUND | NULL | NULL |
| 33 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL |
| 37 | thread/innodb/buf_dump_thread | BACKGROUND | NULL | NULL |
| 38 | thread/innodb/clone_gtid_thread | BACKGROUND | NULL | NULL |
| 39 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | NULL |
| 40 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 41 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 42 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 44 | thread/sql/signal_handler | BACKGROUND | NULL | NULL |
| 45 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL |
| 43 | thread/sql/event_scheduler | FOREGROUND | event_scheduler | localhost |
| 47 | thread/sql/compress_gtid_table | FOREGROUND | NULL | NULL |
| 49 | thread/sql/one_connection | FOREGROUND | root | localhost |
+-----------+---------------------------------------------+------------+------------------+------------------+
이런식으로 현재 실행 중인 스레드 목록들을 볼 수 있고 아까 말한 포그라운드(FOREGROUND)와 백그라운드(BACKGROUND)로 나눠져 있는 것을 볼 수 있다. 이 많은 스레드 중에 마지막에 있는 thread/sql/one_connection이 실제로 사용자의 요청을 처리하는 포그라운드에 존재하는 스레드이다.
이 외에도 백그라운드 스레드의 개수는 서버 설정에 따라 가변적이고 스레드 목록을 보면 똑같은 것이 2개.. 3개 이상 보일 수 있는데 이것은 스레드가 동일한 작업을 병렬로 처리하기 위한 것이다.
포그라운드 스레드(클라이언트 스레드)
포그라운드 스레드는 최소한으로 MySQL 서버에 접속한 클라이언트의 수만큼 존재해야 한다. 하지만 스레드 풀을 사용한다면 말이 달라지는데 지금까지 설명한 스레드 모델은 MySQL 서버가 전통적으로 가지고 있던 스레드 모델이고 스레드 풀은 커뮤니티 에디션에서 사용되지 않고 엔터프라이즈 에디션과 Percona MySQL 서버에서 전통적인 스레드 모델과 스레드 풀 모델을 선택하여 사용할 수 있다.
간단하게 전통적인 스레드 모델과 스레드 풀의 차이점은 포그라운드 스레드와 커넥션의 관계 차이인데 전통적인 모델 같은 경우는 커넥션별로 포그라운드 스레드가 하나씩 생성되어 관계를 맺는 반면에 스레드 풀은 1대 1 관계가 아닌 1대다의 관계를 가질 수 있다는 점이 장점이다.
다시 본론으로 가서 포그라운드 스레드는 주로 각 클라이언트 사용자가 요청하는 쿼리를 처리하는 역할을 한다. 요청을 처리하고 난 후에는 스레드 캐시로 돌아가게 되는데 만약 스레드 캐시에 정해놓은 스레드 개수가 이미 충족했다면 스레드를 아예 종료해 버린다. 스레드 개수는 thread_cache_size 시스템 변수로 설정할 수 있다.
포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시에서 가져오고, 만약 존재하지 않는다면 디스크나 인덱스를 읽어 데이터를 가져오게 된다. MyISAM은 포그라운드 스레드가 디스크에 쓰기 작업까지 처리하기도 하고 쓰기 지연이 있어서 아닐 수도 있다. 그에 비해 InnoDB를 사용하는 테이블은 데이터 버퍼나 캐시에 존재하는 데이터까지만 포그라운드 스레드가 처리한 후 백그라운드 스레드가 이후 작업을 처리한다.
백그라운드 스레드
앞에서 말한 듯이 MyISAM은 포그라운드 스레드가 디스크에 쓰기 작업까지 처리하는 것이 있어 여기는 MyISAM은 별로 해당사항이 존재하지 않고 InnoDB는 아래와 같이 여러 가지 작업을 백그라운드에서 처리한다.
- 인서트 버퍼를 병합하는 스레드
- 로그를 디스크에 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
모두 중요한 역할을 하는 백그라운드 스레드를 볼 수 있고 여기서 가장 중요하다고 볼 수 있는 건 로그 스레드와 버퍼에 있는 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다. MySQL 5.5 버전부터 데이터를 쓰기와 읽기 스레드의 개수를 2개 이상 지정할 수 있는데 이것 또한 시스템 변수로 스레드 개수를 지정한다. InnoDB에서 데이터를 읽는 작업은 클라이언트 스레드에서 처리하기 때문에 읽기 스레드는 그렇게 많이 설정해도 필요가 없다. 다만 쓰기 스레드는 백그라운드에서 처리하기 때문에 일반적인 내장 디스크를 사용한다면 2 ~ 4로 설정하고 DAS(Direct Attached Stoage) 혹은 SAN(Storage Area Network)을 사용할 때는 최적으로 사용할 수 있을 만큼으로 설정해 주면 좋다.
사용자의 요청 중 쓰기 작업은 버퍼링 되어 처리할 수 있지만 읽기 작업은 클라이언트 스레드에서 처리하기 때문에 "5분 뒤에 데이터를 반환하겠습니다."와 같이 절대 지연될 수 없다. 그래서 일반 상용 RDBS 같은 경우는 쓰기 작업은 버퍼링 하여 일괄적으로 처리하는 경우가 있으며, InnoDB 또한 이러한 방식을 사용한다.