InnoDB 스토리지 엔진 아키텍처

공부하는 감자·2024년 2월 25일
mysql
0

MySQL

목록 보기
2/74
post-thumbnail

InnoDB

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

InnoDB 구조

출처: https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

InnoDB 스토리지 엔진의 주요 특징

Primary Key에 의한 클러스터링

  • InnoDB의 모든 테이블은 기본적으로 프라이머리 키(Primary Key)를 기준으로 클러스터링되어 저장된다.
    • = Primary Key 값의 순서대로 디스크에 저장된다.
    • 모든 세컨더리 인덱스(Secondary Index)는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
  • 프라이머리 키가 클러스터링 인덱스이기 때문에, 프라이머리 키를 이용한 레인지 스캔(Range Scan)은 상당히 빨리 처리될 수 있다.
  • 쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높다.
  • InnoDB의 일반적인 테이블의 구조 == 오라클 DBMS의 IOT(Index organized table)과 동일한 구조
💡 **MyISAM 스토리지 엔진에서는**

MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

외래 키 지원

  • InnoDB 스토리지 엔진 레벨에서 외래 키에 대한 지원하는 기능이 있다.
  • InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하다.
  • 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.
    • 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많다.
  • foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
    • OFF 상태로 작업 시, 반드시 일관성을 맞춰준 후 다시 외래 키 체크 기능을 활성화해야 한다.
    • 반드시 현재 작업을 실행하는 세션에서만 멈추게 해야 한다.
-- 외래 키 체크 작업 OFF
SET foreign_key_checks=OFF;
SET SESSION foreign_key_checks=OFF; // 위와 같은 의미

-- // 작업 실행

-- 외래 키 체크 작업 ON
SET foreign_key_checks=OFF;

MVCC (Multi Version Cocurrency Control)

  • 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능
  • MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이다.
    • 언두 로그(Undo log)를 이용해 이 기능을 구현한다.
  • Multi Version: 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다.
    • 예를 들어, UPDATE 시 변경된 컬럼의 기존 값을 언두 로그로 복사해둔다.
    • 관리해야 하는 예전 버전의 데이터는 무한히 많아질 수 있다.
    • 언두 영역의 내용은 필요로 하는 트랜잭션이 더는 없을 때 삭제된다. (커밋된다고 항상 바로 삭제되는 것이 아니다)
  • 하나의 레코드에 대해 여러 버전을 관리하고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.
    • UPDATE 중에 다른 사용자가 조회할 경우, 격리 수준에 따라 버퍼 풀(변경 후, Commit 전)의 데이터를 보여주거나 언두 로그(변경 전 데이터 유지)의 데이터를 보여주거나 한다.

Non-Locking Consistent Read

잠금 없는 일관된 읽기

  • InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
    • 즉, 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.
    • 예를 들어, 특정 사용자의 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.
  • 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.
    • 일관된 읽기를 위해 언두 로그를 계속 유지해야 하기 때문에 MySQL 서버가 느려지거나 문제가 발생할 때가 있다.
    • 따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

자동 데드락 감지

  • InnoDB 스토리지 엔진은 내부적으로 잠금 대기 목록을 그래프(wait-for List) 형태로 관리한다.
  • 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해, 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료한다.
    • 일반적으로 트랜잭션의 언두 로그 양(레코드)이 적은 트랜잭션이 롤백의 대상이 된다.
  • innodb_table_locks 시스템 변수를 활성화하여 테이블 레벨의 잠금까지 감지할 수 있도록 하는 것을 권장한다.
    • InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)은 볼 수가 없어서 데드락 감지가 불확실할 수 있다.
  • 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
    • 이러한 문제를 해결하기 위해 innodb_deadlock_detect 시스템 변수를 제공한다.
    • innodb_deadlock_detect 를 OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 된다.
    • innodb_deadlock_detect 를 OFF할 경우, innodb_lock_wait_timeout 을 기본값인 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장한다.

자동화된 장애 복구

  • MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
  • MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못하는 경우가 발생할 수 있다.
  • InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다.
    • 자동으로 복구할 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료된다.
    • 이 경우 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다.
    • innodb_force_recovery 시스템 변수: MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
  • 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

버퍼 풀의 크기 설정

  • 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 한다.
  • MySQL 5.7 버전부터 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됐다.
  • InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정한 후, 상황을 봐 가면서 증가시키는 방법이 최적이다.
  • InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있다.
    • 버퍼 풀의 크기 변경은 크리티컬한 변경이므로 가능하면 MySQL 서버가 한가한 시점에 진행하도록 한다.
    • 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로, 가능하면 하지 말자.
  • InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리된다.
    • 이는 버퍼 풀의 크기를 줄이거나 늘리기 위한 단위 크기로 사용된다.
  • innodb_buffer_pool_instance 시스템 변수로 버퍼 풀을 여러 개로 분리해서 관리할 수 있다.
    • 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현한다.
    • 버퍼 풀을 여러 개로 쪼개면서, 개별 버퍼 풀 전체를 관리하는 잠금(세마포어) 자체도 경합이 분산되는 효과를 낸다. (내부 잠금 경합을 유발하던 것을 개선)

버퍼 풀의 구조

  • 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어, InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
    • 페이지 크기: innodb_page_size 시스템 변수에 설정되어 있다.
  • 버퍼 풀의 페이지 크기 조각을 관리하기 위해 크게 3개의 자료 구조를 관리한다.
    • LRU (Least Recently Used) 리스트
    • 플러시(Flush) 리스트
    • 프리(Free) 리스트

LRU (Least Recently Used) 리스트

  • 엄밀하게 LRU와 MRU(Most Recently Used) 리스트가 결합된 형태
    • LRU: 거의 사용되지 않는 데이터 페이지들
    • MRU: 자주 사용되는 데이터 페이지들
  • 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는데 목적이 있다.
    • 버퍼 풀 내부에서 데이터 페이지는 최근 접근 여부에 따라 서로 경쟁하며 MRU 또는 LRU로 이동한다.
    • LRU의 끝으로 밀려난 데이터 페이지들을 버퍼 풀에서 제거한다.

플러시(Flush) 리스트

  • 더티 페이지의 변경 시점 기준의 페이지 목록을 관리한다.
    • 더티 페이지(Dirty Page): 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지
    • 클린 페이지(Clean Page): 디스크에서 읽은 상태로 전혀 변경되지 않는 페이지
  • 디스크에서 읽어온 후, 한 번이라도 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록되어야 한다.
  • 데이터가 변경되면 변경 내용을 리두 로그에 기록하고, 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
    • 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.
  • 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다.
    • 그 반대의 경우, InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다.
  • 체크 포인트: MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할

프리(Free) 리스트

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

버퍼 풀과 리두 로그

  • InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다.
  • InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 두 가지 용도가 있다.
    • 데이터 캐시: 버퍼 풀의 메모리 공간을 단순히 늘리는 것으로 향상된다.
    • 쓰기 버퍼링: 이를 향상시키려면 버퍼 풀과 리두 로그의 관계를 먼저 이해해야 한다.

리두 로그

  • InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 이용한다.
    • 데이터 변경이 계속해서 발생하면, 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰인다.
    • 따라서 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 한다.
    • 재사용 불가능한 공간은 활성 리두 로그(Active Redo Log)라고 한다.
  • 리두 로그 파일의 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 된다.
    • 이를 LSN(Log Sequence Number)라고 한다.
  • InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.
    • 활성 리두 로그 공간의 마지막은 계속해서 증가하기 때문에 체크포인트와 무관하다.
  • 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지(checkpoint Age)라고 한다.
    • 체크포인트 에이지 = 활성 리두 로그 공간의 크기

버퍼 풀

  • 버퍼 풀은 클린 페이지와 더티 페이지를 가지고 있다.
    • 더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 한다.
  • 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가진다.
    • 체크포인트 발생 시 체크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 한다.
    • 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화돼야 한다.

Buffer Pool Flush

InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 관련된 시스템 설정을 살펴본다.

디스크 쓰기 폭증 현상

  • MySQL 5.6 버전까지는 InnoDB 스토리지 더티 페이지 플러시 기능이 그다지 부드럽게 처리되지 않았다.
    • 예를 들어, 급작스러운 디스크 기록 폭증으로 MySQL 서버의 사용자 쿼리 처리 성능에 영향을 받음
  • MySQL 5.7 버전을 거쳐, MySQL 8.0 버전으로 업그레이드되면서 대부분의 서비스에서는 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않았다.
  • 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음 2개의 플러시 기능을 백그라운드로 실행한다.
    • 플러시 리스트(Flush_list) 플러시
    • LRU 리스트(LRU_list) 플러시

플러시 리스트 플러시

  • 리두 로그 공간의 재활용을 위해 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
  • 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출한다.
    • 플러시 리스트에서 오래 전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업 수행
    • 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.
  • innodb_page_cleaners 시스템 변수: 클리너 스레드의 개수를 조정
    • 클리너 스레드(Cleaner Thead): 더티 페이지를 디스크로 동기화하는 스레드
    • 하나의 클리너 스레드는 최소 하나의 버퍼 풀 인스턴스를 처리한다.
    • 가능한 innodb_buffer_pool_instances 설정값과 동일한 값으로 설정
  • innodb_max_dirty_pages_pct 시스템 변수: 더티 페이지의 비율 조정
    • InnoDB 버퍼 풀: 클린 페이지 + DML에 의해 변경된 더티 페이지
    • InnoDB 버퍼 풀은 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화할 수 있다.
    • 기본값(전체 버퍼풀이 가진 페이지의 90%)을 유지하는 것을 권장
  • innodb_max_dirty_pages_pct_lwm 시스템 변수: 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록
    • InnoDB 버퍼 풀에 더티 페이지가 많아지면 디스크 쓰기 폭발(Disk IO Burst) 현상이 발생할 가능성이 높아진다.
    • 이러한 디스크 쓰기 폭발 현상을 완화하기 위해 사용한다.
  • innodb_io_capacity 와 innodb_io_capacity_max 시스템 변수: 각 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정하는 값
    • 디스크 읽고 쓰기: InnoDB 스토리지 엔진의 백그라운드 스레드가 수행하는 디스크 작업(대부분 버퍼 풀의 더티 페이지 쓰기)
    • innodb_io_capacity : 설정된 값을 기준으로 더티 페이지 쓰기를 실행. 일반적인 상황에서 디스크가 적절히 처리할 수 있는 수준의 값을 설정한다.
    • innodb_io_capacity_max : 디스크가 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정한다.
  • innodb_adaptive_flushing 시스템 변수: 어댑티브 플러시(Adaptive flush) 기능 활성화(기본값)/비활성화
    • 어댑티브 플러시가 활성화되면 InnoDB 스토리지 엔진은 단순히 버퍼 풀의 더티 페이지 비율이나 innodb_io_capacity 와 innodb_io_capacity_max 설정값에 의존하지 않고 새로운 알고리즘을 사용한다.
    • 어댑티프 플러시 알고리즘: 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다.
  • innodb_adaptive_flushing_lwm 시스템 변수: 전체 리두 로그 공간에서 활성 리두 로그 공간이 몇 %를 넘어서면 어댑티프 플러시 알고리즘이 작동하게 한다. (기본값: 10%)
  • innodb_flush_neighbors 시스템 변수: 더티 페이지를 디스크에 기록할 때, 디스크에서 근접한 더티 페이지를 함께 묶어 디스크로 기록해주는 기능을 활성화할지 결정
    • 요즘은 대부분 솔리드 스테이트 드라이브(SSD)를 사용하기 때문에 기본값인 비활성 모드로 유지

LRU 리스트 플러시

  • LRU 리스트에서 사용 빈도가 낮은 데이터 페이지를 제거하여 새로운 페이지를 읽어올 공간을 만들기 위해 LRU 리스트(LRU_list) 플러시 함수를 사용한다.
  • InnoDB 스토리지 엔진은 LRU 리스트의 끝부분부터 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼 페이지들을 스캔한다.
    • 스캔하면서 더티 페이지는 디스크에 동기화하게 하고, 클린 페이지는 즉시 프리(Free) 리스트로 옮긴다.
    • InnoDB 버퍼 풀 인스턴스별로 최대 innodb_lru_scan_depth 개수만큼 스캔
실직적인 LRU 리스트 스캔 수행 수=(innodb_buffer_pool_instances)∗(innodb_lru_scan_depth)실직적인\ LRU\ 리스트\ 스캔\ 수행\ 수= \\ (innodb\_buffer\_pool\_instances) * (innodb\_lru\_scan\_depth)

버퍼 풀 상태 백업 및 복구

워밍업 (Warming Up)

  • 디스크의 데이터가 버퍼 풀에 적재되어 있는 상태를 말한다.
  • InnoDB 서버의 버퍼 풀은 쿼리 처리 성능과 매우 밀접하게 연결되어 있다.
    • 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있으므로, 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있음
  • 서버를 셧다운했다가 다시 시작하는 경우 쿼리 처리 성능이 평상시보다 떨어지게 된다.
  • MySQL 5.6 버전부터 버퍼 풀 덤프 및 적재 기능이 도입되었다.
    • 서버를 셧다운 하기 전에 버퍼 풀의 상태를 백업하고, 서버를 다시 시작한 후 백업된 상태를 복구한다.
    • 복구 작업은 상당히 많은 디스크 읽기를 필요로 하기 때문에 오래 걸린다.
-- MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;

-- MySQL 서버 재시작 후, 백업된 버퍼 풀의 상태 복구
SET GLOBAL innodb_buffer_pool_load_now=ON;

-- 버퍼 풀 복구 멈추기
SET GLOBAL innodb_buffer_pool_load_abort=ON;
  • 버퍼 풀의 백업과 복구 자동화
    • innodb_buffer_pool_dump_shutdown 과 innodb_buffer_pool_load_at_startup 설정을 MySQL 서버의 설정 파일에 넣어둔다.

버퍼 풀의 적재 내용 확인

  • MySQL 5.6 버전부터 MySQL 서버의 impormaion_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있었다.
    • InnoDB 버퍼 풀이 큰 경우 이 테이블 조회가 상당히 큰 부하를 일으키며 서비스 쿼리가 많이 느려지는 문제가 있었다.
  • MySQL 8.0 버전에서 이러한 문제점을 해결하기 위해 impormaion_schema 데이터베이스에 innodb_cached_indexes 테이블이 새로 추가됐다.
    • 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인 가능

Double Write Buffer

Partial-page (Torn-page)

  • InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.
  • 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial-page) 혹은 톤 페이지(Torn-page)라고 한다.
    • 일부만 기록하는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다.

Double-Write 기법

  • 파셜 페이지와 같은 문제를 막기 위해 이용하는 기법이다.
  1. 실제 데이터 파일에 변경 내용을 기록하기 전에, 플러시할 더티 페이지들을 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.
  2. 그리고 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.
  3. 만약 기록되는 도중 운영체제가 비정상적으로 종료된다면 DoubleWrite 버퍼가 사용된다.
    • InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DouleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.

DoubleWrite 버퍼

  • 데이터의 안전성을 위해 자주 사용된다.
  • 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋다.
    • 만약 데이터베이스 서버의 성능을 위해 InnoDB 리두 로그 동기화 설정( innodb_flush_log_at_trx_commit )을 1이 아닌 값으로 설정했다면 DoubleWrite도 비활성화하는 것이 좋다.

언두 로그

언두 로그(Undo Log)란

  • InnoDB 스토리지 엔진이 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업하는데, 이 백업된 데이터를 언두 로그라고 한다.
  • 트랜잭션 보장
    • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구한다.
    • 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
  • 격리 수준 보장
    • 특정 커넥션에서 데이터를 변경하는 도중에 데이터를 조회하면, 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
  • 언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만 관리 비용도 많이 필요하다.

언두 로그 모니터링

  • 언두 영역은 INSERT, UPDATE, DELETE 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다.

언두 로그의 용도

언두 로그의 데이터는 크게 두 가지 용도로 사용된다.

  1. 트랜잭션의 롤백 대비용
  2. 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 용도

트랜잭션의 롤백

DML을 실행했을 때 트랜잭션이 커밋하지 않아도 실제 데이터 파일(데이터/인덱스 버퍼) 내용은 변경되고, 변경되기 전의 값은 언두 영역에 백업된다.

  • 사용자가 커밋하면 현재 상태가 그대로 유지
  • 사용자가 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.

트랜잭션의 격리 수준

동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지를 결정하는 기준이다.

언두 로그의 양

  • 트랜잭션이 오랜 시간 실행될 때 언두 로그의 양은 급격히 증가할 수 있다.
    • 트랜잭션이 완료되었다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것이 아니다.
    • 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 방치하여, 언두 로그가 누적되어 디스크의 언두 로그 저장 공간이 계속 증가할 수 있다.
    • InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어지게 된다.
  • MySQL 5.5 이전 버전의 MySQL 서버에서는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았다.
    • 따라서 언두 로그의 사용 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하지 않는 한 줄일 수 없다.
    • 백업할 때도 그만큼 더 복사해야하는 문제점이 발생한다.
  • MySQL 5.7과 8.0으로 업그레이드 되면서 언두 로그 공간의 문제점은 완전히 해결됐다.

모니터링

하지만 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장기간 유지되는 것은 성능상 좋지 않기 때문에, MySQL 서버의 언두 로그가 얼마나 증가했는지는 항상 모니터링하는 것이 좋다.

  • MySQL 서버의 언두 로그 건수 확인
-- MySQL 서버의 모든 버전에서 사용 가능한 명령
SHOW ENGINE INNODB STATUS \G
...
-------------------
TRANSACTIONS
-------------------
...

-- MySQL 8.0 버전에서 사용 가능한 명령
SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
  • MySQL 서버에서 실행되는 INSERT, UPDATE, DELETE 문장이 얼마나 많은 데이터를 변경하느냐에 따라 평상시 언두 로그 건수는 상이할 수 있다.
  • 서버별로 안정적인 시점의 언두 로그건수를 확인해 이를 기준으로 언두 로그 급증 여부를 모니터링하는 것이 좋다.

언두 테이블스페이스 관리

언두 테이블스페이스(Undo Tablespace)

  • 언두 로그가 저장되는 공간을 말한다.
  • MySQL 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스에 저장되었다.
    • 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있었다.
  • MySQL 5.7 버전에서는 innodb_undo_tablespaces 시스템 변수를 도입하여, 언두 로그를 별도의 언두 로그 파일에 저장하도록 설정할 수 있다.
    • 하지만 시스템 변수를 0으로 설정하면 이전 버전과 동일하게 시스템 테이블스페이스에 저장된다.
  • MySQL 8.0 버전에서는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선됐다.
    • innodb_undo_tablespaces 시스템 변수는 Deprecated

언두 테이블 스페이스의 구조

  • 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가진다.
  • 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가진다.
    • 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼 언두 슬롯을 가진다.
  • 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 언두 슬롯을 사용하게 된다.
    • 일반적으로는 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다.
최대 동시 트랜잭션 수=(InnoDB 페이지 크기)/16∗(롤백 세그먼트 개수)∗(언두 테이블스페이스 개수)최대\ 동시\ 트랜잭션\ 수=\\ (InnoDB\ 페이지\ 크기)/16*(롤백\ 세그먼트\ 개수)*(언두\ 테이블스페이스\ 개수)

언두 테이블스페이스를 동적으로 추가/삭제

MySQL 8.0 버전부터 새로운 언두 테이블스페이스를 동적으로 추가하고 삭제할 수 있게 개선됐다.

-- 테이블스페이스 확인
SELECT TABLESPACE_NAME, FILE_NAME
FROM IMPORMAION_SCHEMA.FILES
WHERE FILE_TYPE LIKE 'UNDO LOG';

-- 새로운 언두 테이블스페이스 생성
CREATE UNDO TABLESPACE extra_undo_003 ADD DATAFILE '~.ibu';

-- 언두 테이블스페이스를 비활성화
ALTER UNDO TABLESPACE extra_undo_003 SET INACTIVATE;

-- 비활성화된 테이블스페이스 삭제
DROP UNDO TABLESPACE extra_undo_003;

Undo tablespace truncate

  • 언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 말한다.
  • MySQL 8.0 부터 지원되는 자동과 수동 두 가지 방법이 있다.
  • 자동 모드
    • InnoDB 스토리지 엔진의 퍼지 스레드(Purge Thread)는 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제하는 작업을 실행한다. → 언두 퍼지(Undo Purge)
    • innodb_undo_log_truncate 시스템 변수가 ON으로 설정되면, 퍼지 스레드는 주기적으로 언두 로그 공간에서 사용하지 않는 공간을 잘라내고 운영체제로 반납하게 된다.
  • 수동 모드
    • innodb_undo_log_truncate 가 OFF로 설정되어 있거나 자동 모드가 언두 테이블스페이스의 공간 반납이 부진한 경우
    • 퍼지 스레드는 비활성 상태의 언두 테이블스페이스를 찾아 불필요한 공간을 운영체제로 해당 공간을 반납한다.
    • 반납이 완료되면 언두 테이블스페이스를 다시 활성화한다.
    • 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 되어야 작동한다.
-- 언두 테이블스페이스 비활성화
ALTER UNDO TABLESPACE tablespacename SET INACTIVATE;

-- 퍼지 스레드에 의해 언두 테이블스페이스 공간이 반납되면 다시 활성화
ALTER UNDO TABLESPACE tablespacename SET ACTIVATE;

체인지 버퍼 (Change Buffer)

체인지 버퍼란

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시킨다.

이 임시 메모리 공간을 체인지 버퍼라고 한다.

버퍼 머지 스레드 (Merge thread)

  • 유니크 인덱스는 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하므로, 체인지 버퍼를 사용할 수 없다.
  • 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합된다.
    • 이 스레드를 버퍼 머지 스레드라고 한다.

버퍼링

  • MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능하다.
    • MySQL 5.5 이전 버전까지는 이 버퍼를 인서트 버퍼라고 함.
  • MySQL 8.0에서는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있다.

활성화

  • MySQL 5.5 이전 버전에서는 별도의 시스템 변수 설정 없이 기본적으로 기능이 활성화됐다.
  • MySQL 5.5부터는 innodb_change_buffering 시스템 변수를 도입하여, 작업의 종류별로 체인지 버퍼를 활성화할 수 있다.
    • 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있다.
  • innodb_change_buffering 시스템 변수에 설정할 수 있는 값
    • all: 모든 인덱스 관련 작업(inserts+deletes+purges)을 버퍼링
    • none: 버퍼링 안함
    • inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
    • deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제되었다는 마킹 작업)만 버퍼링
    • changes: 인덱스에 추가하고 삭제하는 작업(inserts+deletes)만 버퍼링
    • purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링 (백그라운드 작업)

체인지 버퍼의 공간

  • 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정됐다.
    • 필요 시 50%까지 사용하게 설정할 수 있다.
  • 다음과 같은 경우에는 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 된다.
    • 체인지 버퍼가 너무 많은 버퍼 풀 공간을 사용하지 못하도록 할 경우
    • INSERT나 UPDATE 등이 너무 빈번하게 실행되어, 체인지 버퍼가 더 많은 버퍼 풀을 사용할 수 있게 할 경우
  • 체인지 버퍼가 버퍼 풀의 메모리를 얼마나 사용 중이고 얼마나 많은 변경 사항을 버퍼링하고 있는지 확인할 수 있다.
-- 체인지 버퍼가 사용 중인 메모리 공간의 크기
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/ibuf0ibuf';

-- 체인지 버퍼 관련 오퍼레이션 처리 횟수
SHOW ENGINE INNODB STATUS \G
------------------------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
------------------------------------------------------
...

Reference

참고 서적

📔 Real MySQL 8.0

profile
공부하는 감자
책을 읽거나 강의를 들으며 공부한 내용을 정리합니다. 가끔 개발하는데 있었던 이슈도 올립니다.
이전 포스트

MySQL 엔진 아키텍처

다음 포스트

MyISAM 스토리지 엔진 아키텍처

0개의 댓글