[DB] Real MySQL 4장: 아키텍처

서정범·2024년 3월 4일

Database

목록 보기

4/4

MySQL의 엔진은 다음과 같이 구분됩니다.

MySQL 엔진: 사람의 머리 역할을 담당합니다.
스토리지 엔진: 사람의 손발 역할을 담당합니다.

1. MySQL 엔진 아키텍처

MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특합니다.

사용자에게 여러 혜택을 가져다 주기도 하지만, 다른 DBMS에서는 문제되지 않을 것들이 가끔 문제가 되기도 합니다.

1.1 MySQL의 전체 구조

MySQL은 일반 사용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원합니다.

프로그래밍 언어의 드라이버를 이용하여 MySQL 서버로 쿼리를 보낼 수 있도록 지원합니다.

우선 여기서는 MySQL엔진과 스토리지 엔진에 대해서 살펴봅시다.

1.1.1 MySQL 엔진

MySQL 엔진은 SQL 파서, 옵티마이저, 캐시 등과 같은 핵심 기능을 제공하는 데이터베이스 중앙 처리 유닛입니다.

사용자로부터 SQL 쿼리를 받아 파싱하고, 최적의 실행 계획을 수립한 후, 실제 데이터 처리를 위해 적절한 스토리지 엔진에 요청을 전달합니다.

MySQL 엔진은 데이터베이스의 전반적인 관리와 조정을 담당하며, 클라이언트 요청의 처리, 트랜잭션의 관리, 사용자 및 권한 관리 등의 기능을 포함합니다.

1.1.2 스토리지 엔진

스토리지 엔진은 MySQL에서 데이터의 실제 저장 방식을 담당하는 구성 요소입니다.

데이터의 저장, 검색, 수정 등의 작업을 실행하며, 트랜잭션 처리, 로깅 매커니즘, 인덱싱 방법 등에 대한 구현을 제공합니다.

MySQL은 여러 스토리지 엔진을 지원하며, 각 스토리지 엔진은 서로 다른 특성과 성능, 기능을 가지고 있어서 사용자의 요구 사항에 따라 선택적으로 사용할 수 있습니다.

대표적인 스토리지 엔진으로는 다음과 같은 것들이 있습니다.

InnoDB: "기본 스토리지 엔진"으로, ACID-호환 트랜잭션, 외래 키 제약 조건 등을 지원합니다. 높은 신뢰성과 성능을 제공하며, 대부분의 OTLP(Online Transaction Processing) 환경에서 선호됩니다.
MyISAM: 과거 MySQL의 기본 스토리지 엔진이었으며, 빠른 읽기 속도와 간단한 설계가 특징입니다. 하지만, 트랜잭션을 지원하지 않고, 테이블 단위의 로깅을 사용하기 때문에 동시성 처리에는 제한이 있습니다.
Memory: 모든 데이터를 메모리에 저장하는 스토리지 엔진으로, 빠른 데이터 접근을 요구하는 임시 테이블에 사용됩니다. 서버가 종료되면 데이터가 사라집니다.

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다.

각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있습니다.

1.1.3 핸들러 API

MySQL 엔진은 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 합니다.

InnoDB 스토리지 엔진 또한 MySQL 엔진과 데이터를 주고받을 때 이 핸들러 API를 사용합니다.

1.2 MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 보면 다음과 같이 구분될 수 있습니다.

포그라운드(Foreground) 스레드
백그라운드(Background) 스레드

MySQL는 멀티 스레드 아키텍처를 사용하여 동시에 여러 작업을 수행하는 것입니다.

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

포그라운드 스레드는 "클라이언트와 직접적으로 관련된 스레드"입니다.

클라이언트로부터 요청을 받아 처리하고, 결과를 다시 클라이언트에게 전송하는 역할을 합니다.

클라이언트 스레드는 스레드 캐시(Thread cache)를 통해서 관리되며, 종료된 스레드는 해당 캐시로 돌아가게 됩니다.

스래드 캐시는 스레드의 갯수를 일정 개수로 제한하여 관리합니다.

스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정합니다.

주요 역할로는

클라이언트의 요청 처리
SQL 쿼리 실행
결과 반환

정도로 정리를 할 수 있습니다.

데이터를 가져올 때는 MySQL의 데이터 버퍼나 캐시로부터 가져오며 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리합니다.

MyISAM 테이블은 디스크 쓰기 작업 까지 포그라운드 스레드가 처리합니다.

하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리합니다.

1.2.2 백그라운드 스레드

백그라운드 스레드는 데이터베이스 서버의 관리 및 유지 보수 작업을 담당합니다. 이들은 사용자 요청과는 독립적으로 실행되며, 데이터베이스의 성능 최적화, 데이터 무결성 유지, 안정적인 운영을 위한 다양한 백엔드 작업을 수행합니다.

주요 역할은 다음과 같습니다.

InnoDB I/O 스레드: 디스크 I/O 요청을 처리하고, 비동기적인 읽기/쓰기 작업을 관리합니다.
로그 스레드: 트랜잭션 로그를 디스크에 기록합니다.
페이지 클리너 스레드: 수정된 페이지를 정기적으로 디스크에 flush하여, 버퍼 풀을 관리합니다.
잠금 모니터 및 타임아웃 스레드: 잠금 상태와 트랜잭션 타임아웃을 모니터링합니다.
복제 스레드: MySQL 복제(replication) 환경에서 마스터로부터 데이터 변경 사항을 복사하거나 슬레이브에 적용하는 역할을 합니다.

모두 중요한 역할ㅇ르 하지만 그 중에서도 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드 (Write thread)일 것입니다.

InnoDB에서는 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 많이 설정할 필요는 없지만, 쓰기 쓰레드는 아주 많은 작업을 백그라운드에서 처리하기 때문에 충분하게 설정해둬야 합니다.

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없습니다.

그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있으며, InnoDB 또한 이러한 방식으로 처리됩니다.

1.3 메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있습니다.

글로벌 메모리 영역의 모든 메모리 공간은 "MySQL 서버가 시작되면서 운영체제로부터 할당됩니다.

요청한만큼 할당을 해줄 수도 있으며, 예약해 두고 필요할 때 조금씩 할당해주는 경우도 있습니다. 이는 설계에 따라 달라집니다.

1.3.1 글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됩니다.

필요에 따라 늘어날 수 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유됩니다.

1.3.2 로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 "클라이언트 스레드"가 쿼리를 처리하는데 사용하는 메모리 영역입니다.

클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 합니다.

로컬 메모리는 각 클라이언트 스레드별로 "독립적"으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있습니다.

또 한가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않는 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다는 점입니다.

대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러합니다.

그리고 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼나 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있습니다.

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

MySQL의 독특한 구조 중 대표적인 것이 바로 플러그인 모델입니다.

스토리지 엔진을 핵심 컴포넌트(메인 모듈)로 보자면, 플러그인은 보조 모듈로 볼 수 있습니다.

플러그인은 데이터베이싀이 기능을 확장해주는 역할을 수행하며, 필요에 따라 추가적인 기능을 데이터베이스에 도입할 수 있게 해줍니다.

예를 들어, 패스워드 암호화, 전문 검색 기능, 사용자 인증 방식의 다양화 등과 같은 기능을 플러그인을 통해 추가할 수 있습니다.

이 플러그인 시스템을 통해 데이터베이스 관리자는 시스템의 요구사항과 환경에 맞게 데이터베이스의 기능을 유연하게 확장하고 맞춤화할 수 있습니다.

MySQL에서 쿼리가 실행되는 과정은 아래 그림과 같습니다.

거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 '데이터 읽기/쓰기' 작업만 스토리지 엔진에 의해 처리됩니다.

MySQL에서는 핸들러라는 개념이 나오는데, 최소한 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 '핸들러'를 통해야 한다는 점을 기억해두자.

마지막으로 MySQL에서는 여러 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원하고 있습니다.

필요한 경우 확인하고 사용할 수 있습니다.

1.5 컴포넌트

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

MySQL 서버의 플러그인은 다음과 같은 몇 가지 단점이 있습니다.

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

여기서 첫 번째 단점은 플러그인 간에 직접적인 통신이 불가능하다는 것을 의미합니다. 즉, 만약 두 개의 플러그인이 서로 정보를 교환하거나 상호작용하는 기능을 필요로 한다면, 이를 직접 구현하는 것이 어렵다는 점을 나타냅니다.

이로 인해, 플러그인은 독립적으로 작동하며 "MySQL 서버를 통해서만" 정보를 교환할 수 있습니다.

두 번째 단점은 플러그인이 MySQL 서버의 내부 구조에 깊숙이 개입할 수 있다는 것을 의미합니다. 이는 데이터베이스의 안전성이나 보안에 위험을 초래할 수 있습니다.

직접적인 접근은 "캡슐화 원칙을 위반"하는 것으로, 객체 지향 프로그래밍에서는 내부 구현을 숨기고 공개 인터페이스를 통해서만 상호작용을 하도록 권장합니다.

세 번째 단점은 특정 플러그인이 다른 플러그인의 기능이나 서비스에 의존할 경우, 이러한 의존성을 관리하고 초기화하는 과정이 복잡하다는 것을 의미합니다.

예를 들어, 한 플러그인이 작동하기 위해서는 다른 플러그인이 먼저 초기화되어 있어야 하는 경우, 이러한 의존 관계를 설정하고 관리하는 매커니즘이 부재하거나 제한적일 수 있습니다.

이는 플러그인을 개발하거나 시스템을 구성할 때 "추가적인 복잡성을 초래"하며, "오류를 발생"시킬 가능성을 높일 수 있습니다.

1.6 쿼리 실행 구조

아래 그림은 쿼리를 실행하는 관점에서 MySQL 구조를 간략하게 그림으로 표현한 것이며, 다음과 같이 기능별로 나눠 볼 수 있습니다.

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

쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 됩니다.

전처리기는 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인합니다.

각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행합니다.

실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러집니다.

옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있습니다.

실행 엔진과 핸들러는 손과 발에 비유할 수 있습니다.

핸들러는 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 일어 오는 역할을 담당합니다. 결국 "스토리지 엔진"을 의미합니다.

실행 엔진에 하는 일을 더 쉽게 이해할 수 있게 예를 들어서 확인해보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.

실행 엔진이 '핸들러'에게 임시 테이블을 만들라고 요청
다시 실행 엔진이 WHERE 절에 일치하는 레코드를 읽어오라고 '핸들러'에게 요청
읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 '핸들러'에게 요청
데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 '핸들러'에게 다시 요청
최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

1.7 쿼리 캐시

MySQL 8.0으로 올라오면서 제거된 기능입니다.

MySQL 서버에서 쿼리 캐시(Query Cache)는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했습니다.

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 빠른 성능을 보였습니다.

하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidate)해야 했습니다. 이는 심각한 동시 처리 성능 저하를 유발합니다.

또한 MySQL 서버가 발전하면서 성능이 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인이 되기도 했습니다.

결국 MySQL 8.0으로 올라오면서 쿼리 캐시는 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거되었습니다.

1.8 스레드 풀

여기서는 MySQL 서버 엔터프라이즈 에디션에 내장되어 있는 스레드 풀(Thread Pool) 기능이 아닌 Percona Server에서 제공하는 스레드 풀 기능을 살펴볼 것입니다.

Percona Server의 스레드 풀은 플러그인 형태로 작동합니다.

스레드 풀의 사용 목적은 내부적으로 스레드의 수를 관리하여 CPU의 효율을 높이고, 스레드 처리에만 집중하도록 서버의 자원 소모를 줄이는 것입니다.

스레드 풀을 사용한다고 무조건 성능이 향상되는 것은 아니지만 가용 가능한 스레드의 수를 적절하게 조절한다면 CPU의 성능을 높일 수 있습니다.

제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도(Processor affinity)를 높이고 운영 체제 입장에서는 불필요한 컨텍스트 스위치(Context switch)를 줄여서 오버헤드를 낮출 수 있습니다.

Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하는데, 스레드 그룹의 개수는 thread_pool_size 시스템 변수를 변경해서 조정할 수 있습니다.

하지만 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋습니다.

MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관하는데, 만약 이미 스레드 풀이 처리 중인 작업이 있는 경우에는 thread_pool_oversubscribe 시스템 변수(기본값은 3)에 설정된 개수만큼 추가로 더 받아들여서 처리합니다. 이 값이 너무 크면 스케줄링해야 할 스레드가 많아져서 스레드 풀이 비효율적으로 작동할 수 있습니다.

스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀이 해당 스레드 그룹에 새로운 작업 스레드(Work thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 합니다.

스레드 풀 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가합니다.

따라서, 이 값을 설정할 때 응답 시간에 아주 민감한 서비스라면 이 시스템 변수를 적절히 낮춰서 설정해야 합니다.

그리고 전체 스레드 풀에 있는 스레드의 개수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없습니다.

마지막으로 설명되는 기능으로 Percona Server의 스레드 풀 우선순위 설정입니다.

선순위 큐와 후순위 큐를 이용해 특정 트랜 잭션이나 쿼리를 우선적으로 처리할 수 있는 기능을 제공합니다.

이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상시킬 수 있습니다.

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

데이터베이스 서버에서 테이블 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 하는데, MySQL 서버는 5.7 버전까지 테이블 구조를 파일 기반으로 관리했습니다.

하지만 이러한 파일 기반의 메타데이터는 "생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에" 테이블 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었습니다.

많은 사용자들이 이 같은 현상을 가리켜 '데이터베이스나 테이블이 깨졌다'라고 표현합니다.

MySQL 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선했습니다.

MySQL 서버가 작동하는데 기본적으로 필요한 테이블을 묶어서 시스템 테이블이라고 하는데, 대표적으로 사용자의 인증과 권환에 관련된 테이블등이 있습니다.

시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하도록 하고, mysqlDB는 통째로 mysql.ibd라는 이름의 '테이블스페이스'에 저장됩니다.

MySQL 8.0 버전부터 '데이터 딕셔너리'와 '시스템 테이블'이 모두 트랜잭션 기반의 InnoDB 엔진에 저장되도록 개선되면서 이제 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리됩니다.

MySQL 서버는 InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용합니다.

2. InnoDB 스토리지 엔진 아키텍처

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

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

InnoDB의 '모든 테이블'은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장됩니다.

즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용합니다.

여기서 프라이머리 키와 세컨더리 키 그리고 클러스터링이라는 용어가 언급되고 있습니다. 이부분을 이해하기 위해서는 Index와 B-Tree의 동작 방식을 이해하고 있어야 한다고 판단됩니다. 따라서, 이부분은 Index를 다루는 곳에서 상세히 다루고 여기서는 간략하게 정리만 하겠습니다.

간단하게 살펴봅시다.

먼저 프라이머리 키 기반의 클러스터링부터 확인을 해봅시다.

클러스터링 인덱스(Clustering Index): 데이터가 "프라이머리 키의 값"에 따라 물리적으로 정렬되어 저장되는 방식입니다.
- 이 말은 테이블의 모든 데이터가 프라이머리 키의 순서대로 디스크 상에 배열되어 있다는 의미입니다.
- 프라이머리 키 값에 기반하여 클러스터링되어 있기 때문에, 프라이머리 키를 사용한 검색과 범위 쿼리가 매우 효율적입니다.
클러스터링 이점: 프라이머리 키를 조회할 때, 디스크 I/O가 최소화되어 성능이 향상됩니다. 데이터가 프라이머리 키 순으로 정렬되어 있기 때문에, 연속된 데이터를 읽는 작업이 빨라집니다.
- 또한, 프라이머리 키를 통해 데이터에 접근하면 해당 레코드의 모든 데이터를 직접 얻을 수 있어 추가적인 I/O가 필요 없습니다.

Clustering Index
Clusted Indexß

다음은 세컨더리 인덱스를 확인해 봅시다.

세컨더리 인덱스(Secondary Index): 프라이머리 키가 아닌 다른 컬럼들의 기준으로 생성된 인덱스입니다.
- 세컨더리 인덱스는 해당 인덱스의 키 값에 따라 정렬되어 있지만, 인덱스 자체는 레코드의 물리적 위치 대신 프라이머리 키 값을 포인터(논리 주소)로 사용하여 해당 레코드를 찾습니다.
세컨더리 인덱스의 동작 방식:
1. 세컨더리 인덱스를 통해 조회할 때, 먼저 인덱스를 검색하여 대응하는 프라이머리 키 값을 찾습니다.
2. 그 다음, 이 프라이머리 키 값을 사용하여 클러스터링 인덱스를 검색하고, 실제 제이터에 접근합니다.

이 과정에서 두 단계의 검색이 필요하기 때문에 프라이머리 키를 직접 사용하는 것보다는 상대적으로 느릴 수 있습니다.

세컨더리 인덱스의 동작이 스토리지 엔진에 따라 달라질 수 있는데, 클러스터링 기능을 아예 지원하지 않는다면, 프라이머리 키 값을 사용하여 클러스터링 인덱스를 검색하는 것이 아닌 물리적인 레코드의 주소 값을 가지게 될 것입니다.

실제로 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는데, MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가집니다.

2.2 외래키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이믈에서는 사용할 수 없습니다.

InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 "데드락이 발생할 떄가 많으므로" 개발할 때도 외래 키의 존재에 주의하는 것이 좋습니다.

⚡️ 외래 키를 잘 사용하지 않는 이유 ⚡️

외래 키(Foreign Key)는 데이터베이스에서 관계를 정의하고 데이터 무결성을 유지하는 중요한 도구입니다. 하지만, 실제 운영 환경에서는 외래 키를 사용하지 않는 경우가 종종 있습니다.

1. 성능 저하

성능 문제: 외래 키 제약 조건은 데이터베이스가 추가적인 검증 작업을 수행하도록 만듭니다. 삽입, 업데이트, 삭제 작업 시에 관련된 테이블의 외래 키를 확인해야 하므로, 이러한 작업들의 성능이 저하될 수 있습니다.

잠금 대기 시간 증가: 외래 키 제약 조건으로 인해 더 많은 행이 잠금(lock)될 수 있으며, 이는 대기 시간과 데드락의 가능성을 증가시킵니다.

2. 유연성 감소

변경의 어려움: 외래 키 제약 조건은 데이터베이스 스키마 변경을 더 복잡하게 만들 수 있습니다. 테이블 구조를 변경하거나 데이터를 이동할 때, 외래 키 제약 조건을 만족시켜야 하므로 변경 작업이 더 어려워질 수 있습니다.

3. 복제 및 분산 환경의 복잡성

복제 및 분산 처리: 외래 키는 데이터베이스의 복제나 분산 데이터베이스 환경에서 추가적인 복잡성을 초래할 수 있습니다. 외래 키 제약 조건을 유지하면서 데이터를 여러 노드에 분산시키는 것은 관리상의 어려움을 더할 수 있습니다.

4. 애플리케이션 레벨에서의 데이터 무결성 관리

애플리케이션 레벨의 제어: 일부 개발팀은 데이터베이스 대신 애플리케이션 레벨에서 데이터 무결성을 관리하는 것을 선호합니다. 이는 애플리케이션의 로직을 통해 더 세말하고 유연하게 데이터 관계를 관리할 수 있다고 보기 때문입니다.

5. 테이블 간 결합 감소

결합도 감소: 외래 키를 사용하지 않음으로써, 테이블 간의 결합도를 낮출 수 있습니다. 이는 시스템의 유연성을 증가시키고, 테이블 간의 의존성을 줄여줍니다.

2.3 MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있습니다.

InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현합니다.

여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미입니다.

예를 들어서 보자.

격리 수준(Isolation level)이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 상황입니다. 우선 테이블에 한 건의 레코드를 INSERT한 다음 UPDATE해서 발생하는 변경 작업 및 절차를 확인해 봅시다.

mysql> CREATE TABLE member (
	m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL,
    m_area VARCHAR(100) NOT NULL,
    PRIMARY KEY (m_id),
    INDEX ix_area (m_area)
  );
  
  
mysql> INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');

mysql> COMMIT;

mysql> UPDATE member SET m_area='경기' WHERE m_id=12;

UPDATE 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 '경기'로 업데이트됩니다.

그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있습니다(InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀이나 데이터 파일은 동일한 상태라고 가정해도 무방하다).

아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?

mysql> SELECT * FROM member WHERE m_id=12;

이것은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다릅니다.

격리 수준이 READ_UNCOMMITTED인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환합니다. 즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환합니다.

그렇지 않고 READ_COMMITTED나 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 번경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환합니다.

즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조입니다.

만약, 언두영역으로 복사된 이후 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버립니다.

하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해 버립니다.

커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것이 아닙니다. 이 언두 영역을 필요로 하는 트랜잭션이 더는 없을 떄 비로소 삭제됩니다.

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행합니다.

잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능합니다.

격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED나 READ_COMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행합니다.

아래 그림에서 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다르 사용자의 SELECT 작업을 방해하지 않습니다.

이를 '잠금 없는 일관된 읽기'라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용합니다.

오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 떄가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지하기 때문에 발생하는 문제입니다.

따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤맥이나 커밋을 통해 트랜잭션을 완료하는 것이 좋습니다.

2.5 자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 '교착 상태에 빠지지 않았는지 체크'하기 위해 담금 대기 목록을 그래프(Wait-for List) 형태로 관리합니다.

InnoDB 스토리지 엔진은 이를 위해 데드락 감지 스레드를 가지고 있습니다.

데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 '강제 종료'합니다.

이때 판단 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백이 됩니다.

트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 각제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문입니다.

참고로 InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)을 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데, innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벌의 잠금까지 감지할 수 있게 됩니다.

특별한 이유가 없다면 innodb_table_locks 시스템 변수를 활성화합시다.

만약 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려집니다.

검사를 하기 위해 잠금 목록이 저장된 리스트에 새로운 잠금을 걸고 데드락 스레드를 찾게 됩니다.

데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 됩니다.

이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 됩니다.

물론 이 변수를 OFF로 설정해두면 무한정 대기 상태에 빠질 수 있습니다. 하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 됩니다.

2.6 자동화된 장애 복구

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 매커니즘이 탑재되어 있습니다.

그러한 매커니즘을 이용해 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행됩니다.

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시잘될때 항상 자동 복구를 수행합니다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료되어 버립니다.

이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 합니다.

이 설정값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과저애을 선별적으로 진행할 수 있게 합니다.

InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동한다. (Critical)
InnoDB의 테이블의 데이터 파일이 손상됐다면 1로 설정하고 MySQL 서버를 기동한다. (Generally)
어떤 부분이 문제인지 알 수 없다면 1부터 6까지 변경하면서 MySQL을 재시작해봅니다. innodb_force_recovery 값이 커질수록 그만큼 심각한 상황이어서 데이터 손실 가능성이 커지고 복구 가능성은 적어집니다.

InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지인데, 각 숫자 값을 복구되는 장애 상황과 해결 방법을 간략하게 정리하겠습니다.

1(SRV_FORCE_IGNORE_CORRUPT)
- InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 MySQL 서버를 시작
- 덤프한 후 데이터베이스를 다시 구축
2(SRV_FORCE_NO_BACKGROUND)
- InnoDB는 트랜잭션이 커밋되어 불필요한 언두 데이터를 InnoDB의 메인 스레드에 의해 주기적으로 삭제합니다(Undo purge).
- InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드로 복구
3(SRV_FORCE_TO_TRX_UNDO)
- 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔둔다. 즉, 커밋되지 않고 종료된 트랜잭션은 계속 그상태로 남아 있게 MySQL서버를 시작하는 모드
- 우선 MySQL 서버가 시작되면 mysqldump를 이용해 데이터를 백업해서 다시 데이터베이스를 구축하는 것이 좋다.
4(SRV_FORCE_NO_IBUF_MERGE)
- InnoDB는 인덱스 변경 작업을 상황에따라 인서트 버퍼에 저장해두고 나중에 처리할 수 있는데, 만약 MySQL이 Merge되지 않은 상태에서 종료된 후 재시작되면서 인서트 버퍼의 손상을 감지하면 InnoDB는 에어를 발생시키고 MySQL 서버는 시작하지 못합니다.
- 4로 설정하면 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL이 시작되게 합니다.
- 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 인덱스와 관련된 부분이므로 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구할 수 있습니다.
5(SRV_FORCE_NO_UNDO_LOG_SCAN)
- MySQL 서버가 트랜잭션이 있는 상태에서 종료된 후 다시 시작된다면 '언두 레코드'를 이용해 데이터 페이지를 복구하고 '리두 로그'를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현해 냅니다. 그리고 InnoDB는 마지막으로 커밋되지 않은 트랜잭션에서 변경한 작업은 모두 롤백 처리합니다.
- 그런데 InnoDB의 '언두 로그'를 사용할 수 없다면 InnoDB 엔진의 에러로 서버를 시작할 수 없습니다.
- 이 모드로 복구하면 MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남는 것이라고 볼 수 있습니다.
- mysqldump를 이용해 데이터를 백업하고, 데이터베이스를 새로 구축해야 합니다.
6(SRV_FORCE_NO_LOG_REDO)
- InnoDB 스토리지 엔진의 '리두 로그'가 손상되면 MySQL 서버가 시작되지 못합니다. 리두 로그를 무시한 채로 시작되며, 커밋됐다고 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시됩니다.
- 즉, 마지막 체크포인트 시점의 데이터만 남게 됩니다.
- 이때는 기존 InnoDB의 리두 로그는 모두 삭제하고 MySQL 서버를 시작하는 것이 좋습니다. MySQL 서버가 시작하면서 리두 로그가 없으면 새로 생성하므로 별도로 파일 만들 필요는 없습니다. 이때도 mysqldump를 이용해 데이터를 모두 백업해서 MySQL 서버를 새로 구출하는 것이 좋습니다.

2.7 InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 다음과 같은 주요 작업을 수행합니다.

디스크의 데이터 파일이나 인덱스 정보를 캐시
쓰기 작업을 지연시켜 일괄 작업을 처리할 수 있게 해주는 버퍼 역할

2.7.1 버퍼 풀의 크기 설정

버퍼 풀의 크기를 설정할 때는 고려해야 될 것들이 몇 개 있습니다.

운영체제가 사용할 메모리
각 클라이언트 스레드가 사용할 메모리
가끔 레코드 버퍼가 상당한 메모리를 사용

여기서 레코드 버퍼라는 것은 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말합니다.

커넥션이 많고 사용되는 테이블이 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많아질 수 있습니다. 이것은 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라서 결정됩니다.

InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있기 때문에 가능하면 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 방법이 최적입니다.

운영체제의 전체 메모리 공간이 8GB 미만이면 50% 정도만, 그 이상이라면 조금씩 올려가며 최적점을 찾는다. 50GB 이상이라면, 대략 15GB에서 30GB를 남겨두고 나머지를 InnoDB 버퍼 풀로 할당하기를 권장합니다.

InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개서 관리됩니다. 따라서, 이 크기의 단위로 버퍼 풀의 크기를 줄이거나 늘어나게 됩니다.

InnoDB 버퍼 풀은 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되어 잠금(세마포어)으로 인한 내부 잠금 경합을 분산시켰습니다.

innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 '버퍼 풀 인스턴스'라고 표현합니다.

2.7.2 버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장합니다.

버퍼 풀의 페이지 크기 조각을 관리하기 위해 3개의 자료 구조를 관리합니다.

LRU(Least Recently Used) 리스트:
Flush 리스트
Free 리스트

하나씩 확인해 봅시다.

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

LRU 리스트는 'Old 서브리스트' 영역인 LRU(Least Recently Used)와 'New 서브리스트' 영역인 MRU(Most Recently Used)가 결합된 형태라고 보면 됩니다.

LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것입니다.

InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 대략 다음과 같습니다.

필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
a. InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
b. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
c. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 부분에 추가
버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
(Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있으며, 이런 경우에는 MRU로 이동되지 않음)
버퍼 풀에 상주하는 데이터 페이지는 나이(Age)가 부여되며, 나이가 오래되면 해당 페이지는 결국 버퍼풀에서 제거 됩니다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리합니다.

이것은 얼핏 보면 리두 로그과 동작이 중복되어, 역할이 겹치는 것 같이 보일 수 있습니다. 정리를 하면서 비교를 해봅시다.

플러시 리스트(Flush List)

플러시 리스트는 InnoDB 버퍼 풀 내에서 변경되었지만 아직 디스코로 동기화되지 않은 데이터 페이지를 관리합니다.

이 리스트는 데이터 페이지를 LSN(Log Sequence Number)에 따라 정렬된 순서로 유지합니다. LSN은 각 페이지가 마지막으로 변경된 시점을 나타내는 순서 번호입니다.

플러시 리스트의 주된 목적은 효율적인 페이지 쓰기와 복구 과정을 보장하는 것입니다.

역할: 변경된 페이지를 효율적으로 관리하고, 체크포인트나 백그라운드 쓰기 작업 시 어떤 페이지가 디스크로 동기화되어야 하는지 결정합니다.
차별점: 플러시 리스트는 실제 데이터 페이지의 변경 사항을 추적하고, 이를 디스크로 효율적으로 동기화하기 위한 구체적인 매커니즘을 제공합니다.

리두 로그(Redo Log)

리두 로그는 데이터베이스의 모든 변경 사항(트랜잭션)을 로그 형태로 기록하는 매커니즘입니다. 이 로그는 시스템 장애 발생 시 데이터를 복구하기 위한 목적으로 사용됩니다.

리두 로그는 데이터베이스의 내구성(Durablity)을 보장하는 ACID 속성의 핵심 부분입니다.

역할: 모든 데이터 변경 사항을 순차적으로 기록하며, 시스템 장애 발생 시 마지막 일관된 상태로 데이터베이스를 복구할 수 있게 합니다.
차별점: 리두 로그는 데이터의 복구와 일관성 유지에 중점을 둡니다. 이는 데이터의 내구성과 데이터베이스의 안정성을 보장하는 데 필수적입니다.

즉, 플러시 리스트는 디스크 동기화가 필요한 변경된 페이지를 관리하는 반면, 리두 로그는 모든 데이터 변경 사항을 로깅하여 시스템 장애 후 복구를 가능하게 합니다.

2.7.3 버퍼 풀과 리두 로그

InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있습니다.

InnoDB의 버퍼 풀은 데이터베이스의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 '데이터 캐시 기능만 향상'되는 것입니다.

InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 합니다.

InnoDB의 버퍼 풀은 두 개의 페이지를 가지게 됩니다.

Clean Page: 디스크에서 읽은 상태로 전혀 변경되지 않은 페이지
Dirty Page: 변경된 데이터를 가지고 있는 페이지

더티 페이지는 디스크와 메모리의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 합니다.

InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용합니다.

즉, 데이터 변경이 계속되면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰입니다.

그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리하고, 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log)라고 합니다.

리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 가지게 되는데, 이를 LSN(Log Sequence Number)이라고 합니다.

InnoDB 스토리지 엔진은 '주기적으로' 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 됩니다.

가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN 차이를 체크포인트 에이지(Checkpoint Age)라고 합니다. 즉, 체그포인트 에이지는 '활성 리두 로그 공간의 크기'를 일컫는다.

InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와의 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 합니다.

물론 당연히 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화돼야 합니다.

동작을 명확히 이해하기 위해 주요 매커니즘 동작 방식을 다시 정리해 봅시다.

여기서 알아볼 상황은 요청들이 들어오면서 COMMIT된 내역들이 쌓이고, 그러다가 체크포인트가 발생되는 상황까지 알아볼 것입니다.

1. 더티 페이지와 플러시 리스트

더티 페이지 생성: 데이터베이스에 대한 변경 요청(예: INSERT, UPDATE 등)이 처리되고 COMMIT되면, 해당 변경 사항은 더티 페이지로 '버퍼 풀'에 저장됩니다.
플러시 리스트 저장: 이 더티 페이지들은 플러시 리스트에 등록되어 관리됩니다. 이 리스트는 변경된 페이지를 디스크로 플러시할 순서를 결정하는 데 도움을 줍니다.

2. 리두 로그 파일의 Flush

리두 로그 파일의 Flush: 트랜잭션의 COMMIT 시, 변경 사항은 '리두 로그 버퍼
에 기록되며, 이 버퍼의 내용은 주기적으로 또는 필요에 따라 디스크 상의 리두 로그 파일로 플러시됩니다. 이 과정은 트랜잭션의 내구성(Durability)을 보장합니다.

3. 체크포인트와 데이터 동기화

체크포인트 발생: 체크포인트는 시스템이 주기적으로 또는 특정 조건을 만족할 때 발생합니다. 이 과정에서 '버퍼 풀의 플러시 리스트의 더티 페이지'가 디스크의 데이터 파일로 플러시됩니다.
LSN 업데이트: 체크포인트 시, 버퍼 풀은 디스크로 플러시된 더티 페이지의 최대 LSN을 리두 로그 파일에 기록합니다. 이는 복구 과정에서 어디서부터 복구를 시작해야 하는지를 나타내며, 복구 성능과 공간 활용도를 높입니다.

4. 복구 성능과 공간 활용도

복구 성능 향상: 체크포인트로 인해 업데이트된 LSN은 복구 시 필요한 로그의 양을 최소화하고, 복구 시점 지점을 명확하게 합니다. 이는 복구 시간을 단축시킵니다.
공간 활용도 개선: LSN의 순환을 통해 리두 로그 파일의 사용된 부분을 효율적으로 재사용할 수 있게 되며, 디스크 공간을 보다 효율적으로 관리할 수 있습니다.

중요한 점을 정리해보자면,

플러시 리스트를 통한 Flush 작업은 버퍼 풀의 더티 페이지를 데이터 파일로 반영하는 것으로, 데이터의 지속성과 일관성을 보장하기 위한 주된 목적을 가집니다.
리두 로그 파일의 Flush 작업은 트랜잭션 로그를 디스크에 안전하게 저장하는 것으로, 시스템 복구를 위한 목적으로 사용됩니다.
리두 로그 엔트리의 체크포인트 지점의 LSN 업데이트 작업은 리두 로그 파일 공간의 효율적 관리와 시스템 복구 준비를 위한 것입니다.

2.7.4 버퍼 풀 플러시(Buffer Pool Flush)

MySQL 5.7 버전을 거쳐서 MySQL 8.0 버전으로 업그레이드되면서 대부분의 서비스에서는 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상이 발생하지 않습니다.

이는 위험한 현상일 수 있는데 만약 갑작스럽데 디스크 기록이 폭증한다면 MySQL 서버의 사용자 쿼리 처리 성능에 영향을 주는 경우가 많았습니다.

InnoDB 스토리지 엔진은 버퍼 풀에서 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 '2개의 플러시 기능'을 백그라운드로 실행합니다.

Flush_list 플러시
LRU-list 플러시

2.7.4.1 플러시 리스트 플러시

InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 합니다.

그러기 위해서는 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 합니다.

이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화하는 작업을 수행합니다.

여기서 사용되는 시스템 변수들은 다음과 같습니다.

innodb_page_cleaners: 클리너 스레드(Cleaner Thread) 개수를 조정
innodb_max_dirty_pages_pct_lwm: 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록
innodb_max_dirty_pages_pct: 더티 페이지의 비율을 조정
innodb_io_capacity: 일반적인 상황에서 디스크가 적절히 처리할 수 있는 수준의 값
innodb_io_capacity_max: 디스크가 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정
innodb_flush_neighbors: 더티 페이지를 디스크에 기록할 때 디스크에 근접한 페이지 중에서 더티 페이지가 있다면 묶어서 디스크로 기록해주는 기능
innodb_adaptive_flushing: 버퍼 풀에 적절한 수준의 더티 페이지가 유지될 수 있도록 설정하는 비율
innodb_adaptive_flushing_lwm

클리너 스레드(Cleaner Thread)는 InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드입니다.

하나의 클리너 스레드가 여러 버퍼 풀 인스턴스를 처리하던가, 아니면 하나의 버퍼 풀 인스턴스를 처리하도록 동작합니다.

서버의 트래픽을 봐 가면서 innodb_io_capacity와 innodb_io_capacity_max를 설정하는 것은 상당히 번거로운 일입니다.

그래서 InnoDB 스토리지 엔진은 어댑티브 플러시(Adaptive flush)라는 기능을 제공합니다.

어댑티브 플러시 기능이 활성화 되면 InnoDB 스토리지 엔진은 단순히 버퍼 풀의 더티 페이지 비율이나 innodb_io_capacity, innodb_io_capacity_max 설정값에 의존하지 않고 새로운 알고리즘을 사용합니다.

더티 페이지를 어느 정도 디스크로 기록해야 할지는 사실 어느 정도 속도로 더티 페이지가 생성되는지를 분석하는 것인데, 이는 결국 리두 로그가 어느 정도 속도로 증가하는 지를 분석하는 것과 같습니다.

그래서 어탭티브 플러시 알고리즘은 '리두 로그의 증가 속도를 분석'해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행합니다.

2.7.4.2 LRU 리스트 플러시

InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데, 이를 위해 LRU 리스틐(LRU_list) 플러시 함수가 사용됩니다.

InnoDB 스토리지 엔진은 LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지를 스캔합니다.

InnoDB 스토리지 엔진은 이때 스캔하면서 더티 페이지는 디스크에 동기화하게 되며, 클린 페이지는 즉시 '프리(Free) 리스트'로 페이지를 옮깁니다.

2.7.5 버퍼 풀 상태 백업 및 복구

InnoDB 서버의 버퍼 풀은 쿼리의 성능을 높이기 위해서 쿼리들이 사용할 데이터를 준비하는 기능을 가지고 있습니다.

디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업(Warming Up)이라고 표현하는데, 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보이는 것이 일반적입니다.

MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입되어, MySQL 서버를 재시작해야 하는 경우, 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있습니다.

-- // MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

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

InnoDB 버퍼 풀의 백업은 데이터 디렉터리에 ib_buffer_pool이라는 이름의 파일로 생성되고, 버퍼 풀의 LRU 리스트에서 적재된 페이지의 메타 정보만 가져와서 저장합니다.

그래서 버펖 풀의 백업은 매우 빨리 완료됩니다.

하지만 백업된 버퍼 풀의 내용을 다시 버퍼 풀로 복구하는 과정은 InnoDB 버퍼 풀의 크기에 따라 상당한 시간이 걸릴 수도 있습니다.

버퍼 풀 복구 도중에 급히 서비스를 시작해야 한다면 다음과 같이 버퍼 풀 복구를 멈출 것을 권장합니다.

mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;

2.7.6 버퍼 풀의 적재 내용 확인

InnoDB 버퍼 풀이 큰 경우에는 버퍼 풀의 페이지들을 조회하는 것이 상당히 큰 부하를 일으킬 수 있으며 이것은 서비스 쿼리의 성능 저하를 유발할 수 있습니다.

이러한 문제점을 해결하기 위해 MySQL 8.0 버전에서는 information_schema 데이터베이스에 innodb_cached_indexes 테이블이 새로 추가됐습니다.

이 테이블을 이용하면 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있습니다.

2.8 Double Write Buffer

InnoDB 스토리지 엔진의 리두 로그는 '페이지의 변경된 내용만' 기록한다고 했습니다.

이로 인해 플러시 과정에서 일부만 기록되는 문제가 발생하면 그 페이지의 내용을 복구할 수 없을 수도 있습니다.

이렇게 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 하는데, 이런 현상은 하드웨어 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있습니다.

InnoDB 스토리지 엔진에서는 이 같은 문제를 막기 위해 Double-Write 기법을 이용합니다.

위의 예에서는 InnoDB 스토리지 엔진이 실제 데이터 파일에 변경 내용을 기록하기 전에 'A' ~ 'E'까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록합니다.

그리고 InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행합니다.

데이터 파일에 기록하는 도중에 비정상적으로 종료가 되었을 때, InnoDB 스토리지 엔진은 재시작할 때 항상 DoubleWrite 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있는지 확인합니다.

있다면 DoubleWrite 버퍼의 내용을 데이터 파일로 복사합니다.

2.9 언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업합니다.

이렇게 백업된 데이터를 언두 로그(Undo Log)라고 합니다.

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

2.9.1 언두 로그 모니터링

언두 로그의 데이터는 크게 두 가지 용도로 사용되는데,

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

롤백 대비용이라는 것은 트랜잭션이 이루어지는 과정에서 Commit까지 성공한 경우 변경된 내용을 유지하면 되지만, 실패했을 경우에 롤백을 해줘야 하는데 이러한 상황에서 언두 로그 데이터가 활용됩니다.

높은 동시성을 제공하기 위해 특정 커넥션에서 데이터를 변경하는 도중이더라도 격리 수준에 맞춰서 데이터를 읽을 수 있는 기능을 제공합니다.

대용량의 데이터를 처리하는 트랜잭션 혹은 트랜잭션이 오랜 시간 동안 실행될 때 언두 로그의 양은 급격히 증가할 수 있습니다.

트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아닙니다.

위의 그림과 같이 동작을 하는 3개의 트랜잭션이 있다고 했을 때, 먼저 시작된 A 트랜잭션이 아직 활성 상태 이기 떄문에 B와 C 트랜잭션의 완려 여부와 관계없이 B와 C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않습니다.

언두 로그의 데이터가 많아 지게 되면 언두 로그 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어지게 됩니다.

따라서, 사용 공간을 관리하는 것이 중요한데 MySQL 5.5 버전까지는 언두 로그의 상요 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하지 않는 한 줄일 수가 없었습니다.

언두 로그가 늘어나면 디스크 사용량뿐만 아니라 매번 백업할 때도 그만큼 더 복사를 해야 하는 문제점이 발생합니다.

MySQL 5.7과 MySQL 8.0으로 업그레이드되면서 언두 로그 공간의 문제점은 완전히 해결되었습니다.

MySQL 8.0에서는 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이는 것도 가능하며, 때로는 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여 주기도 합니다.

MySQL 서버에서 언두 로그 건수를 확인하고 싶다면 다음과 같이 하면 됩니다.

-- // UPDATE와 DELETE 문장으로 인한 언두 로그 개수 표시
mysql> SHOW ENGINE INNODB STATUS \G

MySQL 서버에서 INSERT 문장으로 인한 언두 로그와 UPDATE(DELETE 포함) 문장으로 인한 언두 로그는 '별도로' 관리됩니다. UPDATE와 DELETE 문장으로 인한 언두 로그는 MVCC와 데이터 복구(롤백 포함)에 모두 사용되지만, INSERT 문장으로 인한 언두 로그는 MVCC를 위해서는 사용되지 않고 롤백이나 데이터 복구만을 위해서 사용되기 때문입니다.

2.9.2 언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 합니다.

MySQL 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됐스빈다. 하지만 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있습니다.

그래서 MySQL 5.6 버전에서는 innodb_undo_tablespaces 시스템 변수가 도입됐고,innodb_undo_tablespaces 시스템 변수를 2보다 큰 값을 설정하면 InnoDB 스토리지 엔진은 더이상 언두 로그를 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용합니다.

MySQL 8.0으로 업그레이드 되면서 언두 로그는 '항상' 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선되어서 해당 시스템 변수는 Deprecated 되었습니다.

아래 그림은 언두 테이블스페이스가 어떤 형태로 구성되는지를 보여줍니다.

하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬록(Undo Slot)을 가집니다.

하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나누 값의 개수만큼의 언두 슬롯을 가집니다.

이를 통해 최대 동시 처리 가능한 트랜잭션의 개수를 수식으로 예측해볼 수 있습니다.

최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트) * (언두 테이블스페이스 개수)

퍼지 스레드는 주기적으로 언두 로그 파일에서 사용되지 않는 공간을 잘라내고 운영체제로 반납하는 기능을 수행하며, 자동 모드와 '수동 모드' 두 가지 방식을 사용하여 공간을 관리할 수 있습니다.

2.10 체인지 버퍼

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다.

그런데 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 상당한 오버헤드를 발생시킵니다.

이를 위해서 임시 메모리 공간인 체인지 버퍼(Change Buffer)를 사용합니다.

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있다면 바로 업데이트를 수행하지만 그렇지 않고 '디스크로부터' 읽어와서 업데이트를 해야한다면 이를 즉시 실행하지 않고 해당 공간을 활용합니다.

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

MySQL 8.0에서는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서 버퍼링을 할 수 있게 해줍니다.

innodb_change_buffer를 잉요하여 활성화할 수 있고, 해당 변수에 설정할 수 있는 값은 다음과 같습니다.

all: 모든 인덱스 관련 작업(inserts + deletes + purges)을 버퍼링
none: 버퍼링 안함
inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼이
deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
changes: 인덱스에서 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)

2.11 리두 로그 및 로그 버퍼

MySQL 서버를 포함한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록합니다.

거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요합니다.

그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요합니다.

이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구합니다.

데이터 파일과 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료구조도 가지고 있습니다.

MySQL 서버가 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 다음과 같은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있습니다.

커밋됐지만 데이터 파일에 기록되지 않은 데이터
롤백됐지만 데이터 파일에 이미 기록된 데이터

1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됩니다.

2번의 경우 언두 로그의 내용을 가져와서 복사를 하고, 리두 로그를 이용해 트랜잭션의 상태를 확인할 수 있습니다.

InnoDB 스토리지 엔진에서 리두 로그를 어느 주기로 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공합니다.

innodb_flush_log_at_trx_commit = 0: 1초에 한 번씩 리두 로그를 디스크로 write하고 동기화를 실행합니다.
- 서버가 비정상 종료되면 최대 1초 동안의 트랜잭션은 커밋됐다고 하더라도 해당 트랜잭션에서 변경한 데이터는 사라질 수 있습니다.
innodb_flush_log_at_trx_commit = 1: 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행됩니다. 그래서 트랜잭션이 일단 커밋되면 해당 트랜잭션에서 변경한 데이터는 사라집니다.
innodb_flush_log_at_trx_commit = 2: 매번 트랜잭션이 커밋될 때마다 디스크로 write은 되지만 실질적인 동기화는 1초에 한 번씩 실행됩니다.
- 일단 트랜잭션이 커밋되면 변경 내용이 운영체제의 메모리 버퍼로 기록되는 것이 보장됩니다.
- 그래서 MySQL 서버가 비정상 종료됐더라도 운영체제가 정상적으로 작동한다면 해당 트랜잭션의 데이터는 사라지지 않습니다.

마지막으로 InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기가 버퍼 풀의 효율성을 결정한다고 앞서 말했습니다.

이를 위해 다음과 같은 시스템 변수를 설정할 수 있습니다.

innodb_log_file_size: 리두 로그 파일의 크기 설정
innodb_log_files_in_group: 리두 로그 파일의 개수를 결정

2.11.1 리두 로그 아카이빙

리두 로그 아카이빙(Redo Log Archiving)의 개념은 데이터베이스의 지속적인 변경사항을 추적하고 보존하는 과정을 의미합니다.

중요한 포인트는 리두 로그의 내용이 실시간으로 변할 수 있다는 것과, 이러한 변화를 안정적으로 처리하고 보존하는 매커니즘의 필요성입니다.

리두 로그의 위치와 목적지

스토리지 엔진에 쌓이는 리두 로그: 일반적으로 리두 로그는 먼저 메모리 상의 리두 로그 버퍼에 기록됩니다. 이는 트랜잭션의 변경사항을 빠르게 캡처하기 위함입니다.
리두 로그 엔트리의 복사 목적지: 리두 로그 아카이빙 과정에서, 메모리 상의 리두 로그 버퍼에서 디스크 상의 리두 로그 파일로 변경사항이 flush되는 동안, 새로 발생하는 변경사항(리두 로그 엔트리) 역시 추적되어야 합니다.
- 이러한 새로운 리두 로그 엔트리는 디스크에 위치하는 리두 로그 파일에 추가되며, 필요한 경우 별도의 아카이브 로그 파일로도 복사될 수 있습니다.

리두 로그 아카이빙의 목적

리두 로그 아카이빙의 주된 목적은 데이터베이스 백업 중에도 시스템의 변경사항을 계속 추적하고 기록하는 것입니다.

일관성 보장: 데이터베이스의 백업 작업이 진행되는 동안, 새로운 트랜잭션에 의해 데이터베이스 상태가 '계속 변경'될 수 있습니다. 리두 로그 아카이빙을 통해 이러한 변경사항을 추적하고 기록함으로써, 백업이 시작된 시점부터 완료된 시점까지의 '모든 변경사항'을 포착하고, 백업이 완료된 이후에도 이 변경사항을 적용할 수 있게 합니다.

사실 리두 로그 아카이빙의 필요성에 대해서는 아직 명확히 감이 오지 않습니다. 리두 로그 파일만을 이용해도 LSN의 기능이 있어서 순차적으로 적용이 되어 문제없이 동작할 것 같지만, 책에서는 리두 로그가 매우 빠르게 증가되게 되면 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰이는 문제가 발생한다고 합니다.

이는 나중에 한번 더 생각해 볼 내용인 것 같습니다.

2.11.2 리두 로그 활성화 및 비활성화

리두 로그는 MySQL 서버가 비정상정그올 종료됐을 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화되어 있습니다.

MySQL 8.0부터는 이것을 수동으로 비활성할 수 있도록 되었습니다. 그래서 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 다음과 같이 리두 로그를 비활성화해서 데이터 적재 시간을 단축시킬 수 있습니다.

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

--// 리두 로그를 비활성화한 후 대량 데이터 적재를 실행
mysql> LOAD DATA ...

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

2.12 어댑티브 해시 인덱스

일반적으로 '인덱스'라고 하면 이는 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미합니다.

여기서 언급하는 '어댑티브 해시 인덱스(Adaptive Hash Index)'는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 '자동으로 생성'하는 인덱스입니다.

B-Tree 인덱스에서 특정 값을 찾기 위해서는 B-Tree의 루트 노드를 거쳐서 브랜치 노드, 그리고 최종적으로 리프 노드까지 찾아가야 원하는 레코드를 얻을 수 있습니다.

만약 이러한 작업을 동시에 몇천 개의 스레드로 실행하면 컴퓨터의 CPU는 엄청난 프로세스 스케줄링을 하게 되고 자연히 쿼리의 성능은 떨어집니다.

어댑티브 해시 인덱스는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이빈다.

InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 '해시 인덱스'를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있습니다.

해시 인덱스는 '인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데, 인덱스 키 값은 다음의 조합으로 생겨납니다.

B-Tree 인덱스의 고유번호(Id)
B-Tree 인덱스의 실제 키 값

어댑티브 해시 인덱스의 키 값에 'B-Tree 인덱스의 고유번호'가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 '하나만 존재'하기 때문입니다.

그리고 '데이터 페이지 주소'는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미합니다.

그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라집니다.

이것을 사용하면 B-Tree의 루트 노드로부터 검색이 줄면서 InnoDB 내부 잠금(세마포어)의 횟수도 획기적으로 줄어듭니다.

어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로 경합(Contention)이 상당히 심합니다.

그래서 MySQL 8.0부터는 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공합니다.

innodb_adaptive_hash_index_parts 시스템 변수를 이용해 파티션 개수를 변경할 수 있고, 기본값은 8개입니다.

해당 기능은 매우 좋아보이지만, 실제 어댑티브 해시 인덱스를 의도적으로 비활성화 하는 경우도 많습니다.

다음과 같은 경우 성능 향상에 해당 기능이 도움되지 않습니다.

디스크 읽기가 많은 경우
특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

그리고 다음과 같은 경우에는 성능 향상에 많은 도움이 됩니다.

디스크의 데이터가 InnoDB 버퍼 풀 크기가 비슷한 경우(디스크 읽기가 많지 않은 경우)
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

해당 기능은 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 '디스크에서 읽어오는 경우'가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않습니다.

또한, 다음과 같은 단점을 가지고 있습니다.

때로는 상당한 큰 메모리 공간을 사용
InnoDB 스토리지 엔진은 그 키 값이 해시 인덱스에 있든 없든 검색해야 한다.
테이블을 삭제하거나 변경하려고 하면 InnoDB 스토리지 엔진은 이 테이블을 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스 에서 제거

만약, 해당 기능을 사용 중인 상태에서 통계를 보고 싶다면 다음과 같이 명령어를 입력해보자.

mysql> SHOW ENGINE INNODB STATUS\G

어댑티브 해시 인덱스가 사용중인 메모리 사용량이 높다면 어댑티브 해시 인덱스를 비활성화해서 InnoDB 버퍼 풀이 더 많은 메모리를 사용할 수 있게 유도하는 것도 좋은 방법입니다.

2.13에서는 MyISAM과 MEMORY 스토리지 엔진에 대한 설명이 나오고 있는데, 해당 엔진들은 현재는 잘 사용되지 않으므로 넘어가도록 하겠습니다.

4 에서는 로그 파일에 대한 내용을 다룹니다. 해당 부분은 나중에 필요성을 느낄 때 정리를 하도록 하겠습니다.

참고한 자료

운영체제[공룡책]

서정범

개발정리블로그

이전 포스트