MySQL 아키텍처

MySQL 서버의 종류

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

• MySQL 엔진: 사람의 머리 역할
• 스토리지 엔진: 손발 역할
  • 핸들러 API를 만족하면 누구든지 구현해서 MySQL 서버에 추가해서 사용할 수 있다.
  • MySQL 서버에서 기본으로 제공되는 InnoDB 스토리지 엔진
  • MyISAM 스토리지 엔진

MySQL 전체 구조

MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특하다.

• 이러한 구조 때문에 다른 DBMS에서는 가질 수 없는 혜택을 누릴 수 있다.
• 반대로 다른 DBMS에서 문제되지 않을 것들이 문제되기도 한다.

출처: Real MySQL 8.0

MySQL 구조 설명

• 일반 상용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원
• MySQL 고유의 C API, JDBC, ODBC, .NET의 표준 드라이버 제공
  • 드라이버를 이용해 모든 언어로 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다.
  • 지원 언어: C/C++, PHP, 자바, 펄, 파이썬, 루비, .NET, 코볼 등

MySQL 엔진 아키텍처

MySQL 엔진

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

MySQL의 중심 3 가지

• 커넥션 핸들러
  • 접속 및 쿼리 요청을 처리
• SQL 파서 및 전처리기
• 옵티마이저
  • 쿼리의 최적화된 실행을 위한 옵티마이저

MySQL과 타 DMBS 호환

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

스토리지 엔진

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

스토리지 엔진 지정 방법

다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

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

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

핸들러 API

• 핸들러 요청을 할 때 사용되는 API를 핸들러 API라고 한다.
  • 핸들러(Handler) 요청: MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 말한다.
• InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.

핸들러 명령

다음 명령을 통해 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있다.

SHOW GLOBAL STATUS LIKE 'Handler%';

MySQL 스레딩 구조

요약

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

• 여기서 소개하는 스레드 모델은 MySQL 서버가 전통적으로 가지고 있던 스레드 모델이며, MySQL 커뮤니티 에디션에서 사용되는 모델이다.

실행 중인 스레드 목록

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

• thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드다.
• 백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다.
• MySQL 서버 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우, 동일한 이름의 스레드가 2개 이상씩 보인다.

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

포그라운드(Foreground) 스레드

MySQL에서 포그라운드 스레드는 사용자 스레드와 똑같은 의미로 사용된다.

• 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
  • 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thead cache)로 되돌아간다.
  • 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시킨다.
  • 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.
• 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다.
• 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다.
  • 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어온다.
• MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리한다.
  • 지연된 쓰기가 있지만 일반적인 방법은 아니다.
• InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드(Background) 스레드

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

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

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

쓰기 스레드와 읽기 스레드

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

지연(버퍼링)

• 사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.
• 따라서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있다.
  • InnoDB도 이러한 방식으로 처리하므로 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.
  • MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있으므로, 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

메모리 사용 및 할당 구조

MySQL에서 사용되는 메모리 공간은 크게 다음 영역으로 구분할 수 있다.

• 글로벌 메모리 영역
  • 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.
  • 운영체제 종류에 따라 100% 할당해줄 수도 있고, 필요할 때 조금씩 할당해주는 경우도 있다.
• 로컬 메모리 영역

두 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.

글로벌 메모리 영역

• 일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다.
  • 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있다.
• 모든 스레드에 의해 공유된다.

대표적인 글로벌 메모리 영역

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

로컬 메모리 영역

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

대표적인 로컬 메모리 영역

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

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

MySQL 플러그인 모델

MySQL 서버의 기능을 커스텀하게 확장할 수 있게 플러그인 API가 매뉴얼에 공개되어 있다.

MySQL 서버에서는 다양한 기능을 플러그인 형태로 지원한다.

• InnoDB 스토리지 엔진
• validate_password 컴포넌트
• query_rewrite 플러그인
• Transparent Data Encryption 플러그인

스토리지 엔진의 간략한 개념

• MySQL에서 쿼리가 실행될 때, 거의 대부분의 작업은 MySQL 엔진에서 처리되고 ‘데이터 읽기/쓰기’ 작업만 스토리지 엔진에 의해 처리된다.
• ‘데이터 읽기/쓰기’ 작업은 대부분 1건의 레코드 단위로 처리된다.
• MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러(Handler)라는 것을 사용하게 된다.
  • MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다.

스토리지 엔진과 플러그인

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

플러그인 확인

• 모든 플러그인의 내용은 다음과 같이 확인할 수 있다.

SHOW PLUGINS;

컴포넌트

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

• 플러그인의 단점을 보완해서 구현되었다.
• MySQL 5.7 버전까지는 비밀번호 검증 기능이 플러그인 형태로 제공되었지만, MySQL 8.0의 비밀번호 검증 기능은 컴포넌트로 개선되었다.

플러그인의 단점

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

컴포넌트의 간단 사용법

-- 비밀번호 검증 컴포넌트 설치
INSTALL COMPONENT 'file://component_validate_password';

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

쿼리 실행 구조

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어내는 작업을 의미한다.

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

💡 토큰
MySQL이 인식할 수 있는 최소 단위의 어휘나 기호

전처리기

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

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

옵티마이저

DBMS의 두뇌

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

실행 엔진

옵티마이저가 만든 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결한다.

핸들러 (스토리지 엔진)

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 온다.

핸들러는 결국 스토리지 엔진을 의미한다.

• MyISAM 테이블을 조작하는 경우 핸들러가 MyISAM 스토리지 엔진이 된다.
• InnoDB 테이블을 조작하는 경우 핸들러가 InnoDB 스토리지 엔진이 된다.

쿼리 캐시

MySQL 8.0으로 올라오면서 MySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거됐다.

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

스레드 풀

MySQL 서버 엔터프라이즈 에디션은 스레드 풀(Thread Pool) 기능을 제공하지만 MySQL 커뮤니테 에디션은 지원하지 않는다.

여기서는 MySQL 엔터프라이즈 에디션의 스레드 풀 대신 Percona Server에서 제공하는 스레드 풀 기능을 살펴본다.

스레드 풀

• MySQL 서버 엔터프라이즈 에디션의 스레드 풀 기능은 MySQL 서버 프로그램에 내장되어 있지만, Percona Server의 스레드 풀은 플러그인 상태로 작동하게 구현되어 있다.
• 스레드 풀은 동시 실행 중인 스레드들을 MySQL 서버의 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이다.
  • 서버의 자원 소모를 줄이는 것이 목적
  • 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄인다.
  • CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 한다.
• 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 더 느려질 수 있다.
• 제한된 스레드만으로 CPU가 처리하도록 적절히 유도한다면, 다음과 같은 이점을 갖는다.
  • CPU의 프로세서 친화도(Processor affinity)를 높인다.
  • 운영체제 입장에서는 불필요한 컨텍스트 스위치(Context switch)를 줄여서 오버헤드를 낮춘다.

Percona Server의 스레드 풀

• 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다.
  • 혹은 thread_pool_size 시스템 변수를 변경해서 개수를 조정할 수 있다.
  • 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋다.
• MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관한다.
  • 이미 스레드 풀이 처리 중인 작업이 있는 경우, thread_pool_oversubscribe 시스템 변수(기본값: 3)에 설정된 개수만큼 추가로 더 받아들여서 처리한다.
• 스레드 그룹의 모든 스레드가 일을 처리하고 있다면, 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가힐지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 한다.
• 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.

트랜잭션 지원 메타데이터

메타 데이터란

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.

파일 기반의 메타데이터

MySQL 서버는 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리했다.

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

• 이 같은 현상을 “데이터베이스나 테이블이 깨졌다”라고 표현한다.

InnoDB 스토리지 엔진 사용

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

• 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선했다.
  • 시스템 테이블: MySQL 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서 말한다.
• 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장한다.
  • mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다.
• 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 히면서, 파일 기반의 문제점을 개선했다.

기타 스토리지 엔진

• MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.
• MySQL 서버는 InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다.
  • SDI: 직렬화(Serialized)를 위한 포맷
  • InnoDB 테이블들의 구조도 SDI 파일로 변환할 수 있다.

Reference

참고 서적

📔 Real MySQL 8.0