4. 아키텍처

Notice

Recent Posts

Recent Comments

Link

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Tags more

Archives

Today

Total

관리 메뉴

Hello world

4. 아키텍처 본문

카테고리 없음

4. 아키텍처

aad82 2023. 7. 16. 22:20

들어가기 전

이 글은 Real MySQL 8.0 4장을 공부하며 작성한 글입니다.

4. 아키텍쳐

MySQL 서버는 다음 아키텍쳐로 구성된다.

MySQL 엔진(사람 머리)
스토리지 엔진(손발)

MySQL 엔진이 스토리지 엔진에게 실행할 것을 요청하면, 스토리지 엔진이 실제 작업을 실행하는 방식으로 동작한다.

4.1 MySQL 엔진 아키텍쳐

MySQL의 엔진부터 살펴보자.

4.1.1 MySQL의 전체 구조

MySQL 서버는 다음과 같이 나누어 짐.

MySQL 서버
- MySQL 엔진
  - Connection Handler
  - SQL 인터페이스
  - SQL Parser
  - SQL Optimizer
  - Cache / Buffer
- 스토리지 엔진

4.1.1.1 MySQL 엔진

MySQL 엔진은 다음 구성을 가진다.

Connection Handler : 클라이언트와의 접속을 담당
SQL 파서 : 입력된 SQL을 파싱함.
SQL 전처리기 : 파싱된 SQL의 무결성 확인
옵티마이저 : 쿼리 최적화

4.1.1.2 스토리지 엔진

MySQL 엔진은 사용자 요청으로 들어온 SQL 문장을 분석 / 최적화하는 작업을 한다. 스토리지 엔진은 MySQL 엔진이 처리한 결과를 받아서, 실제 읽기 / 쓰기 작업을 처리한다.
테이블마다 어떤 스토리지 엔진을 사용할 지 결정할 수 있다.

# test_table은 스토리지 엔진을 INNO DB를 사용함. 
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

위 SQL로 생성된 test_table에 INSERT / UPDATE / SELECT 같은 쿼리가 실행되면, 읽기/쓰기 작업은 INNO DB가 수행해준다.

4.1.1.3 핸들러 API

MySQL 엔진은 쿼리 실행기를 가지고 있다. 쿼리 실행기는 스토리지 엔진에게 쓰기 / 읽기 요청을 한다.

Handler 요청 : 쿼리 실행기가 스토리지 엔진에게 하는 요청.
Handler API : Handler 요청에 사용되는 API

정리하면 쿼리 실행기가 Handler API라는 인터페이스를 이용해서 스토리지 엔진에게 쿼리 실행 요청을 하는 것이다. MySQL에서는 아래 명령어로 Handler API와 호출된 횟수를 확인할 수 있다.

mysql> SHOW GLOBAL STATUS LIKE "Handler%";

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 3391  |
| Handler_delete             | 892   |
| Handler_discover           | 0     |
| Handler_external_lock      | 33633 |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 892   |
| Handler_read_first         | 264   |
| Handler_read_key           | 10481 |
| Handler_read_last          | 0     |
| Handler_read_next          | 9241  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 642   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 4337  |
| Handler_write              | 1806  |
+----------------------------+-------+

4.1.2 MySQL 쓰레딩 구조

MySQL은 ForeGround / BackGround 쓰레드 구조로 동작한다.

Foreground : 커넥션 연결 / 쓰레드 캐시
Background : Inno DB의 경우 각종 쿼리 실행

자세한 내용은 아래에서 더 설명한다. 대략적인 구조는 다음과 같다.

Client의 요청은 포그라운드 쓰레드로 연결됨.
포그라운드 쓰레드는 쓰레드 캐시에게서 받아옴. (쓰레드풀과 유사한 개념. 실제로는 다름)
- 사용이 완료된 쓰레드는 쓰레드 캐시에 반납 시도됨. (쓰레드 갯수보다 많으면 제거됨)
포그라운드 쓰레드 / 백그라운드 쓰레드는 각각 캐시 / 버퍼 / 로그쪽으로 작업을 함.
- 스토리지 엔진을 어떤 것을 쓰느냐에 따라 백그라운드 스레드의 작업 내용이 달라짐.

MySQL 서버의 쓰레드와 관련된 내용은 아래 쿼리를 이용해서 살펴볼 수 있다.

## performance_schema DB의 threads Table에 데이터가 존재함.
## thread/sql/one_connection : 현재 맺어진 커넥션. 

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             |
|        23 | thread/innodb/srv_lock_timeout_thread       | BACKGROUND | NULL             | NULL             |
|        24 | thread/innodb/srv_error_monitor_thread      | BACKGROUND | NULL             | NULL             |
|        25 | thread/innodb/srv_monitor_thread            | BACKGROUND | NULL             | NULL             |
|        26 | thread/innodb/buf_resize_thread             | BACKGROUND | NULL             | NULL             |
|        27 | thread/innodb/srv_master_thread             | BACKGROUND | NULL             | NULL             |
|        28 | thread/innodb/dict_stats_thread             | BACKGROUND | NULL             | NULL             |
|        29 | thread/innodb/fts_optimize_thread           | BACKGROUND | NULL             | NULL             |
|        30 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        31 | thread/mysqlx/worker                        | BACKGROUND | NULL             | NULL             |
|        32 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        36 | thread/innodb/buf_dump_thread               | BACKGROUND | NULL             | NULL             |
|        37 | thread/innodb/clone_gtid_thread             | BACKGROUND | NULL             | NULL             |
|        38 | thread/innodb/srv_purge_thread              | BACKGROUND | NULL             | NULL             |
|        39 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        40 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        41 | thread/innodb/srv_worker_thread             | BACKGROUND | NULL             | NULL             |
|        42 | thread/sql/signal_handler                   | BACKGROUND | NULL             | NULL             |
|        44 | thread/mysqlx/acceptor_network              | BACKGROUND | NULL             | NULL             |
|        43 | thread/sql/event_scheduler                  | FOREGROUND | event_scheduler  | localhost        |
|        46 | thread/sql/compress_gtid_table              | FOREGROUND | NULL             | NULL             |
|        47 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        48 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        49 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        50 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        51 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        52 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        53 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        54 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        55 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        56 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.86.25       |
|        71 | thread/sql/one_connection                   | FOREGROUND | root             | 50.0.91.245      |
+-----------+---------------------------------------------+------------+------------------+------------------+

4.1.2.1 Foreground Thread (클라이언트 스레드)

Foreground Thread는 주로 클라이언트가 요청하는 쿼리 문장을 처리함.
Foreground Thread는 데이터를 버퍼 / 캐시로부터 가져오는 작업을 함.
InnoDB 테이블을 사용하는 경우, 버퍼 / 캐시까지만 Foreground가 처리함. 버퍼에서 디스크로 기록하는 작업은 BackGround 쓰레드가 처리함.
- 클라이언트 스레드 : 요청 → Buffer / Cache(메모리 영역)
- 백그라운드 스레드 : Buffer / Cache → 디스크
클라이언트와 커넥션이 종료되면 포그라운드 쓰레드는 쓰레드 캐시로 반환 시도됨. 만약, 이미 쓰레드 캐시에 thread_cache_size만큼의 쓰레드가 존재하면 쓰레드는 종료됨.

4.1.2.2 Background Thread

스토리지 엔진으로 InnoDB를 사용하는 경우, 아래 작업은 Background Thread로 처리된다.

Insert Buffer를 병합하는 쓰레드
로그(Redo / Undo)를 디스크로 기록하는 쓰레드
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 쓰레드 (더티 페이지)
디스크에서 버퍼로 데이터를 읽어오는 쓰레드.
Lock / DeadLock 모니터링하는 쓰레드

각각 유의할 점은 다음과 같다.

InnoDB에서 데이터를 읽는 작업은 주로 클라이언트 쓰레드(Foreground Thread)로 처리되기 때문에 많은 쓰레드가 필요하지는 않음.
쓰기 쓰레드는 아주 많은 작업을 처리해야 함. 따라서 충분한 쓰레드 수를 지정해줘야 함.
MySQL은 쓰기 작업에 대해 버퍼링(지연)을 지원함.
- 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재됨. (INNO DB도 지원)
- 이런 이유로 INNO DB에서 INSERT / UPDATE / DELETE 쿼리로 데이터가 변경되는 경우, 데이터가 디스크에 완전히 저장될 때까지 기다리지 않아도 됨.
  - 디스크 파일에 변경점이 반영되지 않더라도, 메모리에 변경 반영이 완료되면 클라이언트에게는 응답함.

4.1.3 메모리 할당 및 사용 구조

MySQL의 메모리 구조는 다음과 같다

Global 메모리
Local 메모리

각 메모리 영역에 대해서는 아래에서 확인해보고자 한다.

4.1.3.1 Global 메모리 영역

일반적으로는 1개의 Global 메모리 영역만 할당 받는다. 그리고 Global 메모리 영역은 모든 쓰레드가 공유해서 사용한다. Global 메모리 영역에는 다음 값들이 저장된다.

테이블 캐시
InnoDB 버퍼 풀
InnoDB Adaptive Hash Index (AHI)
InnoDB Redo Log 버퍼

4.1.3.2 Local 메모리 영역

클라이언트 쓰레드(Foreground Thread)가 SQL 요청을 처리하기 위해서 사용하는 공간을 세션 메모리라고 한다.
클라이언트와 세션을 기반으로 커넥션을 맺고, 각 쓰레드가 이에 대응하기 때문에 세션 메모리라고 함.
각 쓰레드는 독립적인 세션 메모리를 사용함.
MySQL 서버는 필요할 때만 메모리 공간을 할당함. 필요하지 않은 경우에는 메모리를 할당하지 않을 수도 있음.
로컬 메모리 영역
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼

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

MySQL은 플러그인을 추가해서 사용할 수 있다. 스토리지 엔진에도 플러그인을 개발해서 사용할 수 있다. 플러그인 스토리지 엔진을 추가하기 위해서는 MySQL 서버가 어떤 식으로 쿼리를 처리하는지에 대해서 먼저 이해해야한다.

MySQL 엔진은 요청으로 들어온 SQL을 파싱하고 쿼리 최적화를 한 후에 SQL 실행기에게 전달함.
SQL 실행기는 전달된 쿼리를 Handler API를 이용해 스토리지 엔진에게 처리 요청함.
스토리지 엔진은 요청을 받아서 디스크에 읽기/쓰기 작업을 처리함.

쿼리의 복잡한 부분, 예를 들면 GROUP BY / ORDER BY 같은 처리는 MySQL 엔진의 SQL 실행기에서 이미 처리된다. 스토리지 엔진은 단순히 디스크와 읽기 / 쓰기를 실행한다.

중요한 점은 하나의 쿼리 작업은 여러 하위 작업으로 나누어져 실행되고, 각 하위 작업은 MySQL 엔진 영역 / 스토리지 엔진 영역으로 나누어 처리된다. 플러그인으로 스토리지 엔진을 추가한다는 것은 Handler API를 받아서 디스크에 어떻게 읽고/쓰기를 결정할지를 커스텀하게 구현해서 사용한다는 의미로 이해할 수 있다.

아래 명령어를 이용해 현재 MySQL 서버에서 사용할 수 있는 스토리지 엔진 / 플러그인들을 살펴볼 수 있다.

mysql> SHOW engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)

mysql> SHOW plugins;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
...
48 rows in set (0.01 sec)

4.1.5 컴포넌트

MySQL 플러그인에는 몇 가지 단점이 존재하며, 이 단점을 보완한 녀석들이 컴포넌트다. 컴포넌트는 MySQL 8.0 이상부터 지원된다. 단점은 다음과 같다.

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

4.1.6 쿼리 실행 구조

클라이언트 쓰레드에게 전달된 쿼리 요청은 아래처럼 여러 단계로 나누어 실행된다.

클라이언트로부터 요청이 오면 대응되는 클라이언트 쓰레드가 생성된다. 클라이언트 쓰레드는 세션 메모리 영역을 이용해서 SQL 요청을 위 그림처럼 나누어 처리한다. 클라이언트 쓰레드(Foreground Thread)는 MySQL 엔진의 작업을 처리하고, 백그라운드 스레드는 스토리지 엔진의 작업을 처리한다고 이해할 수 있다.

4.1.6.1 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 AST(추상 구문 트리)를 만들어준다. 여기서 기본적인 Syntax 에러가 Lexer / Parser에 의해서 발견된다.

4.1.6.2 전처리기

AST를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인함.

테이블 이름 / Column 이름 / 내장 함수와 같은 개체를 매핑해 존재하는지 확인함.
해당 객체 접근 권한 있는지 판별함. (Permission Denied 같은 것들)

4.1.6.3 옵티마이저 (쿼리 변환 / 비용 최적화 / 실행 계획 수립)

전처리 이후에 받은 쿼리 문장을 최적의 비용으로 실행할 수 있도록 변환하는 역할을 담당한다.
주로 쿼리 변환 / 비용 최적화 / 실행 계획을 수립함.

4.1.6.4 실행 엔진 (쿼리 실행기)

옵티마이저는 쿼리 실행 계획을 생성한다. 쿼리 실행기(실행 엔진)은 쿼리 실행 계획을 실행하도록 각각의 스토리지 엔진에게 요청하는 작업을 한다. 아래와 같이 동작한다.

실행 엔진이 핸들러에게 임시 테이블 생성 요청
실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
실행 엔진은 읽어온 레코드들은 1번에 생성한 임시 테이블로 저장하라고 핸들러에게 요청
실행 엔진은 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

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

실행 엔진은 옵티마이저에게 받은 쿼리 실행 계획을 순차적으로 Handler API를 이용해서 실행 요청한다. Handler API 요청은 결과적으로 스토리지 엔진에게 전달되고, 이 요청을 받은 스토리지 엔진은 Background Thread를 이용해서 처리한다. 주로 디스크와의 작업을 하는데 사용한다.

4.1.7 복제

16장에서 살펴본다.

4.1.8 쿼리 캐시

MySQL 8.0 버전부터 쿼리 캐시는 MySQL 서버에서 완전히 제거되었다.

4.1.9 스레드 풀

MySQL 커뮤니티 버전은 쓰레드 풀을 지원하지 않고, 쓰레드 캐시 모드로 동작한다. 반면 MySQL 엔터프라이즈 버전은 쓰레드 풀을 지원한다고 한다. 만약 MySQL 커뮤니티 버전에서도 쓰레드 풀을 사용하고 싶다면 Percona Server에서 제공하는 플러그인을 설치해서 사용하면 된다. 쓰레드 캐시와 쓰레드 풀의 동작은 다음과 같이 차이가 있다.

쓰레드 캐시
- 쓰레드 캐시에 보관할 수 있는 최대 쓰레드 갯수만 신경 씀.
- 클라이언트의 요청을 처리한 쓰레드는 쓰레드 캐시에 반납됨.
- 쓰레드 캐시에 쓰레드 캐시 사이즈만큼의 쓰레드가 보관 중일 때 쓰레드가 반납되면, 해당 쓰레드는 삭제됨.
- 쓰레드 캐시 사이즈가 10일 때, 쓰레드 캐시에 10개의 쓰레드가 보관중이면서 다른 10개의 쓰레드가 클라이언트의 요청을 처리하고 있을 수 있음.
쓰레드 풀
- 쓰레드 풀 사이즈는 MySQL 서버에서 사용할 수 있는 쓰레드 갯수를 의미함. (병렬 처리할 수 있는 쿼리의 갯수)
- 모든 쓰레드가 일하고 있을 때, 새로운 클라이언트 요청이 들어오면 해당 요청은 즉시 처리할 수는 없는 상태임.

쓰레드 풀을 사용할 때, 너무 많은 쓰레드를 사용하는 것은 좋지 않다. 한정된 CPU를 스케쥴링 해야하기 때문에 쓰레드가 많아지면, 스케쥴링을 빈번하게 해야해서 컨텍스트 스위칭이 많이 발생하며 오히려 비용을 더 발생 시킬 수 있다.

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

MySQL 서버에서 테이블의 구조 정보 / Stored 프로그램의 정보를 메타 데이터(또는 Data Dictionary)라고 한다. MySQL 8.0 이전에는 이 파일들은 파일 시스템에 파일의 형태로 저장되었다. 파일은 트랜잭션을 지원하지 않았기 때문에 테이블의 생성 / 수정 도중에 MySQL 서버가 비정상적으로 종료되면 테이블이 깨지는 상태가 된다.

MySQL 8.0 부터는 테이블의 구조 정보를 InnoDB 테이블에 저장하도록 변경되었다.

MySQL이 동작하는데 필요한 테이블들을 시스템 테이블이라고 한다. MySQL 8.0부터 시스템 테이블들은 InnoDB를 사용하도록 변경되었다. 즉, InnoDB가 제공하는 트랜잭션 기능을 이용해서 '모두 성공' / '모두 실패' 둘 중의 하나를 달성하도록 한다. 시스템 테이블 / 데이터 딕셔너리는 InnoDB 엔진을 이용해 'mysql' DB의 'tables'라는 테이블에 저장된다.

mysql> SELECT * FROM mysql.tables LIMIT 1;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected

위 명령어를 이용해서 mysql.tables에 저장된 데이터를 보려고 한다. 실제로는 볼 수 없지만, 해당 DB에 테이블이 존재한다는 것을 알 수 있다. 또한, 테이블을 생성할 때 만들어지는 데이터 딕셔너리를 다음 명령어를 통해서 확인할 수 있다.

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES;
+--------+------------------------------------------------------
| View   | Create View                                          ...
+--------+------------------------------------------------------
| TABLES | CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`...
+--------+------------------------------------------------------

단, InnoDB는 이렇게 동작하지만 다른 스토리지 엔진(MyISAM, CSV)는 여전히 파일 기반으로 데이터 딕셔너리를 저장하고 있기 때문에 테이블 생성과 관련되어서는 이 기능을 사용할 수 없다.

4.2 InnoDB 스토리지 엔진 아키텍처

InnoDB는 레코드 기반의 Lock을 제공한다. 레코드 기반의 Lock을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. Inno DB의 대략적인 구조는 아래와 같다.

4.2.1 PK에 의한 클러스터링

InnoDB의 모든 테이블은 PK를 기준으로 클러스터링되어 저장된다. 이 말은 PK 값의 순서대로 디스크에 저장된다는 뜻이다.
모든 Secondary Index는 레코드 주소 대신 PK 값을 논리적인 주소로 사용함.
PK가 클러스터링 인덱스이기 때문에 PK를 이용한 Range Scan은 상당히 빨리 처리될 수 있음.

이런 이유들 때문에 쿼리 실행 계획에서 PK는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다. 즉, PK가 선택될 확률이 높아진다.

4.2.2 FK(외래 키) 지원

FK(외래 키) 지원은 InnoDB에서만 가능하다. MyISAM / MEMORY 엔진 테이블에서는 사용할 수 없다.
InnoDB에서 FK는 부모 테이블 / 자식 테이블 모두 해당 Column에 인덱스 생성이 필요함.
- 부모 테이블 : PK를 가지고 있음.
- 자식 테이블 : 부모 테이블의 PK를 FK로 가지고 있음.
- 예시 : 저자(부모 테이블) - 책(자식 테이블)
부모 테이블의 PK 변경 시(Update / Delete)에는 부모 테이블 / 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하는 경우가 있을 수 있으므로, 여러 테이블로 Lock이 전파될 수 있음. 이런 이유 때문에 DB에서 데드락이 많이 발생할 수 있음.
- InnoDB의 이런 행동은 foreign_key_checks 옵션을 ON/OFF로 설정해서 선택할 수 있음.

복잡한 쿼리가 들어와서 여러 테이블로 Lock이 전파되어서 데드락이 걸린 상황이라면 '외래 키 관계 체크' 작업을 멈추는 방법으로 일시적으로 대응할 수 있다.

# 외래 키 체크 OFF (Global)
mysql> SET foreign_key_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
 
# 외래 키 체크 ON (Global)
mysql> SET foreign_key_checks=ON;
Query OK, 0 rows affected (0.01 sec)


# 현재 세션 레벨의 OFF
mysql> SET SESSION foreign_key_checks=OFF;
Query OK, 0 rows affected (0.01 sec)

# 현재 세션 레벨의 ON
mysql> SET SESSION foreign_key_checks=ON;
Query OK, 0 rows affected (0.01 sec)

'foreign_key_checks' 옵션을 이용하면 된다.

외래 키 체크 작업을 멈추면, 레코드 적재 / 삭제 등의 작업에도 부가적인 체크가 필요없기 때문에 데드락 발생이 줄고, 훨씬 빠르게 처리할 수 있음.
부모 테이블의 레코드 삭제 했다면, 개발자가 수동으로 자식 테이블의 레코드도 삭제해야 한다.
- '외래키 체크'가 꺼지면, ON DELETE CASCADE / ON UPDATE CASCADE 옵션도 무시되기 때문임.
foreign_key_checks 옵션은 Global / Session 레벨을 선택해서 적용할 수 있음. 이런 작업은 일반적으로 Session 레벨로 하는 것이 추천됨.

4.2.3 MVCC(Multi Version Concurrency Control)

MySQL은 MVCC를 지원한다.

MVCC의 목적은 Lock을 사용하지 않고도 일관된 읽기를 제공하기 위함.
MVCC는 Undo Log를 이용해서 구현됨.
MVCC는 Inno DB 버퍼 풀 + Undo Log를 이용해서 처리한다. 이 두 가지는 모두 Global 메모리 영역에 있음.
Commit 될 때, Inno DB 버퍼풀에 있는 값을 디스크에 반영함. Rollback 되면, Undo Log에 있는 값을 다시 Inno DB 버퍼풀로 복원함.
커밋 된다고 Undo 영역의 백업 데이터가 바로 삭제되지는 않음. Undo 영역을 필요로 하는 트랜잭션이 모두 삭제되어야 Undo 영역의 로그가 삭제됨.

멀티 버전은 하나의 레코드라도 각 트랜잭션 별로 서로 다른 버전을 가질 수 있다는 것이다. MVCC의 일관된 읽기는 DB의 트랜잭션 격리 수준과 관련된다.

mysql> CREATE TABLE member (
    -> m_id INT NOT NULL,
    -> m_name VARCHAR(20) NOT NULL,
    -> m_area VARCHAR(100) NOT NULL,
    -> PRIMARY KEY (m_id),
    -> INDEX ix_area (m_area)
    -> );
    

# 첫번째 쿼리 (트랜잭션 커밋)
mysql> INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울'); 
mysql> commit;


# 두번째 쿼리 (트랜잭션 커밋 전)
mysql> UPDATE member SET m_area='경기' where m_id=12;

Update 쿼리를 사용하면, Inno DB 버퍼풀은 Inno DB 버퍼풀이 가지고 있던 데이터에 즉시 해당 값을 업데이트 한다. 원본값은 변경된 부분만 Undo Log에 저장한다. Inno DB 버퍼풀에 저장된 변경된 값은 실제 디스크에 아직까지는 반영되지 않는 상황이다. 그림으로 살펴보면 다음 상태다.

왼쪽 그림

INSERT 쿼리를 사용했다. InnoDB 버퍼 풀에 INSERT 쿼리 값이 저장되었고, 디스크에도 값이 저장되었다.

오른쪽 그림

UPDATE 쿼리를 사용함.
InnoDB 버퍼 풀에는 업데이트 된 값이 반영됨 (서울 → 경기)로 변경됨
InnoDB 버퍼에서 변경된 값은 변경된 부분만 Undo Log에 저장됨.

MVCC에서 다른 트랜잭션은 어떤 값을 바라볼까?

앞 상황은 트랜잭션이 커밋되지 않은 상황이다. 이 상황에서 InnoDB 버퍼 풀에 있는 데이터가 있고, Undo Log에 있는 데이터가 있다. 이 때, 다른 트랜잭션이 이 레코드를 읽어오려고 하면 어떤 값을 읽어오까? 정답은 읽을려는 트랜잭션의 격리 수준에 따라 다르다.

Read Uncommit : 트랜잭션은 InnoDB 버퍼 풀에 있는 레코드를 바로 읽음. (커밋되지 않은 데이터도 읽는 수준)
Read Commit : 트랜잭션은 Undo Log에 있는 값을 읽음.

이처럼 하나의 레코드가 서로 다른 버전을 가지고 있고, 이것을 MVCC에서 Multi Version이라고 한다.

Undo Log의 문제점

MVCC는 Lock 없이 일관된 읽기 기능을 제공한다는 장점을 가진다. 이것은 Undo Log를 이용해서 제공된다. Undo Log는 트랜잭션이 열려있는 동안은 삭제되지 않는다. 이 말은 트랜잭션이 길어지면, Undo Log가 길어진다. 따라서 봐야할 부분이 많아지기 때문에 성능 문제가 발생할 수 있다.

4.2.4 잠금 없는 일관된 읽기 (Non-Locking Consistent Read)

MySQL은 undo log를 이용해서 MVCC를 제공하고, MVCC 덕분에 잠금 없이 읽기 작업을 수행할 수 있다. 트랜잭션 격리 수준에 따라 각 트랜잭션은 Inno DB 버퍼풀 / Undo 세그먼트에서 값을 읽어올 수 있다. 그리고 SELECT 쿼리는 Lock을 대기하지 않고 값을 불러올 수 있게 된다.

한 가지 주의할 점은 트랜잭션이 오래 열려있으면, Undo Log가 삭제되지 못한다. Undo Log가 길어지면 서버가 느려지기 때문에 가급적이면 트랜잭션을 빠르게 종료해서 Undo Log가 삭제되도록 해야한다.

4.2.5 자동 데드락 감지

InnoDB는 DB 내부의 데드락 처리를 위한 여러가지 옵션을 제공한다.

InnoDB는 잠금 대기 목록을 그래프 (Wait-For-List) 형태로 제공함.
InnoDB 스토리지 엔진은 데드락 감시 쓰레드가 존재함.
- 데드락 감시 쓰레드는 그래프를 주기적으로 확인하며, 데드락 발생 시 트랜잭션을 하나 롤백함.
- 트랜잭션 롤백의 선택 기준은 가장 적은 Undo Log를 가진 녀석임. Undo 처리 시, 서버 부하가 가장 적게 걸리기 때문임.
InnoDB 스토리지 엔진의 데드락 감시 쓰레드는 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금을 볼 수 없음.
- innodb_table_locks(시스템 변수) 활성화 시, 데드락 감시 쓰레드가 레코드 Lock / 테이블 Lock을 모두 감시할 수 있음.
데드락 감시 쓰레드는 잠금 목록을 검사할 때, 상태가 변하지 않도록 잠금 대기 목록에 Lock을 걸고 잠금 상태를 감시함.
- 동시 처리 쓰레드가 많아지거나, 잠금 목록이 많아지면 데드락 감시 쓰레드가 살펴봐야 할 시간이 더 걸림. 따라서 처리 시간이 느려짐.
- innodb_deadelock_detect (MySQL 시스템 변수)를 OFF로 설정하면, 데드락 감시 쓰레드가 동작하지 않음.
- 이 경우, 데드락 발생 시 개발자가 직접 트랜잭션 롤백해야함.
- innodb_lock_wait_timeout (MySQL 시스템 변수)는 데드락 상황에서 몇 초간 Lock 획득을 위해 대기할지를 설정하는 값임. 이 시간이 지나면, 자동적으로 쿼리 실패 에러를 반환함.

정리하면, 데드락 감시 쓰레드는 일반적인 경우라면 사용하는 것이 좋다. 만약 너무 많은 쓰레드가 동시에 처리되고 있어 MySQL 서버에 부하가 온다면 데드락 감시 쓰레드에 의해서 전반적인 MySQL의 성능이 줄어들 수 있다. 이럴 때는 데드락 감시 쓰레드를 끄고, Lock 획득 제한 시간을 설정해서 쿼리가 실패하도록 하면서 처리할 수 있다.

4.2.6 자동화 된 장애 복구

4.2.7 InnoDB 버퍼 풀 (메모리 공간)

InnoDB 버퍼풀은 InnoDB 스토리지 엔진의 가장 핵심적인 부분이다. 다음 역할을 한다.

디스크의 데이터 / 인덱스 정보를 메모리에 캐시해두는 공간
쓰기 작업을 지연시켜 일괄 처리하도록 해줌 (INSERT / UPDATE / DELETE)
- 데이터 변경은 디스크의 데이터 파일의 이곳저곳에 위치한 레코드를 변경함. 이 때, Random Access 작업으로 처리함.
- 쓰기 작업을 일괄 처리하면서 Random Access 작업 횟수를 줄여서 성능 이득을 가져감.

4.2.7.1 버퍼 풀의 크기 설정

디스크에 쓰기 / 읽기 작업을 할 때는 다음 형태로 작업이 진행된다고 한다.

쓰기 : InnoDB 버퍼풀 → 레코드 버퍼 → 디스크
읽기 : 디스크 → 레코드 버퍼 → InnoDB 버퍼풀

InnoDB 버퍼풀 / 레코드 버퍼 각각 메모리를 사용하는 것인데, 어플리케이션의 크기 마다 서로 다르게 메모리 할당을 해야한다.InnoDB 버퍼풀에 할당하는 메모리는 innodb_buffer_pool 시스템 변수로 크기를 설정할 수 있으며, 동적으로 확장할 수 있다.

InnoDB 버퍼풀은 변경점이 큰 작업이기 때문에 주로 DB가 한가한 시간에 해야한다. 버퍼풀의 사이즈를 키우는 것은 시스템 영향도가 크지 않지만, 줄이는 것은 시스템 영향도가 큰 작업이다.

InnoDB 버퍼풀은 기존에 1개의 인스턴스만 지원되었으나, 최근에는 기본 8개의 InnoDB 버퍼풀 인스턴스를 지원한다. 이렇게 여러 인스턴스를 지원하는 것은 InnoDB 버퍼풀도 잠금(세마포어)로 관리되기 때문이다 .1개의 인스턴스가 세마포어로 관리되면, 여러 클라이언트 쓰레드가 접근했을 때 오래 걸리는데, 8개로 늘리게 되면서 경합이 분산된다.

4.2.7.2 버퍼풀의 구조

InnoDB 스토리지 엔진은 InnoDB 버퍼 풀을 사용한다. 버퍼 풀은 거대한 용량의 메모리로 구성되어 있고 다음과 같이 사용한다.

전체 메모리를 특정 사이즈(innodb_page_size)의 페이지(메모리 조각)들로 관리.
디스크에서 읽어온 메모리는 InnoDB 버퍼풀의 메모리 조각에 각각 저장
페이지는 다음 종류로 구성됨.
- LRU 리스트(Least Recently Used) : New(Most Recently Used) + Old (Least Recently Used)
- 플러시 리스트 : 더티 페이지 목록.
- 프리 리스트 : 비어있는 페이지. 새로운 데이터를 넣을 수 있음.

LRU 리스트 구조 및 목적

LRU 리스트의 목적은 한번 읽어온 페이지(데이터)를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 것이다. 클라이언트가 데이터를 요청했을 때, InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 다음과 같다.

필요한 레코드가 저장된 데이터 페이지가 InnoDB 버퍼 풀에 있는지 검사.
- InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
- 해당 테이블의 인덱스 (B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
- 버퍼 풀에 이미 데이터 페이지가 있었다면, 해당 페이지의 포인터를 MRU 방향으로 승급 (조회 했으니까)
만약 필요한 데이터 페이지가 없다면, 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재함. 적재된 페이지의 포인터를 LRU 헤더 부분에 추가
버퍼 풀의 LRU 헤더에 적재된 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
버퍼 풀에 상주하는 데이터 페이지는 접근된지 오래되면 버퍼 풀에서 방출됨 (Aged). 버퍼 풀에 상주하는 데이터 페이지가 다시 한번 접근되면, Age가 초기화 되고 MRU의 Header로 옮겨짐.
특정 데이터가 자주 접근 되었다면, 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가함.

따라서 InnoDB 버퍼풀에 적재되어서 자주 접근되는 녀석은 MRU 영역에서 오랫동안 살아남을 것이다. 반면, InnoDB 버퍼풀에 적재되었으나 자주 접근되지 않는 녀석들은 LRU의 Tail로 밀려나 InnoDB 버퍼풀에서 삭제될 것이다.

플러시 리스트

InnoDB 버퍼풀에 적재된 데이터 페이지 중에 INSERT / UPDATE / DELETE 쿼리 영향을 받은 페이지가 있을 수 있다. 예를 들면 기존에는 1이라는 값이었는데, 2라는 값으로 바뀐 경우다. 이처럼 InnoDB 버퍼풀에 있는 값 ↔ 디스크에 저장된 값이 차이가 있는 부분을 더티 페이지라고 한다.

더티 페이지가 디스크의 데이터 파일에 반영되어야 데이터가 영속화된다. 따라서 InnoDB 스토리지 엔진은 더티 페이지를 추적하고 디스크와 동기화하는 작업을 해야한다.

InnoDB 스토리지 엔진은 변경점을 리두(redo) 로그에 기록하고, InnoDB 버퍼풀에 반영한다. 스토리지 엔진은 리두 로그를 이용해 플러시 리스트를 관리하고 변경점을 디스크 파일에 반영하는 작업을 한다.

한 가지 주의할 점은 리두로그가 로컬의 로그 파일에 저장되었다고 하더라도, 변경점이 디스크의 데이터 파일에 반영되었다는 보장은 할 수 없다는 점이다.

더티 페이지가 플러시 되는 시점은 체크 포인트 이벤트가 발생하거나, 특정 조건을 만족해서 더티 페이지 자체가 플러시 되는 경우다. 이 때, 더티 페이지에 저장된 변경 레코드가 실제 디스크 데이터 파일에 동기화 된다.

4.2.7.3 버퍼 풀과 리두 로그

InnoDB의 버퍼 풀과 리두 로그는 밀접한 관계를 가지고 있다. InnoDB 버퍼풀이 하는 역할을 다시 살펴보자.

캐싱 역할 : 디스크에서 읽어온 데이터를 메모리에 저장. 디스크 읽기 최소화.
버퍼 역할 : 디스크에 반영되어야 할 변경점을 버퍼에 저장했다가 한번에 반영. 디스크 쓰기 최소화.

그렇다면 캐싱 / 버퍼 성능을 각각 향상 시키고 싶다면 어떻게 해야할까?

InnoDB 버퍼 풀 메모리 할당 증가 : 캐싱 능력 증가
리두 로그 파일 크기 증가 : 버퍼 능력 증가

그렇다면 리두 로그 파일 크기를 증가하는 것이 어떻게 InnoDB 버퍼 풀의 버퍼 능력 증가와 관련이 있을까?

앞서 InnoDB 버퍼풀은 변경점(더티 페이지)을 리두 로그에 기록한다고 했다. 그리고 이것은 버퍼 능력을 증가시킨다고 했다. 바꿔 이야기하면 InnoDB 버퍼풀은 리두 로그를 모은 후, 변경점을 한번에 디스크의 데이터 파일에 반영한다. 이것은 디스크 쓰기 작업을 최소화하기 위한 방법이다.

리두 로그는 순환형 자료구조를 가지고 있다. 따라서 언젠가는 이전 로그 파일에 새로운 로그가 덮어 씌워지게된다. 새로운 로그가 덮어씌워지기 전에 InnoDB 버퍼풀은 더티 페이지를 디스크의 데이터 파일에 반영해야한다.

리두 로그 파일은 다음으로 나누어서 관리된다.

활성 리두 로그 : 디스크에 반영되지 않은 버퍼풀의 더티 페이지를 가리킴.
비활성 리두 로그 : 사용 가능함. (새로운 로그를 적어도 무방함)

리두 로그 파일은 순환되어 사용된다고 했다. 그러면 어디까지 반영되었는지가 중요할텐데, LSN(Log Sequence Number)로 관리한다. 디스크에 더티 페이지가 반영될 때 마다, 디스크와 동기화된 리두 로그의 위치는 증가하는데 이것을 LSN이라고 한다.

InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 버퍼풀의 더티 페이지를 디스크에 동기화 작업을 진행한다. 체크포인트 이벤트가 발생하면, 마지막으로 체크 포인트가 발생했던 LSN부터 특정 시점의 Redo 로그가 디스크로 동기화 된다. 그리고 디스크로 동기화 된 시점을 체크포인트 LSN으로 업데이트 한다. 즉, 그동안 모아두었던 변경점이 디스크의 데이터 파일에 반영된다는 것이다.

바꿔 이야기 하면 다음과 같다

체크 포인트 LSN은 더티 페이지 중 실제 디스크로 동기화 된 레코드의 Redo 로그 LSN을 의미함. 체크포인트 LSN 다음의 더티 페이지는 아직까지 디스크에 동기화 되지 않음.
서버가 다운되는 경우, 체크포인트 LSN 뒤의 데이터는 아직 동기화 되지 않았다. 서버가 재기동 되면, MySQL 서버는 체크포인트 LSN 다음부터는 Redo 로그를 확인해서 반영되지 못한 데이터를 디스크로 동기화한다.

추가로 이해할 부분 (https://blog.ex-em.com/1700)

Innodb 스토리지 엔진에서 체크포인트 이벤트가 발생하면 더티 페이지가 실제 데이터 파일로 동기화 되기 시작한다. 체크 포인트는 두 가지 유형이 존재한다

Sharp Checkpoint
- 모든 더티 페이지를 디스크에 동기화 함.많은 IO가 발생할 수 있기 때문에 일반적으로는 FuzzyCheckPoint로 동작함.
- 이 때, DB 서버의 동작이 일시 중단됨. MySQL이 종료될 때 주로 사용됨.
Fuzzy CheckPoint
- 더티 페이지를 일부만 디스크로 동기화 한다.
- 조금씩 옮기기 때문에 성능 문제도 덜하고, DB도 중단없이 계속 사용 가능하다.
- Fuzzy CheckPoint는 다음의 경우에 발생함.
  - Master Thread CheckPoint : 마스터 쓰레드는 주기적으로 Flush List 확인하여 일정 비율만큼의 Dirty Page를 동기화함.
  - FLUSH_LRU_LIST CheckPoint : Free Page 개수가 약 100개 이하로 여유 공간이 부족한 경우, LRU List의 Old 영역부터 일정 수치만큼 Scan 한 후 더티 페이지가 있으면 체크 포인트를 수행함.
  - Flush CheckPoint : Redo Log 파일이 거의 가득 차서, Redo Log 파일을 사용할 수 없는 경우 발생함.
  - Dirty Page too much Checkpoint : 버퍼 풀 공간 대비 더티 페이지 비율이 특정 수치에 도달하면 체크포인트 수행.

주의할 점

리두 로그의 엔트리는 데이터 페이지의 포인터를 가리킨다. 예를 들어 리두 로그 엔트리 1개가 4kb인데, 데이터 페이지 한 조각이 1MB라는 상황을 가정해보자. 그리고 InnoDB 버퍼풀에서 사용 가능한 메모리가 100MB라고 가정해보자. 이 경우에는 리두 로그 엔트리가 문제가 될 수 있다. 너무 많은 쓰기 작업을 한번에 해야한다는 문제다.

리두 로그 엔트리를 1000개까지 보관할 수 있는 경우라면, 이 때 InnoDB 버퍼풀은 1Mb * 1000 = 1GB 까지 데이터를 보관할 수 있어야 한다. 그렇지만 100개까지만 보관할 수 있게 된다. 문제는 리두 로그를 디스크에 동기화 할 때, 100개의 페이지를 한번에 디스크에 반영해야한다는 것이다. 급작스러운 디스크 작업이 많아진다는 문제가 있다.

일반적으로 변경분만 기록하는 리두 로그와 데이터 페이지를 통째로 가지는 버퍼풀을 고려한다면, 리두 로그는 버퍼 풀에 비해서 아주 적은 메모리만 할당하면 된다.

Log Entry:
{
    Log Sequence Number (LSN): 123456,
    Transaction ID: 7890,
    Page ID: 111,
    Offset within Page: 222,
    Before Image: "John Doe",
    After Image: "John Smith"
}

4.2.7.4 버퍼 풀 플러시

InnoDB 스토리지 엔진은 더티 페이지를 디스크에 동기화 하기 위해 두 가지 플러시 기능을 백그라운드로 사용한다.

플러시 리스트 Flush
LRU 리스트 Flush

4.2.7.4.1 플러시 리스트 플러쉬 (더티 페이지를 반영하는 플러쉬)

InnoDB 스토리지 엔진은 플러시 리스트(더티 페이지 관리하는 리스트)를 디스크에 동기화 하는 작업을 주기적으로 한다. 관련된 내용은 다음과 같다.

클리너 스레드는 더티 페이지를 디스크로 동기화 하는 작업을 함.
하나의 클리너 스레드가 여러 버퍼 풀 인스턴스를 동기화 할 수 있음. 하나의 버퍼 풀을 여러 클리너 스레드가 동기화 할 수 없음.
InnoDB 버퍼 풀에 더티 페이지가 많을 수록 쓰기 작업을 버퍼링할 수 있어, 디스크 쓰기를 줄이는 효과를 가져옴.
'InnoDB 버퍼 풀에 기록되는 더티 페이지 수 > 플러시 되는 더티 페이지 수'인 경우, InnoDB 엔진은 디스크 쓰기를 급작스럽게 늘림.
InnoDB는 Adapative Flush 기능을 제공함.
- 어댑티브 플러시 기능을 사용하면, InnoDB 스토리지 엔진은 리두 로그 증가 속도를 분석해 적절한 수준의 더티 페이지 버퍼링을 사용함.

4.2.7.4.2 LRU 리스트 플러쉬 (오래된 녀석 제거 + 더지 페이지 반영)

InnoDB 버퍼풀은 플러시 / LRU / Free 리스트로 나누어 져서 관리된다고 했다. LRU 리스트 플러쉬는 사용한지 오래된 데이터 페이지들(더티 페이지)을 제거해서 Free 리스트를 확보하는 작업이다. 다음과 같이 동작한다.

LRU 리스트의 끝 부분 ~ 최대 innodb_lru_scan_depth만큼 페이지를 스캔함.
InnoDB 스토리지 엔진이 스캔하면서, 더티 페이지는 디스크에 동기화 함. 클린 페이지는 즉시 Free(프리)리스트로 페이지를 옮김.

4.2.7.5 버퍼 풀 상태 백업 및 복구

MySQL을 사용하다보면 InnoDB 버퍼풀의 LRU 리스트에 적당한 인덱스가 캐싱되어있다. 이 상황에서 MySQL을 껐다키면 캐싱된 인덱스가 모두 사라진다. 이런 상황이라면 조회를 할 때 마다 디스크에서 불러와야하기 때문에 MySQL 서버의 성능이 떨어진다. 서버를 껏다 켰을 때, 어느정도 InnoDB 버퍼풀의 페이지에 인덱스를 채워주는 것이 좋을 것이다.

특정 시점의 InnoDB 버퍼풀의 스냅샷을 찍은 후, 해당 스냅샷을 이용해서 InnoDB 버퍼풀을 복구하는 방법도 있다. 이 때, ib_buffer_pool이라는 이름으로 백업 파일이 생성되는데 몇십 MB 수준이다. 이것은 LRU 리스트에 적재된 데이터 페이지의 메타 정보만 가져와서 만들기 때문이다.

만약 이 파일을 이용해서 InnoDB 버퍼풀을 복구하는 경우, 수십 MB의 메타 데이터에 대한 데이터 페이지를 다시 디스크에서 모두 읽어와야한다. 따라서 꽤 오랜 시간이 걸릴 수도 있다.

이 작업은 사람의 실수를 탈 수 있는 작업이기 때문에 자동화 하는 것이 좋다. MySQL은 아래 시스템 변수를 이용해 자동 백업 + 워밍업 기능을 제공한다.

innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup

4.2.7.6 버퍼 풀의 적재 내용 확인

InnoDB 버퍼 풀에 적재된 내용을 확인하고 싶을 때가 있다. 이럴 경우에는 아래 내용을 참고해서 조회해볼 수 있다.

데이터베이스 : information_schema
- innodb_buffer_page 테이블 : 저장된 인덱스 확인
- innodb_cached_indexs 테이블 : 통계값 확인

위 DB에 저장된 테이블들을 조회하면, 필요한 정보들을 살펴볼 수 있다. 예를 들어 아래 SQL을 이용하면 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되 있는지 확인할 수 있다.

mysql> SELECT
		it.name table_name,
        ii.name index_name,
        ici.n_cached_pages n_cached_pages
       FROM information_schema.innodb_tables it
        INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it.table_id
        INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = ii.index_id
       WHERE it.name=CPONCAT('employees','.','employees');
-------------------------------------------------------------
| table_name          | index_name         | n_cached_pages |
-------------------------------------------------------------
| employees/employees | PRIMARAY           | 299            |
| employees/employees | ix_hiredate        | 299            |
| employees/employees | ix_gender_birthdate| 299            |
| employees/employees | ix_firstname       | 299            |

4.2.8 Double Write Buffer

Double Wirte Buffer 기능은 리두 로그와는 별개로 동작하는 기능이다.

리두 로그 : 로그 공간 낭비를 막기 위해 변경점만 리두 로그에 기록함.

더티 페이지에는 변경점을 포함한 데이터 값이 모두 저장되어있다. 플러시 리스트 플러쉬 작업을 통해 더티 페이지의 데이터를 디스크에 동기화한다. 그런데 더티 페이지의 동기화 작업 중에 일부만 기록되면, 나머지 페이지들은 복원되지 못할 수도 있다. 왜냐하면 리두 로그는 변경점만 가지고 있는데, 완벽하게 복원이 될 수 없을 수도 있기 때문이다.

이를 위해 MySQL은 Double Write Buffer라는 기능을 제공한다. 이것은 다음과 같이 동작한다.

InnoDB 스토리지 엔진은 A~E 더티 페이지를 먼저 시스템 테이블 스페이스(로컬에 있는 파일) 한꺼번에 저장한다.
이후 A, B, C, D, E 더티 페이지를 하나씩 디스크의 데이터 파일에 반영하는 작업을 한다.

다시 말해서, 로컬의 시스템 테이블 스페이스에 통째로 데이터 페이지의 백업을 먼저 한다는 것이다. 이 후, 하나씩 더티 페이지를 로컬 디스크로 옮기다가 비정상적으로 종료되었다고 가정해보자. InnoDB 스토리지 엔진은 재시작 될 때, DoubleWrite 버퍼와 디스크의 데이터 파일의 페이지들을 모두 비교해서, 다른 내용이 있다면 DoubleWrite 버퍼의 내용을 디스크의 페이지로 복사한다.

다시 한번 정리하면 Redo 로그는 변경점만 기록하며, 더티 페이지의 디스크 동기화는 플러시 리스트의 플러쉬 작업을 통해서 처리된다. 이 때, 더티 페이지는 하나씩 반영이 되는데 중단되면 문제가 발생할 수 있다. 이를 방지하기 위해 Double Writer 버퍼 기능을 이용한다. 복사하려는 더티 페이지를 한꺼번에 Writer 버퍼에 먼저 작성해두고, 하나씩 옮긴다. 재시작할 때 Double Writer 버퍼 / 디스크 데이터 페이지를 비교해서 다르다면, Double Wirter에 복사해둔 값을 디스크 데이터 페이지로 옮기는 작업을 한다.

4.2.9 언두 로그

InnoDB 스토리지 엔진은 트랜잭션 / 트랜잭션 격리 수준을 보장하기 위해 MVCC를 제공한다. MVCC의 핵심은 언두 로그인데, 언두 로그는 변경점 이전의 값을 저장하는 녀석이다. 언두 로그는 다음과 같이 사용된다.

트랜잭션 보장
- 트랜잭션이 롤백 되면, Undo Log를 읽어서 이전의 값으로 복원해줌.
격리 수준 보장
- 트랜잭션 격리 수준에 따라 필요한 값을 언두 로그에서 읽어가기도 함. 예를 들어 Read Commit이라면 Undo Log에서 읽어서 반환하기도 함. (InnoDB 버퍼풀의 값이 커밋되지 않았다면)

언두 로그는 InnoDB 엔진에서 중요한 역할을 하지만, 꽤 큰 관리 비용을 필요로 한다.

4.2.9.1 언두 로그 모니터링

예를 들어 아래 쿼리를 실행했다고 가정해보자.

mysql > UPDATE member SET name='홍길동' WHERE member_id=1;

이 문장이 실행되면 트랜잭션 커밋 여부와 관계없이 아래 작업이 수행된다.

Redo 로그로 '홍길동'으로 변경되었다는 변경점 추가 됨.
InnoDB 버퍼풀의 데이터 페이지에 '홍길동'으로 변경된 데이터 값이 저장됨.
Undo 로그에 이전 값이 '벽계수'였다는 것이 저장됨.

만약 이 상태에서 트랜잭션 롤백이 발생한다면, Undo 로그에 있는 값이 InnoDB 버퍼풀에 반영될 것이다.

Undo 로그의 사용처

언두 로그는 다음과 같은 용도로 사용된다.

롤백 용도로 사용됨.
Repeatable Read 구현 용도로 사용됨.

Repeatable Read는 트랜잭션 내에서 항상 동일한 값을 읽어야 한다는 격리 수준이다. 이 격리 수준을 구현하기 위해 언두 로그가 사용되기도 한다. 사용은 다음과 같이 된다.

각 트랜잭션은 트랜잭션 ID를 가지고 시작함.
InnoDB의 데이터 페이지는 아래 저장 구조로 저장됨. 모든 트랜잭션이 같은 InnoDB 버퍼풀을 사용하며, 특정 데이터 페이지에 변경이 발생하면 마지막으로 변경한 트랜잭션 ID를 기록함.
Undo 로그에는 트랜잭션 ID와 값을 함께 저장함.
Repeatable Read에서 다시 레코드를 읽었을 때, 데이터 페이지의 트랜잭션 ID가 현재 트랜잭션 ID와 다르다면, Undo Log에서 현재 트랜잭션 ID의 값이 나올 때 까지 읽은 후 그 값을 사용함.
1. 더 중요한 것은 Repeatable Read 수준에서 트랜잭션이 시작할 때 현재 LSN을 캡처하고 이것을 읽기 뷰라고 함.
2. 읽기 뷰를 기반으로 작동하며, 읽기 뷰 이후에 발생한 변화는 보지 않음. (Redo 로그 관점)

// 데이터 페이지 저장 구조
+-------------------+
| 트랜잭션 ID       |
+-------------------+
| 롤백 포인터      |
+-------------------+
| 레코드 데이터     |
+-------------------+

위와 같은 형태로 구현되기 때문에 만약 너무 많은 Undo 레코드가 있을 때, 쿼리의 성능 문제를 불러올 수 있다는 것을 의미한다.

Undo 로그의 성능 문제

아래 작업이 진행되면, 긴 Undo Log가 생성될 수 있다.

대용량 데이터의 UPDATE / DLETE 처리
- 100만건의 데이터를 삭제했다고 하면, Undo Log에는 각 레코드에 대한 언두 로그 100만건이 남겨질 것임.
Long Running 트랜잭션
- 트랜잭션 A : 긴 트랜잭션 가져감. 커밋되지 않음.
- 트랜잭션 B : 트랜잭션 A보다 늦게 시작. DELETE 후 커밋 완료
- 트랜잭션 C : 트랜잭션 B보다 늦게 시작. UPDATE 후 커밋 완료.
- 트랜잭션 B / C는 각각 언두 로그를 만들어서 저장함. B/C트랜잭션은 커밋되었지만, 이 녀석이 삭제한 Undo Log는 삭제되지 않음.
- 왜냐하면 트랜잭션 A가 커밋되지 않았기 때문이다. 트랜잭션 A가 종료되어야 B/C에 의해 생성된 Undo 로그가 삭제됨.
Long Running 트랜잭션 최악의 경우.
- 사용자가 MySQL에 접속해서 트랜잭션 열어두고 까먹는 경우
- 1일 까먹고 커밋하는 경우, 1일치에 대한 Undo Log가 계속 생성되고 처리되지 못함.

언두 로그가 길어지는 것은 우선 용량 문제를 가져온다. 그런데 충분한 용량을 제공할 수 있다면 문제가 되지 않는다. 가장 결정적인 것은 '길어진 언두'로그는 '쿼리 성능'에 문제를 가져온다는 것이다.

길어진 언두 로그 상황에서 'Read Commit' 이상의 격리 수준의 트랜잭션에 변경된 레코드를 빈번히 조회하는 쿼리가 들어온다고 가정해보자. 그러면 이 트랜잭션들은 모두 필요한만큼 Undo 로그를 탐색해야한다. Undo 로그가 짧을 경우 1개만 살펴보면 되겠지만, Undo 로그가 수백이라면 최악의 경우 수백 개의 Undo 로그를 모두 살펴봐야한다. 이것은 쿼리 조회 성능에 문제를 가져올 수 있다.

현재 활성화 된 Undo 로그를 살펴보는 방법은 다음과 같다. 아래 명령어를 이용해서 살펴보면 현재 81개의 언두 로그가 존재하는 것을 알 수 있다.

mysql > 
  SELECT count
  FROM information_schema.innodb_metrics
  WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
  
+-------+
| count |
+-------+
|    81 |
+-------+

4.2.9.2 언두 테이블 스페이스 관리

언두 로그는 MySQL 8.0으로 업데이트 되면서, 언두 로그는 항상 외부의 별도 로그 파일에 기록되도록 개선되었다.

언두 테이블 스페이스는 1 ~ 128개의 롤백 세그먼트를 가진
롤백 세그먼트는 1개 이상의 언두 Slot을 가짐.
일반적인 트랜잭션은 2개 정도의 언두 Slot이 필요함.

만약 Undo 슬랏이 부족하다면, 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다. 따라서 충분한 Undo Slot을 확보해야한다.

Undo Log의 불필요한 공간. 삭제

MySQL은 자동 + 수동 모드를 이용해서 불필요한 Undo Log를 삭제하도록 도와준다.

자동 모드
- 트랜잭션이 데이터를 변경하면 이전 버전의 데이터를 언두 로그에 기록함. 트랜잭션이 커밋되면 언두 로그에 복사된 이전 값은 불필요해짐.
- InnoDB 스토리지 엔진의 퍼지 스레드는 주기적으로 언두 로그 공간에서 불필요한 언두 로그를 삭제함. (Undo Purger)

4.2.10 체인지 버퍼

체인지 버퍼는 인덱스와 관련된 작업을 할 때, 성능 향상을 위한 버퍼다.

UPDATE / INSERT 쿼리가 발생하면 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요함.
인덱스 업데이트 작업은 랜덤 Access를 해야하기 때문에 작업 비용이 큼. (기본적으로 인덱스는 로컬에 저장된 파일임)
성능 향상을 위해 InnoDB는 다음과 같이 동작함.
- InnoDB 버퍼 풀에 있으면, 바로 인덱스 업데이트.
- 디스크에서 읽어와야하면, 사용자에게는 바로 결과 응답함. ChangeBuffer에 업데이트 해야하는 것을 남겨둠.
ChangeBeffer에 임시 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드(Buffer Merge Thread)에 의해서 동기화 됨.
- 유니크 인덱스가 업데이트 되는 것이라면 ChangeBuffer는 동작하지 않음. 왜냐하면 무결성 조건을 위해서 항상 로컬 디스크를 먼저 살펴봐야 하기 때문임.

https://creampuffy.tistory.com/168

Hello world

4. 아키텍처 본문

4. 아키텍처

들어가기 전

4. 아키텍쳐

4.1 MySQL 엔진 아키텍쳐

4.1.1 MySQL의 전체 구조

4.1.1.1 MySQL 엔진

4.1.1.2 스토리지 엔진

4.1.1.3 핸들러 API

4.1.2 MySQL 쓰레딩 구조

4.1.2.1 Foreground Thread (클라이언트 스레드)

4.1.2.2 Background Thread

4.1.3 메모리 할당 및 사용 구조

4.1.3.1 Global 메모리 영역

4.1.3.2 Local 메모리 영역

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

4.1.5 컴포넌트

4.1.6 쿼리 실행 구조

4.1.6.1 쿼리 파서

4.1.6.2 전처리기

4.1.6.3 옵티마이저 (쿼리 변환 / 비용 최적화 / 실행 계획 수립)

4.1.6.4 실행 엔진 (쿼리 실행기)

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

4.1.7 복제

4.1.8 쿼리 캐시

4.1.9 스레드 풀

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

4.2 InnoDB 스토리지 엔진 아키텍처

4.2.1 PK에 의한 클러스터링

4.2.2 FK(외래 키) 지원

4.2.3 MVCC(Multi Version Concurrency Control)

MVCC에서 다른 트랜잭션은 어떤 값을 바라볼까?

Undo Log의 문제점

4.2.4 잠금 없는 일관된 읽기 (Non-Locking Consistent Read)

4.2.5 자동 데드락 감지

4.2.6 자동화 된 장애 복구

4.2.7 InnoDB 버퍼 풀 (메모리 공간)

4.2.7.1 버퍼 풀의 크기 설정

4.2.7.2 버퍼풀의 구조

LRU 리스트 구조 및 목적

플러시 리스트

4.2.7.3 버퍼 풀과 리두 로그

추가로 이해할 부분 (https://blog.ex-em.com/1700)

주의할 점

4.2.7.4 버퍼 풀 플러시

4.2.7.4.1 플러시 리스트 플러쉬 (더티 페이지를 반영하는 플러쉬)

4.2.7.4.2 LRU 리스트 플러쉬 (오래된 녀석 제거 + 더지 페이지 반영)

4.2.7.5 버퍼 풀 상태 백업 및 복구

4.2.7.6 버퍼 풀의 적재 내용 확인

4.2.8 Double Write Buffer

4.2.9 언두 로그

4.2.9.1 언두 로그 모니터링

Undo 로그의 사용처

Undo 로그의 성능 문제

4.2.9.2 언두 테이블 스페이스 관리

Undo Log의 불필요한 공간. 삭제

4.2.10 체인지 버퍼

티스토리툴바