[Real MySQL 8.0] MySQL 엔진과 스토리지 엔진, 쿼리 실행 처리 순서

🌱 MySQL 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 나눌 수 있다.
 
MySQL 엔진의 경우 클라이언트로부터 접속하고, 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서, 옵지마이저로 이루어져 있다.
쿼리 관련 처리는 이곳에서 하기 때문에, 거의 '두뇌'와 같은 역할을 하면 된다고 생각하자.
 
스토리지 엔진의 경우 데이터를 실제 디스크에 저장하거나, 혹은 디스크에 저장된 데이터를 읽어오는 작업을 하며 하나의 MySQL 서버는 여러 개의 스토리지 엔진을 사용할 수 있다. 
 
MySQL 엔진과 스토리지 엔진은 서로 '핸들러 API'라는 것을 사용하여 데이터를 주고받으며, 어느 정도의 데이터 작업이 일어났는지 확인하고 싶다면 아래와 같이 명령어를 입력하면 된다.
 

show global status like 'handler%';

요렇게 다양한 핸들러 API들을 확인할 수 있다.
 

---

 

🌱 MySQL Thread

MySQL 서버는 기본적으로 스레드 기반으로 동작하며, Foreground / background 스레드로 나누어져 있다.
아래와 같이 입력하면 현재 실행 중인 스레드 목록을 확인할 수 있다.

select thread_id, name, type, processlist_user, processlist_host
from performance_schema.threads order by type, thread_id;

엄청 많기는 한데, 현재 나는 38개의 스레드가 실행 중인 것을 볼 수 있었다.
여기서 4, 5행을 보면 동일한 스레드 이름을 가진 것을 볼 수 있는데, 이는 여러 스레드가 동일한 작업을 병렬로 처리할 때 나온다.

특히 마지막 3개는 foreground로 동작하고 있으며, 이 중에서 thread/sql/one_connection이 실제로 사용자의 요청을 처리하는 foreground 스레드이다.
 

 
💬 Foreground Thread

foreground thread의 경우 최소 MySQL에 접속된 클라이언트 수만큼 존재하며, 클라이언트가 요청하는 쿼리문을 수행한다.
데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오고, 없으면 직접 디스크에 있는 데이터를 가져오거나 인덱스 파일로부터 파일을 읽어온다. 이때, MyISAM 같은 스토리지 엔진은 디스크 쓰기 작업까지 foreground가 하지만, InnoDB의 경우 데이터 버퍼 / 캐시까지만 관리하고 디스크 쓰기는 background가 처리한다.
 
실행 후 커넥션 종료 시 해당 스레드는 스레드 캐시로 돌아가는데, 만약 스레드 캐시에 이미 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드 자체를 종료시켜서 스레드 캐시에 존재하는 스레드 수를 관리하는 역할도 한다.
보통, 해당 스레드 수는 thread_cache_size라는 시스템 변수로 컨트롤이 가능하다.
 
 

💬 Background Thread

InnoDB 스토리지 엔진의 경우 백그라운드 스레드가 정말 여러 가지를 처리한다.

- 인서트 버퍼 (insert buffer) 병합
- ⭐️ 디스크에 로그 기록 (Log Thread)
- ⭐️ 버퍼 풀 (buffer pool)의 데이터를 디스크에 기록 (Write Thread)
- 데이터를 버퍼로 읽어오기
- 잠금, 데드락 모니터링

여기서 중요한 log / write thread의 경우 innodb_write_io_threads, innodb_read_io_threads 시스템 변수로 스레드 개수를 제어할 수 있다. 쓰기 스레드의 경우 2~4개, 별도의 스토리지 사용 시 디스크를 최적으로 사용할 만큼 설정하는 것이 좋다.
 
추가로, 데이터 쓰기 작업은 지연이 가능하지만 읽기 작업은 지연 처리가 안 되기 때문에 보통 DBMS에서 쓰기 작업은 버퍼링을 통해 일괄 처리하는 기능이 존재한다. 덕분에 CUD 쿼리 작성 시 디스크에 반영될 때까지 완전히 기다릴 필요가 없다. (InnoDB) 하지만, MyISAM에서는 포그라운드 스레드가 쓰기 작업까지 하다 보니까 쓰기 버퍼링 기능을 사용할 수 없다.
 

---

 

🌱 MySQL Memory Allocation

MySQL에서 사용하는 메모리 공간은 크게 글로벌 메모리 영역과 세션 메모리 영역 (=로컬 메모리 영역)으로 구분된다.
 
글로벌 메모리 영역의 경우 MySQL 서버가 시작되면서 OS로부터 할당되며, OS마다 조금씩 정책이 다르다. 글로벌 메모리 영역은 단 하나의 메모리 공간만 할당받으며, 모든 스레드에게 공유된다.
 
반면, 세션 메모리 영역의 경우 스레드당 하나씩 생성되며, 공유되지 않고 사용된다.
쿼리의 용도별로 공간이 할당되고, 필요하지 않으면 아예 메모리 공간 자체가 생성되지 않을 수도 있기 때문에 (정렬이나 조인 버퍼 같은 경우) 주의해야 한다. 
 
 

💬 MySQL에서의 쿼리 실행 과정

⭐️ SqL 파서 -> SQL 옵티마이저 -> SQL 실행기 -> 데이터의 읽기/쓰기 작업 -> 디스크

MySQL에서는 MySQL 엔진에 의해 SQL 실행기까지 실행되고, 마지막으로 디스크에 데이터를 읽거나 쓰는 작업은 스토리지 엔진이 처리한다. 어떤 스토리지 엔진을 사용하든 MySQL 엔진이 실행하는 부분은 대부분 비슷하며, 어떤 식으로 디스크에 데이터를 읽고 쓰는지가 스토리지 엔진별로 갈린다고 생각하면 된다. 이때, 데이터를 읽고 쓰는 작업은 대부분 1건의 레코드 단위로 처리된다.
이때, MySQL 엔진은 스토리지 엔진을 조정하기 위해 '핸들러'라는 것을 사용한다. (추후 다시 다룰 예정이다.)
 

SHOW ENGINES;

이거는 MySQL 서버에서 지원하는 스토리지 엔진의 목록이다. 여기서 support 컬럼에는 4가지의 옵션이 있는데, 다음과 같다.

- YES: MySQL 서버에 포함되어 있고, 사용 가능으로 활성화
- DEFAULT: YES와 동일한데 필수 스토리지 엔진임
- NO: MySQL 서버에 포함 X, 사용 시 재빌드 진행해야 함
- DISABLED: MySQL 서버에 포함은 되어 있는데 비활성화

 
또한, MySQL 서버에는 여러 플러그인도 있기 때문에 확인하고 싶다면 show plugins으로 확인하면 된다!
참고로, MySQL 8.0부터는 컴포넌트 아키텍처라는 것을 지원하는데, 이는 플러그인의 단점을 보완하기 위해 구현되었다.

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

설치된 컴포넌트는 아래와 같은 명령어로 확인이 가능하다.

select * from mysql.component;

 

---

 

🌱 MySQL Query Execution

MySQL에서 쿼리를 실행하는 구조이다. 차례로 따라가보자.
 

 
💬 쿼리 파서 (SQL Parser)

사용자 요청으로 들어온 쿼리 문장을 Token으로 분리하여 트리 형태의 구조로 만들어내는 작업을 의미한다.
쿼리 문장의 기본적인 문법 오류는 이 단계에서 발생하며, 이 경우 오류 메시지가 사용자에게 나가게 된다.
 

 
💬 전처리기

파서를 통해 나온 트리를 바탕으로 쿼리 문장에 구조적인 문제가 있는지 확인한다.
테이블 이름이나 컬럼 이름, 내장 함수 존재 여부, 객체의 접근 권한 등을 여기에서 확인한다.
 
 

💬 옵티마이저

쿼리 문장을 가장 저렴한 비용으로 처리하기 위한 최적화를 진행한다. 매우 중요한 과정이다.
 
 

💬 실행 엔진

핸들러에게 요청해서 받은 결과를 사용자나 또 다른 핸들러의 요청에 대한 입력으로 연결하는 역할을 수행한다.
핸들러는 실제로 일을 처리하는 역할을 수행하고, 실행 엔진이 옵티마이저와 실행기 사이에서 연결하는 역할을 한다.

ex) group by 처리 과정
실행 엔진이 핸들러에게 임시 테이블 생성 요청
-> 실행 엔진은 where절에 대한 레코드를 읽으라고 핸들러에게 요청
-> 읽어온 테이블을 임시 테이블에게 저장하라고 핸들러에게 요청
-> 생성된 임시테이블에 대해 필요한 방식으로 데이터를 읽어오라고 핸들러에게 요청
-> 최종 결과를 실행 엔진이 사용자나 다른 핸들러에게 넘긴다.

 
 
💬 핸들러 (=스토리지 엔진)

실행 엔진의 요청에 따라서 데이터를 디스크로 저장하거나 디스크로부터 읽어오는 역할을 진행한다.
어떤 스토리지 엔진을 가진 테이블을 처리하는지에 따라서 InnoDB 엔진을 사용할지, MyISAM 엔진을 사용할지 (혹은 그외) 채택하게 된다.
 
 

💬 쿼리 캐시

그림에는 없지만, MySQL의 쿼리 캐시는 SQL 실행 결과를 메모리에 캐시하고 동일한 쿼리가 실행되면 테이블을 읽는 대신에 메모리에 저장된 결과를 바로 반환하는 역할을 한다.
하지만, 테이블의 데이터가 변경되면 캐시도 함께 갱신되어야 하기 때문에 변경된 테이블에 관련된 내용은 전부 삭제했어야 했다. 전부 삭제하다 보니 성능상에도 치명적인 문제가 생겨, 8.0부터는 쿼리 캐시 기능 자체가 제거되었다.
 

 
💬 스레드 풀

MySQL 엔터프라이즈 버전에서는 스레드 풀 기능을 지원한다. 여기서는 Percona Server에서 제공하는 스레드 풀 기능을 살펴보자.
스레드 풀의 경우 동시에 실행 중인 스레드를 CPU가 최대한 잘 처리할 수 있는 수준까지 줄여서 빠르게 처리하도록 만드는 기능이기 때문에, CPU 시간을 잘 확보하지 못한다면 오히려 쿼리 처리가 느려질 수도 있다.
 
💡 thread_pool_size
Percona Server에서는 기본적으로 CPU 코어 수만큼 스레드 그룹을 생성하며, thread_pool_size 시스템 변수를 수정하여 조정이 가능하다.
 
💡 thread_pool_oversubscribe
만약, 이미 스레드 풀이 처리하는 작업이 있으면 thread_pool_oversubscribe 변수를 통해 얼마나 더 추가로 처리할지 받아들일 수 있다. (물론, 무작정 늘려도 좋지 않다. 그만큼 스케줄링해야 하는 스레드 수가 많아진다.) 기본값은 3이다.
 
💡 thread_pool_stall_limit
스레드 그룹 내의 모든 스레드가 일을 처리하고 있다면 해당 스레드 그룹에 새로운 Worker Thread를 추가할지, 아니면 기존 작업이 끝날 때까지 기다릴지 판단해야 한다. 스레드 풀 내의 타이머 스레드는 스레드 그룹의 상태를 주기적으로 체크하여 thread_pool_stall_limit 변수에 정의된 초만큼 작업 스레드가 처리 중인 작업이 끝나지 않았을 때 새로운 스레드를 생성해서 추가하도록 만든다. 당연하게도 이 수는 할당 가능한 전체 스레드 수 (thread_pool_max_threads)를 넘을 수 없다.
 

- 그렇다면, thread_pool_stall_limit을 0으로 생성해서 바로 스레드를 생성하도록 만드는 게 좋지 않을까?
이것은 아니다. 스레드 풀을 사용하는 의미 자체가 없어지는 것과 동일하다. (계속 새롭게 만드니 풀이 의미가 없어짐)

 
💡 선순위 / 후순위 큐
Percona Server에서는 선순위 / 후순위 큐를 통해서 특정 트랜잭션이나 쿼리를 우선으로 처리하도록 만드는 기능도 있다. (thread_pool_high_prio_tickets)
 
이는 동시에 실행되는 쿼리의 수를 제한하며, 열려 있는 트랜잭션의 수를 낮추기 위해 사용한다.
새로운 커넥션이 생성될 때마다 '우선순위 티켓' 같은 것을 할당하여 선순위 큐에 들어갈 수 있도록 만들며, 기본적으로 이미 시작된 커넥션의 경우 선순위 큐에 들어가게 된다. 만약 모든 커넥션에 대해 이런 스케줄링을 사용하고 싶지 않다면, 변수 값을 0으로 할당하고, 값이 클수록 각각의 트랜잭션은 선순위 큐에 들어갈 기회를 더 많이 얻게 된다.
 
반대로, 모든 워크 스레드 수가 oversubscribe까지 도달하게 된다면 우선순위가 낮은 커넥션을 제한하여서 방지할 수 있다. 이미 시작된 트랜잭션을 우선으로 처리하고, 해당 트랜잭션이 처리될 때까지 새로운 트랜잭션을 시작하지 않아서 스레드 그룹이 과다하게 사용되는 것을 제한하는 것이다.
 
 

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

DB 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 or 메타데이터라고 한다.
MySQL 5.7까지는 FRM 파일에 테이블의 구조 정보를 관리했으나, 이는 MySQL 서버가 비정상적으로 종료되거나 생성, 변경 시 트랜잭션을 지원하지 않아서 일관성이 자주 깨지는 현상이 발생하였다.
그래서 8.0부터는 모두 InnoDB 테이블에 저장되며, '시스템 테이블' 같은 형태로 관리된다. 

show databases;

 

실제로, show databases를 입력했을 때 나오는 'mysql'이라는 DB에 저장되는 것을 확인할 수 있다.
(mysql.ibd라는 테이블스페이스에 저장되기 때문에 해당 파일에 대해 관리가 필요하다!)
 
여기서 의문인 점은, use tables를 통해 확인해도 실제 테이블의 구조가 저장된 테이블을 볼 수 없다는 것이다.
이는 사용자가 직접 임의로 수정할까봐 조회 불가능하게 만든 것이며, 이 대신에 information_schema DB를 통해서 확인하도록 만들었다.

select * from information_schema.tables;

실제로 이렇게 내가 생성했던 테이블의 정보를 조회할 수 있는 것을 확인할 수 있다.
 

select * from mysql.tables;

반면에, mysql DB를 통해서 테이블 목록을 확인하려고 보면 위와 같이 권한이 없다는 문구가 뜬다!
 
그렇다면, 이렇게 innoDB 테이블로 관리되면 어떤 것이 좋을까?
바로, '원자성'을 만족할 수 있다는 점이다.
테이블로 관리된다는 것은 설정정보가 테이블에 커밋되거나, 혹은 롤백 (Atomicity)되는 것을 보장할 수밖에 없기 때문에 기존처럼 파일로 관리했을 때와 다르게 데이터의 일관성이 깨지는 일이 발생하지 않는 것이다.
하지만, innoDB가 아닌 다른 스토리지 엔진을 사용하는 테이블의 경우, 여전히 메타 정보를 SDI 파일으로 관리하기 때문에 유의해야 한다.
 

---

 
다음 포스팅부터 InnoDB 스토리지 엔진에 대해서 알아보도록 하자.