RealMySQL 8.0 | 4.1 MySQL 엔진 아키텍처

RealMySQL 8.0 을 읽고 정리했습니다.

4.1.1 전체 구조

MySQL 서버 = 사람의 머리 역할을 담당하는 MySQL 엔진 + 손발 역할을 담당하는 스토리지 엔진

MySQL 엔진

1. 커넥션 핸들러 : 클라이언트로부터 커넥션 및 쿼리 요청을 처리
2. SQL 인터페이스 : DML, DDL, Procedure, View 등 SQL 인터페이스 제공
3. SQL 파서 : SQL 문법 오류 탐지 및 SQL 쿼리 문장을 MySQL이 처리하기 좋은 토큰 단위로 나눠서 트리 형태로 파싱하는 작업 담당
4. SQL 옵티마이저 : 쿼리의 최적화된 실행을 담당
5. 캐시 & 버퍼 : 성능 향상을 위한 보조 저장소 기능 담당

스토리지 엔진

• 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현하여 MySQL을 추가하여 사용할 수 있음
• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분

💡 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있음

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

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

핸들러 API

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

4.1.2 MySQL 스레딩 구조

MySQL은 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드 + 백그라운드 스레드

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

• MySQL 서버에 접속된 클라이언트의 수만큼 존재
• 주로 클라이언트가 요청하는 쿼리 문장을 처리(고객 창구 역할)
• 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아간다.
  • 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.
  • 스레드의 개수를 일정하게 유지하게 만들어주는 파라미터 = thread_cache_size

백그라운드 스레드

• Insert buffer 를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어들이는 스레드
• 그리고 기타 여러 가지 잠금이나 데드락을 모니터링 하는 스레드
• 모든 백그라운드 스레드를 관리하는 메인 스레드

가장 중요한 것은 **로그 스레드(Log thread)**와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 쓰레드

읽기 / 쓰기 쓰레드의 개수를 지정하는 파라미터 = innodb_write_io_threads / nodb_read_io_threads

4.1.3 메모리 할당 및 사용구조

글로벌 메모리 영역

• MySQL 서버가 시작되면서 운영체제로부터 할당된다.
• 하나의 메모리 공간만 할당된다. 모든 스레드에 의해 공유된다.

로컬(세션) 메모리 영역

• MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역, 서버와의 커넥션을 세션 이라고 하기 때문에 세션 메모리 영역 이라고도 한다.
• 각 클라이언트 스레드 별로 독립적으로 할당되며 절대 공유되지 않는다.

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

MySQL의 독특한 구조 중 대표적인 것이 바로 플러그인 모델이다!

• 사용자 인증 방식, 검색 엔진, 스토리지 엔진 등을 플러그인 형태로 개발해서 사용할 수 있다.
• 핸들러 API MySQL 엔진의 쿼리 실행기는 스토리지 엔진이 제공하는 API를 이용하여 스토리지 엔진에게 CRUD 작업을 위임한다. → 이 때, 스토리지 엔진이 제공하는 API = 핸들러 API
• 플러그인 모델 핸들러 API 규칙을 만족하도록 구현하면 누구든지 스토리지 엔진을 만들어서 MySQL 서버에 추가해서 사용할 수 있다.
• 대부분의 작업은 엔진에서 처리가 되며, 마지막인 “읽기/쓰기” 만이 스토리지 엔진에서 처리하게 된다.
  • GROUP BY, ORDER BY 등 복잡한 쿼리도 MySQL 엔진에서 처리하므로 실제로 데이터 읽기/쓰기 기능은 작아보이겠지만, 처리 방식이 얼마나 많이 달라질 수 있는지 알 수 있게 될것이다.
• 쿼리 작업은 여러 하위 작업으로 나뉘는데, 이 작업에서 MySQL 엔진 영역과 스토리지 엔진 영역의 차이를 아는 것이 중요하다.

4.1.5 컴포넌트

• MySQL 8.0 에 기존 플러그인 아키텍처를 대체하기 위함
• 플러그인의 단점을 보완해서 구현됨
• INSTALL COMPONENT  를 통해서 컴포넌트를 설치하고 관련 새로운 시스템 변수를 설치하면 된다.
• MySQL 서버 플러그인의 단점
  1. 오직 MySQL 서버와 인터페이스 할 수 있고, 플러그인끼리 통신할 수 없다.
  2. MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다. (캡슐화 안 함)
  3. 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

4.1.6 쿼리 실행 구조

• 쿼리 파서(Parser) : 쿼리 문장에 대해서 문법(Syntax) 확인을 하고 쿼리 문장을 적절한 단위로 분할하여 파서 트리(parser tree)를 만든다.
• 전처리기(Pre-processor) : 앞에서 만들어진 파서 트리를 보고 구조적으로 문제가 있는지 확인한다. (예를 들면, 존재하지 않는 테이블이나 컬럼 등에 접근한다거나 접근하려고 보니 권한이 없다거나 하는 등)
• 옵티마이저(Optimizer) : 요청받은 쿼리를 어떻게 하면 가장 적은 비용으로 빠르게 → 즉, 최적으로 처리할지 결정한다.
  • 앞으로 개발자가 해야할 일은 이 옵티마이저가 더 나은 결정을 할 수 있도록 쿼리를 작성하는 것 = 실행 계획 생성
• 실행 엔진 : 옵티마이저에 의해 결정된 실행 계획대로 핸들러에게 지시한다.
  • ex) 임시 테이블 만들어 → where 조건대로 레코드 읽어와 → 읽은 레코드를 임시 테이블에 써라 → …
• 핸들러(스토리지 엔진) : MySQL 서버의 가장 밑단에서 실행 엔진이 내린 지시대로 작업을 수행한다.

4.1.7 복제

• 복제(Replication) = 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간 동기화하는 기술
• 데이터베이스의 데이터가 갈수록 대용량화 되어 가는 것을 생각하면 확장성(Scalability)는 DBMS에서 아주 중요한 요소
• MySQL의 복제는 거의 2000년도부터 제공됐기 때문에 타 DBMS의 복제보다 훨씬 이전부터 제공된 기능이며 또한 그만큼 안정적

• 마스터 서버(INSERT/UPDATE 이용, Primary) + 슬레이브 서버(SELECT만 가능, Secondary)
  • 마스터는 반드시 1개, 슬레이브는 1개 이상이지만 서버 하나로 마스터이면서 슬레이브도 수행하도록 설정하는 것도 가능
  • 단지 서버의 역할 모델을 지칭하는 것일뿐!
  마스터 서버
  1. 마스터에 쓰인 데이터를 슬레이브에 “복제"하여 데이터를 동기화한다.
  2. 마스터 서버는 복제를 위해서 MySQL 바이너리 로그를 활성화해서 남겨야 한다.
     1. 바이너리 로그에는 DML(데이터를 조작하는 문장)과 DDL(스키마를 변경하는 문장)이 기록됨
  슬레이브 서버
  1. 슬레이브 서버는 **마스터 서버에 접속할 정보(IP, Port, 계정)**를 가지고 있으면 된다.
  2. 마스터 서버로 동기화 요청을 하고 받아온 내역을 릴레이 로그에 기록한다.
     1. 참고로 바이너리 로그와 릴레이 로그는 내용이 동일하다.
• 🚨 복제시 주의사항
  1. 슬레이브는 하나의 마스터만 설정 가능
  2. 슬레이브는 항상 읽기 전용
  3. 마스터 장비와 슬레이브 장비 사양은 동일한 것으로 사용
  4. 복제가 불필요한 경우 바이너리 로그 중지(바이너리 로그 작성은 비용이 굉장히 큰 작업)
  5. 바이너리 로그와 트랜잭션 격리 수준(isolation level)
     1. 바이너리 로그 파일은 어떤 내용이 기록되느냐에 따라 STATEMENT 포맷 방식과 ROW 포맷 방식
     • STATEMENT 포맷 : 마스터에서 실행되는 쿼리 문장 기록
     • ROW 포맷 : 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록

4.1.8 쿼리 캐시

• 쿼리(SQL)를 캐시하지 않고, 쿼리 결과를 메모리에 캐시한다.
• 쿼리 캐시는 key, value 쌍의 맵(Map) 같은 자료구조로 되어있다.
• MySQL에만 있는 기능이며, 생각보다 효율적이지 않은 경우가 많다.
• 🔽 쿼리 캐시 확인 절차
  1. 요청으로 들어온 문장이 쿼리 캐시에 존재하는가? (공백, 대소문자 등 완벽하게 일치해야)
  2. 사용자의 권한은 충분한가?
  3. 트랜잭션 내에서 실행된 쿼리면, 결과도 같은 가시 범위 내의 트랜잭션에서 만들어진 결과인가?
     1. 가시 범위 : 자신의 트랜잭션 아이디보다 작은 것(자신의 트랜잭션보다 이전에 실행된 트랜잭션)만 확인 가능한 조건
  4. 쿼리에 사용된 기능이 캐시돼도 동일한 결과를 보장할 수 있는가?
  5. → CURRENT_DATE, SYSDATE, RAND 같이 호출 시점에 따라 달라지는 요소가 있는지?, 변수가 결과에 영향을 미치는지?
  6. 캐시 생성 후 해당 데이터에 변경이 있었는지?
  7. 쿼리 결과가 너무 큰 데이터는 아닌지?
  8. 쿼리 캐시를 사용하지 못하게 하는 요소가 있는지?
     1. 캐시할 수 없는 요소
     • 임시 테이블에 대한 쿼리
     • 사용자 변수 사용한 쿼리
     • 컬럼(column)기반 권한 설정
     • LOCK IN SHARE MODE 힌트
     • FOR UPDATE 힌트
     • User Defined Function 사용
     • 독립적인 SELECT 문이 아닌 일부분의 서브 쿼리
     • Stored Procedure, Function, Trigger에서 사용한 쿼리
     • SQL_NO_CACHE 힌트

  🧐 복잡한 쿼리 캐시 확인 절차(이 밖에도 더 있음)가 있기에 캐시 적중률이 떨어질 수 있고, 
  쿼리 결과가 너무 크면 캐시를 비우는데도 오버헤드가 걸리므로 
  전체적으로 보았을 때 과연 효율적인가에 대해서는 사용 환경을 잘 따져봐야 할 필요가 있다…
  

4.1.9 스레드 풀

일정량의 스레드를 미리 생성해놓고, 스레드가 필요할 때 미리 생성된 스레드를 제공하는 방식 → why? 요청마다 스레드를 생성하는 경우 오버헤드가 많이 발생하기 때문에 이를 줄이고자!

• 발생하는 오버헤드
  • 스레드 생성, 제거
  • context switching
  • 스레드 스케줄링
• Enterprise 버전 : 스레드 풀 제공 Community 버전 : 제공 X → Percona Server 사용하면 됨..

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

• 메타데이터 : DB 서버에서 테이블의 구조 정보, 스토어드 프로그램 등의 정보
• 메타데이터들을 InnoDB에 저장