MySQL 엔진 아키텍처

2023.10.9 복습 리팩토링 시작

 

Real MySQL을 읽기 시작했는데, 이에 관한 공부 기록을 남겨보려고 합니다.

4장 아키텍처파트의 MySQL 엔진 아키텍처부터 시작하겠습니다.

MySQL 엔진 아키텍처

MySQL 전체 구조 이미지

MySQL 아키텍처

 

• MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.
• 이 둘을 모두 합쳐서 MySQL 또는 MySQL 서버라고 부른다.

MySQL 엔진

• MySQL엔진은 커넥션 핸들러, SQL 인터페이스, SQL 파서, SQL 옵티마이저, 캐시와 버퍼로 이루어진다.
• 표준 SQL인 ANSI SQL 문법을 지원하므로 타 DBMS에서 사용할 수 있다.
• 커넥션 핸들러를 사용해 접속 및 쿼리 요청을 처리하고 옵티마이저로 쿼리를 최적화한다.

스토리지 엔진

• MySQL 엔진은 요청된 SQL을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.
• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.
• MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
• 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM), InnoDB 버퍼 풀과 같은 기능을 내장하고 있다.
• 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.
• 아래 예시는 InnoDB 스토리지 엔진을 사용하도록 정의했다.

CREATE TABLE test_talbe (fd1 INT, fid2 INT) ENGINE = INNODB;

핸들러 API

• MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청한다. 
• 이러한 요청을 핸들러 요청이라고 한다. 여기서 사용되는 API를 핸들러 API라고 한다.
• 이 핸들러 API를 통해 얼마나 많은 데이터 로그 작업이 있었는지는 아래 명령어를 통해 이미지와 같이 확인할 수 있다.

SHOW GLOBAL STATUS LIKE 'Handler%';

데이터 작업 확인

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 포그라운드 스레드와 백그라운드 스레드로 구분이 가능하다.

MySQL 스레딩 모델

서버에서 실행 중인 스레드는 아래 명령어를 통해 확인할 수 있다.

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

아래는 명령어를 입력했을 때 출력되는 로그다. 

3개의 스레드만 포그라운드 스레드고, 나머지는 전부 백그라운드 스레드였다.

performance_schema.threads에서 출력한 내용들

• thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드다.
• 백그라운드 스레드의 개수는 MySQL 서버 설정의 내용에 따라 가변적일 수 있다.
• 동일한 이름의 스레드가 2개 이상 보이는 것은 여러 스레드가 동일 작업을 병렬로 처리하는 경우다.
• 참고로 MySQL 엔터프라이즈 에디션과 같은 서버는 전통적인 스레드 모델이 아니라 스레드 풀 모델을 사용할 수 있다.
• 전통적인 스레드모델에서는 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당된다.
• 하지만 스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 하나의 스레드가 여러 개의 커넥션 요청을 전담한다.

포그라운드 스레드 (클라이언트 스레드)

• 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하는 스레드는 다시 스레드 캐시로 되돌아간다.
• 이때 이미 스레드 캐시에 일정 개수 이상의 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다. 
• 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
• MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만 InnoDB 테이블은 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
• MySQL 사용자 스레드 == MySQL 포그라운드 스레드

백그라운드 스레드

MyISAM의 경우에는 별로 해당 사항이 없지만 InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다.

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

모두 중요하지만 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 하는 쓰기 스레드다.

• MySQL 5.5 버전 이상부터는 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정 가능하다.
• InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없다.
• 반면 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2 ~ 4정도, DAS나 SAN과 같은 스토리지를 사용할 때는 충분히 설정하는 것이 좋다.

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.

그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 존재한다. InnoDB 또한 이런 방식으로 처리한다.

 

하지만 MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있다.

이런 이유로 InnoDB에서는 CUD 쿼리로 데이터가 변경되는 경우
데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. 

하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

MySQL 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

MySQL 메모리 사용 및 구조

• 글로벌 메모리 영역의 모든 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.
• MySQL 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다고 생각해도 된다.

글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.

단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트 스레드 수와는 무관하며,

생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다. 대표적인 글로벌 메모리 영역은 다음과 같다.

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

로컬 메모리 영역

• 세션 메모리 영역이라고도 하며, MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
• 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드 1개를 할당한다.
• 이때 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.
• 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하므로 세션 메모리 영역이라고도 한다.
• 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.
• 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만, 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 가능성은 낮지만 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰버릴 수도 있으므로 적절한 메모리 공간을 할당해야 한다.
• 로컬 메모리 공간의 중요한 특징은 각 쿼리의  용도 별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 점이다. 대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다.
• 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼, 결과 버퍼)도 있고 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼, 조인 버퍼)도 있다.

대표적인 로컬 메모리 영역은 아래와 같다.

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

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

MySQL 플러그인 모델

플로그인에서 사용할 수 있는 것은 스토리지 엔진만 있는 것은 아니다.

전문 검색 엔진을 위한 검색어 파서도 플러그인 형태로 개발 할 수 있고, 사용자의 인증을 위한 기능도 모두 플러그인으로 구현되어 제공된다.

다른 개발 전문 회사 또는 사용자가 직접 스토리지 엔진을 개발하는 것도 가능하다.

 

MySQL 쿼리가 실행되는 과정은 아래와 같다.

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

• 대부분의 작업은 MySQL 엔진에서 처리되고, 마지막 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다.
• 만약 사용자가 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성한다.
• 위 그림의 각 처리 영역에서 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위로 처리된다.

MySQL 서버에서는 MySQL 엔진은 사람 역할을 하고 각 스토리지 엔진은 자동차 역할을 한다.

MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용한다.

MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다는 점은 꼭 기억하자.

 

MySQL 상태 변수에는 Handler_로 시작하는 변수들이 많다.

Handler_로 시작하는 상태 변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수라고 이해하면 된다.

MySQL에서 MyISAM이나 InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 데이터 읽기/쓰기 영역의 차이만 존재한다.

실질적인 GROUP BY, ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 쿼리 실행기에서 처리된다.

 

MyISAM과 InnoDB의 중요한 차이점을 방금 언급했다. 바로 데이터 읽기/쓰기 작업 처리 방식의 차이점이다.

중요한 내용은 '하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면,

스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 점이다.'

 

이제 아래 명령어를 입력해 MySQL 서버에서 지원되는 스토리지의 엔진 목록을 확인해보자.

show engines;

스토리지 엔진 목록

Support 칼럼에 표시될 수 있는 값은 YES, DEFAULT, NO, DISABLED다.

MySQL 서버에 포함되지 않은 스토리지 엔진은 Support 칼럼 값이 NO인데 이를 사용하려면 서버를 다시 빌드해야 한다.

하지만 우리 MySQL 서버가 적절히 준비가 되어 있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운해서 끼워 넣으면

사용할 수 있다고 한다. 아래와 같은 명령어를 입력하면 모든 플러그인의 내용을 확인할 수 있다.

show plugins;

MySQL은 비밀번호 검증, 커넥션 제어, 쿼리 재작성과 같은 다양한 플러그인을 제공한다.

조회한 플러그인들

컴포넌트

MySQL 8.0부터는 플러그인 아키텍처의 단점을 보완하고 대체하기 위해 컴포넌트 아키텍처를 지원한다.

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

쿼리 실행 구조

쿼리 실행 구조

쿼리 파서

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

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다.
• 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 해당 단계에서 수행한다.
• 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다.
• DBMS의 두뇌다. 매우 중요하다.
• 앞으로 대부분 옵티마이저가 선택하는 내용이 나오며, 옵티마이저가 더 나은 선택을 할 수 있게 유도 과정을 배운다.

실행 엔진

• 옵티마이저가 두뇌면, 실행 엔진은 손과 발이다.
• 실행 엔진이 하는 일을 예시로 들어보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 가정하자.

아래는 예시 절차다.

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과나 사용자나 다른 모듈로 넘긴다.

즉 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

핸들러(스토리지 엔진)

• 핸들러는 MySQL 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할이다.
• 핸들러는 결국 스토리지 엔진을 의미한다.
• MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 엔진이되고,
• InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 핸들러 엔진이 된다.

쿼리 캐시

• MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했다.
• 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다.
• 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 했다.
• 이는 심각한 성능 저하를 유발한다. 또한 MySQL이 발전하며 쿼리 캐시는 많은 버그의 원인이 되었다.
• 결국 MySQL 8.0에서 쿼리캐시는 완전히 제거되었다.

스레드 풀

• MySQL 커뮤니티 버전은 스레드 풀 기능을 지원하지 않지만, 엔터프라이즈 버전은 스레드 풀 기능을 제공한다.
• Perocona Server에서 제공하는 스레드 풀 기준으로 플러그인 형태로 작동하게 구현돼 있다.
• MySQL 커뮤니티 버전에서도 스레드 풀 기능을 사용하고자 하면 동일 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리를 MySQL 커뮤니티 에디션 서버에 설치해서 사용하면 된다.
• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적
• 많은 사람이 스레드 풀만 설치하면 MySQL 서버 성능이 2배가 되는 줄 알지만 이는 아니다.
• 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도를 높이고 불필요한 문맥교환을 줄여 오버헤드를 낮출 수 있다.
• Percona Server 스레드 풀은 기본적으로 CPU 개수만큼 스레드 그룹을 생성한다. 이 방식이 제일 좋다고 한다.
• 물론 thread_pool_size 변수를 통해 조정 가능하다.
• 스레드 풀의 스레드가 전부 일하고 있다면, 새로운 요청이 와서 스레드를 생성하는 방식으로 설정해도
  thread_pool_max_threads 시스템 변수에 설정된 개수를 넘을 수 없다.
• 응답 시간에 민감한 서비스라면 thread_pool_stall_limit 시스템 변수를 적절히 낮춰서 설정해야 한다.
• 만약 thread_pool_stall_limit 를 0에 가까운 값으로 설정해야 한다면 스레드 풀을 사용하지 않는 것이 좋다.

Perocona Server의 스레드 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.

이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상 시킬 수 있다.

트랜잭션 지원 메타데이터

• 데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.
• MySQL 5.7 버전까지는 메타데이터 생성 및 변경 작업이 트랜잭션을 지원하지 않아서 테이블의 생성 또는 변경 도중에 데이터베이스 서버가 종료되면 일관되지 않은 상태로 남는 문제가 있었다.
• MySQL 8.0 버전부터는 이런 문제를 해결하기 위해 테이블의 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB에 저장하도록 개선됐다.
• 시스템 테이블과 같은 모든 메타 데이터를 InnoDB 스토리지 엔진을 사용하도록 개선했다.

 

이상으로 포스팅을 마치겠습니다. 감사합니다.

 

참고자료

Real MySQL 8.0 1권 4단원

 

 

2023.10.10 복습 리팩토링 마무리