Real MySQL 책을 읽으며 활용하기 위해 간략히 정리한 내용입니다.
더 자세한 내용을 알고 싶다면 책 또는 구글 검색을 통해 찾아주시면 감사하겠습니다.


아키텍처

MySQL 서버는 머리 역할 담당하는 MySQL 엔진과 손발 역할을 하는 스토리지 엔진으로 구성되어 있다.

스토리지 엔진은 핸들러 API를 만족시 스토리지 엔진을 구현하여 MySQL 서버에 추가해 사용 가능하다.


MySQL 엔진

구조

images_fortice_post_d869f1e2-246c-49cf-9315-a98b2156b17e_image


MySQL 엔진

  • 클라이언트로부터 접속 or 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기 캐시 옵티마이저가 중심이며 표준 SQL 문법을 지원
  • 사람의 역할 (mysql 엔진이 스토리지 엔진을 조정하기 위해 핸들러를 사용)
  • 요약: MySQL 엔진은 요청에 따른 SQL 문장을 분석 및 최적화 담당하는 두뇌 역할

스토리지 엔진

  • 스토리지 엔진을 여러개 동시에 사용 가능하며 실제 데이터를 디스크 스토리지에 저장, 조회등을 담당
    • 실제 CRUD 작업이 발생시 처리 담당
  • 자동차 역할 (mysql 엔진에 의해 조정)
  • MyISAM 스토리지 엔진, InnoDB 스토리지 엔진

InnoDB

  • 높은 신뢰성, 고성능으로 범용 스토리지 엔진
  • 많은 기능을 제공해 상대적 무거움
  • 테이블과 인덱스를 테이블 공간에 저장
  • Row level locking 지원으로 데이터 변경이 많을 때 효율적 (행 단위 잠금)
    • Row level locking
      • row 수준의 잠금 → 하나의 데이터를 읽고, 수정시 다른 행에 대한 접근 허용 여부 잠금
        • Shared Lock
          • select를 위한 read lock
          • read lock의 경우 다른 트랜잭션이 row 접근시 read lock 보장, write lock 접근 불가
        • Exclusive Lock
          • update, delete를 위한 write lock
          • 해당 lock이 동작하면 다른 트랜잭이 row에 조회, 수정등의 lock을 획득하지 못하고 대기

MyISAM

  • 5.5 버전 이전 default 스토리지 엔진
  • InnoDB에 비해 상대적으로 가벼움
  • 테이블과 인덱스를 별도 파일로 저장 및 관리
  • 트랜잭션을 지원하지 않음
  • Table level locking 지원으로 조회가 많은 서비스에 효율적 (테이블 단위 락)
    • Table level locking
      • table 수준의 잠금 → 테이블의 하나 데이터를 수정시 해당 테이블 전체 데이터 수준에 잠금
      • 테이블 수정하려는 다른 세션은 현재 점유한 세션이 종료될때까지 접근 불가 (동시성이 줄어듬)
      • 적은 메모리 사용으로 효율적

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터 쓰기, 읽기시 스토리지 엔진을 통해 쓰기, 읽기를 요청하게 된다. 이때 요청을 핸들러라 부르고 사용되는 API를 합쳐 핸들러 API라 명칭

  • mysql> show global status LIKE 'Handler%';

MySQL Threading

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작하며 포그라운드, 백그라운드 스레드로 구분된다. MySQL은 데이터를 가져올 때 버퍼 or 캐시를 통해 가져오며 없을시 디스크에 데이터 or 인덱스를 통해 가져온다. 이때 스레드가 처리

  • Foreground Thread
    • MySQL 서버에 접속한 클라이언트 수만큼 존재하며 각 사용자 요청 쿼리 처리
    • 사용자 작업 세션 종료시 해당 스레드는 Thread Pool(캐시)로 돌아감
      • 이때 Thread Pool(캐시)가 일정수 이상 대기중이면 그냥 종료시킴
    • MyISAM은 읽고 쓰는 작업 모두 Foreground Thread가 처리
    • InnoDB는 데이터 버퍼 캐시까지만 Foreground Thread가 처리
      • 버퍼에서 디스크로 기록하는 작업은 Background Thread 처리
    • thread_cache_size 시스템 변수로 최대 스레드 갯수 설정 가능
  • Background Thread
    • 스토리지엔진 InnoDB에선 많은 작업이 Background Thread에서 처리
    • 멀티 스레드 방식으로 동작
      • 로그를 디스크로 기록하는 스레드
      • InnoDB 버퍼를 읽어오는 스레드
      • 잠금 or 데드락 모니터링 스레드
      • insert 버퍼를 병합하는 스레드
    • 읽기, 쓰기 스레드 변수 설정 가능
      • innodb_write_io_threads → 내장 디스크 사용시 2~4 적당
      • innodb_read_io_threads → 클라 스레드에서 처리되기에 낮게 설정
    • InnoDB에선 위와 같은 이유로 쿼리로 데이터 변경되는 경우 대기 불필요 (버퍼링 x)

메모리

글로벌 메모리 영역

  • 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당
  • 생성된 글로벌 영역이 N개 → 모든 스레드에 공유
  • 글로벌 메모리 영역
    • 테이블 캐시
    • InnoDB 버퍼 풀
    • InnoDB 어댑티브 해시 인덱스
    • InnoDB 리두 로그 버퍼

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

  • 세션 메모리 영역으로도 표현하며, mysql 서버상 존재하는 클라이언트 스레드 쿼리를 처라하는 메모리 영역
    • 클라이언트 커넥션의 요청을 처리하기 위해 스레드 할당하게 되는데 이때 사용하는 영역
  • 각 스레드별로 독립적 할당(공유 x) → 메모리 영역 설정시 적절하게 생성하여야함 (공유되지 않기에)
  • 로컬 메모리 영역
    • 조인 버퍼 (쿼리 실행 순간 할당 후 해제)
    • 정렬 버퍼 (쿼리 실행 순간 할당 후 해제)
    • 바이너리 로그 캐시
    • 네트워크 버프

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

MySQL의 독특한 구조중 하나로 플러그인으로 사용할 수 있는 것이 스토리지 엔진, 검색어 파서, 사용자 인증이 있다. 다양한 스토리지 엔진을 가질 수 있으며 사용자가 직접 개발하는 것도 가능하다.

SQL Parser → SQL Optimizier → SQL Execution → Data Read/Write → Disk (MySQL 엔진 처리 영역: 스토리지 엔진)

위 처리 영역을 미뤄 보아 스토리지 엔진을 생성한다고 하여도 일부의 기능 범위만 작성 가능 한 것을 알 수 있다.

  • MySQL 8 컴포넌트
    • 기존 플러그인 아키텍처 단점을 대체하기 위해 컴포넌트 아키텍처 지원 (플러그인 단점은 아래)
      • mysql 서버와 인터페이스할 수 있고 플러그인끼리 서로 통신 불가
      • 캡슐화 지원 x (직접 호출 가능)
      • 상호 의존 관계를 설정할 수 없어 초기화의 어려움

쿼리 실행 구조

154495992-9093bdc4-a539-4dc0-9ee1-e6d35a726d60

출처: Real MySQL 8.0 1권


쿼리 파서 (MySQL 엔진)

  • 사용자 요청 쿼리 문장을 토큰으로 분리해 트리 형태 구조로 만들어내는 작업 (문법 오류 체크)

전처리기 (MySQL 엔진)

  • 파서 과정에서 만들어진 트리 형태를 구조적 문제 있는지 확인
  • 토큰을 테이블과 컬럼, 내장 함수등에 매핑해 존재 여부와 권한을 체크
  • 존재하지 않거나 권한이 없음은 이때 체크

옵티마이저 (MySQL 엔진)

  • 사용자의 요청 쿼리 문장을 빠르고 효율적인 비용으로 처리할지 여부를 처리하는 역할
  • 중요한 영역 및 영향 범위 큼

실행 엔진 (MySQL 엔진)

  • 만들어진 수행 flow를 핸들러에게 순차적으로 요청하는 역할

헨들러(스토리지 엔진)

  • MySQL 서버의 가장 밑단에 MySQL 실행 엔진의 요청에 따라 디스크 저장 및 조회 역할
  • 쿼리 실행기
    • group by, order by 는 스토리지 엔진 영역이 아니라 mysql 엔진 처리 영역인 쿼리 실행기에서 실행
  • 쿼리 캐시
    • 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고 동일 SQL 쿼리가 실행시 테이블 조회가 아닌 즉시 결과 반환을 하여 빠른 성능을 보장한다.
    • 단점으로는 데이터 변경시 의존 관계인 데이터를 모두 삭제하는 작업이 발생하여 성능저하 및 버그 발생
      • MySQL 8 버전부터는 제거
  • 스레드 풀
    • 각 새로운 요청에 대해 스레드를 생성했다가 해당 요청이 종료시 테스크를 제거하는 방식이 아닌 풀을 생성해 그 내부적으로 관리하는 것을 말한다. (컨텍스트 스위치에 대한 비용 절감 및 오버헤드 감소)
    • 내부적으로 사용자의 요청을 처리하는 스레드 수량을 줄여 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 수량의 스레드 처리만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 목적이 있다.
    • 스레드 그룹 수량: thread_pool_size 변수 사용 → CPU 코어 갯수와 맞추는게 CPU 친화도 상승
  • 트랜잭션 지원 메타데이터
    • DB 서버에 테이블의 구조 정보, 스토어드 프로그램등의 정보를 데이터 딕셔너리 또는 메타데이터라 부른다.
    • 기존 파일 기반의 메타데이터는 생성, 변경 도중 서버가 비정상적 종료될 때 테이블의 깨짐 현상이 발생했기에 8 버전부터는 innoDB 테이블에 저장하도록 변경됨 (mysql DB에 저장 → mysql.ibd 파일)
    • 트랜잭션 기반의 스토리지엔진에 저장되기 때문에 중간에 비정상적 종료시 성공 or 실패로 분류


InnoDB 스토리지 엔진 아키텍처

img

출처: Real MySQL 8.0 1권


InnoDB 스토리지 엔진

  • 스토리지 엔진중 거의 유일한 레코드 기반 잠금
  • 높은 동시성 처리, 안정적이며 성능 뛰어남
  • PK 클러스터링
    • InnoDB의 모든 테이블은 PK를 기준으로 클러스터링(여러 개체를 하나로 묶는 작업)된다.
    • 즉, 키값의 순서대로 DB에 저장되어 스캔의 성능이 좋다.
  • 외래키 지원
    • 서버 운영의 불편함 때문에 서비스용 DB에서는 잘 사용하지 않는 경우가 종종 발생
      • 외래 관계를 맺으면 양쪽 테이블 모두 인덱스 생성과 변경시 데이터 유무 확인으로 인한 잠금이 발생하고 그로 인해 데드락 발생할 가능성이 크기 때문
      • foreign_key_checks 변수의 값을 off시 외래키 관계 체크 작업 일시적 멈춤
      • 일시적 외래키 off 후 다시 on할때 반드시 상위 레코드 하위 레코드 일관성을 맞춰줘야 한다.
  • MVCC (Multi Version Concurrency Control)
    • 레코드 레벨의 트랜잭션을 지원하는 DBMS 기능
      • 잠금을 사용하지 않는 일관된 읽기를 제공함이 목적
    • InnoDB는 undo log를 통해 기능 구현
    • 멀티 버전은 하나의 레코드에 여러개 버전이 동시 관리의 의미
  • Buffer Pool
    • InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로 데이터 파일이나 인덱스 정보를 저장하기 위해 캐시해두는 메모리 공간을 말한다.
  • Redo Log
    • MySQL DB 장애 발생시 버퍼 풀에 저장되어 있던 데이터 유실을 방지하기 위해 복구에 사용되는 로그
    • 버퍼풀과 밀접한 관계
    • InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리 성능이 빨라진다 (이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이면 성능 효과 없음)
  • Undo Log
    • 실행 취소(before) 레코드의 집합으로 데이터의 입력, 수정등 업데이트로 트랜잭션 실행 후 롤백시 해당 로그를 참조하는 기능을 한다.
    • 테이블 → 데이터 입력 → 커밋 → 업데이트-> 조회
      1. 업데이트 요청시 InnoDB의 버퍼 풀은 요청 set 값으로 업데이트
      2. 기존 데이터는 언두 영역으로 복사
      3. 조회 시 디스크 파일엔 데이터가 업데이트 된지 아닌지 확인
        • 커밋 또는 롤백이 되지 않은 상태에선 시스템 변수에 설정된 격리 수준에 따라 진행된다.
        • read_committed, repeatable_read, serializable인 경우 이전 내용을 보관하고 있는 언두 영역 반환
        • commit시 InnoDB는 버퍼풀의 상태를 영구적으로 변경하고 언두 영역 제거
        • rollback시 언두 영역에서 꺼내 버퍼풀 영역으로 복구 (트랜잭션이 없을때 삭제)
        • 위 과정을 MVCC

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

  • MVCC 기술을 이용해 잠금을 걸지 않고 read 작업을 수행한다.
  • InnoDB에서는 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 작업이 가능하다. (단 격리수준이 serializable이 아닌 경우)
  • 특정 사용자의 요청이 레코드를 변경하고 커밋을 수행하지 않아도 다른 사용자의 select 요청 작업을 방해하지 않음 (이때 조회 작업은 언두 로그에서 가져옴)

자동 데드락 감지

  • InnoDB 스토리지 엔진은 내부적 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리 (데드락 감지 스레드로 주기적 검사 후 교착 상태의 트랜잭션을 강종)
    • 강제 종료의 우선 순위는 언두 로그의 양이 적을 수록 롤백 대상 → 롤백 내용이 적어야 부하 덜 유발
    • 테이블 레벨의 잠금 감지시 innodb_table_locks 시스템 변수 활성화하기
  • 동시 처리 스레드가 많거나 트랜잭션이 가진 잠금의 수량이 많아지면 데드락 감지 스레드가 느려짐
    • 이를 해결하기 위해 innodb_deadlock_detect=off시스템 변수 사용 (데드락 감지 사용 x)
    • 데드락 감지 스레드를 종료하는 것이며 이후 처리로 innodb_lock_wait_timeout 시스템 변수 사용
      • 데드락 감지 스레드 종료 이후에 대한 에러 처리하도록 timeout 설정

자동화된 장애 복구

  • InnoDB에는 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션 또는 디스크에 일부만 기록된 데이터 페이지등에 일련의 복구 작업이 진행되는 기능이 탑재되어 있다.
  • InnoDB 스토리지 엔진은 견고 (데이터 파일 손상 또는 MySQL 서버가 시작되지 못하는 경우 거의 없음)
  • 자동 복구 불가한 손상이 있는 경우 MySQL 서버 설정 파일 변수 설정
    • innodb_force_recovery 설정 값은 1~6까지 존재
      • 각 숫자별로 복구 장애 상황이 다름 (책 참조)
  • 해당 시스템 변수에 따라 시작해도 동작하지 않을시엔 백업을 이용해 DB 서버 재구축
    • 마지막 백업으로 구축하며 바이너리 로그를 사용해 장애 시점까지 데이터 복구

InnoDB 버퍼 풀

  • 버퍼는 임시 저장 공간으로 한번에 전달하는 의미를 가지고 있다.
  • InnoDB 스토리지 엔진의 가장 핵심 (디스크 데이터 파일 또는 인덱스 정보를 메모리에 캐시해두는 공간)
  • 쓰기 작업 지연시켜 일괄로 처리할 수 있는 역할도 병행 (랜덤한 디스크 작업을 한대 모아 처리)
  • 크기 설정은 OS와 클라이언트 스레드가 사용할 메모리를 충분히 고려해 설정
    • 8버전부터는 동적 조절 가능
    • 해당 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀 메모리 크기 조정
      • OS의 메모리 50%정도 시작으로 적절히 올려가며 최적점 찾기
      • innodb_buffer_pool_size 시스템 변수 설정
    • 버퍼 풀의 크기를 감소시키는 작업은 서비스 영향도가 매우 크므로 주의

InnoDB 버퍼 풀 구조

  • InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(시스템 변수에 설정된 값)을 기준으로 조각내 필요할 때마다 데이터 페이지를 읽어 조각을 저장한다.
  • 페이지 조각을 아래 3가지 자료구조로 관리
    • LRU List
    • Flush List
    • Free List

LRU List

  • 디스크로부터 한번 읽어온 페이지를 최대한 오랜 시간동안 InnoDB 버퍼풀의

    메모리에 유지해 디스크 읽기를 최소화 시키는 기능 (캐시와 비슷)

    • Old: MRU 영역
    • New: LRU 영역

Flush List

  • 더티 페이지(디스크로 동기화되지 않은 데이터를 데이터 페이지)의 변경 시점 기준의 페이지 목록 관리

    • 한번 데이터 변경이 가해진 데이터 페이지는 플러시에서 관리되며 특정 시점이 되면 디스크로 기록

      • 데이터 변경시 리두 로그, 버퍼 풀 데이터 페이지 둘 다 반영

      • 리두 로그의 각 엔트리와 데이터 페이지가 연결

      • 체크포인트: MySQL 서버가 시작시 InnoDB 스토리지 엔진이 리두 로그 복구 기준점 만드는 역할

Free List

  • InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
    • 사용자의 쿼리가 새롭게 디스크의 페이지를 읽어와야 하는 경우 사용

버퍼 풀과 리두 로그

클린 페이지

  • 디스크에서 읽은 상태로 전혀 변경되지 않은 페이지

더티 페이지

  • CUD 명령으로 변경된 데이터를 가진 페이지
  • 더티 페이지는 버퍼 풀의 데이터 상태가 동기화되어지지 않았기에 언젠가 디스크로 기록되어야 한다. 데이터 변경이 계속 발생하면 새로운 로그 엔트리에 덮어 쓰이게 되기에 관리 포인트 2가지로 나눈다.
    • 재사용 불가능 공간: 활성 리두 로그
    • 재사용 가능 공간
  • LSN: 로그 파일이 기록되는 숫자 값
  • 특정 리두 로그 엔트리와 관계를 가지고 체크 포인트 발생시 체크 포인트보다 LSN이 낮은 리두 로그 엔트리와 더티 페이지는 모두 디스크 동기화

버퍼 풀 플러시

InnoDB 스토리지 엔진은 더티 페이지들을 성능상 효율적으로 디스크 동기화를 위해 2가지 기능을 백그라운드로 실행

  • 플러시 리스트 플러시
  • LRU 리스트 플러시

플러시 리스트 플러시

  • 리두 로그 공간을 재활용하기 위해 주기적으로 old한 리두 로그 엔트리를 비워준다. 이때 우선순위로 버퍼풀의 더티 페이지가 우선 디스크에 동기화되어야 한다.
    • 플러시 리스트 플러시 함수를 통해 동기화 작업 수행
  • 클리너 스레드: InnoDB 스토리지 엔진에서 더티 페이지를 디스크 동기화 스레드
    • 클리너 스레드의 갯수 조정 가능 innodb_page_clearers

LRU 리스트 플러시

  • LRU 리스트에 사용 빈도가 낮은 데이터 페이지들을 제거 후 새로운 페이지를 읽어올 공간을 만드는 기능
  • 더티페이지는 디스크에 동기화하고 클린 페이지는 즉시 프리 리스트 페이지로 이동

버퍼풀 상태 백업 및 복구

  • InnoDB 서버의 버퍼 풀은 쿼리 성능에 매우 밀접한 관계
  • 디스크의 데이터가 버퍼 풀에 적재되어 있는 상태 → 워밍업
    • 이 워밍업이 잘 된 상태에서는 일반적으로 몇십배의 쿼리 처리 속도 차이를 보인다.
  • LRU리스트에 적재된 데이터 페이지의 메타 정보만 가지고 저장하기 때문에 디렉토리 파일 크기가 작다.
  • 수동으로 InnoDB의 버퍼 풀을 복구하는 작업은 상당히 많은 디스크 읽기 작업이 수행되기에 실행중인 서비스에서 시작하는건 추천하지 않는다.
  • 자동으로 백업된 버퍼 풀 상태 복구 기능 제공
    • innodb_buffer_pool_dump_at_shutdown 변수
    • innodb_buffer_pool_load_at_start_up 변수

버퍼풀 적재 내용 확인

  • infomation_schema db에 innodb_cached_indexes 테이블에 인덱스별 데이터 페이지가 InnoDB 버퍼풀에 적재되어 있는지 확인 가능

Double Write Buffer

InnoDB 스토리지 엔진에 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제 발생시 해당 페이지 내용은 복구할 수 없을 수도 있다. 일부만 기록되는 현상을 partial-page 라고 부르기도 하는데 하드웨어 오작동, 시스템 비정상 종료에 의해 발생한다. (비정상 종료시에만 사용)

이때 이걸 해결하기 위해 Double-Write 기법을 사용하는데 Double Write 버퍼 공간에 변경 내용을 기록한다. 이후 실제 데이터파일에 적용되면 필요가 없어지게 된다.

innodb_double_write 시스템 변수로 제어할 수 있으며 데이터 무결성이 매우 중요한 경우 활성화 권장


체인지 버퍼

RDBMS에 레코드가 등록되거나 수정될때 데이터 파일을 변경하는 작업뿐 아니라 테이블의 인덱스 정보를 업데이트하기도 한다. 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하기에 많은 자원을 소모하게 된다.

이때 변경할 페이지가 버퍼 풀에 있으면 바로 업데이트, 그렇지 않고 디스크 읽어와 업데이트를 해야한다면 임시 공간에 저장 후 바로 사용자에게 결과 반환하는 구조로 성능 향상 → 임시 공간: 체인지 버퍼


어댑티브 해시 인덱스

  • InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동 생성하는 인덱스 (수동 x)
  • B-Tree 검색 시간을 줄여주기 위해 도입된 기능 (B-Tree 검색시간이 왜 오래 걸려?)
  • InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스 생성 후 필요시 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 바로 찾을 수 있다.
  • InnoDB 스토리지 엔진에 유일하게 하나만 존재 (B-Tree 인덱스 고유번호 + B-Tree 인덱스 실제 키값)
  • 성능 향상에 영향이 적은 경우
    • 디스크 읽기 많은 경우
    • 특정 패턴 쿼리 많은 경우 (join, like 검색)
    • 매우 큰 데이터를 가진 테이블을 넓게 읽는 요청
  • 성능 향상에 영향이 큰 경우
    • 디스크 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우 (디스크 읽기 적음)
    • 동등 조건 검색 (== 비교 연산, in 연산등)
    • 쿼리가 데이터중 일부만 집중된 경우


References