배경 🐈

요즘 책 'Real MySQL 8.0'과 'MySQL을 더 빠르게, 성능 최적화 선택과 집중' 이라는 책을 가지고 현업 개발자 2분과 스터디를 진행하고 있습니다. 각자 챕터를 읽어오고 각 주제별 리더가 주제에 맞는 방식으로 스터디를 리드했습니다.
챕터별 핵심 내용 위주로 가볍게 정리하고, 스터디에서 느낀점과 생각했던 부분을 정리하고자 합니다.
이 챕터는 'Real MySQL 8.0'을 읽고 이를 중심으로 작성한 글입니다.

📍 아키텍처

📌 MySQL 엔진 아키텍처

MySQL 서버는 ‘쿼리를 처리하는’ MySQL 엔진과 ‘데이터 읽기/쓰기를 지원’하는 스토리지 엔진으로 구성된다.
MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

• MySQL 스레딩 구조
  MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작하는데, 스레드는 포그라운드 스레드와 백그라운드 스레드로 나뉜다.

  • 포그라운드 스레드 (클라이언트 스레드)
    • 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리
    • MySQL에서는 포그라운드 스레드는 스레드 캐시(Thread Cache)라는 풀에 생성된 스레드를 재활용하는 방식으로 동작한다.
      • 스레드 캐시는 새로운 포그라운드 스레드를 생성하는 비용을 절감하는데 사용된다. 스레드 캐시에는 일반적으로 미리 생성된 포그라운드 스레드가 저장되어 있어 새 쿼리 요청이 들어오면 스레드 캐시에 있는 스레드가 요청 쿼리에 할당되는 방식으로 처리된다.
    • InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
  • 백그라운드 스레드
    • InnoDB는 여러 작업을 백그라운드로 처리
    • 그 중에서도 로그 스레드(로그를 디스크로 기록)와 디스크로 내려쓰는 작업하는 쓰기 스레드가 가장 중요하다.

• 메모리 할당 및 사용 구조

  • 스레드 간 공간 공유 여부에 따라 구분
  • 글로벌 메모리 영역
    • 생성된 글로벌 메모리 영역은 모든 스레드에 의해 공유
    • 대표적인 글로벌 메모리 영역
      • 테이블 캐시, InnDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼
  • 로컬 메모리 영역
    • MySQL 서버상에서 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
    • 스레드 별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.
    • 대표적인 로컬 메모리 영역
      • 정렬 버퍼, 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼

• 쿼리 실행 구조

  • MySQL 엔진에서 쿼리 실행하는 과정
  • 쿼리 파서 → 전처리기 → 옵티마이저 → 쿼리 실행기
  • 쿼리 파서
    • 들어온 쿼리 문장을 토큰으로 분리해 트리 형태 구조로 만들어내는 작업
    • 이 과정에서 기본 문법 오류 발견해 오류 메시지 전달
  • 전처리기
    • 각 토큰을 DB의 같은 객체에 매핑해 해당 객체의 존재 여부와 객체 접근 권한 등을 확인
    • 이 과정에서 존재하지 않거나 접근할 수 없는 개체의 토큰 걸러짐
  • 옵티마이저
    • 쿼리 문장을 제일 저렴한 비용을 가장 빠르게 처리할 수 있는 방법 무엇인지 찾아 선택
  • 실행 엔진
    • 옵티마이저가 정한 계획대로 실행하기 위해 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할 수행
  • 핸들러(스토리지 엔진)
    • MySQL 실행 엔진 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할 담당

• 스레드 풀

  • 제한된 CPU를 효율적으로 사용하기 위해 스레드 수를 제한하는 방식
  • 더 좋은 엔터프라이즈 버전에서만 스레드 풀 기능 제공하는데, 만약 커뮤니티 버전에서도 스레드 풀 기능 사용하려면 동일한 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리를 설치해 사용하자.

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

  • 데이터 딕셔너리(메타 데이터) : 테이블 구조 정보와 스토어드 프로그램 등의 정보
  • 5.7 버전까지는 파일 기반으로 메타 데이터를 저장 및 관리했는데 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블 생성 및 변경 도중 MySQL 서버가 비정상 종료되면 테이블이 깨지는 문제가 발생했다.
  • 8.0 버전부터 InnoDB 스토리지 엔진에선 메타 데이터를 InnoDB 테이블에 저장하는 방식으로 문제 해결했다.

📌 InnoDB 스토리지 엔진 아키텍처

MySQL의 기본 스토리지 엔진이 InnoDB 스토리지 엔진이기 때문에 InnoDB 스토리지 엔진에 대해 알아보고자 한다.

• InnoDB 스토리지 엔진이란?
  • MySQL에서 기본으로 사용하는 스토리지 엔진
  • InnoDB는 스토리지 엔진 중 거의 유일하게 테이블 기반 잠금이 아닌 레코드 기반 잠금이라 높은 동시성 처리가 가능하다.
  • pk 값을 리프 노드로 하는 B-tree 알고리즘을 사용하여 인덱스를 구성하여 더 효율적으로 데이터 저장 및 검색이 가능하다. (covering index 비율 ⬆️)

• MVCC (Multi Version Concurrency Control)

  • 쓰기 트랜잭션 실행하는 중에 읽기 트랜잭션이 가능하게 하는 기술 (잠금을 사용하지 않는 일관된 읽기 가능)로 undo log(언두 로그)를 이용해 기능 구현이 가능하다.
  • 만약 쓰기 트랜잭션이 실행되어 출신 지역을 '경기' 에서 '서울'로 바꾸는 쓰기 트랜잭션이 실행된다면 이전 데이터인 '경기'를 언두 로그에 저장하고 읽기 트랜잭션이 이 데이터를 사용하는 방식으로 동작한다.
  • 트랜잭션이 길어지면 예전 데이터가 언두 로그에서 오랫동안 관리되어야 해서 언두 영역이 저장되는 시스템 테이블스페이스 공간이 많이 늘어나는 상황이 발생할 수 있어서 트랜잭션을 적절한 크기로 잘라주어야 한다.

• InnoDB 버퍼 풀

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간으로 이 공간에 적절한 데이터를 배치하여 효율적으로 관리하는 것이 중요하다. 버퍼 풀에 있던 데이터를 비우는 방식은 크게 2가지가 있는데, 플러시 리스트 플러시와 LRU 리스트 플러시가 있다.

  • 플러시 리스트 플러시 : 리두 로그 공간 비우기 위해 더티 페이지를 디스크로 동기화

  • LRU 리스 플러시 : 프리 페이지 늘리기 위해 LRU 낮은 페이지 디스크로 이동

• 리두 로그(redo log)와 언두 로그(undo log)

  • 리두 로그
    • 리두 로그는 쓰기 트랜잭션 도중에 발생하는 비정상 종료 상황에서 이전에 커밋된 변경 사항을 복구하기 위해 사용된다.
    • 리두 로그는 버퍼 풀 공간을 사용하기 때문에 리두 로그 파일의 크기를 신중하게 결정해야 한다.
  • 언두 로그
    • 언두 로그는 위의 MVCC에서의 설명처럼 isolation level에 맞게 적절한 데이터를 반환하기 위한 데이터 백업 용도로 사용된다.
    • 언두 로그의 경우, 언두 테이블스페이스라는 공간에 저장된다.

• 어댑티브 해시 인덱스

  • secondary index 처럼 사용자가 수동으로 생성하는 인덱스가 아니라 innoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스

📌 MySQL 로그 파일

• 에러 로그 파일

  • MySQL 실행 도중 발생하는 에러나 경고 메시지가 출력되는 로그 파일

• 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)

  • MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때 사용

• 슬로우 쿼리 로그

  • 쿼리 중에서 실행 시간이 느린 슬로운 쿼리를 확인하여 쿼리 최적화를 할 수 있다.

느낀점 💡

관계형 데이터베이스 중에서도 대표적으로 사용되는 RDBMS인 MySQL이 SQL 문을 어떻게 처리하고 데이터를 어떻게 관리하는지는 아는 것은 매우 중요한 일이라 생각합니다. 그래서 여러번 반복해서 전체적인 틀을 익히는 시간을 가졌습니다.

추가로 궁금한 부분 🧐

• 스레드 풀이 없을 때 어떻게 동작하는가?
• 언두 로그와 리두 로그가 메모리에서 디스크로 이동하는 시점은 언제인가?