MySQL의 전체 구조

MySQL 전체 구조 MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.
사람으로 비유하면 MySQL 엔진은 머리 역할을 담담하고, 스토리지 엔진은 손과 발의 역할을 담당한다.

MySQL 엔진

MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.

커넥션 핸들러: 클라이언트 요청에 따라 새로운 연결을 생성하고 관리
SQL 파서 및 전처리기: SQL 쿼리를 최적화 및 실행하기 전에 구문 분석 및 전처리를 담당
옵티마이저: 쿼리의 최적화

MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.

스토리지 엔진

스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할 수행한다.

MySQL 서버에서 MySQL엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

1
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

위처럼 테이블이 사용할 스토리지 엔진을 지정하면 해당 테이블의 모든 읽기 작업과 변경 작업은 정의된 스토리지 엔진이 처리한다.

각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM), 버퍼풀(InnoDB) 같은 기능을 내장하고 있다.

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러 요청이라고 하며, 사용되는 API를 핸들러 API라고 한다.

InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받는다.

핸들러 API를 통해 발생한 작업은 아래 쿼리로 확인 가능하다.

1
SHOW GLOBAL STATUS LIKE 'Handler%';

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 동작한다.

MySQL 서버에서 실행 중인 스레드 목록은 performance_schema 데이터베이스에 threads 테이블을 통해 확인할 수 있다.

1
2
3
4
5
6
7
8
9
SELECT 
    thread_id
    ,name
    ,type
    ,processlist_user
    ,processlist_host
FROM performance_schema.threads
ORDER BY type,thread_id
;

백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다. 동일한 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우이다.

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

포그라운드 스레드는 클라이언트 연결 요청을 처리하고 데이터베이스 작업을 수행한다. 이러한 스레드는 쿼리 실행 중에 CPU 및 I/O 리소스를 사용하므로, 성능에 중요한 역할을 한다.

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.

클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아간다.

이때 이미 스레드 캐시에 일정 개수 이상의 대기중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.

스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

포그라운드 스레드는 데이터를 MySQL 데이터 버퍼나 캐시로 부터 가져오며, 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

MyISAM: 디스크 쓰기 작업까지 포그라운드 스레드가 처리
InnoDB: 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리

백그라운드 스레드

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

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

모두 중요한 역할을 수행하지만 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread)가 특히 중요하다.

MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며, innodb_write_io_thread, innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다.

InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요는 없지만, 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할때는 2~4 정도, DAS, SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다.

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 있다.

InnoDB: INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.
MyISAM: 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어, 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

MySQL 메모리 할당 구조

글로벌 메모리 영역과 로컬 메모리 영역으로 구분되며, 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.

글로벌 메모리 영역

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

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

등이 대표적인 글로벌 메모리 영역이다.

로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.

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

MySQL 서버에 클라이언트가 접속하면, 클라이언트 커넥션(세션)으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.

일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 오컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우 MySQL 서버가 메모리 부족으로 멈춰 버릴수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다.

커넥션이 열러있는 동안 계속 할당된 상태로 남아있는 경우: 커넥션 버퍼, 결과 버퍼
쿼리를 실행하는 순간에만 할당: 소트 버퍼, 조인 버퍼

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

MySQL의 독특한 구조 중 대표적인 중 하나가 플러그인 모델이다.

스토리지 엔진
검색 엔진을 위한 검색어 파서
사용자의 인증을 위한 Native Authentication, Caching SHA-2 Authentication 등

MySQL은 이미 기본적으로 많은 스토리지 엔진을 가지고 있지만, 필요에 의해 직접 스토리지 엔진을 만드는 것도 가능하다.

MySQL에서 쿼리가 실행되는 과정을 보면 대부분 작업이 MySQL엔진에서 처리되고, 마지막 데이터 읽기, 쓰기 작업만 스토리지 엔진에 의해 처리한다.

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

스토리지 엔진에 따라 데이터 읽기/쓰기 작업 처리 방식이 크게 달라질 수 있다.

하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.

1
2
/* 스토리지 엔진 조회 */
SHOW ENGINES;

서버에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드해야 한다. 준비만 되어있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣기만 하면 사용할 수 있다.

1
2
/* 플러그인 조회 */
SHOW PLUGINS;

컴포넌트

플러그인 아키텍처는 다음과 같은 단점이 있다.

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

이러한 문제를 개선하기 위해 MySQL 8.0 부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.

예를 들면, MySQL 5.7 버전까지는 비밀번호 검증 기능이 플러그인 형태로 제공됐지만 MySQL8.0의 비밀번호 검증 기능은 컴포넌트로 개선됐다.

1
2
3
4
5
/* validate_password 설치 */
INSTALL COMPONENT 'file://component_validate_password';

/* 설치된 컴포넌트 확인 */
SELECT * FROM mysql.component;

쿼리 실행 구조

쿼리 파서

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.

쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.

각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 수행한다.

실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰(컬럼, 내장 함수)은 이 단계에서 걸러진다.

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다.

DBMS의 두뇌에 비유되며, 옵티마이저가 더 나은 선택을 하도록 유도하는 것이 매우 중요하다.

실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.

옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다면 아래 과정을 거칠 수 있다.

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

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

핸들러(스토리지 엔진)

MySQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.

핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우 InnoDB 스토리지 엔진이 된다.

복제

MySQL 서버에서 복제(Replication)는 매우 중요한 역할을 담당하며, 지금까지 MySQL 서버에서 복제는 많은 발전을 거듭해왔다.(16장)

쿼리 캐시

쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했다. 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다.

하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 테이블과 관련된 것들은 모두 삭제(Invalidate)해야 하므로, 심각한 동시 처리 성능 저하를 유발한다. 또한 MySQL 서버가 발전하면서 성능이 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인이 되기도 했다.

다수의 클라이언트가 동시에 같은 쿼리를 실행하는 경우 쿼리 캐시 락(query cache lock)이 발생 가능하다. 이는 쿼리 캐시에 새로운 결과를 저장하거나 기존 결과를 반환하기 위해 필요한 락(lock)으로, 동시 처리가 많은 시스템에서는 쿼리 캐시를 사용하지 않는 것이 더 나은 성능을 보일 수 있다.

MySQL 5.6 이하 버전에서는 쿼리 캐시가 InnoDB 또는 NDB Cluster 스토리지 엔진을 사용하는 경우에만 동작하는데 MyISAM 스토리지 엔진을 사용하는 경우에도 쿼리 캐시를 켜면 쿼리 결과가 무한정 캐시될 수 있는 버그가 있었다. 이러한 버그는 시스템의 부하를 높일 뿐만 아니라, 캐시 메모리의 공간을 차지해 다른 쿼리의 실행에 영향을 미칠 수 있다.

이러한 이유로 MySQL 8.0으로 올라오면서 완전히 제거되고, 관련 시스템 변수도 모두 제거되었다.

스레드 풀

MySQL 서버 엔터프라이즈 에디션은 스레드풀 기능을 제공하지만 커뮤니티 에디션은 지원하지 않는다. 따라서 Percona Server 플러그인에서 제공하는 스레드풀 기능을 살펴본다.

스레드풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 하여 서버의 자원 소모를 줄이는것이 목적이다.

하지만 스레드풀이 실제 서비스에서 눈에띄는 성능 향상을 보여준 경우는 드물다.

실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능으므로 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 더 느려지는 사례도 발생할 수 있다.

제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도하면 CPU의 프로세서 친화도(Processor affinity)도 높히고 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있다.

스레드 그룹 개수

Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하며 일반적으로 CPU 코어의 개수와 맞추는것이 CPU 프로세서 친화도를 높이는 데 좋다.

MySQL 서버가 처리해야할 요청이 생기면 스레드풀로 처리를 이관하는데, 이미 스레드풀이 처리중인 작업이 있는 경우 시스템 변수에 설정된 개수만큼 추가로 더 받아들여서 처리한다. 너무 많으면 스케줄링해야 할 수레드가 많아져 비효율적으로 작동할 수 있다.

타이머 스레드

스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가할지, 기존 작업 스레드가 처리를 완료할 때가지 기다릴지 여부를 판단해야 한다.

주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 시간에 작업을 끝내지 못했다면 새로운 스레드를 생성해 스레드 그룹에 추가한다.

모든 스레드 그룹의 스레드가 작업을 수행중이라면 시스템 변수에 설정된 개수를 넘어설 수 없어 대기해야 한다.

응답 시간이 아주 민감한 서비스라면 시스템 변수를 적절히 낮춰 설정해야하며, 0에 가까운 값으로 설정하는 것은 좋지 않고 이런 경우는 스레드풀을 사용하지 않는 것이 좋을 수 있다.

우선순위 큐

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

트랜잭션 지원 메타데이터

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 하는데, MySQL 서버는 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리 되었다.

이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남게되는 문제가 있었다.

이에따라 8버전 부터는 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선되었다.

4.1 MySQL 엔진 아키텍처

Real MySQL 8.0