[스터디] 04. 아키텍처

목차

 
4장 분량이 많아서 4.1을 따로 분리해서 공부하기로 했습니다. 이번 장에서는 MySQL 엔진과 MySQL 서버에서 기본으로 제공되는 스토리지 엔진에 대해 학습해 봅니다.

04. 아키텍처

4.1 MySQL 엔진 아키텍처

---

▲ MySQL 서버의 전체 구조

4.1.1 MySQL의 전체 구조

---

• MySQL은 대부분의 프로그래밍 언어의 접근 방법을 모두 지원함.
  • 예) C/C++, PHP, 자바, 펄, 파이썬, 루비, .NET, 코볼 등 모든 언어
• MySQL(또는 MySQL 서버) = MySQL 엔진 + 스토리지 엔진

 
4.1.1.1 MySQL 엔진
 

• 커넥션 핸들러: 다양한 클라이언트로부터의 접속 요청을 처리함.
• SQL 파서: 클라이언트로부터 받은 SQL 쿼리를 해석하고 구문적인 정확성을 검사함. 쿼리를 여러 컴포넌트로 분해하여 데이터베이스가 이해할 수 있는 형태로 변환함.
• 전처리기: SQL 파서에 의해 분석된 쿼리를 데이터베이스 내의 데이터와 연결함. 이 과정에서 쿼리가 참조하는 데이터의 유효성을 검증하고, 필요한 보안 검사를 수행함.
• 옵티마이저: 쿼리의 최적화된 실행을 위한 방법을 결정함.

 
4.1.1.2 스토리지 엔진
 

• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할.
• MySQL은 단일 데이터베이스 내에서 여러 개의 스토리지 엔진을 동시에 사용할 수 있는 유연성을 제공함.

--- 테이블(test_table)이 사용할 스토리지 엔진(InnoDB) 지정
--- 이후 해당 테이블의 모든 읽기 및 변경 작업은 정의된 스토리지 엔진이 처리
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

책에서 알려준 코드를 통해 InnoDB 스토리지 엔진을 사용하는 test_table이라는 테이블을 생성해보겠습니다.

테이블 생성 후, 해당 테이블이 정상적으로 InnoDB 엔진을 사용하고 있는지 확인하기 위해 SHOW TABLE STATUS 명령을 사용합니다. 이 명령은 테이블의 상세 정보를 보여줍니다.

SHOW TABLE STATUS FROM test_database LIKE 'test_table';

아래 사진은 SHOW TABLE STATUS 명령을 실행한 결과입니다. Engine 열에서 InnoDB로 표시되는 것을 확인할 수 있습니다.

 
4.1.1.4 핸들러 API
 

• MySQL 엔진과 스토리지 엔진 간의 인터페이스로서 쿼리 실행기가 데이터를 읽거나 쓸 필요가 있을 때, 해당 요청을 스토리지 엔진으로 전달함.
• 핸들러(Handler): MySQL 엔진이 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때, 각 스토리지 엔진에 보내는 쓰기 또는 읽기 요청.

--- 핸들러 API의 데이터 작업 확인
SHOW GLOBAL STATUS LIKE 'Handler%';

위 예제의 SHOW GLOBAL STATUS 명령을 실행하면 아래 사진과 같습니다. 어떤 유형의 작업이 데이터베이스에서 가장 자주 발생하는지, 또 어떤 작업이 자원을 많이 사용하는지 확인할 수 있습니다. 로컬 컴퓨터에서 외부 잠금이 가장 많이 발생하고 있는 것을 알 수 있습니다.

4.1.2 MySQL 스레딩 구조

---

• MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동함.
• 포그라운드(Foreground) 스레드 + 백그라운드(Background) 스레드

SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads
ORDER BY type, thread_id;

위 예제의 조회문을 실행하면 아래 사진과 같습니다. MySQL에서 실행 중인 스레드의 상태를 확인하기 위해, performance_schema 데이터베이스의 threads 테이블에서 정보를 조회합니다.

type 컬럼은 스레드가 포그라운드인지 백그라운드인지를 나타냅니다. 전체 44개의 스레드가 실행 중이고, 그중 41개는 백그라운드 스레드이고 3개는 포그라운드 스레드입니다.
 
대부분의 백그라운드 스레드는 사용자 이름과 호스트 이름이 NULL로 설정되어 있습니다. 스레드들이 시스템 내부에서 자동으로 생성되고 관리되기 때문입니다. 동일 작업을 병렬로 처리하는 경우, 같은 이름의 여러 스레드가 활성화될 수 있습니다.
 
실제 사용자 요청을 처리하는 포그라운드 스레드 중 'thread/sql/one_connection'은 사용자가 MySQL 데이터베이스에 연결되어 직접 쿼리를 실행할 때 활성화됩니다. 이 스레드는 사용자와 데이터베이스 간의 인터랙션을 처리하며, 사용자의 요청을 데이터베이스 시스템 내에서 실행합니다.

+-----------+---------------------------------------------+------------+------------------+------------------+
| 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/page_flush_thread             | BACKGROUND | NULL             | NULL             |
|        15 | thread/innodb/page_flush_thread             | BACKGROUND | NULL             | NULL             |
|        16 | thread/innodb/page_flush_thread             | BACKGROUND | NULL             | NULL             |
|        17 | thread/innodb/log_checkpointer_thread       | BACKGROUND | NULL             | NULL             |
|        18 | thread/innodb/log_flush_notifier_thread     | BACKGROUND | NULL             | NULL             |
|        19 | thread/innodb/log_flusher_thread            | BACKGROUND | NULL             | NULL             |
|        20 | thread/innodb/log_write_notifier_thread     | BACKGROUND | NULL             | NULL             |
|        21 | thread/innodb/log_writer_thread             | BACKGROUND | NULL             | NULL             |
|        22 | thread/innodb/log_files_governor_thread     | BACKGROUND | NULL             | NULL             |
|        27 | thread/innodb/srv_lock_timeout_thread       | BACKGROUND | NULL             | NULL             |
|        28 | thread/innodb/srv_error_monitor_thread      | BACKGROUND | NULL             | NULL             |
|        29 | thread/innodb/srv_monitor_thread            | BACKGROUND | NULL             | NULL             |
|        30 | thread/innodb/buf_resize_thread             | BACKGROUND | NULL             | NULL             |
|        31 | thread/innodb/srv_master_thread             | BACKGROUND | NULL             | NULL             |
|        32 | thread/innodb/dict_stats_thread             | BACKGROUND | NULL             | NULL             |
|        33 | thread/innodb/fts_optimize_thread           | BACKGROUND | NULL             | NULL             |
|        36 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        40 | thread/innodb/buf_dump_thread               | BACKGROUND | NULL             | NULL             |
|        41 | thread/innodb/clone_gtid_thread             | BACKGROUND | NULL             | NULL             |
|        42 | thread/innodb/srv_purge_thread              | BACKGROUND | NULL             | NULL             |
|        43 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        44 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        45 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        47 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        50 | thread/sql/con_sockets                      | BACKGROUND | NULL             | NULL             |
|       285 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|       286 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        46 | thread/sql/event_scheduler                  | FOREGROUND | event_scheduler  | localhost        |
|        49 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|       279 | thread/sql/one_connection                   | FOREGROUND | root             | localhost        |
+-----------+---------------------------------------------+------------+------------------+------------------+

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

• 포그라운드 스레드의 수는 최소한 MySQL 서버에 접속된 클라이언트의 수와 동일함.
• 각 클라이언트가 서버에 접속하면 해당 클라이언트를 위한 스레드가 생성되어, 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리함.
• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 스레드는 스레드 캐시(Thread cache)로 반환됨.
• 스레드 캐시에 최대 스레드 개수(thread_cache_size 시스템 변수)가 설정되어 있으면, 추가적인 스레드는 종료됨.
• MySQL의 데이터 버퍼나 캐시에서 데이터를 읽어오거나, 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 작업을 수행함.

 
4.1.2.2 백그라운드 스레드
 

• 대부분의 DBMS에서 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만, 데이터의 읽기 작업은 절대 지연될 수 없음.
• MyISAM: 백그라운드 스레드를 사용하지 않음. 모든 작업(읽기 및 쓰기)은 사용자 스레드가 직접 처리함. 따라서 INSERT, UPDATE, DELETE 쿼리가 완료될 때까지 사용자가 기다릴 필요 없음.
• InnoDB: 다양한 백그라운드 작업을 처리하기 위해 여러 백그라운드 스레드를 활용함.
  • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
  • 로그를 디스크로 기록하는 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 데이터를 버퍼로 읽어 오는 스레드
  • 잠금이나 데드락을 모니터링하는 스레드

4.1.3 메모리 할당 및 사용 구조

---

4.1.3.1 글로벌 메모리 영역
 

• 모든 메모리 공간이 MySQL 서버가 시작되면서 운영체제로부터 할당됨.
• 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됨.
• 대표적인 글로벌 메모리 영역
  • 테이블 캐시: 자주 접근하는 테이블 메타데이터를 캐싱하여 성능을 향상시킴.
  • InnoDB 버퍼 풀: InnoDB 테이블의 데이터와 인덱스를 캐싱함.
  • InnoDB 어댑티브 해시 인덱스: 자주 접근하는 데이터에 대한 해시 인덱싱을 제공하여 검색 속도를 향상시킴.
  • InnoDB 리두 로그 버퍼: 데이터 변경 사항을 먼저 로그에 기록하여 데이터 무결성을 보장함.

 
4.1.3.2 로컬 메모리 영역
 

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
• 세션 메모리 영역, 클라이언트 메모리 영역, 세션 메모리 영역이라고도 부름.
  • 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간 → 클라이언트 메모리 영역
  • 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문 → 세션 메모리 영역
• 메모리 할당
  • 각 클라이언트 스레드별로 독립적으로 할당되어 절대 공유되어 사용되지 않음.
  • 각 쿼리의 용도별로 필요할 때만 공간이 할당되고, 필요하지 않는 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있음.
• 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있음.
  • 예) 커넥션 버퍼, 결과 버퍼
• 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간.
  • 예) 소트 버퍼, 조인 버퍼
• 대표적인 로컬 메모리 영역
  • 커넥션 버퍼: 각 클라이언트 커넥션에 대해 할당되며, 커넥션이 열려 있는 동안 데이터를 저장하는 데 사용됨.
  • 정렬(Sort) 버퍼: 쿼리에서 ORDER BY나 GROUP BY를 처리할 때 사용되며, 필요한 데이터를 정렬하는 데 사용됨.
  • 조인 버퍼: 두 테이블의 조인 연산을 수행할 때 사용되며, 조인된 데이터를 임시로 저장함.
  • 바이너리 로그 캐시: 트랜잭션의 변경 사항을 임시로 저장하며, 트랜잭션이 커밋되면 바이너리 로그로 기록됨.
  • 네트워크 버퍼: 클라이언트와 서버 간의 데이터 전송을 위해 사용됨.

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

---

• MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 함.
• MySQL에서 MyISAM이나 InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일함. 데이터 읽기/쓰기 영역의 처리만 다름.
• 하나의 쿼리작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 함.

SHOW ENGINES;

예제에 있는 스토리지 엔진 확인 쿼리를 입력하면 아래와 같이 결과가 나옵니다.

• Support 칼럼에 표시될 수 있는 값
  • YES: MySQL 서버에 해당 스토리지 엔진이 포함되어 있고, 사용 가능으로 활성화된 상태.
  • DEFAULT: 'YES'와 동일한 상태지만 필수 스토리지 엔진.
  • NO: 현재 MySQL 서버에 포함되지 않았음.
  • DISABLED: 현재 MySQL 서버에는 포함되어 있지만 파라미터에 의해 비활성화된 상태.

SHOW PLUGINS;

위의 SHOW PLUGINS 명령으로 스토리지 엔진 외 인증 및 전문 검색용 파서와 같은 플러그인도 확인할 수 있습니다.

4.1.5 컴포넌트

---

• 기존의 플러그인 아키텍처를 대체하기 위해 MySQL 8.0 버전부터는 컴포넌트 아키텍처가 지원됨.
  • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음.
  • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음.
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움.

--- validate_password 컴포넌트 설치(비밀번호 검증 기능)
INSTALL COMPONENT 'file://component_validate_password';

--- 설치된 컴포넌트 확인
SELECT * FROM mysql.component;

4.1.6 쿼리 실행 구조

---

▲ 쿼리 실행 구조

 
4.1.6.1 쿼리 파서
 

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어내는 작업
• 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되어 사용자에게 오류 메시지를 전달함.

 
4.1.6.2 전처리기
 

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

 
4.1.6.3 옵티마이저
 

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정함.

 
4.1.6.4 실행 엔진
 

• 만들어진 계획대로 각 핸들러에 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결함. 

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

• MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어옴.
• MySIAM 테이블을 조작하는 경우 핸들러가 MySIAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우 핸들러가 InnoDB 스토리지 엔진이 됨. 

4.1.7 복제

---

• 복제(Replication)는 매우 중요한 역할을 하므로, 별도의 장에서 다루도록 함.

4.1.8 쿼리 캐시

---

• 쿼리 캐시(Query Cache)는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환함.
• 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidate)해야 함.
• MySQL 8.0 버전부터 쿼리 캐시는 MySQL 서버의 기능에서 제거됨.

4.1.9 스레드 풀

---

• MySQL 서버 엔터프라이즈 에디션에서 제공하는 스레드 풀(Thread Pool) 대신 Percona Server에서 제공하는 스레드 풀 기능을 살펴봄.
• MySQL 엔터프라이즈 스레드 풀 기능은 MySQL 서버 프로그램에 내장되어 있지만, Percona Server의 스레드 풀은 플러그인 형태로 구현됨.
• 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많아도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 해서 서버의 자원 소모를 줄임.
• 스레드 그룹의 모든 스레드가 일을 처리하고 있다면, 스레드 풀은 해당 스레드 그룸에 새로운 작업 스레드를 추가할지 기존 작업 스레드가 처리를 완료할 때까지 가다릴지 여부를 판단함. 

4.1.10 트랜잭션 지원 메타데이터

---

• 데이터 딕셔너리(또는 메타데이터): 데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보
• MySQL 5.7 버전까지
  • 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리함.
• MySQL 8.0 버전부터
  • 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보(시스템 테이블)를 모두 InnoDB의 테이블에 저장하도록 개선함.
  • 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리됨.
  • MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요함.

참고자료

1) 백은빈, 이성욱. Real MySQL 8.0 (1권). 위키북스, 2021.
2) dolmeng2, "[Real MySQL 8.0] MySQL 엔진과 스토리지 엔진, 쿼리 실행 처리 순서", 2023. 06. 11, https://cl8d.tistory.com/100
3) MySQL DOCUMENTATION, https://dev.mysql.com/doc/refman/8.4/en/performance-schema-threads-table.html

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.