Real Mysql 8.0 - Part 1. MYSQL Architecture

Intro

배치성 업무 특성상 로직이 쿼리에 있는 경우가 대부분이고, 복잡한 쿼리로직이 많음에 따라 orm보다는 raw query를 다루게 된다.

뿐만 아니라 운영 과정에서 DB 관련된 timeout 이나 세션 사용문제 등으로 문제가 생김에 따라 자바나 스프링 이상으로 DB에 대한 숙련도가 필요하다고 느꼈고, 사용 스택인 Mysql 의 바이블 책이라고 불리우는 Real Mysql 8.0을 다시 읽게 되었다.

물론 인덱스나 파티셔닝 등 실제상황의 문제들을 해결하기 위한 직접적인 주제들도 중요하지만,

이전에 큰 그림을 먼저 잡고 가고자 본 주제를 다루었다.

본 포스팅은 Real MYSQL 8.0 책의 4장 내용을 다룹니다.

 

MYSQL Architecture

MYSQL Engine 구조

MYSQL은 여타 RDBMS처럼 다양한 프로그래밍 언어로부터의 접근방법을 모두 지원한다. C API, JDBC, ODBC, .NET의 표준 드라이버 제공해 이 드라이버를 이용하여 다양한 언어로 MYSQL 서버에서 쿼리를 사용할 수 있도록 지원한다.

'MYSQL 서버' 란 쿼리 분석부터 최적화까지 두뇌 역할을 하는 Mysql 엔진과, 메모리에 접근하는 스토리지 엔진을 통틀어 일컫는다.

MYSQL 서버는 mysql 엔진이 끝나서 실행계획이 나오면, 그에 따라 스토리지 엔진을 통해 엑세스하여 값을 조회, 저장하는 작업을 수행한다.

 

MYSQL Server

Perspective 1. Query Exectution Process

MYSQL Server에서 쿼리가 실행되는 과정

MYSQL Engine

Connection Handler 커넥션 핸들러

- 클라이언트로부터 접속 / 쿼리 요청

 

SQL Parser  쿼리 파서

- 쿼리 문장을 토큰 단위로 분리해 트리 형태의 구조로 만드는 작업

- 기본 문법 오류 발견하는 부분

 

Preprocessor 전처리기

- 파서 과정에서 만들어진 파서 트리 기반 쿼리 문장에 구조적인 문제점이 있는지 확인

- 각 토큰을 테이블 이름, 칼럼 이름, 또는 내장 함수 같은 개체를 매핑
   -> 해당 객체의 존재여부와 객체의 접근 권한 확인하는 과정 수행

 

Optimizer 옵티마이저                a.k.a 경영진

- 비용 최적화하여 실행 계획 수립

- 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는 방법 중요

 

Query Executor 실행엔진        a.k.a 중간 관리자

- 옵티마이저의 실행계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결

- main 함수 느낌

EX. 옵티마이저가 GROUP BY 처리하기 위해 임시 테이블을 사용하기로 결정한 케이스

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

Storage Engine (=> Handler)    a.k.a 실무자

- MYSQL 서버의 가장 밑단에서 MYSQL 실행 엔진의 요청에 따라 데이터를 디스크로, 디스크로부터 R/W

- MYSQL 엔진이 storage 엔진에 데이터 읽기 쓰기 명령을 하려면 핸들러를 통해야 함.

- MyISAM, InnoDB, ... 등 플러그인 모델로 사용 가능

 

하나의 쿼리 작업은 여러 하위 작업으로 구성되는데,
각 하위 작업이 MYSQL 엔진 영역에서 처리되는지 / 스토리지 엔진에서 처리되는지 구분할 수 있어야 한다.

 

Perspective 2. Threading

MYSQL Threading 구조

MYSQL 서버는 프로세스가 아니라 스레드 기반으로 동작하며, 스레드는 크게 포그라운드 / 백그라운드로 구분할 수 있다.

 

Foreground Thread

• 조회한 MYSQL 서버에 접속된 클라이언트의 수만큼 존재
  -> 각 클라이언트 사용자가 요청하는 쿼리 문장 처리
• 클라이언트 사용자가 작업을 마치고 커넥션 종료 시 해당 커넥션 담당하는 스레드는
   i. Thread cache로 돌아가거나
  ii. thread_cache_size 시스템 변수 이상의 대기 스레드 존재 시 종료
• MYSQL의 데이터 버퍼 / 캐시로부터 데이터 가져오고, 버퍼/캐시에 없는 경우 디스크 엑세스
  - InnoDB table : ~Buffer / Cache 포그라운드 스레드가 처리, Buffer ~ Disk 기록 작업은 백그라운드가 처리 

MYSQL Enterprise 버전의 스레드 풀 

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수르 줄여서 동시 처리되는 요청이 많다하더라도  MYSQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적.

주의할 점
 동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이므로
스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 더 느려지는 사례도 발생

 

Background Thread

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

 

Perspective 3. Memory

글로벌 메모리 영역

MySQL 서버가 시작되면서 운영체제로부터 할당되는 공간, 클라이언트 스레드의 수와 무관

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

로컬 메모리 영역

세션 메모리 영역이라고도 하며, MySQL 서버 상 존재하는 클라이언트 스레드가 쿼리 처리하는 데 사용하는 메모리 영역

• Sort Buffer
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

 

InnoDB Storage Engine Architecture

InnoDB 스토리지 엔진 구조

Primary Key Clustering

• InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장
  -> 프라이머리 키 값의 순서대로 디스크에 저장
        모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용
• PK 사용한 레인지 스캔은 상당히 빨리 처리됨
  -> 쿼리의 실행 계획에서 보조 인덱스보다 PK가 선택될 확률이 높도록 설정

 

Undo & Redo

Undo Log

• 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전에 백업한 이전 버전의 데이터
  • 트랜잭션 보장
    트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데,
    이 때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구
  • 격리 수준 보장
    특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하는 경우
    트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두로그에 백업해둔 데이터를 읽어 반환
• UPDATE member SET name = 'John' WHERE member_id=1;
  • 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 John으로 변경
  • 커밋 -> 현 상태 유지
  • 롤백 -> 언두 영역의 데이터를 다시 데이터 파일로 복구
• Undo table truncate

Redo Log

• 비정상적으로 서버가 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치
• 쓰기 비용이 상대적으로 낮은 자료구조를 가진 Redo 로그에 변경내용 발생 시 바로 write

MySQL 서버가 비정상 종료되는 경우 데이터 sync 깨지는 케이스

1. 커밋되었지만 데이터 파일에 기록되지 않은 데이터
2. 롤백되었지만 데이터 파일에 이미 기록된 데이터

1 -> 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사

2 -> 변경 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사

 

MVCC

• Multi Version Concurrency Control 의 약자
• Multi-version : 하나의 레코드에 대해 여러 개의 버전 동시에 관리
• 언두로그를 사용해 잠금을 사용하지 않는 일관적 읽기(Non-locking Consistent Read) 제공
• Example
  update를 수행하고 커밋하지 않은 상태에서 다른 사용자가 select로 해당 레코드를 조회한다면?
  - READ_UNCOMMITTED : InnoDB 버퍼풀 / 데이터 파일로부터 변경되지 않은 데이터 읽어 반환
  - 그 이상의 격리 수준 : 변경되기 이전의 내용을 보관하는 언두 영역의 데이터를 반환
• 트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리되어 시스템 테이블 스페이스 공간 늘어남

 

InnoDB Buffer Pool

• InnoDB 스토리지 엔진의 핵심부분
• 디스크의 데이터 파일 / 인덱스 정보를 메모리에 캐시해두는 공간이자,
• 쓰기 작업을 지연시켜 일괄로 처리할 수 있도록 하는 버퍼 역할
  • INSERT, UPDATE, DELETE 처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드 변경하므로
    랜덤 디스크 작업 수행, 헤드 왔다갔다... 무한반복
  • 하지만 버퍼 풀이 변경된 데이터를 모아 처리하면 랜덤 디스크 작업 횟수 줄일 수 있음.

버퍼 풀의 구조

• 버퍼풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size 변수에 설정) 단위의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장
• 버퍼풀의 페이지 크기 조각 관리 위해 관리하는 자료구조

자료구조	설명
LRU 리스트	LRU와 MRU 리스트가 결합된 형태 - 목적 : 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기 최소화
Flush 리스트	디스크로 동기화되지 않은 데이터를 가진 더티 페이지의 변경 시점 기준의 페이지 목록 관리 한번 데이터의 변경이 가해진 데이터 페이지는 플러시 리스트에 관리, 특정 시점이 되면 디스크로 기록 데이터가 변경되면 리두로그에 변경 내용 기록, 버퍼 풀의 데이터 페이지에도 변경 내용을 반영 -> 리두로그의 각 엔트리는 특정 데이터 페이지와 연결
Free 리스트	InnoDB 버퍼풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용

InnoDB 스토리지 엔진에서 데이터를 찾는 과정

버퍼 풀 관리를 위한 LRU 리스트 구조

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
   1. InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
   2. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지 검색
   3. 버퍼 풀에 이미 데이터 페이지가 잇었다면 해당 페이지의 포인터를 MRU 방향으로 승급
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4. 버퍼풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지가 aging되고, 결국 해당 페이지가 버퍼 풀에서 제거됨. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다
5. 필요한 데이터가 자주 접근되었다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

 

Change Buffer

• 레코드가 INSERT / UPDATE 된다? -> 인덱스도 업데이트 -> 랜덤 디스크 액세스, 고비용
• 버퍼풀에 없는 경우 즉시 실행하지 않고 임시 공간에 저장해둔 후 바로 사용자에게 결과 반환하는 형태로 성능 향상
  -> 이 때 사용하는 메모리 공간
• MYSQL 8.0 : INSERT, DELETE, UPDATE 등에 대해서 / ~MySQL 5.5 : INSERT 체인지 버퍼 활성화 가능
• 체인지 버퍼 머지 스레드 : 임시로 저장된 인덱스 레코드 조각을 이후 병합하는 백그라운드 스레드
• Unique Index 걸린 경우 : 사용자에게 결과를 전달하기 전에 중복 여부 체크해야 하므로 사용 불가

 

Adaptive Hash Index

• 사용자가 수동으로 생성하는 B-Tree 인덱스가 아니라,
  InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
• 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 검색해서 즉시 데이터 페이지 찾아갈 수 있음.
• B-Tree 검색시간을 줄여주기 위해 도입된 기능 : 루트 ~ 리프까지 찾는 시간 줄임
• 인덱스 키 값[B-Tree 인덱스의 Id, B-Tree 인덱스의 실제 키값), 데이터 페이지 주소
• 성능 향상에 크게 도움되지 않는 경우
  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우 : 조인이나 LIKE 패턴 검색
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

 

Wrap up

Performance

InnoDB 버퍼풀의 크기 확대   -> 데이터 캐시 기능 향상

리두 로그 파일의 크기 확대     -> 쓰기 버퍼링 기능 향상

체크 포인트 이벤트 발생 : 체크 포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화

 

Reference

http://www.kyobobook.co.kr/product/detailViewKor.laf?mallGb=KOR&ejkGb=KOR&barcode=9791158392703 

Real MySQL 8.0 (1권) - 교보문고