Chapter 04.01 MySQL 엔진 아키텍처(architecture)

 

MySQL (서버)에 대한 구분

• 사람의 머리 역할을 하는 MySQL 엔진
  • 커넥션 핸들러, SQL 인터페이스, SQL 파서, SQL 옵티마이저, 캐시 & 버퍼
• 손발 역할 담당하는 스토리지 엔진: 핸들러 API를 만족하면 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용 가능하다.
  • InnoDB, MyISAM, Memory
• MySQL (서버)은 크게 MySQL엔진 + 스토리지 엔진으로 구성된다.

 

 

4.1 MySQL 엔진 아키텍처

• MySQL(서버)는 다른 DBMS에 비해 독특한 구조로 이뤄져서 큰 혜택이 있는 동시에 다른 DBMS에서는 생기지 않는 문제가 발생하기도 한다.

 

4.1.1 MySQL의 전체 구조

출처 - 「Real MySQL 8.0」

 

프로그래밍 언어로부터 MySQL에 접근하는 방법

• C API, JDBC, .NET 의 표준 드라이버 제공
• C/C++, PHP, Java, 펄, Python, 루비, .NET 및 코볼까지 모든 언어에서 MySQL 서버에서 쿼리를 사용 가능

 

4.1.1.1 MySQL 엔진

• 커넥션 핸들러(connection handler): 클라이언트로부터의 접속 및 요청을 처리한다.
• SQL 파서 
• 전처리기 
• 옵티마이저: 쿼리를 최적화해서 실행한다.

 

4.1.1.2 스토리지 엔진

• MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 두뇌와 같은 처리를 한다면, 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 전담한다.
• MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시 사용 가능

 

  Ex) 스토리지 엔진 지정

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

• 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후에 해당 테이블의 읽기, 쓰기 작업이나 변경 작업은 정의되 스토리지 엔진이 처리한다.
• test_table이 InnoDB 스토리지 엔진을 사용하도록 정의한다.

 

4.1.1.3 핸들러 API

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

 

  Ex) 핸들러 API - 얼마나 많이 데이터(레코드) 작업이 있었는지 확인 가능하다.

SHOW GLOBAL STATUS LIKE 'Handler%';

 

 

4.1.2 MySQL 스레딩 구조

• MySQL은 프로세스 기반이 아닌 스레드 기반으로 작동한다.
  • ex) 프로세스 기반 :  Oracle DB
• 포그라운드(Foreground) 스레드, 백그라운드(Background) 스레드로 구분한다.

 

  Ex) MySQL 서버에서 실행중인 스레드의 목록 확인하기

• performances_schema 데이터베이스의 threads 테이블을 통해 확인 가능하다.

SELECT thread_id, name, type, ..
FROM performances_schema.threads;

 

4.1.2.1 포그라운드 스레드 (Foreground Thread, 클라이언트 스레드, 사용자 스레드)

• 클라이언트가 MySQL 서버에 접속하면 MySQL 서버는 클라이언트의 요청을 처리해줄 스레드를 생성해서 그 클라이언트에게 할당한다. 이 스레드는 DBMS의 앞단에서 사용자와 통신하기 때문에 "포그라운드 스레드"라고 한다.
• 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다.
• 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
• 포그라운드 스레드는 MySQL의 데이터 버퍼나 캐시로부터 데이터를 가져오며 버퍼나 캐시에  없는 경우는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 가져와서 작업한다. 
  • MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만,
  • InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
  • 데이터 파일: xp

 

  cf) MyISAM이란?

• MySQL 5.5 버전 이전의 기본 스토리지 엔진이다. 
• 트랜잭션 지원이 없다는 단점이 있다. 
• MySQL 5.5 다음 버전 부터는 참조 무결성 제한 & 동시성 보장을 위해 InnoDB 엔진으로 전환됐다.
• MyISAM 테이블은 디스크에 3개의 파일로 저장된다. 
  • .frm(테이블 정의 파일), .MYD(데이터 파일), .MYI(인덱스 파일) 
  • 각 테이블마다 위 세 개의 파일이 존재한다. 

 

4.1.2.2 백그라운드 스레드

• 인서트 버퍼(Insert Buffer)를 병합
• 로그를 디스크로 기록
• Inno DB 버퍼 풀의 데이터를 디스크에 기록
• 데이터를 버퍼로 읽어오기
• 잠금이나 데드락을 모니터링
  • 쓰기 스레드(버퍼의 데이터를 디스크로 내려씀)와 로그 스레드가 가장 중요한 역할을 한다.
  • 로그 스레드와 쓰기 스레드의 개수를 2개 이상 지정 가능하다.

 

4.1.3  메모리 할당 및 사용 구조

• MySQL에서 사용되는 메모리 영역을 글로벌 메모리 영역과 로컬 메모리 영역으로 구분한다.
  • 글로벌 메모리 영역
  • 로컬 메모리 영역

 

4.1.3.1 글로벌 메모리 영역

• 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.
• 클라이언트 스레드 수와 관련 없이 하나의 메모리 공간만 할당된다.

 

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

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

 

4.1.3.2 로컬 메모리 영역 (세션 메모리 영역, 커넥션 메모리 영역, 클라이언트 메모리 영역)

• 로컬 메모리 영역은 MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역을 말한다. 
• 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하는데 클라이언트 스레드가 사용하는 메모리 공간, 즉 클라이언트 스레드가 사용하는 메모리 공간이므로 =>  "클라이언트 메모리 영역"라고 한다.
• 클라이언트와 MySQL 서버와의 커넥션을 "세션"이라고 하니까 => "세션 메모리 영역"

 

대표적인 로컬 메모리 영역

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

 

로컬 메모리 영역의 특징 

• 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 공유되지 않는다.
• 커넥션 버퍼, 결과 버퍼: 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간
• 소트 버퍼, 조인 버퍼: 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간

 

 

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

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」

 

• MySQL의 독특한 구조 중 대표적인 것이 플러그인 모델이다.
• ex) 검색어 파서(인덱싱할 키워드를 분리해내는 작업), 사용자 인증을 위한 Native Authentication, ... 플러그인으로 구현되어 제공한다.

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」

• MySQL에서 쿼리가 실행되는 과정을 나누면  대부분 MySQL 엔진에서 처리되고 데이터 읽기/쓰기 작업만 스토리지 엔진에서 처리된다. 
  • 데이터 읽기/쓰기 작업은 1건의 레코드 단위로 처리된다. 
  • 핸들러(Handler): MySQL 엔진이 스토리지 엔진을 사용하기 위해 핸들러를 이용한다.  MySQL 엔진이 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 핸들러가 필요하다.
  • "Handler_"로 시작하는 변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미한다.

 

 

MySQL 서버(mysqld)에서 지원되는 스토리지 엔진

• Support 컬럼에 표시되는 값
  • YES: MySQL 서버(mysqls)에 해당 스토리지 엔진이 포함돼있고 사용 가능으로 활성화된 상태
  • DEFAULT: 'YES'와 동일한 상태이지만 필수 스토리지 엔진을 의미한다. 없으면 MySQL이 시작되지 않을 수도 있다. 
  • NO: 현재 MySQL 서버에 포함되지 않았다는 뜻이다. 이 경우에는 MySQL 서버를 다시 빌드해야한다. 
  • DISABLED: 현재 MySQL 서버에는 포함됐으나 파라미터에 의해 비활성화 됐다는 뜻이다.
• plugins
  • "SHOW PLUGINS" 명령을 통해 스토리지 엔진 뿐만 아니라 모든 인증 및 전문 검색용 파서와 같은 플러그인도 확인 가능하다.
  • 인증이나 전문 검색 파서(parser) 또는 쿼리 재작성과 같은 플러그인이 있다.
  • 비밀번호 검증, 커넥션 제어 등에 관련된 플러그인이 있다.

 

 

4.1.5 컴포넌트

• MySQL 8.0부터 MySQL 서버의 플러그인의 단점을 보완하기위해 컴포넌트를 구현한다.
• MySQL 서버의 플러그인의 단점
  • 플러그인은 오직 MySQL 서버와 인터페이스할(?) 수 있고 플러그인끼리는 통신 불가능
  • 플러그인은 MySQL 서버의 변수, 함수를 직접 호출해서 안전하지 않다. (캡슐화 불가능)
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

 

 

4.1.6 쿼리 실행 구조

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」

 

4.1.6.1 쿼리 파서(Query Parser)

• 쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식하는 최소 단위의 어휘나 기호)로 분리해서 트리 형태의 구조로 만드는 작업을 의미한다. (파스 트리, Parse tree)
• ex) 기본 문법 오류 발견

 

4.1.6.2 전처리기

• 파서 과정에서 만들어진 파스 트리(Parse tree)를 기반으로 쿼리 문장에 구조적인 문제를 파악한다.
• 각 토큰을 테이블 이름이나 컬럼명, 내장 함수와 같은 개체를 매핑해서 해당 객체의 존재 여부, 객체의 접근 권한을 확인한다.
• ex) 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰이 걸러진다.

 

4.1.6.3 옵티마이저

• 옵티마이저는 사용자의 요청으로 들어온 쿼리 문장을 어떻게 저렴한 비용으로 빠르게 처리할 것인지를 결정하는 역할을 한다. 
• DBMS의 두뇌 역할
• 옵티마이저의 역할이 중요하고 영향 범위가 넓다.

 

4.1.6.4 실행 엔진

• 옵티마이저 = 두뇌, 실행 엔진 & 핸들러 = 손과 발
• 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
• ex) 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하는 경우
  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청한다.
  3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에 요청
  5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘긴다.

 

4.1.6.5 핸들러(스토리지 엔진)

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

 

4.1.7 복제(Replication)

• 아주 중요한 역할 - 16장에서 볼 예정

 

4.1.8 쿼리 캐시(Query Cache)

• MySQL 8.0 으로 올라오면서 쿼리 캐시 기능은 완전히 제거됐다.  쿼리 캐시 기능은 데이터 변경 없이 읽기만 하는 서비스에서 훌륭한 기능인데 이러한 서비스는 흔치 않기 때문이다.
• MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할 담당
• 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시해서 동일한 쿼리가 실행되면 테이블을 읽지 않고 결과를 바로 반환한다.
  • 하지만 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야한다. => 동시 처리 성능이 저하된다.

 

4.1.9 스레드 풀(Thread Pool)

출처 - https://www.wrapuppro.com/programing/view/jAuG3VNBCbGnQWU

• 스레드 풀이란 작업 처리에 사용되는 스레드의 개수를 정해 놓고 작업 큐에 들어오는 작업들을 하나씩 스레드가 맡아 처리하는 것을 말한다. 작업이 끝난 스레드는 작업 큐에서 새로운 작업을 가져와 처리한다.
• 스레드 풀(Thread Pool)은 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이다. 
  • 단점: 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하면 쿼리 처리가 더 느려질 가능성도 있다.
• MySQL 서버 엔터프라이즈 에디션(유료)은 스레드 풀 기능을 제공하지만 커뮤니티 버전(무료)은 스레드 풀 기능을 지원하지 않는다. 
  • 커뮤니티 버전에서 스레드 풀 기능을 사용하려면?  동일 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리를 커뮤니티 버전에 설치해서 사용 가능하다.
• MySQL 서버 엔터프라이즈 에디션의 스레드 풀 기능은 MySQL 서버 프로그램에 내장되어있다.
• 스레드 그룹의 모든 스레드가 작업 중이면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야한다.
• 응답 시간에 민감한 서비스인 경우에는 "thread_pool_stall_limit"을 적절히 낮춰서 설정한다.
  • thread_pool_stall_limit: 스레드 풀의 타이머 스레드는 스레드 그룹의 상태를 주기적으로 체크해서 변수 thread_pool_stall_limit에 정의된 밀리초만큼 작업 스레드가 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다.

 

 

스레드 풀의 목적

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

 

Percona Server의 스레드 풀

• 기본적으로 CPU 코어의 개수 만큼 스레드 그룹을 생성한다. CPU 코어의 개수에 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋다. 
• 플러그인 형태로 작동된다.
• 선순위 큐와 후순위 큐를 이용해서 특정 트랜잭션이나 쿼리를 우선적으로 처리하는 기능을 사용한다. 
  • 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상할 수 있다. 

아래 그림: Percona Server의 선순위, 후순위 큐로 재배치된 순서,   출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」 위키북스

 

 

4.1.10 트랜잭션 지원 메타데이터

• 메타 데이터(데이터 딕셔너리): 데이터베이스 서버에서 테이블의 구조 정보와 스토어드(stored) 프로그램 등의 정보를 말한다. 
• MySQL 5.7 버전까지는 테이블 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일로 관리헀다. 
  • 이러한 파일 기반의 메타 데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성, 변경 도중에 MySQL 서버가 비정상 종료되면 일관되지 않는 상태로 남는 문제가 있다. 
  • "데이터베이스나 테이블이 깨진다."
• MySQL 8.0 버전 부터는 위와 같이 깨지는 문제 해결 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 바뀌었다. 

 

ISSUES

• 이슈
• 궁금한 점

 

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」 위키북스