4.1 아키텍처 - MySQL 엔진 아키텍처

• MySQL 엔진: 머리 역할
• 스토리지 엔진: 손발 역할
• Ex) InnoDB 스토리지 엔진, MyISAM 스토리지 엔진
• 핸들러 API를 만족할 경우, 누구나 스토리지 엔진을 구현해 MySQL 서버에 추가해 사용 가능

4.1.1 MySQL 전체 구조

4.1.1.1 MySQL 엔진 (머리)

역할

• 요청된 SQL문장을 분석하거나 최적화하는 등의 두뇌에 해당되는 처리 담당

구성

• 커넥션 핸들러: 클라이언트로부터 접속 및 쿼리 요청 처리 담당
• SQL 파서/전처리기
• 옵티마이저: 쿼리 최적화 실행 담당

특징

• MySQL 서버에서 MySQL 엔진은 하나
• MySQL은 표준 SQL(ANSI SQLK) 문법을 지원하여, 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행 가능

4.1.1.2 스토리지 엔진 (손발)

역할

• 실제 데이터 디스크 스토리지에 저장
• 디스크 스토리지부터 데이터를 읽어오는 기능
  특징
• MySQL 서버에서 스토리지 엔진은 여러 개 동시에 사용 가능
• 아래 예제처럼 테이블이 사용할 스토리지 엔진 지정할 경우, 이후 당 테이블의 모든 읽기 작업 or 변경 작업은 정의된 스토리지 엔진이 처리

이렇게 지정했을 때 이점? => 테이블 특성에 맞는 최적화 가능

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

여러 개의 InnoDB를 만들 수 있는지? +> 여러 '종류'의 스토리지 엔진을 동시에 사용할 수 있다는 의미 (InnoDB 자체를 여러 개 만드는 게 X)

예제 처리 흐름
1. test_table에 INSERT, UPDATE, DELETE, SELECT 등의 작업 발생 => InnoDB 스토리지 엔진이 처리 담당
2. 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진) 같은 기능 내장

4.1.1.2 핸들러 API

=> 대충 그러니까 MySQL 엔진에서 API(핸들러 API)로 스토리지 엔진에 요청하는 그림이겠음

• 핸들러(Handler) 요청: MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기 or 읽기를 요청하는 행위
• 핸들러 API: 위의 행위에서 사용되는 API
• InnoDB 스토리지 엔진도 이 핸들러 API를 통해 MySQL 엔진과 데이터를 주고받음
• SHOW GLOBAL STATUS LIKE 'Handler%': 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 알 수 있는 명령어

  데이터 작업 확인 필요성? -> 쿼리 최적화 분석, 성능 튜닝 시 병목 지점 파악, 인덱스 제대로 사용되는지 검증

4.1.2 MySQL 스레딩 구조

스레딩 구조는 어디에 속하는가? -> MySQL 서버 전체를 관리하는 시스템 레벨 구조로, MySQL 엔진과 스토리지 엔진보다 상위 레벨에서 전체 서버 동작 방식을 정의

• MySQL 서버는 스레드 기반으로 작동

  스레드 기반 작동의 이점? -> 멀티 유저 동시 처리가 가능하여, 각 클라이언트 연결마다 독립적인 스레드가 처리해 동시에 여러 쿼리 실행 가능

  • PostgreSQL은 프로세스 기반, Oracle은 하이브리드 방식

• 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분

• 실행 중인 스레드 목록은 performance_schema 데이터베이스의 threds 테이블을 통해 확인 가능

• 확인했을 경우

1. 대부분 백그라운드 스레드
2. 포그라운드 스레드는 소수 -> thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드
3. 백그라운드 스레드 개수는 MySQL 서버의 설정 내용에 따라 가변적
   4.동일한 이름의 스레드가 2개 이상으로 보이는 경우 => MySQL 설정 내용에 의해 여러 스레드가 동일 작업 병렬 처리

참고
책: MySQL이 전통적으로 가지고 있는 스레드 모델 소개
IF MySQL 엔터프라이즈 에디션, Percona MySQL 서버는 스레드 풀(Thred Pool) 모델을 사용할 수 있음
차이**: 포그라운드 스레드와 커넥션 관계

• 기본: 커넥션별로 포그라운드 스레드가 하나씩 생성되어 할당 (1:1)
• 스레드 풀: 하나의 스레드가 여러 개의 커넥션 요청을 담당 (1:N)

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

동작 흐름

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

4.1.2.2 백그라운드 스레드

• MyISAM은 해당 사항 별로 X
  InnoDB의 경우
• 인서트 버터를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드(Log thread)
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드(Write thread)
• 데이터를 버퍼로 읽어 오는 스레드
• 잠금이나 데드락을 모니터링하는 스레드

쓰기 스레드(Write thread)

• MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드 개수를 2개 이상 지정할 수 있게 됨
• innodb_write_io_threads, innodb_read_io_threads 시스템 변수로 스레드 개수 설정
• 읽기 => 보통 클라이언트 스레드에서 처리해서 많이 설정 필요 X
• 쓰기 => 아주 많은 작업을 백그라운드로 처리
  - 일반적인 내장 디스크 사용: 2~4
  • DAS, SAN 같은 스토리지 사용: 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정 (고성능 스토리지 시스템, 프로덕션 환경 의미)

사용자의 요청 처리 도중

• 데이터 쓰기 작업은 지연(버퍼링)되어 처리 가능

• 데이터의 읽기 작업은 절대 지연될 수 없음

  데이터 읽기만 왜 지연 X? -> 사용자가 SELECT를 실행하면 즉시 결과를 받아와야함

  • INSERT/UPDATE는 저장했다고 응답하고 디스크에 나중에 써도 괜찮음

• 일반적인 상용 DBMS(InnoDB): 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능 탑재
  - InnoDB: INSERT, UPDATE, DELETE 쿼리로 데이터가 변경될 경우 => 데이터가 디스크 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됨 (쓰기 작업을 지연시켜서 한번에 처리를 시키니까??)

• MyISAM: 사용자 스레드가 쓰기 작업깢지 함께 처리되도록 처리
  - 일반적인 쿼리는 쓰기 버퍼링 기능 사용 불가

4.1.3 메모리 할당 및 사용 구조

• MySQL에서 사용되는 메모리 구조는 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분
• 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당받음 => OS 종류에 따라 다르지만 요청된 메모리 공간을 100% 할당해 주기도 하고, 그 공간만큼 예약해 놓고 필요한 만큼 쪼개서 주는 경우도 있음
• 복잡하니까 일단 MySQL 시스템 변수로 설정해 둔 만큼 OS로부터 메모리를 할당 받는다고 생각하자
• 영역 구분은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분

4.1.3.1 글로벌 메모리 영역

• 모든 클라이언트가 공유
• 일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당
• 필요에 따라 2개 이상의 메모리 공간을 할당 받을 수 있지만, 클라이언트 스레드 수와 무관
• 생성된 글로벌 영역이 N개라도 모든 스레드에 의해 공유됨

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

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

4.1.3.2 로컬 메모리 영역(세션 메모리 영역)

• 각 쿼리마다 독립적 할당
• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
• 대표적으로 커넥션 버퍼, 정렬(소트) 버퍼 등
• 클라이언트가 MySQL에 접속할 경우 => MySQL 서버에서 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데,
• 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 함
• 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 해서 => 세션 메모리 영역이라고도 함

로컬 메모리 특징
1. 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유 X
- 일반적으로 로컬 메모리 영역 크기는 크게 신경 쓰지 않고 설정
- 재수없으면 MySQL 서버가 메모리 부족으로 멈춰 버릴 수 ㅣ있으므로 주의
2. 각 쿼리의 용도별로 필요할 때만 할당되고, 필요하지 않으면 MySQL이 메모리 공간을 할당조차도 않을 수 있음
- 대표적으로 소트 버퍼, 조인 버퍼
3. 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간 존재(커넥션 버퍼, 결과 버퍼)
4. 쿼리를 실행하는 순간에만 할당하다가 다시 해제하는 공간도 존재(소트 버퍼, 조인 버퍼)

대표적인 로컬 메모리 영역

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

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

• MySQL의 독특한 구조 중 하나가 플러그인 모델
  MySQL이 플러그인으로 제공하는 것

1. 스토리지 엔진
2. 전문 검색 엔진을 위한 검색어 파서: 인덱싱할 키워드 분리하는 작업
   3.Native Authentication, Caching SHA-2 Authentication: 사용자 인증 위함
3. 직접 스토리지 엔진 개발(기본 제공 엔진 말고도 부가적인 기능을 제공하는 스토리지 엔진이 필요한 경우)

MySQL 엔진과 스톨기지 엔진의 처리 영역
MySQL 엔진[SQL 파서 <-> SQL 옵티마이저 <-> SQL 실행기]<->스토리지 엔진[데이터 읽기/쓰기]<->디스크

• 대부분의 작업은 MySQL 엔진이 처리하고, 데이터 읽기/쓰기만 스토리지 엔진이 처리

• 각 처리 영역에서 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위로 처리

  MySQL에서

• MySQL 엔진: 사람 역할

• 스토리지 엔진: 자동차 역할

• 핸들러: MySQL 엔진이 스토리지 엔진을 조정하기 위한 핸들 역할
  => MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령할 때 반드시 핸들러를 통해야 된다는 것만 일단 기억하자

• MyISAM이나 InnoDB처럼 다른 스토리지 엔진을 사용하는 쿼리를 실행해도 MySQL 처리 내용은 대부분 동일, 데이터 쓰기/읽기 영역의 처리만 차이 존재
  => 그러면 둘 중 뭘 써도 상관없는 게 아닌가?

상관 있음! => but, 데이터 쓰기/읽기 처리 방식이 얼마나 달라질 수 있는지는 책을 읽어 이해하자

중요 point

• 하나의 쿼리 작업은 여러 개의 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진과 스토리지 엔진 영역 중 어디에서 처리되는지 구분할 줄 알아야 함

• MySQL 서버에 포함 X 스토리지 엔진 => MySQL 서버를 다시 빌드(컴파일) 필요
• 이미 준비된 상태 => 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣기만 하면 사용 가능

SHOW ENGINS; //MySQL 서버에서 지원하는 스토리지 엔진 확인
SHOW PLUGINS; //다른 플러그인도 확인 가능

4.1.5 컴포넌트

• MySQL 8.0부터 기존의 플러그인 아키텍처 대체제로 컴포넌트 아키텍처 지원
  서버 플러그인의 단점

1. 플러그인은 오직 MySQL 서버와 인터페이스 가능, 플러그인끼리 통신 X
2. 플러그인은 MySQL 서버 변수나 함수를 직접 호출 => 안전하지 X(캡슐화 안 됨)
3. 플러그인은 상호 의존 관계를 설정할 수 없어 초기화가 어려움
   (해당 단점들이 어떤 문제를 느끼게 하는지 예시가 있으면 좋겠음)

4.1.6 쿼리의 실행 구조

4.1.6.1 쿼리 파서

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

• 쿼리 문장의 기본 문법 오류가 이 과정에서 발견되고, 사용자에게 오류 메시지 전달

  왜 트리 형태의 구조로 만드는 걸까? -> SQL의 계층 구조를 표현하기 위해 단계별 처리를 가능하게 함

4.1.6.2 전처리기

• 전처리기: 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적 문제점이 있는지 확인
• 각 토큰을 테이블 이름, 칼럼 이름, 내장 함수 같은 개체를 매핑해 해당 객체의 존재 여부와 객체 의 접근 권한 등을 확인하는 과정 수행
• 실제로 존재 x, 권한상 사용할수 없는 개체의 토큰이 이 단계에서 걸러짐

4.1.6.3 옵티마이저 (DBMS의 두뇌)

• 옵티마이저: 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할
• 책에서 다루는 건 대부분 옵티마이저의 일이며, 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도할지에 대해 알려줄 것

4.1.6.4 실행 엔진

• 옵티마이저가 두뇌라면, 실행 엔진과 핸들러는 손과 발
• 옵티마이너가 회사의 경영진이라면, 실행 엔진은 중간 관리자, 핸들러는 각 업무의 실무자
  실행 엔진이 하는 일: if 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용한다면?

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 where절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 읽어오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈에게 넘김
   실행 엔진 => 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러의 요청의 입력으로 연결하는 역할

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

• MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할 담당
• 핸들러는 결국 스토리지 엔진을 의미
  (얘가 실행하니까??)
• MyISAM 테이블 조작 => 핸들러가 MyISAM 스토리지 엔진이 됨
• InnoDB 테이블 조작 => 핸들러가 InnoDB 스토리지 엔진이 됨

4.1.7 복제

• MySQL 서버에서 복제(Replication)은 매우 중요하기 때문에 16장 복제에서 별도로 다룰 것

4.1.8 쿼리 캐시 (삭제됨)

• 빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할이었으나 실질적으로 버그는 많고 잘 안 써서 8.0에서 제거
  쿼리 캐시 설명
• SQL 실행 결과를 메모리에 캐시 => 동일 SQL 쿼리가 실행되면 즉시 결과를 반환해서 짱 빨랐음
• 근데 테이블의 데이터가 변경되면 => 캐시에 저장된 결과 중 변경된 테이블과 관련된 것을 모두 삭제해서 심각한 동시 처리 성능 저하 유발
• 데이터 변경은 거의 없고 읽기만 하는 서비스에는 Good이지만, 그런 서비스 드물어서 도움이 별로 안 됨

4.1.9 스레드 풀 (엔터프라이즈 Ver)

• MySQL 엔터프라이즈: MySQL 서버 프로그램에 내장
• Percona Server: 플러그인 형태로 작동하게 구현되어 있음
• MySQL 커뮤니티: 동일 버전 Percona Server에서 스레드 풀 플러그인 라이브러리를 MySQL 커뮤니티 에디션 서버에 설치하면 쓸 수 있음

목적

• 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여 동시 처릳되는 요청이 많아질 경우,
  => MySQL 서버의 CPU가 제한된 개수의 스레드 처리만 집중할 수 있게 해서 서버의 자원 소모를 줄이겠다

현실

• 스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보인 적은 드물다
• 실행 중인 스레드들을 CPU가 최대한 잘 처리할 수 있는 수준으로 줄여 빨리 처리하는 기능이기 때문에 => 스케줄링 과정에서 cpu 시간을 제대로 확보하지 못하면 쿼리 처리가 더 느려지는 사태도 발생 가능하므로 주의

  제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면?

• CPU 프로세서의 친화도가 올라감
• OS는 불필요한 컨텍스트 스위치를 줄여 오버헤드 낮출 수 있음

• CPU 친화도: 같은 CPU 코어가 같은 스레드를 계속 처리하면 캐시 효율 올라감
• 컨텍스트 스위치: CPU가 스레드를 전환할 때 발생하는 오버헤드

Percona Server의 스레드 풀

• 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성 (스레드 그룹 개수는 thread_pool_size 시스템 변수를 변경해서 조정 가능)
• 일반적으로는 CPU 코어와 개수 맞추는 게 CPU 프로세서 친화도를 높이는 데 좋음
• MySQL 서버가 처리할 요청이 생기면 => 스레드 풀로 처리 이관
• IF) 스레드 풀이 처리 중인 작업이 있다면 => thread_pool_oversubscribe 시스템 변수에 설정된 개수만큼 추가로 더 받아들여서 처리
  - 이 값이 너무 크면 스케줄링이 처리해야 할 스레드가 많아져서 스레드 풀이 비효율적으로 작동 가능

스레드 그룹의 모든 스레드가 일을 처리 중인 경우

• 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 함
• 스레드 풀의 타이머 스레드가 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 만들어서 스레드 그룹에 추가
• 스레드 풀에 있는 스레드 개수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없음
  => 그러니까 개많이 요청이 들어와도? 꽉 찼으면 thread_pool_stall_limit 시간 동안 기다려야 함
  => 따라서 응답 시간에 아주 민감한 서비스라면 thread_pool_stall_limit 시간을 낮춰서 설정하자
• 하지만 0에 가깝게 설정할 바에 스레드 풀 쓰지 마라

Percona Server의 스레드 풀 플레그인의 추가 기능

• 선순위 큐, 후순위 큐를 이용해 특정 트랜잭션 or 쿼리를 우선적으로 처리할 수 있는 기능도 존재
  (그렇다면 내장 기능 탑재된 엔터프라이즈 Ver는 특정 트랜잭션 or 쿼리를 우선적으로 처리할 수 있는 기능이 없나?)
• 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리하게 되면 => 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제 => 잠금 경합을 낮춰 전체적인 처리 성능 향상 가능

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

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

• 원래 파일 기반 메타데이터로 관리하다가 트랜잭션 X 때문에 일관성 보장 X
  8.0부터 메타데이터를 모조리 InnoDB 테이블에 저장하게 바뀜
  => 트랜잭션을 지원해서 일관성 보장
• MySQL 서버가 비정상적으로 종료된 경우 => 스키마 변경이 완전한 성공 or 완전한 실패로 정리됨
• 시스템 테이블
  - MySQL 서버가 작동하는 데 기본적으로 필요한 테이블들
  • ex) 사용자 인증, 권한 관련 테이블
• 8.0부터 시스템 테이블 모두 InnoDB 스토리지 엔진 사용하게 개선, 시스템 테이블/데이터 딕셔너리 정보 모두 mysqlDB에 저장
• mysqlDB는 통으로 mysql.ibd라는 이름의 테이블스페이스에 저장(조심히 다루자)

MyISAM, CSV 같은 스토리지 엔진의 경우

• InnoDB와 달리 저장 공간 필요
• MySQL 서버는 다른 스토리지 엔진을 사용하는 테이블들을 위해 SDI 파일 사용