[MySQL] 아키텍처

 

 

 

MySQL 서버는 MySQL 엔진과 스토리지 엔진으로 구분된다.

 

MySQL 엔진 : 클라이언트와의 연결을 관리하는 커넥션 핸들러와 쿼리 최적화를 수행하는 옵티마이저가 중심을 이룬다. 

스토리지 엔진 : 실제 데이터를 저장하고 관리한다. MySQL 엔진에서 데이터를 쓰거나 읽어야 할 경우 스토리지 엔진에 핸들러 요청을 보낸다.(InnoDB, MyISAM...)

 

 

 

 

 

스토리지 엔진은 MySQL 서버에 플러그인 형태로 추가돼 각 테이블마다 가장 적합한 스토리지 엔진을 선택해서 사용할 수 있다.

 

InnoDB는 트랜잭션 처리에 강하고, MyISAM은 빠른 읽기 연산에 강하다.

스토리지 엔진들의 특성을 고려해 테이블별로 적합한 스토리지 엔진을 적용할 수 있다.

 

스토리지 엔진 뿐만 아니라 검색어 파서, 인증 도구 등도 모두 플로그인으로 구현돼 제공하니 필요에 따라 찾아서 사용하자.

 

 

MySQL 서버는 자바 스프링처럼 다수의 사용자 요청을 쓰레드 단위로 처리한다.

 

Foreground 쓰레드는 서버에 접속된 클라이언트의 수 만큼 존재한다.

스프링처럼 클라이언트의 요청이 도착하면 요청을 처리할 쓰레드를 생성한 후 클라이언트에게 할당한다.

 

클라이언트의 연결, 쿼리 실행같은 사용자 요청을 버퍼, 캐시, 디스크, 인덱스 파일로부터 데이터를 읽어서 처리하고,

작업을 마치고 커넥션을 종료하면 쓰레드는 쓰레드 캐시로 돌아간다. (캐시가 가득 차 있는 경우 쓰레드를 종료시킨다)

 

Background 쓰레드는 서버의 내부 작업을 처리한다. 

로그를 디스크로 기록, 데이터를 버퍼로 읽기, 잠금과 데드락을 모니터링하는 역할을 수행한다.

 

MySQL 서버의 전체적인 성능과 안정성을 유지하기 위해 사용된다.

 

(쓰레드가 하는 작업은 스토리지 엔진에 따라 다르지만 일반적으로 위와 같이 동작한다)

 

Foreground는 사용자의 요청에 직접 응답하기에 빠른 반응시간이 요구되고, Background는 사용자 인터페이스와는 무관하게 시스템 내부 최적화 작업을 수행한다. 

 

Foregroud 작업과 Background 작업을 구분해 자원을 효율적으로 분배하고 관리한다.

 

클라이언트의 수 만큼 Foreground 쓰레드가 생성되고 이 쓰레드들이 사용하는 메모리 영역에는 크게 두 가지가 있다.

 

1. 글로벌 메모리 영역

모든 쓰레드가 공유해서 사용한다.

InnoDB 버퍼 풀, 해시 인덱스, 테이블 캐시 등이 포함된다.

 

2. 로컬 (세션) 메모리 영역

클라이언트 쓰레드가 쿼리를 처리할 때 사용하는 메모리 영역이다. 

조인 버퍼, 커넥션 버퍼, 정렬 버퍼 등이 포함되고, 쓰레드별로 할당돼 독립적으로 동작한다.

 

 

 

쿼리 실행 구조에 대해 살펴보자.

 

클라이언트가 SQL을 MySQL 서버에 보내면 MySQL 엔진과 스토리지 엔진이 아래와 같은 순서로 작업을 처리한다.

 

쿼리 파서 → 전처리기 → 옵티마이저 → 쿼리 실행기 → 스토리지 엔진 

 

1. 쿼리 파서 

SQL을 MySQL이 인식할 수 있는 토큰으로 분리해 문법 트리로 변환한다.

SQL의 문법 오류는 이 과정에서 발견된다.

 

2. 전처리기

파서 과정에서 만들어진 트리를 기반으로 쿼리의 구조를 파악한다.

각 토큰을 테이블명, 칼럼명 등과 실제 객체와 매핑해 접근 권한과 존재 여부를 확인한다.

 

3. 옵티마이저

쿼리를 최적화한다.

여러 실행 전략 중에서 최적의 전략을 선택한다. 

 

4. 쿼리 실행기

옵티마이저가 선택한 실행 전략을 스토리지 엔진에게 전달한다.

만들어진 전략을 스토리지 엔진에게 전달하고, 받은 결과를 다시 전달하는 역할을 수행한다.

 

5. 스토리지 엔진 (핸들러) 

핸들러는 MySQL 서버의 최하단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어 오는 역할을 담당한다.

스토리지 엔진에 따라서 InnoDB가 핸들러가 될 수 있고, MyISAM이 핸들러가 될 수 있다.

 

 

* 쿼리 캐시 

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고 같은 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하는데...

동시 처리 성능 저하 문제로 8.0 버전부터는 제거됐다.

 

* 쓰레드 풀 

커뮤니티 버전에서는 제공하지 않는 기능이지만 Percona Server에서 플러그인 형태로 사용할 수 있다.

사용자의 요청을 처리할 때 제한된 수의 쓰레드만 사용하도록 한다.

 

CPU가 제한된 개수의 쓰레드 처리에만 집중할 수 있게 해 서버 자원 소모 감축을 목표로 한다.

CPU 시간을 제대로 확보하지 못 하는 경우 쿼리 처리가 더 느려질 수 있으니 주의하자. 

 

Percona Server의 쓰레드 풀은 선순위 큐와 후순위 큐를 사용해 작업을 재배치하는 기능을 제공하는데,

먼저 시작된 트랜잭션 내부에 속한 SQL을 빨리 처리할 수 있어 트랜잭션의 락이 빨리 해제돼 쿼리 성능이 좋아진다.

 

 

 

 

 

위 그림은 스토리지 엔진 중 가장 많이 사용되는 InnoDB 스토리지 엔진의 구조이다.

 

InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링돼 저장된다.

PK 순서대로 디스크에 저장되고, 클러스팅 된 인덱스가 아닌 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용한다.

 

즉, PK를 기준으로 클러스터드 인덱스를 만들고 이 인덱스를 통해 데이터의 물리적인 저장 순서를 결정하게 된다.

 

PK를 정의하지 않은 테이블에도 인덱스를 만들기는 한다.

UNIQUE 칼럼이 있는 경우 이 칼럼을 클러스터드 인덱스로 사용되고, UNIQUE 칼럼도 없는 경우 내부적으로 6바이트의 숨겨진 칼럼을 추가해 이 칼럼을 클러스터드 인덱스로 활용한다.

 

 

 

InnoDB 스토리지 엔진에서는 동시성과 일관성을 높이기 위해 MVCC (Multi Version Concurrency Control) 를 사용한다.

 

각 트랜잭션에 데이터의 버전을 제공해 여러 트랜잭션이 동시에 같은 데이터를 읽을 수 있게 한다.

InnoDB는 Undo log를 사용해 MVCC을 구현한다. 

 

A가 데이터를 변경했으면 InnoDB 버퍼 풀에는 변경된 데이터가 저장되고, Undo log에는 변경되기 전의 데이터가 저장된다.

(실제 디스크는 보통 버퍼 풀의 데이터와 sync를 맞춘다)

 

즉, 하나의 레코드에 대해 2개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 달라지게 된다.

MVCC를 사용하면 데이터를 조회할 때 트랜잭션의 격리 수준에 따라 다른 결과를 반환한다.

 

MVCC 덕분에 여러 트랜잭션이 동시에 같은 데이터를 읽을 수 있어 동시성이 높아지고, 읽기 작업에 대해 공유 락이 필요하지 않아 데드락 위험을 줄일 수 있다.

 

기본적으로 SELECT는 MVCC 덕분에 락에 상관없이 수행되지만, SELECT FOR UPDATE는 락을 가져가려 시도하기에 다른 트랜잭션에서 락을 가져간 경우 MVCC 환경에서라도 SELECT FOR UPDATE는 락이 해제될 때 까지 기다리게 된다.

 

데드락 상태를 감지한 경우 교착 상태에 빠진 트랜잭션들 중 Undo Log의 길이가 짧은 것 하나를 롤백시킨다.

 

 

 

InnoDB 버퍼 풀은 InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 데이터나 인덱스 정보를 메모리에 캐시하는 공간이다.

자주 액세스되는 데이터를 버퍼 풀에 캐싱해 디스크 I/O를 줄여 성능을 끌어올린다.

 

InnoDB는 버퍼 풀을 페이지 크기의 조각으로 쪼개고 3개의 자료구조로 페이지를 관리한다.

 

1. LRU 리스트 (Least Recently Used List) 

LRU 알고리즘에 기반해 데이터를 관리한다.

버퍼 풀에 페이지가 있는 경우 페이지를 캐시 내 가장 최근에 사용된 위치로 이동시킨다.

페이지가 없으면 새로운 페이지가 캐시로 로드된다.

한 번 읽힌 데이터 페이지가 자주 사용된다면 버퍼 풀의 Most Recently Used 영역에서 살아남고, 

잘 사용되지 않는다면 LRU 리스트의 끝으로 밀려나 버퍼 풀에서 제거된다.

 

2. Flush 리스트

버퍼 풀 내부에서 데이터나 인덱스 페이지를 수정하면 해당 페이지는 더티 페이지로 표시된다.

더티 페이지는 메모리 상태와 디스크 상태의 불일치를 나타내기에 적당히 디스크로 플러시 돼야 한다.

페이지를 변경할 때 먼저 Redo Log에 변경사항을 기록한 후 더티 페이지를 디스크로 플러시하는 방식으로 동작한다.

 

3. Free List

사용되지 않는 페이지를 관리하는 리스트이다.

 

 

InnoDB가 더티 페이지를 디스크와 동기화하고 Undo Log를 업데이트하는 트리거는 다음과 같다.

 

체크포인트 : 체크포인트 생성 시 플러시한다.

Undo Log 크기 : 용량이 임계치에 도달하면 플러시한다.

Shutdown : MySQL 서버가 꺼질 때 플러시한다.

Master Thread : 주기적으로 플러시한다.

 

Redo Log를 업데이트 할 때는 변경된 내용만 기록하는데, 더티 페이지에서 디스크 파일로 플러시 할 때 일부만 기록된다면 페이지의 내용이 Redo Log에 업데이트되지 않을 수 있다.

 

이를 극복하기 위해 Double Write Buffer를 사용한다.

데이터 파일의 쓰기가 실패할 때를 대비해서 더티 페이지들을 특정 공간에 저장하고 활용하는 기법이다.

 

 

스토리지 엔진마다 처리하는 방식이 다르니 주의하자.

 

 

이전까지는 MyISAM을 기본 스토리지 엔진으로 사용하는 경우가 많았지만, 8.0 버전부터는 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체되고 MyISAM 에서만 지원하던 기능들도 InnoDB 엔진에서 지원하게 되면서 MyISAM 스토리지 엔진의 사용이 많이 줄었다.

 

 

InnoDB

ACID 트랜잭션, FK과 행 레벨의 락을 제공해 동시성이 높은 쓰기 작업에 유리하다.

MySQL 8.0의 기본 스토리지 엔진으로 사용되며 데이터 무결성이 중요한 시스템에서 사용된다.

 

MyISAM

트랜잭션과 FK를 지원하지 않고, 테이블 레벨 락을 사용한다.

읽기 작업에 최적화 되어 있어 GIS 시스템에서 자주 사용된다.

 

MEMORY

데이터를 RAM에 저장해 임시 테이블과 캐시에서 사용된다.

가변 길이의 타입을 지원하지 않아 8.0 부터는 TempTable 스토리지 엔진이 대체해서 사용되고 있다. 

 

 

테이블 레벨의 락은 특정 작업을 수행할 때 전체 테이블을 잠그고, 행 레벨의 락은 특정 작업을 수행할 때 행을 잠근다.

동시 사용자가 많고 특정 행의 데이터가 자주 엑세스되는 경우 행 단위 락을 사용하고, 

테이블을 백업하거나 테이블의 구조를 변경하는 등 테이블 단위로 작업을 수행할 때는 테이블 단위 락을 사용하자.

 

MySQL의 스토리지 엔진은 테이블 단위로 설정할 수 있다.

기본적으로 InnoDB 엔진을 사용하고, 대량의 읽기 작업이 수행되는 테이블에서만 MyISAM 엔진을 사용하는 방식으로..

각 스토리지 엔진의 장점을 살려서 사용하자.