RealMySQL - 아키텍처

4.1 MySQL 엔진 아키텍쳐

• MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있다.

• 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용할 수 있다.

MySQL 엔진

• MySQL 엔진은

1. 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기

2. 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

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

--- 일반적인 조인 (모든 DB에서 사용가능)
SELECT *
FROM user, post
WHERE user.user_id = post.user_id;

--- ANSI SQL 조인 형식
SELECT *
FROM user LEFT OUTER JOIN post  // LEFT OUTER JOIN
ON user.user_id = post.user_id;


--- 오라클 조인 형식
SELECT *
FROM user, post
WHERE user.user_id = post.user_id(+) // (+) LEFT OUTER JOIN 
//데이터가 전부 포함되지 않는 쪽에 (+) 표시

스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 점담한다.

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

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

위 예제에서 스토리지 엔진을 지정하면, 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

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

핸들러 API

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

MySQL 스레딩 구조

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

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

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

• 여러개 스레드 중에서 'thread/sql/one_connection' 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드이다.

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

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

• 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재

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

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

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

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

• MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만 InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

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

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

• MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며

• innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다.

• InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만

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

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

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

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

메모리 할당 및 사용 구조

• MySQL에서 사용되는 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

• MySQL의 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당 받는다고 생각해도 된다.

글로벌 메모리 영역

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

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

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

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

로컬 메모리 영역

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

• 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데

• 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고 한다.

• 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고 표현한다.

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

• 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 점이다.

• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼나 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있다.

• 대표적인 로컬 메모리 영역

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

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

• MySQL은 플러그인 형태로 다양한 스토리지 엔진을 제공한다.

• 스토리지 엔진 뿐만 아니라, 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 제공되고, 직접 개발할 수 있다. (쿼리 재작성, 비밀번호 검증, 커넥션 제어 등에 관련된 다양한 플러그인 제공)

• MySQL에서 쿼리가 실행되는 과정은

• 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 '데이터 읽기/쓰기' 작업만 스토리지 엔진에 의해 처리된다

• 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위로 처리된다.

• 핸들러란 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러라고 표현한다.

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

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

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

mysql> SHOW ENGINES;
mysql> SHOW PLUGINS;

• 위 명령어로 설치된 엔진과 플러그인을 확인할 수 있다.

컴포넌트

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

• 플러그인은 몇 가지 단점이 있는데 컴포넌트는 이러한 단점들을 보완해서 구현 됨

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

쿼리 실행 구조

쿼리 파서

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

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

전처리기

• 토큰 트리 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.

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

• 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 걸러진다.

옵티마이저

• SQL문을 최적화, DBMS의 두뇌에 해당

실행 엔진

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

• 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 했을 때

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

쿼리 캐시

• MySQL 8.0으로 올라오면서 쿼리 캐시는 기능에서 완전히 제거 됨

• 기존에는 실행 결과를 메모리에 캐시

• 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제 해야 했다.

• 이는 심각한 동시 처리 성능 저하 유발, 많은 버그 발생

• 쿼리 캐시 기능은 데이터 변경은 거의 없고 읽기만 하는 서비스에서는 훌륭한 기능이다.

스레드 풀

• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도

• MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.

• 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만, 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다.

• Percona Server에서 제공하는 플러그인 형태의 스레드 풀을 이용해야 한다.

트랜잭션 지원 메타데이터

• 데이터 딕셔너리(메타데이터) : 데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보

• MySQL 서버는 5.7버전까지 테이블의 구조를 파일 기반으로 관리했다.

• 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 비정상적으로 종료되면 테이블이 깨지는 현상이 있다.

• 8.0 버전부터는 데이터 딕셔너리(메타데이터)를 모두 InnoDB의 테이블에 저장하도록 개선 (완전한 성공 또는 완전한 실패)

• 시스템 테이블(MySQL 서버가 작동하는데 기본적으로 필요한 테이블, 사용자의 인증과 권한에 관련된 테이블) 모두 InnoDB 엔진 사용

• 시스템 테이블과 데이터 딕셔너리 모두 mysql DB에 저장

• mysql DB는 통째로 mysql.ibd라는 이름의 테이블 스페이스에 저장된다.

• 시스템 정보라 해당 db에는 사용자가 접근할 수 없다.

• MyISAM이나 csv 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.