MySQL 아키텍처

1. MySQL 엔지 아키텍처

 

MySQL 전체구조

MySQL 구조도

MySQL은 일반 상용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다.  MySQL 고유의

C API부터 시작해 JDBC나 ODBC 등응 모든 언어로 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다.

 

MySQL서버는 크게 엔진과 스토리지 엔진으로 구분할 수 있다. 그러면 한 번 살펴보자.

 

MySQL 엔진

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 sQL 파서 및 전처리기, 퀴리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.  또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.

 

스토리지 엔진

MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

CREATE TABLE test_table (fb1 INT, fb2 INT) ENGINE=INNODB;

위 예제에서 test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다. 이 때 INSERT, UPDATE, DELETE, SELECT, ... 등의 작업이 발생하면 InnoDB 스토리지 엔진이 그러한 처리를 담당한다. 그리고 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀과 같은 기능을 내장하고 있다.

 

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다. 이 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지는 SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 확인할 수 있다.

 

실제로 실행시켰을 때 다음과 같이 나오는 것을 알 수 있다. 그렇다면 다음 내용들에 대해 알아보자

Variable	Value
Handler_commit	커밋 수
Handler_delete	행을 삭제 하기 위한 요청 횟수
Handler_discover	NDBCLUSTER 스토리지 엔진에 요청하여 테이블 이름을 요청한 횟수
Handler_external_lock	external_lock() 호출한 횟수, 일반적으로 테이블 엑세스 시작과 끝에 발생
Handler_mrr_init	테이블 엑세스에 다중 범위 읽기 구현을 사용한 횟수
Handler_prepare	2단계 커밋 작업을 준비한 횟수
Handler_read_first	인덱스의 첫 번째 키 값을 패치(fetch)한 횟수
Handler_read_key	단일 행의 인덱스 키 값을 읽은 횟수
Handler_read_last	인덱스의 마지막 키를 읽은 횟수
Handler_read_prev	인덱스의 이전 행의 키를 읽은 횟수
Handler_read_prev	인덱스의 이전 행의 키를 읽은 횟수
Handler_read_rnd	고정된 위치의 특정 행을 읽은 횟수
Handler_read_rnd_next	고정된 위치의 특정 행에 대한 후속 행 읽기 횟수
Handler_rollback	스토리지 엔진이 롤백 요청을 받은 횟수
Handler_savepoint	SavePoint를 요청한 횟수
Handler_savepoint_rollback	SavePoint 지점으로 롤백을 요청한 횟수
Handle_update	행을 업데이트 하기 위한 요청 횟수
Handler_write	행을 삽입 하기 위한 요청 횟수

 

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 Thread와 백그라운드 Thread로 구분할 수 있다. MySQL 서버에서 실행 중인 스레드의 목록은 다음과 같은 쿼리로 작동할 수 있다.

SELECT thread_id, name, type, processlist_user, processlist_host 
FROM perfomance. threads ORDER BY type, thread_id;

포그라운드 Thread

최소한 MySQL 서버에 접속된 클라리언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 Thread pool로 돌아간다. 

 

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

 

백그라운드 Thread

InnoDB는 여러가지 작업이 백그라운드로 처리된다. 대표적으로 Insert Buffer를 병합하는 Thread, 로그를 디스크로 기록하는 Thread, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 Thread 여러가지 잠금이나 데드락을 모니터링하는 Thread등이 있으며 이러한 Thread를 총괄하는 main Thread가 존재한다.

 

이 중 가장 중요한 건 log Thread와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 write Thread일 것이다. MySQL 5.5 버전 부터 데이터 쓰기 Thread와 읽기 Thread의 개수를 2개 이상 지정할 수 있게 됐으며, innodb_wirte_io_threads와 innodb_read_io_threads 시스템 변수로 Thread의 개수를 설정한다. InnoDB에서도 데이터를 읽는 작업은 주로 클라리언트 Thread에서 처리되기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정도, DAS나 SAN 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있게 충분하게 설정하는 것이 좋다.

 

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링) 되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며, InnoDB 또한 이러한 방식으로 처리한다. InnoDB 또한 이러한 방식으로 처리하는데, 이러한 이유로 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. 

 

메모리 할당 및 사용 구조

메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.

 

운영체제마다 다르지만 요청된 메모리 공간을 100% 할당해줄 수도 있고,  그 공간 만큼 예약해두고 필요한 반큼 할당해주는 경우도 있다. 그렇다면 두 메모리의 특성에 대해 알아보자

 

글로벌 메모리 영역

일반적으로 클라이언트 Thread의 수와 무관하게 하나의 메모리 공간만 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 Thread 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 Thread에 의해 공유된다.

 

로컬 메모리 영역

MySQL 서버상에 존재하는 클라이언트 Thread가 쿼리를 처리하는데 사용하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬(sort) 버퍼 등이 있다. MySQL 서버에 클라이언트가 접속하면 클라이언트 커넥션으로부터의 요청을 처리하기 위해 Thread를 하나씩 할당하게 되는데, 클라이언트 Thread가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 

 

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

 

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

MySQL의 대표적인 독특한 구조이다. 플러그인은 스토리지엔진, 전문 검색엔진을 위한 검색어 파서 사용자 인증을 위한 Native Authentication과 Caching SHA-2 Authentication 등도 모두 플러그인으로 구현되어 제공된다.

 

MySQL에서 쿼리가 실행되는 과정을 크게 나눈다면 다음과 같이 나올 것이다. 

MySQL 서버에서 MySQL 엔진은 사람 역할을 하고 각 스토리지 엔진은 자동차 역할을 하는데, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것이 사용하게 되는데 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다는 점만 기억하자.

 

MySQL의 서버의 상태 변수 가운데 Handler_로 시작하는 것이 많을 것이다. Handler_로 시작하는 상태 변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수라고 이해하면 된다. MySQL에서 MyISAM이나 InnoDB같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라고 MySQL의 처리 내용은 대부분 동일하며, 단순히 데이터 읽기/쓰기 영역의 처리만 차이가 있을 뿐이다. 실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.

 

그렇다면 MyISAM이나 InnoDB 스토리지 엔진 가운데 뭘 사용하든 별 차이가 없는 것 아닌가, 라고 생각할 수 있는데 여기서 중요한 건 '하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는 지 구분할 줄 알아야 한다.' 는 점이다. 각 단위 작업을 누가 처리하고 MySQL 엔진 영역과 스토리지 엔진 영역의 차이를 설명하는데 목적이 있다.

위 사진은 실제로 MySQL에서 show engines라는 명령어를 입력한 화면이다. Support 칼럼에 표시될 수 있는 값은 4가지 이다. 

 

• YES : MySQL 서버에 해당 스토리지 엔진이 포함되어 있고, 사용 가능으로 활성화된 상태임
• DEFAULT : 'YES' 와 동일한 상태이지만 필수 스토리지 엔진을 의미함(즉, 이 스토리지 엔진이 없으면 MySQL이 시작되지 않을 수도 있음을 의미한다.)
• NO : 현재 MySQL 서버에 포함하지 않았음을 의미함
• DISABLED : 현재 MySQL 서버에는 포함됐지만 파라미터에 의해 비활성화된 상태임

MySQL 서버에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드(컴파일)해야 한다. 하지만 MySQL 서버가 적절히 준비만 돼있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣기만 하면 사용할 수 있다. 또한 플러그인 형태의 스토리지 엔진은 손쉽게 업그레이드할 수 있다. 스토리지 엔진뿐만 아니라 모든 플러그인 내용은 다음과 같이 확인할 수 있다.

다음은 플러그인의 일부이다 MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증이나 전문 검색 파서 또는 쿼리 재작성과 같은 플러그인이 있으며, 비밀번호 검증과 커넥션 제어 등에 관련된 다양한 플러그인이 제공된다.

 

컴포넌트

MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다. MySQL 서버의 플러그인은 다음과 같은 몇 가지 단점이 있는데, 컴포넌트는 이러한 단점들을 보완해서 구현됐다.

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

쿼리 실행 구조

쿼리 구조는 다음과 같다 그러면 세부 사항에 대해 알아보자.

 

쿼리 파서

쿼리파서는 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태로 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.

전처리기

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

옵티마이저

옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다. 이 책에서는 대부분 옵티마이저가 선택하는 내용을 설명할 것이며, 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는가를 알려줄 것이다.

 

실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 실행 엔진이 하는 일을 더 쉽게 이해할 수 있게 간단하게 예를 들어 살펴보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.

 

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청

2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청

3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청

4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청

5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

 

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

 

핸들러(스토리지 엔진)

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

 

쿼리 캐시

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

 

결국 8.0으로 올라오면서 쿼리 캐시는 완전히 제거되고, 실제 도움을 받은 적이 없는 서비스 였기 때문에 제거하는 것이 좋은 선택이라 여겨져 없어졌다.

 

Thread Pool

MySQL 서버 엔터프라이즈 에디션은 Thread pool 기능을 제공하지만 MySQL 커뮤니티 에디션은 지원하지 않는다. 그래서 Percona Server에서 제공하는 Thread pool 기능을 살펴보자.

 

Percona Server의 Thread pool은 플러그인 형태로 작동하게 구현돼 있다. 만약 커뮤니티 에디션에서도 사용하고자 한다면 동일 버전의 Percona Server에서 Thread pool 플러그인 라이브러리(thread_pool.so)를 설치해서 사용하면 된다.(INSTALL PLUGIN 명령어 입력)

 

Thread pool은 내부적으로 사용자의 요청을 처리하는 Thread 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 Thread 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다. 하지만 이것이 성능 향상으로 이어지는 것은 아니다. 스케쥴링 과정에서 CPU가 제한된 갯수만 처리를 하다보니 시간을 제대로 확보하지 못하면 쿼리처리가 더 느려질 수 있다.

 

Percona Server의 Thread pool은 기본적으로 CPU 코어의 개수만큼 Thread 그룹을 생성한다. 만약 그룹의 개수를 조정하고 싶다면 thread_pool_size 시스템 변수를 변경해서 조정할 수 있지만 CPU코어 갯수에 맞추는 것이 좋은데 이유는 MySQL 서버가 처리해야할 요청이 생기면 Thread pool로 처리를 이관하는데, 만약 이미 Thread pool이 처리 중인 작업이 있는 경우에는 thread_pool_oversubscribe 시스템 변수에 설정된 개수만큼 추가로 더 받아들여서 치리한다. 이 값이 커지면 스케줄링해야 할 Thread가 많아져서 Thread pool이 비효율적으로 작동할 수 있다.

 

Percona Server의 Thread pool 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 갖고 있던 잠금이 발리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상시킬 수 있다.

 

트랜잭션 지원 메타데이터

MySQL 8.0 버전에서 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 만들었다. MySQL 서버가 작동하는 데 기본적으로 필요한 테이블을 묶어서 시스템 테이블이라 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다. MySQL 서버 8.0 부터는 이러한 시스템 테이블들을 모두 InnoDB 스토리지 엔진을 사용하도록 만들었으며, 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다. mysql DB는 통째로 mysql.ibd라는 이름의 테이블 스페이스에 저장된다. 그래서 MySQL 서버의 데이터 디렉터리에 존재하는 mysql.ibd라는 파일은 다른 *.ibd 파일과 함께 특별히 주의해야 한다.

 

MySQL 8.0 버전부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서 이제 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다. 기존의 파일 기반 메타데이터를 사용할 때와 같이 작업 진행 중인 상태로 남으면서 문제를 유발하지 않게 개선된 것이다.

 

MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 기반의 딕셔너리에 저장되지만 MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다. MySQL 서버는 InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다. InnoDB 이외의 테이블들에 대해서는 SDI 포맷의 *.sdi 파일이 존재한다. 그리고 SDI는 직렬화를 위한 포맷이므로 InnoDB 테이블들의 구조도 SDI 파일로 변환할 수 있다. ibd2sdi 유틸리티를 이용하면 InnoDB 테이블스페이스에서 스키마 정보를 추출할 수 있다. 

 

 

Reference.

Real MySQL - 백은빈, 이성욱

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jevida&logNo=221252404199 

MySQL/MariaDB Handler 모니터링

https://junghyungil.tistory.com/135

https://futurists.tistory.com/11