[Real MySQL 8.0 북스터디] 04_아키텍처

MySQL 서버 전체 구조

MySQL 서버는 머리 역할을 하는 MySQL 엔진과 손발 역할을 하는 Storage 엔진으로 구성되어 있고 MySQL 엔진은 핸들러 API를 통해 Storage 엔진에 요청을 보냅니다.

MySQL 엔진

요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하며 다음과 같은 구성 요소를 가집니다.

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

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데 이를 핸들러 요청이라고 하고 이때 쓰이는 API가 핸들러 API입니다.

 

핸들러 API를 만족하는 새로운 스토리지 엔진을 구현하면 추가해서 사용이 가능합니다.

 

핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인

SHOW GLOBAL STATUS LIKE ‘Handler%’

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당합니다.

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작합니다.

실행 중인 스레드 목록 확인은 performance_schema 데이터베이스의 threads 테이블을 통해 가능합니다.

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

• 클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 클라이언트 요청을 처리해줄 스레드를 생성해 클라이언트에게 할당해줍니다.
• 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하며 최소한 MySQL 서버에 접속된 클라이언트 수만큼 존재합니다.
• 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 읽어와서 작업을 처리합니다.
  • InnoDB는 데이터 버퍼나 캐시까지만 포그라운드, 버퍼로부터 디스크까지는 백그라운드
  • MyISAM 테이블은 디스크 쓰기 작업까지 모두 포그라운드
• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아갑니다. (스레드 캐시에 일정 개수(thread_cache_size) 이상 이미 존재하면 그냥 종료)

백그라운드(Background) 스레드

• InnoDB는 다음과 같은 작업을 백그라운드 스레드에서 처리합니다.
  • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
  • 로그를 디스크로 기록하는 스레드 = Log Thread
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 = Write Thread
    • 일반적인 내장 디스크를 사용할 때는 2~4, 스토리지를 사용할 때는 충분히 설정
  • 데이터를 버퍼로 읽어오는 스레드
  • 잠금이나 데드락을 모니터링하는 스레드
• 지연 처리
  • InnoDB는 쓰기 작업을 버퍼링해서 일괄 처리하는 기능 제공
  • MyISAM은 사용자 스레드(포그라운드 스레드)가 쓰기 작업까지 함께 처리

메모리 할당 및 사용 구조

• 글로벌 메모리 영역
  • MySQL 서버가 시작되면서 운영체제로부터 할당받습니다.
  • 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됩니다. (필요에 따라 2개 이상 할당되어도 결국 모두 공유하는 형태입니다.)
• 로컬 메모리 영역 (= 세션 메모리 영역)
  • 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다.
  • 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않습니다.
  • 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 할당되지 않을 수 있습니다.
  • 커넥션이 열려 있는 동안 계속 할당 → 커넥션 버퍼, 결과 버퍼
  • 쿼리를 실행하는 순간에만 할당 → 소트 버퍼, 조인 버퍼

플러그인

스토리지 엔진 뿐만 아니라 검색어 파서, 인증 모듈 등도 플러그인으로 구현되어 제공될 수 있습니다.

다음 명령을 통해 제공되는 플러그인을 확인할 수 있습니다.

SHOW PLUGINS;

하지만 다음과 같은 단점이 있습니다.

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

컴포넌트

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

쿼리 실행 구조

• 쿼리 파서
  • 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 수행합니다.
  • 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지가 전달됩니다.
• 전처리기
  • 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인합니다.
  • 각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체 접근 권한 등을 확인합니다.
• 옵티마이저
  • 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정합니다. (=DBMS의 두뇌)
• 쿼리 실행기
  • 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 합니다.
• 데이터 읽기/쓰기

쿼리 캐시

• SQL의 실행 결과를 메모리에 캐시하고 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였습니다.
• 하지만 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제 필요했고 이로 인해 동시 처리 성능 저하 유발하였습니다.
• MySQL 8.0으로 올라오면서 쿼리 캐시 및 관련 시스템 변수는 MySQL 서버의 기능에서 완전히 제거되었습니다.

스레드 풀

• 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적입니다.
• 엔터프라이즈 에디션에서는 기본 기능으로 제공하고 있고 커뮤니티 에디션에서도 사용하고 싶다면 Percona Server에서 제공하는 스레드 풀 기능을 플러그인 라이브러리로 설치해 사용 가능합니다.
• Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성합니다.
• 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공합니다.

트랜잭션 지원 메타데이터

• 메타데이터(= 데이터 딕셔너리): 테이블의 구조 정보와 스토어드 프로그램 등의 정보
• MySQL 5.7 버전까지는
  • 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리하였는데 파일 기반 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되면 일관되지 않는 상태로 남는 문제 발생하였습니다.
• MySQL 8.0 버전부터는
  • 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoD B의 테이블에 저장하도록 개선되었습니다.
  • mysql DB에 MySQL 서버가 작동하는 데 기본적으로 필요한 테이블인 시스템 테이블과 데이터 딕셔너리 정보를 모두 저장합니다.
  • MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보 저장을 위해 SDI(Serialized Dictionary Information) 파일을 사용합니다.

---

InnoDB 스토리지 엔진

MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어납니다.

프라이머리 키에 의한 클러스터링

• InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장(= 프라이머리 키 값의 순서대로 디스크에 저장, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적 주소로 사용)
• 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정
• MyISAM 스토리지 엔진은 클러스터링 키 지원 x

외래 키 지원

• InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 주의 필요
• foreign_key_checks 시스템 변수를 OFF 로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있지만 최종적으로 일관성을 맞추고 다시 활성화 시키는게 중요

MVCC(Multi Version Concurrency Control)

• 멀티 버전 = 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미
• 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
• InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현 → 격리 수준(Isolation)과 연관

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

• InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행
• 격리 수준이 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행
• 가능한 빨리 커밋 or 롤백을 해서 트랜잭션을 완료하는게 좋음

자동 데드락 감지

• 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리
• 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료 → 강제 종료 대상 판단 기준은 트랜잭션의 언두 로그 양 (더 적게 가진 트랜잭션이 롤백 대상)
• innodb_table_locks 시스템 변수를 활성화해서 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지
• innodb_deadlock_detect, innodb_lock_wait_timeout

자동화된 장애 복구

• MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행
• innodb_force_recovery

InnoDB 버퍼 풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
• 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 함
• MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있기 때문에 적절히 작은 값으로 설정해서 조금씩 늘려가는 방식으로 조절
• 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선
• 구조
  • InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장
  • 페이지 크기 조각을 관리하기 위해 크게 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 프리(Free) 리스트라는 3개의 자료 구조를 관리
  • 프리 리스트: 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용
  • LRU 리스트: Old 서브리스트(LRU) + New 서브리스트(MRU)
  • 플러시 리스트: 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(= 더티 페이지)의 변경 시점 기준의 페이지 목록을 관리
  • 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리 성능이 빨라짐 → 데이터 캐싱 기능 향상
  • 쓰기 버퍼링 기능 향상을 하려면 리두 로그

버퍼 풀 플러시

• MySQL 8.0 버전으로 업그레이드되면서 대부분의 서비스에서는 더티 페이지를 디스크에 동기화하는 부분에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않음
• 플러시 리스트 플러시
  • 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 하는데 이때 먼저 더티 페이지가 디스크로 동기화돼야 하고 이를 위해 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행
• LRU 리스트 플러시
  • LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어여야 하는데, 이를 위해 사용
  • LRU 리스트의 끝부분부터 시작해서 최대 innodb_scan_depth 시스템 변수에 설정된 개수만큼의 페이지들을 스캔하여 더티 페이지는 디스크에 동기화하고 클린 페이지는 즉시 프리 리스트로 옮김
• 버퍼 풀 상태 백업 및 복구
  • MySQL 5.6 버전부터는 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.

Double Write Buffer

• 페이지의 일부만 기록되는 현상(= 파셜 페이지, 톤 페이지)을 막기 위해 사용
• 기록이 필요한 더티 페이지들을 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록 → 이후 원래대로 각 더티 페이지를 파일의 적당한 위치에 랜덤 쓰기

언두 로그

• 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업
• 트랜잭션 보장
  • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구
• 격리 수준 보장
  • 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환

체인지 버퍼

• 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시키게 되는데 이때 사용하는 임시 메모리 공간
• 반드시 중복 여부를 확인해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

리두 로그 및 로그 버퍼

• MySQL 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치
• 대부분 데이터 변경 내용을 로그로 먼저 기록

어댑티브 해시 인덱스

• InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
• B-Tree 검색 시간을 줄여주기 위해 도입된 기능
• 도움이 되는 경우
  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우
  • 동등 조건 검색이 많은 경우
  • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
• 도움이 되지 않는 경우
  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

---

MyISAM 스토리지 엔진

키 캐시

• InnoDB의 버퍼 풀과 비슷한 역할
• 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할

운영체제의 캐시 및 버퍼

데이터 파일과 프라이머리 키(인덱스) 구조

• 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용
• 즉, 프라이머리 키 값과 무관하게 INSERT 되는 순서대로 데이터 파일에 저장되고 모두 ROWID라는 물리적 주소값을 가짐

---

MySQL 로그 파일

에러 로그 파일

• MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일
• 주요 메시지
  • MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
  • 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
  • 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
  • 비정상적으로 종료된 커넥션 메시지
  • InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
  • MySQL 종료 메시지

제너럴 쿼리 로그 파일

• MySQL 서버에서 실행되는 쿼리 목록으로 제너럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록
• 쿼리 로그를 파일 또는 테이블에 저장할 수 있음

슬로우 쿼리 로그

• 쿼리 튜닝
  • 서비스가 적용되기 전에 전체적으로 튜닝하는 경우
  • 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝
• 정상적으로 실행이 완료됐고 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록
• 쿼리 로그를 파일 또는 테이블에 저장할 수 있음