MySQL 서버 구조 및 MySQL Engine 알아보기

이전 글: https://gojs.tistory.com/36

MySQL 서버 및 시스템 변수 설정

 

 

MySQL은 쿼리를 실행하기 위해서 여러 단계의 과정을 거친다.

이 때 동작하는 MySQL 서버를 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

MySQL 서버 구조

위 그림에서 이해할 수 있는 부분은 커넥션 핸들러를 통해 들어오는 요청을 SQL 인터페이스, 파서, 옵티마이저, 캐시, 버퍼 등의 모듈을 거쳐 실제 데이터에 대한 작업은 스토리지 엔진이 수행한다는 점이다.

(MySQL 엔진은 요청에 대한 전처리 모듈들을 포함하고 있다)

MySQL 서버에서 MySQL 엔진은 하나이지만, 스토리지 엔진은 여러 개 일 수있다.

예를들어, CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB; 와 같은 쿼리를 입력하면 해당 테이블에 대한 작업에 대해서는 InnoDB가 처리하게된다.

 

핸들러 API

MySQL 엔진의 쿼리 실행기에서는 각 스토리지 엔진에 쿼리에 대한 요청을 하게되는데, 이러한 요청을 핸들러 요청이라고 한다.

이 핸들러 요청은 각 스토리지 엔진이 구현한 핸들러 API를 사용하게 된다.

SHOW GLOBAL STATUS LIKE 'Handler%'; 쿼리로 각 핸들러 요청이 얼마나 발생했는지 확인할 수 있다.

핸들러 요청 조회

 

MySQL 스레드

MySQL 서버는 멀티 스레드로 동작하는 구조인데 포그라운드 스레드, 백그라운드 스레드로 구분된다.

MySQL 스레드 모델

 

포그라운드 스레드

위 그림에서는 하나의 클라이언트 커넥션에 하나의 포그라운드 스레드가 생성되고 이 스레드가 캐시, 버퍼 등에 접근하여 작업을 수행한다.

MyISAM 엔진의 경우 버퍼나 캐시에 필요한 데이터가 없으면 직접 디스크 읽기, 쓰기 작업까지 수행한다.

InnoDB같은 경우 백그라운드 스레드가 대신 디스크에 접근하여 읽기, 쓰기 작업을 수행해준다.

이 때 커넥션이 종료되게 되면, 포그라운드 스레드는 다시 스레드 캐시로 돌아가게된다.

thread_cache_size라는 시스템 변수에 지정된 갯수만큰 스레드 캐시에서 스레드를 가지고 있게 되고, 그 이상 넘어가는 숫자의 스레드가 생기는 경우 스레드를 종료시켜버린다.

스레드 관련 시스템 변수

 

백그라운드 스레드

MyISAM과 달리 InnoDB같은 경우에는 여러 가지 작업을 백그라운드 스레드가 처리해준다.

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

이 중 가장 중요한 역할을 하는 스레드는 로그 스레드와 쓰기 스레드이다.

innodb_write_io_threads, innodb_read_io_threads 시스템 변수로 읽기/쓰기 스레드 숫자를 지정할 수 있다.

innodb 엔진의 thread 관련 시스템 변수

InnoDB의 경우에도 읽기 작업의 경우에는 포그라운드 스레드가 작업하기 때문에 많은 숫자의 읽기 백그라운드 스레드가 필요하진 않지만 숫자를 잘 고려해야한다.

현재 실행 중인 스레드

performance_schema.thread 테이블에서 현재 실행 중인 스레드를 조회할 수 있다.

나는 root 계정으로 접속해서 하나의 커넥션을 가지고 있기 때문에, 그에 매칭되는 하나의 포그라운드 스레드가 생성되어 있는 것을 확인할 수 있다.

 

MySQL 메모리 구조

MySQL에서 사용되는 메모리는 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다.

글로벌 메모리 영역은 MySQL 서버 프로세스에 OS가 할당하며 여러 스레드가 공유하는 메모리 영역이다.

로컬 메모리 영역은 세션 메모리 영역이라고도 하며, 각 세션(커넥션)마다 할당된 스레드에 MySQL 서버에서 할당하는 메모리 영역이다. 쿼리를 처리하는데 필요한 메모리이고, 쿼리의 종류에 따라 필요없는 경우 할당하지 않기도 한다.

예를 들어 소트 버퍼나 조인 버퍼의 경우에는 해당 동작이 필요없으면 할당하지 않는다.

MySQL 메모리 구조

플러그인 모델

MySQL의 독특한 구조 중 하나는 플러그인 모델이라는 점이다.

MySQL 서버의 전체 구조를 보면 SQL 파서, SQL 옵티마이저, 스토리지 엔진 등 쿼리를 수행하는데 필요한 모듈들이 존재하는데, 이는 플러그인으로 구현되어있다.

따라서 내가 새로운 스토리지 엔진을 개발해서 해당 엔진을 플러그인으로 사용하게 할 수도 있다.

MySQL 플러그인

SHOW PLUGINS; 쿼리로 현재 설치되어 있는 플러그인을 조회할 수 있다.

TYPE 컬럼을 보면 각 플러그인들의 종류에 대해서 알 수 있는데 인증, 스토리지엔진, 파서 등 다양한 종류의 플러그인이 존재한다.

 

MySQL 엔진

SHOW ENGINES; 쿼리를 실행하여 지원되는 스토리지 엔진도 조회할 수 있다.

이 때 Support 컬럼에 표시되는 값은 4가지이다.

1. YES: 사용가능
2. DEFAULT: 사용가능, 없는 경우 MySQL 서버 실행에 오류가 생길 수 있음
3. NO: 사용불가능
4. DISABLED: 사용가능하지만 파라미터에 의해 비활성화된 상태

 

컴포넌트 모델

MySQL 8.0부터 컴포넌트 모델을 지원한다. 이는 플러그인 모델이 아래와 같은 단점을 가지고 있기 때문에 이를 보완하기 위해 구현되었다.

• 플러그인 간 통신 불가
• 플러그인 내부적으로 MySQL 변수, 함수 직접호출 (캡슐화X)
• 플러그인 상호 의존관계 설정안됨

MySQL 컴포넌트

MySQL 5.7 버전 까지는 비밀번호 검증기능이 플러그인 형태로 제공되었지만, MySQL 8.0 버전부터는 컴포넌트 형태로 개선되었다.

 

쿼리 실행 구조

MySQL에서의 쿼리 실행 구조와 구성하는 모듈에 대해서 알아보자.

MySQL 쿼리 실행 구조

 

쿼리 파서

사용자가 만들어낸 쿼리 문장을 파싱하여 트리 형태의 구조를 생성한다. (기본 문법오류 검출)

전처리기

파서 트리를 기준으로 쿼리 문장의 구조적 문제를 검출한다.

테이블, 컬럼, 함수 등의 객체의 존재 여부 및 권한 여부를 확인한다.

옵티마이저

쿼리 문장을 어떻게 가장 저렴한 비용으로 처리할지 결정하여 계획을 세운다.

실행 엔진

옵티마이저가 세운 계획과 핸들러에게 받은 결과를 가지고 또 다른 핸들러에게 실행을 요청한다.

• GROUP BY를 처리하기 위한 임시테이블 사용
  1. 실행 엔진 → 핸들러 (임시 테이블 생성)
  2. 실행 엔진 → 핸들러 (WHERE 절에 일치하는 레코드 Read)
  3. 실행 엔진 → 핸들러 (2번에서 읽은 데이터를 1번의 임시 테이블에 Write)
  4. 실행 엔진 → 핸들러 (임시 테이블의 데이터를 필요한 방식으로 Read)
  5. 실행 엔진 → 사용자 (조회 결과)

핸들러 (스토리지 엔진)

실행 엔진의 요청에 따라 물리적으로 데이터를 Read/Write 한다.

쿼리 캐시

SQL의 실행결과를 캐싱하여 동일 쿼리가 발생하는 경우 즉시 결과를 반환한다.

(데이터 변경되는 경우 캐싱 데이터 삭제 → 성능저하, 버그)

→ 8.0 버전 이상부터는 사용되지 않음

스레드 풀

스레드 풀은 엔터프라이즈 에디션에서만 제공한다.

스레드 풀은 스레드 갯수를 제한하여 CPU는 제한된 스레드 처리에만 집중할 수 있도록 하며 성능 향상을 도모한다.

따라서 thread_pool_size 변수로 스레드 그룹 갯수를 적절하게 조절할 필요가 있다.

(일반적으로 CPU 코어 갯수와 맞추는 것이 좋다)

 

새로운 요청이 발생하는 경우 thread_pool_max_threads 변수에 설정된 값 이상의 스레드는 생성할 수 없다.

실시간성이 중요한 서비스의 경우 thread_pool_stall_limit 변수 값을 낮춰서 사용하는 것이 좋다.

(설정값만큼 스레드 할당을 기다리고 작업이 안끝나면 신규 스레드 생성)

 

스레드 풀이 처리 중인 작업이 있더라도 thread_pool_oversubscribe 변수의 설정값만큼 추가로 스레드를 생성할 수 있다.

(설정값이 너무 크면 스레드 스케줄링에 오버헤드 발생)

 

MySQL의 스레드 풀에서는 선순위/후순위 큐를 사용하여 여러 쿼리의 요청 중 먼저 시작된 트랜잭션을 먼저 처리한다. (FIFO)

 

트랜잭션 지원 메타데이터

5.7 버전까지는 메타데이터를 .frm 파일에 저장

→ MySQL 서버 비정상 종료시 원자성 깨지는 현상 발생

 

8.0 버전부터 InnoDB의 mysql DB에 저장하도록 개선

→ 서버 비정상 종료시에도 트랜잭션 처리되어 원자성 보장

(mysql DB는 mysql.idb라는 파일에 통째로 저장됨)

 

하지만 MyISAM, CSV등의 스토리지 엔진을 사용하는 테이블은 여전히 저장할 별도공간 필요

*.sdi 형식의 파일에 저장된다

아래와 같이 ibd2sdi 유틸리티를 이용하면 InnoDB 테이블도 sdi 형식의 파일로 저장할 수 있다.

ibd2sdi 유틸리티로 sdi 파일 저장

 

다음 글: https://gojs.tistory.com/38

MySQL 스토리지 엔진 아키텍처