04. 아키텍처

4.1 MySQL 엔진 아키텍처

MySQL 서버 = MySQL 엔진 + 스토리지 엔진
MySQL 엔진: 프론트, 기획자, 옵티마이제이션 및 실행계획을 세움
스토리지 엔진: 백엔드, 실무자, 세워진 계획을 실제로 실행
- 스토리지 엔진에 따라 트랜잭션 처리 방법이 달라진다.
- 스토리지 엔진에서 보는 트랜잭션과, MySQL 엔진에서 보는 테이블락은 다르다.

4.1.1 MySQL 전체 구조

MySQL 독특한 구조
- 보통은 모놀리식 같은 느낌
- MySQL 은 plugin 형태 (MSA 같은 느낌?) 으로 잘 나뉘어짐
- 장점
  - 유지보수 좋음
- 단점
  - 예상과 다른 동작을 할 수 있는 복잡함
MySQL 엔진
- SQL 인터페이스
- SQL 파서
- SQL 옵티마이저
- 캐시 & 버퍼
스토리지 엔진
- InnoDB
- MyISAM
- Memory
- 성능 향상을 위해서 키 캐시(MyISAM) 버퍼풀 (InnoDB)

4.1.1.1 MySQL 엔진

커넥션 핸들러
SQL 파서
전처리기
옵티마이저
ANSI SQL (American Standard SQL 지원)

4.1.1.2 스토리지 엔진

InnoDB 스토리지 엔진
- CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB
캐싱하는 법
- MyISAM 키캐시
- InnoDB 버퍼 풀

4.1.1.3 핸들러 API

핸들러(Handler) 요청: 스토리지 엔진에 쓰기 또는 읽기 요청
핸들러 API: 핸들러 요청에 사용되는 API
SHOW GLOBAL STATUS LIKE 'Handler%'
MySQL 엔진과 스토리지 엔진 사이에 주고받는 API

4.1.2 MySQL 스레딩 구조

Foreground 스레드 타입
Background 스레드 타입

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

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

클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 스레드

기본적으론 클라이언트 당 thread
클라이언트 요청시 스레드가 한개씩 물리는데, 최소 접속한 클라이언트 갯수만큼 존재
근데 쓰레드를 만드는 비용이 커서 쓰레드 캐시라는 걸로 미리 만들어놓음
따라서 실제 접속한 클라이언트보다 살짝 더 많은 쓰레드가 존재할 수 있음
이를 조절할 수 있는 thread_cache_size 라는 설정 값이 존재
쓰레드 풀과 쓰레드 캐시는 다르다!
- 쓰레드 캐시는 그냥 쓰레드를 미리 생성해 놓고, 연결해줌
- 요청과 쓰레드가 1 대 1 매핑, 처리 종료 시에 쓰레드 캐시에 쓰레드가 max 개수를 넘어가면 해당 쓰레드를 종료
- 쓰레드 캐시에 max 보다 작으면 반환
- 쓰레드 풀은 만들어 놓은 쓰레드를 재사용할 수 있음
- 요청과 쓰레드가 1 대 1 매핑이 아니라, 요청이 큐 자료구조로 들어감

4.1.2.2 백그라운드 스레드

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

4.1.3 메모리 할당 및 사용 구조

글로벌 메모리 영역
- InnoDB 버퍼 풀
- MyISAM 키 캐시
- Binary 로그 버퍼
- Redo 로그 버퍼
- 테이블 캐시
- 모든 스레드에서 공유하는 영역
세션(커넥션) 로컬 메모리 영역
- Join 버퍼
- 정렬(Sort) 버퍼
- 네트워크 버퍼
- Read 버퍼
- 스레드 별로 절대 공유하지 않는 영역

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

스토리지 엔진 플러그인
인증, 전문 검색 파서, 쿼리 재작성 플러그인
비밀번호 검증, 커넥션 제어 플러그인

4.1.5 컴포넌트

기존의 플러그인 아키텍처를 컴포넌트 아키텍처로 대체 중

4.1.6 쿼리 실행 구조

SQL 요청
-> MySQL 엔진
  -> 쿼리파서
  -> 전처리기
  -> 옵티마이저 (DBA 핵심역량)
  -> 쿼리 실행기
-> 스토리지 엔진(핸들러)
  - InnoDB, MyISAM, Memory
-> SQL 결과

4.1.7 복제(Replication)

4.1.8 쿼리 캐시(Query Cache)

버그 투성이라서 지금은 완전히 제거됨

4.1.9 스레드 풀

돈을 내야하는 엔터프라이즈 에디션에서만 지원하는 기능

일반적인 쓰레드 풀 구조

요청이 여러개 들어올 시, 요청을 큐에 저장
쓰레드 풀에 있는 임의의 쓰레드가 요청을 가져와서 처리
쓰레드 그룹의 갯수는 실행 환경의 코어 갯수 나 그 이상 만큼 설정
모든 쓰레드가 동작 중일 때, 새 요청에 대해서 새로운 쓰레드를 만들게 되면 스레드 풀의 의미가 없다.
- thread_pool_stail_limit 을 0 에 가까운 값으로 설정하지 말자.
특정 트렌잭션이나 쿼리를 우선적으로 처리하는 선순위 큐와 후순위 큐

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

데이터 딕셔너리 = 메타데이터
- 테이블의 구조 정보, 스토어드 프로그램 정보
- 5.6 까지는 FRM 파일에, 8.0 부터는 InnoDB 테이블에 저장

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

InnoDB 가 스토리지 엔진 중 유일하게 레코드 기반의 잠금을 제공

4.2.1 프라이머리 키에 의한 클러스터링

PK 순서대로 디스크에 저장

4.2.2 외래 키 지원

InnoDB 스토리지 엔진에서 지원
다른 엔진에서는 지원하지 않음
실무에서 솔직히 외래키 안 쓴다!

4.2.3 MVCC(Multi Version Concurrency Control)

잠금을 사용하지 않는 일관된 읽기를 지원하기 위해 필요
InnoDB 처럼 레코드 레벨의 트랜잭션이 가능해야 지원 가능

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

MVCC 를 통해 지원가능

4.2.5 자동 데드락 감지

잠금 대기 목록을 Wait-for List 로 그려서 관리
데드락 발생시, 잠금을 일으킨 트랜잭션을 강제 종료하고 롤백함

4.2.6 자동화된 장애 복구

복구모드: innodb_force_recovery 시스템 변수 설정 후 MySQL 서버를 재시작한다.
복구모드 중에는 SELECT 외의 쿼리는 불가능하다.

4.2.7 InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간
INSET, UPDATE, DELETE 를 모아서 한꺼번에 처리

4.2.8 Double Write Buffer

Partial-page == Torn-page 문제
- 페이지가 일부만 기록되는 현상
- Redo 로그가 페이지 전체가 아니라 변경된 내용만 기록하기 때문에 발생
Double-Write 를 통해 문제를 해결
- HDD 에서는 좋지만, SSD 에서는 Double-Write 를 비활성화하는게 좋음

4.2.9 언두 로그(Undo Log)

DML(INSERT, UPDATE, DELETE) 를 롤백할 수 있게 해줌
Transaction 과 Isolation Level 에 필요

4.2.10 체인지 버퍼

INSERT 와 UPDATE 변화를 체인지 버퍼 메모리에 올려두고 나중에 디스크에 반영
쿼리 속도를 빠르게 해줌

4.2.11 Redo 로그 및 로그 버퍼

ACID 중 Durable
서버가 비정상적으로 종료시, 기록되지 못한 데이터를 잃지 않게 해줌
데이터 변경 내용을 Redo Log 로 기록해서, 변경실패시 재 요청

4.2.12 어댑티브 해시 인덱스

인덱스 == B-Tree 인덱스
어댑티브 해시 인덱스
- 사용자가 만든 인덱스가 아님
- 자주 요청되는 데이터에 대해서 InnoDB 스토리지 엔진이 자동으로 생성한 인덱스

4.2.13 InnoDB 와 MyISAM, MEMORY 스토리지 엔진 비교

결론: InnoDB 가 짱이다!

4.3 MyISAM 스토리지 엔진 아키텍처

InnoDB 가 짱이므로 자세한 설명은 생략한다.

4.3.1 키 캐시

InnoDB 의 버퍼 풀과 비슷한 녀석

4.3.2 운영체제의 캐시 및 버퍼

InnoDB 보다 후져서 운영체제의 캐시 및 버퍼를 직접 사용한다.

4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB 가 짱좋은 PK 클러스터링을 사용한다면,
MyISAM 은 후져서 클러스터링 없이, Heap 을 활용한다.

4.4 MySQL 로그 파일

MySQL 서버가 맛탱이가 갔을 때, 다음 로그들을 보면 좋다.

4.4.1 에러 로그 파일

실행 도중 발생하는 에러나 경고 메시지의 로그 파일
my.cnf 에서 log_error 에 정의된 경로에 .err 확장자 파일로 생성됨

4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)

실행된 쿼리가 무엇이 있었는지 확인가능한 로그
my.cnf 의 general_log_file 에 정의된 경로에 생성
로그를 파일이 아닌 테이블에 저장하도록 할 수도 있음

4.4.3 슬로우 쿼리 로그

long_query_time 으로 설정한 실행 시간을 초과한 쿼리들을 기록
쿼리 실행시간을 통계낼 때 편리한 로그

Jane-s-Lab / books

[4장] 인덱스 #7