[데이터베이스] MySQL 아키텍처

 

MySQL 전체 구조

MySQL 구조는 클라이언트의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와

SQL 파서 및 전처리기 SQL 옵티마이저와 캐시 와 버퍼로 이루어진 MYSQL 엔진과 스토리지 엔진으로 구성되어 있다

MySQL 엔진은

클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 담당하는 옵티마이저가 주를 이룬다.

SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.

스토리지 엔진은

, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.

스토리지 엔진을 여러대를 사용할 수 있다?

MySQL 서버에서 엔진은 MySQL 엔진은 하나이지만 스토리지 엔진은 여러 대를 사용할 수 있다

Copy

CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

위 명령어를 사용하면 test_table 은 모든 CRUD 명령어가 INNODB 엔진을 사용하게 된다.

Handler API 란

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 handler 요청이라고 하고, 여기서 사용되는 API를 핸들러 API 라고 한다

InnoDB 또한 이 handler api 를 이용하여 데이터를 주고 받는데 위는 handler api 의 기능을 명세한 것이다.

MySQL 스레딩 구조

MySQL 은 프로세스 기반이 아닌 스레드 기반이다.

MySQL 의 스레드는 크게 foreground 쓰레드와 background 쓰레드로 나뉘어 진다.

해당 이미지는 총 쓰레드를 출력한 것

foreground 는 3개 밖에 안된다. 나머지는 전부 background 이다.

one_connection 만이 사용자의 요청을 처리하는 포그라운드 스레드이다.

💡
참고위 설명은 MySQL의 전통적인 쓰레드 모델이다.쓰레드 모델과 다른 것은 쓰레드 풀 모델을 사용할 수 있다.전통적인 쓰레드 모델은 클라이언트의 커넥션 당 쓰레드를 생성 해서 할당한다.쓰레드 풀 모델은 1개의 쓰레드가 여러 커넥션을 담당할 수 있다.

사용자 쓰레드 == 클라이언트 쓰레드 == 포그라운드 쓰레드

포그라운드 쓰레드는 클라이언트 커넥션 요청이 오면 쓰레드를 생성하고

요청이 종료되면 쓰레드 캐시로 돌아간다.

이때 thread_cache_size 가 꽉 찼다면 쓰레드는 대기하지 않고 삭제된다.

thread_cache_size는 변수이므로 변경 가능하다.

Copy

SHOW VARIABLES LIKE 'thread_cache_size';

InnoDB는 포그라운드 쓰레드는 캐시&데이터 버퍼에 데이터를 읽어오고 데이터가 존재하지 않는다면 디스크나 인덱스 파일로 부터 데이터를 읽어온다.

(쓰기 작업은 수행하지 않는다)

IsamDB 는 포그라운드 쓰레드가 읽기 작업 뿐 아니라 쓰기 작업까지 관여한다.

백그라운드 쓰레드

InnoDB에서 백그라운드 쓰레드가 수행하는 작업들

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

MySQL 5.5 버전부터 데이터 읽기 쓰레드와 데이터 쓰기 스레드를 2개 이상 지정할 수 있게 됐으며. innodb_write_io_threads 와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다.

read 작업은 포그라운드에서 어느 정도 전담해서 괜찮지만

쓰기 작업은 디스크인 경우에는 2~4 개 정도

DAS 나 SAN 과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다.

쓰기 작업은 지연될 수 있어도 읽기 작업은 지연될 수 없다.

따라서 쓰기 작업은 버퍼에 데이터를 저장하여 한번에 디스크에 반영한다.

InnoDB가 위와 같은 방식이고

MyISAM 은 포그라운드가 읽기와 쓰기를 모두 관여하여 쓰기 지연을 할 수 없고 (할 순 있어도 추천하지 않는 방식) 따라서 읽기에 지연이 생길 수 있다.

하지만 디스크 반영은 즉각적이다.

💡

<요약>

포그라운드 쓰레드는 데이터를 버퍼 풀이나 캐시나 디스크나 상관없이 읽기 작업이라면 포그라운드에서 처리한다.

백그라운드 쓰레드는 로그를 디스크에 기록하거나, 버퍼 풀의 데이터를 (더티 페이지) 디스크에 기록하는 작업을 수행하거나 데드락 감지까지 관여한다

메모리 할당 및 사용 구조

MySQL 에서 사용하는 메모리는 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

글로벌 메모리 영역은 MySQL 서버가 시작하면서 운영체제로부터 할당 받는다.

운영체제 메모리에 얼마만큼인지는 운영체제마다 다를 수 있고

간단하게 시스템 변수로 설정된 크기 만큼 할당 받는다고 생각해도 된다.

글로벌 메로리 영역

글로벌 메모리 영역은 클라이언트 스레드의 수와 무관하다.

보통 하나의 메모리 공간만 할당하고 상황에 따라 N개로 늘어날 수는 있지만 이 또한 클라이언트 스레드의 수와는 무관하다.

글로벌 메모리 영역은 모든 스레드에서 공유 가능하다.

대표적인 글로벌 메모리 영역은 다음과 같다.

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

로컬 메모리 영역 == 세션 메모리 영역 == 클라이언트 메모리 영역

커넥션으로 인해 클라이언트 스레드가 할당되고 클라이언트가 스레드가 사용하는 메모리 영역

클라이언트 스레드가 쿼리를 처리하는 데 사용한다.

대표적으로 다음이 존재

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

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.

로컬 메모리의 크기를 잘 설정하는 것도 필요할 수 있다. (최악의 경우에는 메모리 오버플로우가 생길 수도)

각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다.

소트버퍼 나 조인 버퍼가 그러함

커넥션 버퍼와 결과 버퍼 같은 것들은 커넥션이 맺어져 있으면 할당 되어 있음

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

• 스토리지 엔진 뿐 아니라 다양한 인증 모델이라던가 파서 모델을 확장할 수 있다.
• 사용자의 쿼리를 실행하는 과정은 다음과 같음

플러그인 방식의 단점과 MySQL 8.0 컴포넌트

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

MySQL 8.0 부터는 기존 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처를 지원한다.

쿼리 캐시

쿼리 실행 구조

사용자의 쿼리는 다음 순서로 실행된다.

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

쿼리파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 냄
• 기본 문법 오류는 이 단계에서 발견되어 오류 메시지를 전달함

전처리기

• 파서 트리를 기반으로 구조적으로 문제가 있는지 확인
• 객체의 존재 여부와 접근 권한등을 확인

옵티마이저

• 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할 수 있도록 결정
• 이 책의 상당수 내용은 옵티마이저와 관련됨
• 사실상 MySQL 서버의 두뇌 역활 실행 엔진과 핸들러는 손과 발 정도

실행 엔진

• 옵티마이저가 수행한 최적화 쿼리를 핸들러 요청을 통해 쿼리를 실행하는 역할

핸들러(사실상 스토리지 엔진)

• 실행 엔진이 명령하는 읽기와 쓰기 작업을 해당 핸들러가 수행

쿼리 캐시

동일한 쿼리에 대한 실행 결과를 캐싱하는 역할을 맡는 기능

하지만 여러가지 에러(데이터 정합성 같은) 때문에 MySQL 8.0 부터는 사라진 기능

아마도 쿼리 자체에 대한 결과물을 parser 앞단에서 캐싱하는 것 같음

딱 보기만 해도 데이터 정합성 같은 부분에서 에러 발생 확률 높음

스레드 풀

• 커뮤니티 에디션엔 없음
• 일반적인 서버의 스레드 풀과 유사