Mysql - 엔진 아키텍처

MySQL의 전체 구조

---

MySQL 서버는 크게 Mysql 엔진과 스토리지 엔진으로 크게 구분할 수 있습니다.

 

MySQL 엔진

• MySQL 엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룹니다. 
• SQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행합니다.

스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 부분을 스토리지 엔진이 전담합니다. Mysql 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다.

핸들러 API

• MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데 이러한 요청을
  핸들러(Handler) 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 합니다.
• 아래 명령어를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있습니다.

SHOW GLOBAL STATUS LIKE 'handler%'

 

Mysql 스레딩 구조

---

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

포그라운드 스레드

• 포그라운드 스레드는 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트가 요청하는 쿼리 문장을 처리합니다. 클라이언트가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아 갑니다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 됩니다. 이때 스레드 캐시에 유지할 수 있는 최대 스레드 갯수는 아래 명령어를 통해 확인할 수 있습니다.

SHOW VARIABLES LIKE 'thread_cache_size';

• 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 데이터를 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리합니다. MyISAM 테이블은 디스크를 쓰기 작업까지 포그라운드 스레드가 처리하지만 InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지 작업은 백그라운드 스레드가 처리합니다.

백그라운드 스레드

• 인서트 버퍼를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어 오는 스레드
• 잠금이나 데드락을 모니터링하는 스레드
• 백그라운드 스레드에는 위에 작성한거처럼 종류가 있으며 모두 중요한 역할을 담당하지만 그중에서도 가장 중요한 스레드는 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드입니다. 
• InnoDB 에서는 읽기 작업은 절대로 지연될 수 없지만 쓰기 작업은 쓰기 스레드에게 작업을 맡김으로써 지연되어 처리될 수 있기 때문에 INSERT와 UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됩니다.

메모리 할당 및 사용 구조

---

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있습니다.

 

글로벌 메모리 영역

• 일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당합니다. 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유됩니다.

로컬 메모리 영역

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다. Mysql 스레딩 구조의 이미지를 보시면 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 하며, 클라이언트와 MySQL 서버와의 커넥션을 세션이라고도 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현합니다.
• 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는 특징을 가지고 있습니다. 
• 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않는 경우에는 MySQL이 메모리 공간을 할당조차 하지 않을 수도 있습니다.
• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간은 커넥션 버퍼나 결과 버퍼가 있고 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 소트 버퍼나 조인 버퍼가 있습니다.

 

Mysql 엔진과 스토리지 엔진의 처리 영역

---

 

쿼리 실행 구조

---

쿼리 파서

• 쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소의 단위)으로 분리하여 트리 형태의 구조를 만들어 내는 작업을 의미합니다. 쿼리 파서를 진행하면서 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달합니다.

전처리기

• 각 토큰을 테이블 이름이나 컬럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 함께 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행합니다. 실제 존재하지 않거나 접근 권한이 없는 개체의 토큰은 이 과정에서 걸러집니다.

옵티마이저

• 옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당합니다. 

실행 엔진

• 옵티마이저가 두뇌에 해당한다면 실행 엔진은과 핸들러는 손과 발에 비유할 수 있습니다. (회사에 비유한다면 옵티마이저는 경영진,
  실행 엔진은 중간 관리자, 핸들러는 실무자)
• 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 작업을 수행합니다.
• 옵티마이저가 GROUP BY를 처리하기 위해 임시테이블을 사용하기로 결정했다는 가정
  1. 실행 엔진이 핸들러 A에게 임시 테이블을 만들라고 요청
  2. 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러 B에게 요청
  3. 실행 엔진은 WHERE 절을 통해 읽어온 레코드를 핸들러 A가 만든 임시 테이블에 저장하라고 핸들러 C에게 요청
  4. 실행 엔진은 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러 D에게 요청
  5. 최종적으로 실행 엔진은 결과를 사용자에게 반환

핸들러(스토리지 엔진)

• 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 또는 읽어오는 역할을 담당합니다. 핸들러는 결국스토리지 엔진을 의미합니다.

쿼리 캐시

• 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일한 SQL 쿼리가 실행되면 테이블을 읽어 오지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였으나 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야했습니다. 이는 심각한 동시 처리 성능 저하를 유발하며 결국 MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고 관련된 시스템 변수도 모두 제거가 되었습니다.

스레드 풀

• MySQL 서버 엔터프라이즈 에디션은 스레드 풀을 지원하지만 커뮤니티 에디션은 스레드 풀을 지원하지 않습니다.
• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다하더라도 MySQL 서버의 CPU가 제환된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 목적을 가지고 있습니다. 
• 스레드 풀은 동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못한다면 쿼리 처리가 더 느려지는 사태가 발생할 수 있습니다. 이 점에서 주의하고 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도도 높이고 운영체제 입장에서는 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있습니다.

 

 

---

해당 내용은 Real Mysql 8.0 책을 바탕으로 작성되었습니다.