[Real MySQL] 4.1. MySQL 아키텍처

MySQL 엔진 아키텍처

MySQL 서버 전체 구조 (출처)

클라이언트
MySQL 엔진
- 요청된 SQL 문장 분석 최적화 처리
- 표준 SQL(ANSI SQL) 문법 지원
- 포함되는 항목
  - 커넥션 핸들러 : 클라이언트로부터 접속 및 쿼리 요청 처리
  - SQL 파서 및 전처리기
  - 옵티마이저 : 최적화된 쿼리 실행
스토리지 엔진
- 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽음
- 하나의 MySQL 엔진에서 여러 개의 스토리지 엔진을 동시에 사용 가능
  
  특정 테이블에 사용할 스토리지 엔진 지정
```
mysql> CREATE TABLE test_table (fb1 INT, fb2 INT) ENGINE=INNODB;
```
핸들러(Handler) API
- 핸들러 요청 : 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기/읽기 요청
- 핸들러 API : 핸들러 요청에 사용되는 API InnoDB 스토리지 엔진도 해당 API를 통해 MySQL 엔진과 데이터를 주고 받음 SHOW GLOBLA STATUS LIKE 'Handler%' 를 통해 데이터 작업량 확인 가능

쓰레딩 구조

MySQL 서버는 쓰레드 기반으로 작동한다. (다른 것은 프로세스 기반)

포그라운드(Foreground) 스레드, 백그라운드(Background) 스레드로 구분할 수 있다.

MySQL 서버에서 실행중인 쓰레드 목록 확인 performance_schema 데이터베이스의 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             |
|         3 | thread/innodb/io_ibuf_thread                | BACKGROUND | NULL             | NULL             |
|         4 | thread/innodb/io_read_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_write_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/page_flush_coordinator_thread | BACKGROUND | NULL             | NULL             |
|        13 | thread/innodb/log_checkpointer_thread       | BACKGROUND | NULL             | NULL             |
|        14 | thread/innodb/log_flush_notifier_thread     | BACKGROUND | NULL             | NULL             |
|        15 | thread/innodb/log_flusher_thread            | BACKGROUND | NULL             | NULL             |
|        16 | thread/innodb/log_write_notifier_thread     | BACKGROUND | NULL             | NULL             |
|        17 | thread/innodb/log_writer_thread             | BACKGROUND | NULL             | NULL             |
|        18 | thread/innodb/log_files_governor_thread     | BACKGROUND | NULL             | NULL             |
|        21 | thread/innodb/srv_lock_timeout_thread       | BACKGROUND | NULL             | NULL             |
|        22 | thread/innodb/srv_error_monitor_thread      | BACKGROUND | NULL             | NULL             |
|        23 | thread/innodb/srv_monitor_thread            | BACKGROUND | NULL             | NULL             |
|        24 | thread/innodb/buf_resize_thread             | BACKGROUND | NULL             | NULL             |
|        25 | thread/innodb/srv_master_thread             | BACKGROUND | NULL             | NULL             |
|        26 | thread/innodb/dict_stats_thread             | BACKGROUND | NULL             | NULL             |
|        27 | thread/innodb/fts_optimize_thread           | BACKGROUND | NULL             | NULL             |
|        28 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        29 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        34 | thread/innodb/buf_dump_thread               | BACKGROUND | NULL             | NULL             |
|        35 | thread/innodb/clone_gtid_thread             | BACKGROUND | NULL             | NULL             |
|        36 | thread/innodb/srv_purge_thread              | BACKGROUND | NULL             | NULL             |
|        37 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        38 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        39 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        41 | thread/sql/signal_handler                   | BACKGROUND | NULL             | NULL             |
|        42 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        43 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        40 | thread/sql/event_scheduler                  | FOREGROUND | event_scheduler  | localhost        |
|        44 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|        76 | thread/sql/one_connection                   | FOREGROUND | root             | localhost        |
+-----------+---------------------------------------------+------------+------------------+------------------+
38 rows in set (0.01 sec)

type 컬럼을 통해 포그라운드인지 백그라운드인지 확인 가능하다.

processlist_user, processlist_host를 통해

백그라운드 스레드의 개수는 MySQL 서버의 설정에서 조정 가능하다.

동일한 이름의 쓰레드가 2개 이상이라면 병렬로 처리하는 경우이다.

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

포그라운드 쓰레드는 적어도 MySQL 서버에 접속된 클라이언트 수만큼 존재한다.

각 클라이언트 사용자가 요청한 쿼리를 처리한다.

작업을 마치고 커넥션을 종료하면 쓰레드 캐시(Thread Cache)로 돌아간다.

쓰레드 캐시에 일정 개수 이상의 대기 중인 쓰레드가 있다면 종료된다.

최대 쓰레드 개수 설정 시스템 변수 : thread_cache_size

MyISAM : 디스크 쓰기 작업까지 처리
InnoDB : 데이터 버퍼나 캐시까지만 처리 (나머지는 백그라운드 쓰레드가 처리)

백그라운드 쓰레드

MyISAM은 백그라운드 쓰레드로 처리하지 않는다.

InnoDB에서는 아래 내용이 처리된다.

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

Log Thread

Write Thread

메모리 할당 및 사용 구조

글로벌 메모리 영역

MySQL 서버가 시작하면서 생성되는 영역으로 생성되면 모든 쓰레드에 의해 공유된다.

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

로컬 메모리 영역

정렬 버퍼(Sort buffer)
조인 버퍼
바이너리 로그 캐시
네트워크 버퍼

플러그인 스토리지 엔진 모델

사용자 인증에 필요한 Native Authentication, Caching SHA-2 Authentication 도 플러그인으로 구현

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Engine	Support	Comment	Transactions	XA	Savepoints
ndbcluster	NO	Clustered, fault-tolerant tables	null	null	null
FEDERATED	NO	Federated MySQL storage engine	null	null	null
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
ndbinfo	NO	MySQL Cluster system information storage engine	null	null	null
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO

컴포넌트

MySQL 8.0 부터는 기존의 플러그인 아키텍처를 대처하기 위해 지원된다.

플러그인의 단점

플러그인은 MySQL 서버와 인터페이스 할 수 있고 플러그인끼리 통신 불가
플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음 (캡슐화 되어 있지 않음)
플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

쿼리 실행 구조

쿼리 실행 과정

쿼리 캐시 → 퀴리 파서 → 전처리기 → 옵티마이저 → 쿼리 실행 엔진 → 스토리지 엔진

쿼리 파서

기본적인 SQL 문법 오류 체크한다.
SQL 문장을 토큰(의미 있는 단위)으로 분리하여 트리(Parse Tree)로 만든다.

https://shardingsphere.apache.org/document/5.1.0/en/reference/sharding/parse/

전처리기

Parser Tree 기반으로 SQL 문장에 구조적인 문제가 있는지 확인하다.
각 토큰을 테이블 명, 칼럼 명, 내장 함수가 존재하는지와 사용할 수 있는 권한이 있는지 하나씩 검증한다.

옵티마이저

SQL을 최적화하여 처리할 수 있도록 쿼리 실행 계획을 수립한다.

실행 엔진

옵티마이저가 만든 계획대로 각 핸들러에게 요청하고 결과를 다른 핸들러에게 전달한다.

핸들러(스토리지 엔진)

MySQL 실행 엔진 요청에 따라 데이터를 디스크로 읽기/쓰기 작업을 수행한다.

복제(Replication)

쿼리 캐시(Query Cache) (제거됨)

빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 중요 역할을 담당했었다.

SQL 실행 결과를 메모리에 캐싱하여 같은 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하여 빠른 성능을 보였다.

동시 처리 성능 저하와 버그 등의 원인으로 MySQL 8.0 부터는 기능에서 완전히 제거되었다.

쓰레드 풀(Thread Pool)

MySQL Enterprise에서만 기능을 지원한다.

MySQL Community에서 사용하려면 플러그인을 설치해야 한다.

책에서는 Percona Server 플러그인을 소개하고 있다.

트랜잭션 지원 메타데이터

테이블 구조, 스토어드 프로그램 등의 정보를 저장하는 곳을 데이터 딕셔너리 또는 메타데이터 라고 한다.

5.7까지는 테이블 구조를 파일 기반으로 관리했으나 이런 파일 기반 메타데이터는 생성/변경 작업 트랜잭션을 지원하지 않는다.

서버가 비정상적으로 종료되면 데이터가 일관되지 않는 경우가 발생한다. (무결성 깨짐)

8.0 부터는 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되었다.

중간에 비정상적으로 종료되면 완전한 성공/실패로 정리된다. (원자성 보장)

참고 내용

Real MySQL 8.0 1권 (백은빈, 이성욱)