1. MySQL 아키텍쳐

1-1. MySQL 구조 && 동작과정

쿼리 파서(Parser) -> 전처리기(Preprocessor) -> 쿼리 옵티마이저(Query Optimizer) -> 쿼리 실행기(Query Execution) -> 핸들러(스토리지 엔진)

MySQL Connectors에 요청이 도착합니다.
사용자 스레드가 할당되고 요청이 MySQL 엔진에 전달됩니다.
쿼리 파서가 SQL을 MySQL이 이해 가능한 최소 단위로 잘라내고, 문법 유효성을 검증합니다.
1. 쿼리 문장을 토큰(MySQL 이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 구조로 만드는 과정이며, 쿼리의 문법 오류가 이 단계에서 확인 되며, 문법 오류가 있을 경우 사용자에게 오류 메세지가 전달 됩니다.
전처리기가 컬럼명, 테이블명 등이 존재하는지 확인하고, 접근 권한이 있는지 검증합니다.
1. 이전의 Parser 과정을 통해서 만들어진 파서 트리를 기반으로 문장에 구조적인 문제점이 있는지를 확인 하며, 토큰의 테이블 이름,컬럼명, 내장함수 와 같은 객체를 매핑해 해당 객체의 존재 여부와 접근 권한 등을 확인하는 과정을 해당 과정에서 수행하게 됩니다.
옵티마이저가 사용자가 전달한 SQL문을 어떻게 실행해야 효율적일지 결정합니다.
1. 쿼리 문장을 저렴한 비용(Cost)으로 가장 빠르게 처리 할지를 결정하는 역할 과 실행계획을 작성을 담당하며, DBMS의 두뇌에 해당한다고 할 수 있습니다. 옵티마이저에는 대표적으로 지정된 우선 순위에 따라서 스코어를 매겨서 실행 계획을 수립하는 RBO (Rule-Based Optimizer) 와 수행 가능한 방법의 비용 과 테이블 통계 정보를 통한 Cost 에 따른 실행 계획을 수립하는 CBO(Cost-Based Optimizer) 가 있습니다.
2. MySQL 에서는 CBO 를 사용하며 최근의 대부분 RDB 에서의 기본 옵티마이저는 CBO 를 사용하고 있습니다. 여기에서 사용되는 Cost 의 기준은 여러가지가 있으며 where 절의 검색조건 유무, join 유무, join 조건, 인덱스 , 인덱스 통계 정보 등의 조건들과 그로 인하여 가능한 여러 실행 계획의 Cost 를 비교하여 결정하게 됩니다.
3. 쿼리를 수행 후 아래와 같이 마지막 실행한 쿼리의 Cost 를 확인 할 수 있습니다.
```
  mysql> SHOW STATUS LIKE '%Last_query_cost%';

  +-----------------+--------------+

  | Variable_name   | Value        |

  +-----------------+--------------+

  | Last_query_cost | 19069.348704 |

  +-----------------+--------------+
```
실행 엔진이 핸들러 ( InnoDB스토리지 엔진)를 호출하고 결과를 가지고 다시 호출하며 흐름을 제어합니다.
1. 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있을 것 같습니다.
  옵티마이저 -> 실행 엔진 -> 핸들러
  실행 엔진은 만들어진 실행 계획대로 각 핸들러에게 요청을 하게 되고 또 다시 받은 결과를 또 다른 핸들러 요청하여 입력으로 연결하는 역할을 하게 됩니다.
결과를 반환합니다.
백그라운드 스레드에서 커밋되었으나 디스크에 반영되지 않은 내용을 디스크에 접근하여 일괄 처리합니다.

💡 위 내용은 MySQL가 어떻게 동작하는지 느낌만 본 것입니다. 더 자세히 이해하려면 `쓰레드`와 `메모리`를 이해해야 합니다. 아래에서 살펴보겠습니다.

1-2. 쓰레드

MySQL은 프로세스 기반이 아닌 스레드 기반으로 동작합니다.

그래서 크게 포그라운드 스레드와 백그라운드 스레드로 구분 할 수 있습니다.

1-2-1. 포그라운드 스레드

포그라운드 스레드(동시에 작업할 수 있는 일의 단위)는 최소한 MySQL 서버에 접속된 클라이언트 수 만큼 존재 하며, 주로 각 사용자 요청한 쿼리를 처리 합니다.

작업 마친 쓰레드는 쓰레드 캐시(Not 쓰레드 풀)로 반환
- 이때 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있다면 캐시에 넣지 않고 스레드를 종료 합니다
데이터 읽기: MySQL의 데이터 버퍼나 캐시로 부터 가져오며 버퍼가 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로 부터 데이터를 읽어서 처리
- MyISAM 테이블은 메모리 작업(읽기, 쓰기) + 디스크 쓰기 + ETC작업까지 포그라운드 스레드가 처리하지만
- InnoDB 테이블은 데이터 버퍼(쓰기)나 캐시(읽기)까지만 포그라운드 스레드가 처리하고 나머지 작업은 백그라운드 스레드가 처리 하게 됩니다.

1-2-2. 백그라운드 스레드

위에서 언급했다시피 MyISAM은 디스크 쓰기와 ETC까지 포그라운드 스레드가 하기 때문에 백그라운드 스레드가 없습니다.

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

인서트 버퍼(Insert Buffer)를 병합하는 스레드
로그를 디스크로 기록하는 스레드
InnoDB 버퍼 풀의 데이터를 디스크로 기록하는 스레드
데이터를 버퍼로 읽어 오는 스레드
잠금이나 데드락을 모니터랑 하는 스레드

1-2-3. MySQL 쓰레드 확인방법

MySQL 서버에서 실행 중인 스레드의 목록은 performance_schema 데이터베이스의 threads 테이블을 통해 확인 할 수 있습니다

SQL> select thread_id, name,type, processlist_user,processlist_host
from performance_schema.threads
order by type, thread_id;

확인되는 여러개의 스레드 중에서 'thread/sql/one_connection' 스레드가 실제 사용자의 요청을 처리 하는 포그라운드 스레드 입니다.

백그라운드 스레드 개수는 MySQL 서버의 설정에 따라서 내용과 수는 가변적이며, 동일한 이름의 스레드가 2개 이상 보이는 것은 여러개 스레드가 동일 작업을 병렬로 처리하는 경우 입니다

1-2. 메모리

MySQL가 사용하는 메모리에는 글로벌 메모리 영역과 로컬 메모리 영역이 있습니다.

1-2-1. 글로벌 메모리

InnoDB Buffer Pool(InnoDB를 사용하면 이 영역을 사용)
- Data Pages: 테이블 데이터 캐시
- Index Pages: 인덱스 데이터 캐시
- Insert Buffer: 인덱스 변경 작업 버퍼
- Data Dictionary: 테이블 구조 정보
- Log Buffer: 트랜잭션 로그를 임시로 저장하는 메모리 영역입니다.
- Change Buffer: 인덱스 페이지에 대한 변경 사항을 임시로 저장하여, 실제 인덱스 페이지에 대한 디스크 쓰기를 지연시키는 역할을 합니다. 이로 인해 성능이 향상됩니다.
Adaptive Hash Index: 자주 사용되는 인덱스의 해시 테이블
Table Cache: 테이블 정의 캐시(메타 데이터)
Redo Log Buffer: 리두 로그 버퍼
- 변경사항 → Redo Log Buffer에 저장 → 디스크에 있는 Redo Log Files에 저장
- 디스크에 저장 시점
  - 매 1초마다
  - 트랜잭션 커밋 시(체크 포인트)
  - Buffer가 1/2 이상 차면
Thread Cache: 생성된 스레드를 재사용하기 위한 캐시
Key Cache: MyISAM 스토리지 엔진의 인덱스 캐시(MyISAM을 사용하면 이 영역 존재)

🤔 Redo Log Buffer와 Redo Log Files 차이점

Redo Log Buffer , Undo Log Buffer는 결국 메모리에 있습니다. 즉 MySQL 서버가 비정상적으로 종료된다면, 디스크와 동기화 되지 않은 메모리의 정보는 사라지게 되어 데이터의 ACID를 보장할 수 없게됩니다. 이럴 때를 대비해 , Redo Log Buffer , Undo Log Buffer를 디스크에 넣어놓습니다. 이것들을 Redo Log Files, Undo Log Files 라고 합니다. 즉 비정상적인 종료를 위해 디스크에 ~ 로그 파일들이 있는 것입니다.

Redo Log Buffer , Undo Log Buffe 가 디지몬 진화를 해서 디스크에 기록 되면 Redo Log Files, Undo Log Files 라고 합니다.

1-2-1-1. ⭐️ LRU, MRU, 더티, 클린 페이지

위 글로벌 메모리의 InnoDB 버퍼풀 의 Data Pages와 Index Pages는 실제 데이터가 저장되는 공간이고, 이 페이지들을 세 가지 리스트(LRU, Flush, Free)로 관리합니다.

버퍼 풀 관리를 위해 InnoDB 스토리지 엔진은 LRU (Least Recently Used), 리스트와 Flush 리스트, Free 리스트 3개의 자료 구조로 관리한다.

Free List : 버퍼 풀에서 실제 사용자 데이터로 채워 지지 않은 비어 있는 페이지 목록, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용.
LRU List : 내부적으로 MRU(6x퍼센트)와 LRU(3x퍼센트)로 관리되며 오래될 수록 LRU의 tail 부분으로 이동되며 버퍼 풀에서 제거된다. 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 목적.
Flush List : 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록 관리 (메모리 -> 디스크 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결이 되어있다).

1-2-1-2. 체크포인트

InnoDB 엔진은 주기적으로 리두 로그 버퍼와 버퍼 풀의 더티 페이지를 디스크로 동기화하는 작업을 수행합니다. 이를 체크 포인트라고 합니다.

체크 포인트가 일어나는 시점

주기적 체크포인트:
- 데이터베이스 시스템은 일정한 시간 간격으로 자동 체크포인트를 생성.
로그 파일 크기 제한:
- 로그 파일이 특정 크기에 도달하면 체크포인트 발생.
메모리 사용량:
- 버퍼 풀 사용량이 특정 임계값 초과 시 체크포인트 발생.
시스템 이벤트:
- 특정 이벤트(예: 시스템 종료, DB 관리자 수동 요청 등) 발생 시 체크포인트 생성.
트랜잭션 수:
- 일정 수의 트랜잭션 커밋 후 체크포인트 생성.

체크포인트의 주요 작업

`Flush List Flush` (더티 페이지 디스크 기록)
LSN 갱신 (체크포인트 LSN 업데이트)
Redo Log 공간 관리
- 체크포인트 LSN보다 이전의 Redo Log 공간 재사용 가능하게 함

이 Flush 이야기 나오는 김에 말합니다. Flush List Flush 말고 LRU List Flush가 있습니다. 이는 사용 빈도가 낮은 데이터 페이지를 제거해서 새로운 페이지들을 읽어올 공간을 만드는 작업을 말합니다. LRU에서 자주 쓰인다면 MRU로 승격하지 않습니까? LRU List Flush는 그 반대로 생각하면 됩니다.

🤔 `Read Log Buffer`으로 `변경사항` 기록하는데굳이 `Change Buffer,` `Dirty Pages`가 왜 또 있지?

Dirty Page(오른쪽 초록색)

실제 변경된 페이지 데이터 저장
체크 포인트 시 디스크 기록
캐싱이 주목적

Redo Log Buffer (왼쪽 파란색)

트랜잭션의 전체 변경사항 기록
데이터 복구를 위한 상세 정보 포함
복구가 주목적

Change Buffer(중앙 보라색)

인덱스 관련 정보만 저장
최소한의 필요 정보만 보관
레코드 위치 정보 중심

❗️ 정리

정상적인 상황에서 더티페이지를 이용하여 데이터 변경사항을 디스크에 저장하고, 비정상적인 종료 상황에서는 Redo Log Files 이용하여 변경 사항을 저장합니다. 그럼 의혹이 생길 수도 있습니다. 정상적인 상황에서도 더티페이지 말고 Redo Log Files를 이용하여 저장하면 되지 않나?

⇒
더티페이지는 변경사항 저장 말고도 할 일이 많습니다.. 여러 변경을 모아서 처리해야하고요, 디스크에 있는 것들을 메모리에 미리 올려놔서 빠르게 읽게 해야합니다. 그래서 더티페이지가 필요합니다.

1-2-2. 로컬 메모리(Session/Thread Memory)

스레드 별로 독립적으로 사용 되고, 다른 스레드와 공유 되지 않으며, 로컬 메모리 공간의 중요한 특징으로 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 할당되지 않을수 있습니다.

또한 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있으며(커넥션 버퍼나 결과 버퍼), 그렇지 않고 쿼리를 실행하는 순간만 할당하는 소트 버퍼나 조인 버퍼 등이 있습니다.

커넥션이 열려있는 동안 할당되는 영역

Connection Buffer: 클라이언트 커넥션 버퍼
Result Buffer: 쿼리 실행 결과를 클라이언트에 전송하기 위한 버퍼

쿼리 실행하는 순간만 할당되는 영역

Sort Buffer: ORDER BY, GROUP BY 작업용
Join Buffer: 조인 작업용
Read Buffer: 테이블 풀 스캔용
Binary Log Cache: 바이너리 로그 캐시
Table Buffer: 임시 테이블용

🤔 글로벌 메모리 영역 InnoDB 버퍼 풀이 있는데 왜 로컬 메모리 영역인 Reader Buffer가 왜 존재하지?

InnoDB 버퍼 풀은 캐싱이 주목적이고, Reader Buffer는 쿼리 작업 공간이라고 생각하면 됩니다.

로컬 메모리에 있는 버퍼들에 쓰기 전에 InnoDB Buffer Pool 에 페이지가 존재하면 거기서 데이터를 가져오고 페이지가 없다면, 직접 디스크 접근하여 가져옵니다. 따라서, InnoDB Buffer Pool의 효율성이 높을수록 Reader Buffer의 성능도 향상됩니다.

2. MVCC : Multi Version Concurrency Control

하나의 레코드에 대해 여러 버전이 동시에 관리되는 기술
가장 큰 목적은 잠금 없는 일관된 읽기(Non-Locking Consistent Read) 제공

2-1. 언두 로그

UPDATE 요청을 처리하고 있고 COMMIT이 완료되기 이전에 SELECT 조회 요청이 오면 어떻게 될까요?

아직 디스크에 반영되지 않았으나 UPDATE 요청이 처리되고 있는 버퍼 풀의 내용을 반환해야 할까요
아니면 최종 COMMIT 버전(디스크에 있는 내용과는 다름)인 리드 로그 버퍼에 있는 내용을 반환해야 할까요?

(체크포인트 혹은 비동기화 과정이 일어나야지 리두 로그에 있는 내용들이 디스크에 저장됨)

이러한 고민들을 안은 채 격리 수준별 MVCC 동작을 살펴보겠습니다.

변경된 사항은 모두 리드 로그에 기록됩니다. 즉, 커밋된 데이터와 커밋되지 않은 데이터 모두 리두 로그 버퍼에 기록됩니다.(상위 블로그 글에 잘못된 설명이 많습니다. “커밋된 데이터만 리드 로그에 기록된다.” 이것은 틀린 말입니다.)

그 결과는 트랜잭션의 격리 수준(Isolation Level)에 따라 다릅니다.
만약 커밋되지 않은 내용도 조회하도록 해주는 READ_UNCOMMITTED라면 버퍼 풀에서 가장 최신 버전의 데이터를 읽되, 해당 데이터가 커밋되지 않은 상태라도 읽습니다. (Dirty Read 발생). 만약 READ_COMMITED 이나 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)이라면 변경되기 이전의 Undo 로그 영역의 데이터를 반환하게 된다. 이것이 가능한 이유는 하나의 데이터에 대해 여러 버전을 관리하는 MVCC 덕분이다.

격리 수준별 MVCC 동작

READ UNCOMMITTED:

버퍼 풀의 현재 데이터를 그대로 읽음
커밋되지 않은 변경사항도 읽음 (Dirty Read)

READ COMMITTED:

언두 로그에서 가장 최근에 커밋된 버전을 읽음,
한 트랜잭션 내에서도 매 SELECT 마다 달라질 수 있음 (Non-Repeatable Read 발생)

REPEATABLE READ:

트랜잭션 시작 시점의 언두 로그 데이터를 읽음
트랜잭션 내에서는 동일한 결과 보장
Phantom Read는 발생할 수 있음 (새로운 레코드 삽입 시)

SERIALIZABLE:

가장 엄격한 격리 수준
읽는 레코드에 shared lock을 걸음
동시성이 매우 낮아짐 (성능 저하)
실제로는 거의 사용되지 않음

MySQL: REPEATABLE_READ가 기본값

주의사항

오래 실행되는 트랜잭션은 언두 로그 공간을 많이 차지

마무리 하며

MySQL은 많이 써서 친숙하지만, 속은 알 수 없는 신비의 친구였습니다. 이번 기회에 Real MySQL 스터디를 하며 MySQL의 속사정까지 알아가고 있습니다. 어렵게만 느껴지던 MySQL을 톺아보니 다른 기술들도 부담감 없이 다가갈 수 있을 거라는 자신감이 생겼습니다. 일종의 이 배움이 작은 성공이라 생각합니다. 이번의 작은 성공이 큰 나중에 큰 성공의 한 몫을 한 것임에 확신합니다.

저작자표시

'CS > 데이터베이스' 카테고리의 다른 글

[Real MySQL 9장] 옵티마이저와 힌트 (0)	2025.01.09
[Real MYSQL 10장] 실행 계획 (0)	2025.01.03
공유 락과 배타 락 (0)	2024.12.10
MySQL 인덱스 (0)	2024.11.26

아무튼, 쓰기

MySQL 아키텍쳐

1. MySQL 아키텍쳐

1-1. MySQL 구조 && 동작과정