다음 글은 Real MySQL 스터디를 진행하면서 정리한 4.1장 내용입니다. 🙌

💡 Intro

  • 프로젝트를 진행해보니 서비스의 대부분의 병목은 데이터베이스에서 발생한다는 것을 알 수 있었다.
  • DBA가 아니더라도 기본적으로 파생된 쿼리가 어느 과정을 거쳐서 처리되는지, 성능을 좌우하는 시스템 변수들은 어떠한 것들이 있는지 아는 것이 매우 중요하다고 생각한다.
  • MySQL 엔진은 데이터베이스의 뇌의 역할을 한다. 기본적은 서버 구조와 MySQL 엔진과 스토리지 엔진의 차이점 및 담당 부분을 이해해보자.

🌩 MySQL 서버의 구조

머리 역할을 하는 MySQL 엔진과 손발 역할을 하는 스토리지 엔진(InnoDB, MyISAM)이 있다.

스토리지 엔진은 핸들러 API를 만족하면 직접 구현하여 추가해서 사용할 수 있다.


🌩 MySQL 엔진 아키텍쳐

MySQL 전체구조

  1. MySQL 엔진

먼저 MySQL 서버는 대부분의 상용 언어에서 지원할 수 있으며 MySQL 서버의 커넥션 핸들러에서 커넥션을 관리한다. 이렇게 클라이언트로부터의 접속과 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전 처리기, 옵티마이저가 여기에 속한다.

표준 SQL 문법을 지원하기 때문에 표준 문법에 따라서 작성된 쿼리는 다른 DBMS에서 실행될 수 있다.

  1. 스토리지 엔진

실제 데이터를 디스크에 저장하고 데이터를 디스크로부터 읽어오는 부분을 담당한다. 테이블마다 다른 스토리지 엔진을 정의하여 처리할 수 있다. 각 스토리지 엔진은 성능을 향상하기 위해서 키 캐시(MyISAM)나 버퍼 풀(InnoDB)와 같은 기능을 내장한다.

  1. 핸들러 API

각 스토리지 엔진에 읽기 또는 쓰기를 요청하는 것을 핸들러(handler) 요청이라고 하며 그 API를 핸들러API 라고 한다. SHOW GLOBAL STATUS LIKE 'Handler%'; 등을 통해서 핸들러 API의 요청 작업을 확인할 수 있다.


MySQL 스레딩 구조

MySQL 서버는 프로세스가 아닌 스레드 기반으로 작동한다. 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분하여 실행되며 MySQL의 performance_schema 데이터베이스의 threads 테이블로 스레드 현황을 확인할 수 있다.

대부분 포그라운드 스레드가 더 적고 백그라운드 스레드가 많으며 하나의 작업에 대해 여러 스레드가 할당되어 병렬 처리를 할 수 있다. 커뮤니티 에디션이 아닌 MySQL 서버에서는 스레드 풀(thread pool) 모델을 사용할 수도 있다.

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

MySQL 서버에 접속된 클라이언트의 수만큼 존재하며 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 커넥션이 종료되었을 때 해당 스레드가 스레드 캐시(thread cache)로 돌아간다. 이때 스레드 캐시에 있는 스레드의 개수가 설정된 thread_cache_size 보다 크다면 해당 스레드는 캐시로 넣지않고 바로 종료한다.

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

백그라운드 스레드

InnoDB에서는 다음 작업들이 백그라운드 스레드로 처리된다.

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

이 중에서 로그를 기록하는 로그 스레드(Log thread)와 데이터를 디스크에 기록하는 쓰기 스레드(Write Thread)가 가장 중요하다.

MySQL 5.5 부터 읽기 및 쓰기 스레드를 여러개 설정할 수 있으며 innodb_write_io_thread, innodb_read_io_thread 로 설정할 수 있다. 읽기는 주로 클라이언트 스레드에서 처리되지만 쓰기는 백그라운드로 많이 처리하므로 일반적으로 2~4 정도를 설정하는 것이 좋다.

데이터 쓰기 작업은 버퍼링되어 처리될 수 있으므로 일반 DBMS나 InnoDB는 쓰기 데이터가 디스크에 완전히 저장될 때까지 기다리지 않아도 된다. 이후에 일괄 처리할 수 있다. 하지만 MyISAM 같은 경우에는 사용자 스레드가 쓰기작업까지 함께 처리하게 되어 있어 쓰기 버퍼링 기능을 사용할 수 없다.


메모리 할당 및 사용 구조

MySQL의 메모리 구조는 글로벌 메모리 영역과 로컬 메모리 영역으로 나뉘어지는데 운영체제에 따라서 글로벌 메모리 영역은 요청 메모리 공간을 100% 할당 받거나 일부 예약하고 필요할 때 할당받거나 한다. 메모리 영역의 크기를 측정하는 것은 어렵고 최대 MySQL 시스템 변수 만큼 운영체제로부터 메모리를 할당받는다.

글로벌 메모리 영역과 로컬 메모리 영역의 차이는 MySQL 서버 내의 스레드가 공유하는지 아닌지에 따라서 구분이 된다.

글로벌 메모리 영역

클라이언트 스레드의 수와 상관없이 주로 1개만 존재한다. 여러개가 존재할 수도 있지만 그래도 모든 스레드가 공유한다.

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

로컬 메모리 영역

세션 메모리 영역이며 클라이언트 스레드가 쿼리를 처리할 때 사용하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬 버퍼등이 있다. 커넥션 마다 할당되는 클라이언트 스레드가 할당받는 메모리 영역이기 때문에 클라이언트 메모리 영역이라고도 하며 커넥션을 일반적으로 세션이라고도 부르기 때문에 세션 메모리 영역이라고도 한다.

로컬 메모리는 스레드 별로 독립적으로 할당되어 절대 공유되지 않는다. 또한 필요할 때만 공간을 할당하고 필요하지 않으면 MySQL은 공간을 전혀 할당하지 않을 수도 있다.

로컬 메모리 영역 중 커넥션 버퍼나 결과 버퍼는 커넥션의 생명주기에 따라서 계속 할당되어 있고 소트 버퍼나 조인 버퍼는 쿼리를 실행하는 순간에만 할당하고 바로 해제한다.

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

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

플러그인 모델은 MySQL의 독특한 특징이다. 스토리지 엔진은 플러그인 해서 사용할 수 있다. (이외에도 검색어 파서, 사용자 인증 기능, 비밀번호 검증, 쿼리 재작성 등을 플러그인으로 구현되어 제공된다) 기본적으로 제공되는 스토리지 엔진에 부가기능을 추가로 제공하여 직접 스토리지 엔진을 개발할 수도 있다.

대부분의 작업은 MySQL 엔진에서 처리되고 스토리지 엔진은 데이터 읽기/쓰기 작업만 처리한다. 스토리지 엔진의 데이터 읽기/쓰기 작업은 1건의 레코드 단위로 처리된다. 스토리지 엔진을 조정하기 위해서 핸들러라는 것이 사용되며 자주 등장하는 개념이다.

MySQL에서 핸들러는 개념적인 내용이다. MySQL엔진이 스토리지 엔진에서 데이터를 읽어오거나 저장하도록 명령하려면 핸들러를 통해서 할 수 있다. 시스템 변수 중 'Handler_' 로 시작하는 것들은 MySQL엔진이 스토리지 엔진에게 보낸 명령의 횟수와 같은 정보들이다. 스토리지 엔진에서는 철처히 데이터 읽기/쓰기만 실행되고 GROUP BY, ORDER BY 와 같은 복잡한 처리는 쿼리 실행기에서 처리된다.

데이터 읽기/쓰기 처리는 스토리지 엔진에 따라서 그 방식이 매우 달라질 수 있다. 또한 하나의 쿼리 작업은 여러 하위 작업으로 나뉠 수 있으며 어떤 작업이 MySQL 엔진에서 실행되는지 스토리지 엔진에서 실행되는지 구분할 필요가 있다.


컴포넌트

MySQL 5.5까지는 플러그인 아키텍처 였는데 이것을 대체하기 위해서 컴포넌트 아키텍처를 지원할 수 있다. 이것은 플러그인 아키텍쳐의 단점들을 보완한다.

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

컴포넌트를 사용하려면 INSTALL COMPONENT 를 통해서 컴포넌트를 설치하고 관련 새로운 시스템 변수를 설치하면 된다.


쿼리 실행 구조

다음 순서에 따라서 쿼리를 실행하고 결과를 가져와 요청자에게 응답한다.

쿼리 파서 → 전처리기 → 옵티마이저(쿼리 변환, 비용 최적화, 실행 계획 수립) → 쿼리 실행기

  1. 쿼리 파서

사용자로부터 들어온 쿼리 문장을 토큰(MYSQL이 인식하는 최 단위의 어휘)으로 분리하여 트리 형태의 구조로 만든다. 문법 오류가 발견되는 지점이다.

  1. 전처리기

파서 트리를 기반으로 쿼리 문장 구조적 문제를 확인한다. 테이블 이름, 칼럼 이름, 내장 함수 등등을 매핑하여 존재 여부와 접근 권한 등을 확인한다.

  1. 옵티마이저

사용자 요청으로 들어온 쿼리 문장을 가장 저렴하게 처리할 수 있는 방법을 찾는 두뇌 역할을 한다. 옵티마이저가 어떻게 하면 더 나은 선택을 할 수 있도록 할지 유도하는 것이 매우 중요하며 전체적인 성능에 영향을 끼치는 부분이다.

  1. 실행 엔진

실행 엔진은 손과 발에 비유한다. 다음 순서를 다라서 실행 엔진의 일을 이해해보자.

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

옵티마이저에 의해 만들어진 계획대로 핸들러에게 요청해서 받은 결과를 다른 핸들러에게 요청하여 연결하는 역할을 한다.

  1. 핸들러(스토리지 엔진)

실행 엔진의 요청에 따라서 데이터를 디스크로 저장하고 디스크로 읽어온다. 핸들러는 스토리지 엔진을 의미한다.


쿼리 캐시

빠른 응답을 필요로 하는 웹 기반 프로그램에서 중요하다. SQL 실행 결과를 메모리에 캐시하고 동일한 SQL쿼리가 실행되면 테이블을 읽지 않고 동일한 결과를 즉시 반환한다.

하지만 테이블의 데이터가 변경되면 그 결과가 바뀌므로 관련 캐시는 모두 삭제해야 하는데 이것이 동시 처리 성능 저하를 유발한다. 또한 많은 버그의 원인이 되기도 하였다.

따라서 MySQL8.0부터는 쿼리 캐시가 완전히 제거되었다.


스레드 풀

커뮤니티 에디션에서는 지원하지 않지만 엔터프라이즈 에디션에서는 스레드 풀(Thread pool) 기능을 제공한다. 이 책에서는 Percona Server 스레드 풀을 기반으로 설명한다.

스레드 풀의 목적은 사용자의 요청마다 생성되는 스레드 개수를 줄여 동시 요청수가 많아도 제한된 개수의 스레드 처리만 집중할 수 있도록 하는 것이다. 매우 성능이 향상될 것 같지만 실제로 눈에 띄는 성능 향상을 보여주는 경우는 많이 없다. 제한된 수의 스레드를 CPU가 잘 처리할 수 있도록 하는 기능인데 스케줄링 과정에서 CPU 자원이 잘 확보되지 못하면 쿼리 처리가 더 느려진다. 이것이 잘 처리되도록 유도한다면 CPU 프로세서 친화도도 높이고 운영체제 입장에서 컨텍스트 스위치도 줄여서 오버헤드를 줄일 수 있다.

Percona Server와 같은 경우 기본적으로 CPU 코어 개수만큼 스레드를 생성하여 사용한다. (그래야 CPU 프로세서 친화도를 높일 수 있다) 요청이 들어왔을 때 스레드 풀이 처리중인 작업이 있다면 thread_pool_oversubscribe 변수 만큼 추가로 스레드를 받아드려서 처리한다. 만약에 이 값이 너무 크면 스레드가 많아지고 스케줄링을 해야해서 스레드 풀이 비효율적으로 작동할 수 있다.

만약에 모든 스레드가 일을 처리하고 있다면 새로운 worker thread를 추가할지 기다릴지 결정할 수 있다. 이때는 thread_pool_stall_limit 만큼 기다렸다가 그래도 작업이 끝나지 않으면 새로운 스레드를 추가하여 작업을 처리한다. 하지만 여전히 thread_pool_max_threads 를 넘을수는 없다.

Perconam Server는 선순위 큐와 후순위 큐를 사용하여 먼저 처리할 수 있는 트랜잭션이나 쿼리를 우선적으로 처리하여 잠금 경합을 낮추고 전체적인 성능을 향상시키도록 하는 기능을 제공한다.


트랜잭션 지원 메타데이터

우선 데이터베이스 서버에서 테이블 구조 정보나 스토어드 프로그램 등의 정보를 데이터 딕셔너리 혹은 메타데이터라고 한다. 이전에는 이것을 파일 기반의 메타데이터로 저장했다. 이 데이터 생성 및 변경 작업을 트랜잭션을 지원하지 않기 때문에 중간에 에러가 나거나 종료가 되면일관되지 않은 상태로 남아있어 문제가 생기게 된다.

따라서 MySQL8.0 부터는 테이블의 구조 정보나 스토어드 프로그램의 코드는 모두 InnoDB의 테이블에 저장하도록 했다. MySQL 서버가 실행하는데 기본적으로 필요한 테이블들을 시스템 테이블이라고 하며 사용자 인증 및 권한 등과 관련된 테이블이 있다. 이런 시스템 테이블과 데이터 딕셔너리 정보를 mysql DB에 저장하고 그것을 mysql.ibd 라는 테이블스페이스에 저장하므로 이것은 각별히 주의하여 관리해야 한다.

이제 트랜잭션 기반의 InnoDB에 저장이 되므로 중간에 실패를 하면 트랜잭션의 원자성 특성에 따라서 완전히 성공하거나 실패한 것은 완전히 정리된다.