InnoDB 스토리지 엔진의 구조를 파헤쳐보자

MySQL에는 여러 스토리지 엔진이 있지만 그중 InnoDB 스토리지 엔진이 기본값으로 설정되어 있다.

그렇다면 왜 기본값으로 설정되어있는지 InnoDB의 특징에 대해 한번 알아보자.

 

클러스터링 인덱스와 논 클러스터리(세컨더리) 인덱스 구조

대표적인 스토리지 엔진 중 MyISAM과 MEMORY 스토리지 엔진은 클러스터링 인덱스를 지원하지 않지만

InnoDB에서 데이터는 기본적으로 PK를 기준으로 클러스터링되어 테이블에 저장되고

논 클러스터링 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용한다.

InnoDB의 인덱스 구조

그림을 보면 PK 주소값을 가지고 클러스터링 인덱스의 루트 노드부터 리프 노드까지 인덱스 키의 대소비교를 통해

원하는 PK값이 존재하는 리프 노드까지 이동하여 찾는다.

 

반면 MyISAM의 경우에는 아래와 같이 논 클러스터링 인덱스의 리프 노드가 물리적 데이터의 실제 주소 값을 갖는다.

MyISAM의 인덱스 구조

InnoDB는 왜 클러스터링 인덱스를 추가해 사용하는걸까?

클러스터링 인덱스는 데이터를 물리적으로 한 가지 순서만을 유지해 저장하도록 정의한다.

예를 들어, 주문번호로 클러스터링된 테이블에서는 모든 데이터 레코드가 주문번호 순서대로 물리적으로 저장된다.

만약, 동시에 고객ID로도 클러스터링하려 한다면, 같은 데이터를 두 가지 다른 순서로 재 정렬해 기존의 주문 번호 순서를 깨트릴 수 있다.

이러한 물리적 제약으로 인해 테이블당 하나의 클러스터링 인덱스만 가질 수 있고

하나의 정렬 순서만을 유지함으로써, 연속된 데이터 접근 시 순차적 I/O가 가능해져 성능이 크게 향상된다.

 

논 클러스터링(세컨더리) 인덱스는 데이터 검색을 위한 '포인터' 역할을 한다.

두 인덱스 모두 정렬되어 있지만, 세컨더리 인덱스는 실제 데이터를 직접 포함하지 않고 PK를 통해 간접적으로 데이터에 접근하는 방식을 사용한다.

리프 노드에는 인덱스 키 값과 해당 레코드의 PK만을 저장하는데 이는 저장 공간을 효율적으로 사용할 수 있게 된다.

또 여러 정렬 기준이 필요하다면 복합 인덱스로 정의할수도 있고 찾고자하는 데이터는 결국 PK 주소를 바라본다.

이렇게 클러스터링 인덱스는 인덱스 키 값에 해당하는 모든 테이블 데이터를 포함하고 관리하고,

논 클러스터링 인덱스는 PK의 주소값만 가짐으로써 저장 공간을 효율적으로 사용하고 데이터 일관성도 유지할 수 있게 된다.

 

InnoDB 클러스터링 인덱스의 장단점

이렇게만 보면 클러스터링 인덱스는 무적인 것 같다.

물론 장단점이 존재한다.

장점

• 클러스터링 인덱스는 실제 데이터가 인덱스 키 순서대로 물리적으로 정렬되어 저장되기 때문에, 범위 검색 시 연속된 데이터를 빠르게 읽을 수 있다.
• 세컨더리 인덱스가 PK를 참조하므로 커버링 인덱스를 사용하면 인덱스만으로 빠르게 처리할 수 있다.
• 세컨더리 인덱스가 클러스터링 키를 참조하므로, 데이터 접근의 일관성이 보장된다.

 

단점

• 세컨더리 인덱스가 PK를 갖기 때문에 PK 크기가 크다면 전체적으로 인덱스의 크기가 커진다.
• 세컨더리 인덱스를 통해 검색할 때 PK로 다시 검색을 해야함으로 처리 성능이 상대적으로 느리다.
• 데이터 추가시 PK에 의해 레코드의 저장 위치가 결정되어 처리 성능이 느리다.
• PK를 변경하면 B-Tree 특성상 레코드를 DELETE하고 INSERT하기 때문에 처리 성능이 느리다.

 

왜 InnoDB 스토리지 엔진은 세컨더리 인덱스가 클러스터 인덱스를 한번 더 거치도록 설계했을까?

클러스터링 인덱스는 모든 레코드에 대해 B-Tree 구조로 관리 하고

세컨더리 인덱스는 인덱스를 건 해당 컬럼에 대한 레코드만 B-Tree 구조로 관리한다.

레코드의 값이 변경된다면 B-Tree에서는 항상 정렬된 상태를 유지하기 위해 삭제 -> 삽입 과정이 일어난다는 점은 같지만

클러스터링 인덱스는 리프 노드가 실제 데이터 페이지로 인덱스가 적용된 컬럼이 아닌 컬럼의 변경에 대해서도 리프 노드를 수정한다.

 

또한 세컨더리 인덱스는 클러스터링 인덱스의 주소값을 참조하기 때문에 클러스터링 인덱스가 변경되던말든 상관없다.

반면 클러스터링 인덱스 값 자체가 변경(PK 변경)이 된다면 클러스터링 인덱스의 레코드 위치를 정렬된 상태로 변경(삭제 -> 삽입)하고

해당 클러스터링 인덱스를 참조하는 모든 세컨더리 인덱스의 주소값을 변경하게 된다.

 

만약 세컨더리 인덱스가 MyISAM처럼 실제 레코드가 저장된 주소를 갖게 되면 레코드의 주소값이 변경될 때마다

해당 주소값을 참조하는 모든 세컨더리 인덱스가 물리적인 주소값을 변경해야하는 오버헤드가 발생할 수 있다는 것이다.

반면 PK 값 자체를 주소값으로 지정하게 되면 레코드이 주소값이 변경되더라도 PK를 참조하기 때문에 영향이 없다.

 

또한 InnoDB는 데이터를 페이지 단위로 관리하기 때문에 '페이지 분할/병합'이 자주 발생한다.

따라서 실제 레코드가 저장된 주소를 갖게된다면 레코드가 저장된 페이지 위치가 자주 변경될 때마다

모든 세컨더리 인덱스의 참조 주소값을 수정해야하기 때문에 이러한 방식을 채택한 것 같다.

 

페이지 분할 병합이란?

페이지 분할은 인덱스 페이지가 너무 커져서 더 이상 새로운 데이터를 저장할 수 없을 때 발생하며

하나의 페이지를 두 개의 페이지로 나누는 작업이다.

반대로 페이지 병합은 인덱스 페이지의 데이터가 너무 적어질 때 발생하며 데이터가 적은 두 개의 페이지를 하나로 합치는 작업이다.

이 분할/병합은 B-tree 인덱스의 균형을 유지해주기 위한 작업이다.

 

왜 MyISAM과 InnoDB의 인덱스 구조가 다를까 ?

MyISAM 스토리지 엔진은 빠른 읽기 목적으로 단순한 구조를 갖는 반면 ACID를 보장해주지 못한다.

인덱스가 실제 레코드가 저장된 주소를 갖고 정렬 기준 없이

INSERT되는 순서대로 데이터 페이지에 저장되어 힙 공간처럼 활용된다.

 

InnoDB는 대용량 트래픽에서의 동시성 문제를 안전하게 처리해주는 목적으로 ACID를 보장해준다.

클러스터링 인덱스가 순서데로 정렬된 데이터 페이지를 관리한다.

 

즉, 각각의 목적에 맞게 MyISAM은 빠른 읽기를 위한 단순한 인덱스 구조

InnoDB는 unique한 PK를 둠으로써 데이터의 유일성 보장하고 외래키의 기준값으로 PK를 사용해

RDB의 참조 무결성을 보장해주기 위한 인덱스 구조라고 생각한다.

 

PK는 신중히…

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 PK 값을 포함한다.

그래서 PK의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

인덱스 크기가 커질수록 랜덤 I/O가 증가하고, 버퍼 풀이 큰 데이터를 다 담지 못해 메모리도 더 필요해질 것이다.

따라서 PK의 크기도 신중하게 선택해야한다.

 

아마 AUTO_INCREMENT로 INT나 BIGINT 타입의 PK을 만드는 것이

다른 데이터 타입보다 상대적으로 크기가 작고 변함없이 고유하게 식별가능한 값이라 주로 사용된다고 생각한다.

 

또한 테이블에 PK를 생성하지 않는 경우도 조금씩 있는 것 같다.

PK가 없다면 어차피 InnoDB 스토리지 엔진이 내부적으로 일련번호 컬럼을 추가하지만 이 컬럼은 보이지 않기 때문에 사용할수가 없다.

즉, PK를 정의하나 안하나 똑같지만 사용할 수 있냐에 대해서는 차이가 있기 때문에 사용자가 직접 사용할 수 있도록 PK를 정의하는 것이 좋.

또한 복제 기본 값인 ROW 기반 복제나 InnoDB 클러스터에서는 모든 테이블이 PK를 가져야만 정상적인 복제 성능을 보장한다고 한다.

 

외래키 지원

외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며

외래키 제약 조건이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다.

외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

특히 데이터를 삭제할 때 연관관계가 걸려있는 엔티티들로 인해 불편함을 겪던적이 다들 한번씩을 있을 것이다.

다음과 같이 외래키 제약조건을 ON/OFF하여 해결할 수도 있다.

-- MySQL 서버 명령어
-- 현재 세션에만 적용할 것이라면 SET SESSION 명령어를 사용
SET foreingn_key_checks=OFF
SET foreingn_key_checks=ON

-- JPA 엔티티 매니저로 적용하기
entityManager.createNativeQuery(SET FOREIGN_KEY_CHECKS = 0).executeUpdate(); // OFF
entityManager.createNativeQuery(SET FOREIGN_KEY_CHECKS = 1).executeUpdate(); // ON

 

DB에 외래 키를 걸게 되면 잠금 경합까지 설계하는 것이 중요한데

잠금이 발생하는 경우는 다음과 같이 외래키와 연관된 컬럼의 데이터 변경 작업을 수행할 때이므로 주의하자.

• 자식 테이블에 새로운 데이터를 삽입할 때 (부모 테이블의 참조키 존재 여부 확인)
• 부모 테이블의 참조키를 수정하거나 삭제할 때 (자식 테이블의 참조 무결성 검사)
• 외래키와 연관된 컬럼이 아닌 경우에는 잠금이 발생하지 않는다.

 

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해

잠금 대기 목록을 그래프(Wait-For-List)형태로 관리한다.

 

자동 데드락 감지 순서

자동 데드락 감지는 다음과 같은 순서로 교착 상태에 빠진 트랜잭션을 찾고 종료시킨다.

1. 대기 중인 트랜잭션들의 잠금 대기 목록 그래프 생성
2. 주기적으로 그래프를 탐색하여 교착 상태 확인
3. 데드락 발견 시 롤백할 트랜잭션을 선택하고 해당 트랜잭션을 롤백

롤백할 트랜잭션을 선택하는 기준은 언두 로그의 양을 적게 가진 트랜잭션이 된다.

이는 롤백을 해도 언두 처리를 덜 해도되서 MySQL 서버 부하를 덜 유발하기 때문이라고 한다.

 

롤백 대상이된 트랜잭션의 Lock을 해재하고 나면 MySQL 서버에선 다음과 같은 에러를 응답한다.

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

만약 재시도가 필요하다면 이 에러를 잡아 재시도 로직을 구현할수도 있을 것이다.

 

이 자동 데드락 감지는 inoodb_deadlock_detect 시스템 변수로 활성화/비활성화할 수 있다. (Default = ON)

 

자동 데드락 감지가 문제가 되는 상황

동시성 처리가 매우많거나 트랜잭션의 Lock 개수가 많아지면 데드락 감지 스레드가 느려지고

다른 MySQL 스레드에도 악영향을 끼쳐 많은 CPU 자원을 소모한다고 한다.

 

실제로 구글의 경우 높은 동시성이 요구되 inoodb_deadlock_detect를 비활성화해

데드락 감지 스레드를 사용하지 않음으로 성능향상했다고 한다.

 

만약 자동 데드락 감지를 비활성화한다면 innodb_lock_wait_timeout 시스템 변수의 기본값인 50초보다 낮춰

Lock 대기 시간을 적절히 조절하는 것이 좋다.

 

MVCC (Multi Version Concurrency Control)

하나의 레코드에 대해 다양한 버전을 동시에 관리하는 기술이다.

Lock을 사용하지 않아 동시성 상황에서도 빠르게 일관된 데이터를 읽을 수 있도록 해준다.

 

그렇다면 어떻게 '잠금 없이 일관된 읽기'가 가능한것일까?

바로 '언두 로그'를 활용하기 때문에 가능하다.

 

언두 로그란?

트랜잭션의 롤백과 MVCC를 지원하기 위해 데이터 변경 이전의 상태를 기록해두는 로그다.

 

MVCC는 트랜잭션 격리 수준에 따라 언두 로그를 활용하여 잠금 없는 일괄된 읽기를 제공한다.

트랜잭션 격리 수준에 따라 다르게 활용하는 점에 대해서는 아래 포스팅에서 설명한다.

2024.12.01 - [◼ DB] - MySQL의 트랜잭션과 격리 수준 이해하기

 

InnoDB는 MVCC로 잠금이 없는 상황에서도 언두 로그를 활용하여 일관성을 보장해주지만

이 언두 로그는 트랜잭션이 활성상태일 때 계속 사용되고 종료되면 제거된다.

트랜잭션 지속시간이 길어진다면 계속해서 언두 로그가 쌓일 것이고 커진 언두 로그를 뒤지게 된다면 성능 또한 좋지 않아진다.

 

따라서 트랜잭션을 가능한 짧게 유지해주는 것이 중요하다.

 

Buffer pool (버퍼 풀)

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

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

쓰기 작업을 지연시켜 일괄 작업으로 처리해 디스크 I/O를 줄일 수 있게 해주고

자주 조회 되는 데이터를 캐시해 재 조회시에 버퍼 풀에 있는 데이터를 읽어 랜덤 I/O를 최소화 해주는 역할을 한다. 

 

버퍼 풀의 구조

메모리 공간을 Innodb_page_size 변수 만큼 쪼개어 페이지 단위로 데이터를 관리한다.

그리고 이 페이지 단위의 데이터들을 관리하기 위해 다음과 같이 3가지 자료구조를 사용해 관리한다.

 

LRU(Least Recently Used) 리스트

가장 최근에 많이 사용되지 않은 데이터를 제거하는 방식의 알고리즘으로 

다음과 같이 New 영역인 MRU(Most Recently Used)과 Old 영역인 LRU(Least Recently Used)으로 나뉜다.

만약 새로운 페이지가 추가된다면 중간 지점(기본값: LRU 리스트의 3/8 지점)에 추가되고

이 때 List가 꽉 차있다면 LRU 끝부분부터 페이지를 제거한다.

반대로 LRU에 있는 페이지가 재접근된다면 MRU의 헤더로 이동하는데 재접근마다 다 이동하면 버퍼 풀이 오염될 위험이 있다.

따라서 innodb_old_blocks_time(기본값 : 1000ms)초 이후 재접근될 때 MRU로 이동한다.

 

왜 innodb_old_blocks_time 내에 접근하는게 더 자주 되는 접근일 텐데 이후에 재접근될 때 MRU로 이동하냐?가 궁금할 수 있다.

중요한점은 특정 데이터를 개별적으로 관리하는 것이 아닌 페이지로 관리한다는 것이다.

풀 스캔처럼 일시적으로 많은 접근이 발생하는 경우는 1초 이내에 접근이 몰리고 그 후에는 접근이 없다면 버퍼 풀을 오염시킬 수 있다.

따라서 "진짜로" 자주 사용되는 데이터라면 1초 후에도 계속 접근이 발생할 것이고, 이때 MRU로 이동하여 캐시 히트율을 높일 수 있다.

 

플러시 리스트

디스크에 아직 기록되지 않은 더티 페이지(버퍼 풀의 데이터와 디스크의 데이터가 다른 상태의 페이지)를 관리한다.

이는 변경된 시점 순서대로 정렬되어 있다.

한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 추가되고, 리두 로그 및 버퍼 풀의 더티 페이지에도 변경 사항이 반영되고

디스크 쓰기 후 제거된다.

 

프리 리스트

버퍼 풀에서 아직 사용되지 않은 비어있는 페이지들을 관리하여 버퍼 풀의 효율적인 페이지 재사용을 가능하게 해준다.

새로운 데이터를 버퍼 풀에 적재할 때 프리 리스트에서 페이지를 가져오고, 할당된 페이지는 프리 리스트에서 제거되고 LRU 리스트로 이동한다.

더 이상 프리 리스트에 공간이 없다면 LRU 리스트 끝부분의 페이지를 제거하여 공간을 확보하고 

LRU 리스트 끝부분의 페이지가 제거될 때는 해당 페이지가 프리 리스트로 이동한다.

 

이 3개의 리스트는 다음과 같이 서로 연계된다.

 

- SELECT 쿼리로 새로운 데이터를 읽을 때.

SELECT * FROM users WHERE id = 1;

1. 프리 리스트에서 빈 페이지를 가져옴
2. 디스크에서 데이터를 읽어서 이 페이지에 저장
3. 해당 페이지를 LRU 리스트의 Old 영역에 추가

 

- UPDATE 쿼리로 데이터를 변경할 때

UPDATE users SET name = 'kaki' WHERE id = 1;

1. LRU 리스트에서 해당 페이지를 찾음
2. 페이지의 데이터를 변경
3. 변경된 페이지(더티 페이지)를 플러시 리스트에 추가
4. 이 페이지는 LRU 리스트에서 유지

 

리두 로그

버퍼 풀과 리두 로그는 상당한 연관 관계를 갖고 있다.

 

리두 로그는 다음과 같은 역할을 한다.

• LSN(Log Sequence Number)으로 데이터 변경 사항을 순차적으로 기록
• 트랜잭션의 영구 보존을 위한 로그 (리두 로그에 백업된 데이터 복원)
• DML, DDL, TCL 작업 등 DATA 변경이 일어나는 모든 것을 기록
• 장애 발생 시 데이터 복구를 보장
• 모든 변경 사항을 자세하게 기록하는 일지와 같음

더티 페이지는 플러시 리스트에서 관뢰되고

디스크와 메모리의 데이터 상태가 달라 언젠간 디스크로 기록되어야 하지만

메모리 특성상 메모리 부족이나 서버 종료 시에 휘발될 수 있어 버퍼 풀에 영구적으로 저장되진 않는다.

 

하지만 리두 로그는 모든 변경 사항들을 순차적으로 디스크에 기록한다.

따라서 버퍼 풀의 더티 페이지가 디스크에 기록되기 전에 문제가 발생하더라도 안전하게 데이터를 복구하게 해주는 안전장치 역할을 한다.

 

리두 로그 작동 방식

리두 로그는 WAL(Write-Ahead Logging)으로 작동하고 로그 버퍼를 활용해 디스크 I/O를 최소화 한다.

1. 페이지의 데이터가 변경
2. 변경사항을 로그 버퍼에 기록
3. 리두 로그 파일 기록 조건에 해당하면 한 번에 리드 로그 파일(디스크)에 기록

이 리두 로그 파일 기록 조건은 트랜잭션 커밋, 로그 버퍼가 절반 이상 찼을 때 등이 될 수 있다.

 

또한 리두 로그는 LSN을 사용하여 항상 최신의 데이터를 보장해준다.

만약 특정 데이터를 조회 시에 LSN 번호가 90이고 해당 데이터에 대한 리두 로그의 LSN 번호가 100이라면

90 ~ 100 사이의 리두 로그를 적용하여 최신 상태의 LSN 번호 100에 대한 데이터를 반환해준다.

 

로그 버퍼

변경 작업이 빈번한 DBMS의 경우 리두 로그의 기록이 커져 문제가 될 수 있는데

이런 점을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 변경된 데이터 정보를 버퍼링하는 공간이 로그 버퍼이다.

 

로그 버퍼의 크기는 기본값인 16MB 수준에서 설정하는 것이 적합한데

BLOB이나 TEXT 같이 큰 데이터를 자주 변경할 경우 더 크게 설정하는 것이 좋다고 한다.

 

리두 로그 활성화/비활성화

대용량 데이터를 복구하거나 한번에 적재할 경우 다음과 같이 리두 로그를 비활성화해 데이터 적재 시간을 단축시킬 수 있다.

-- 리두 로그 비활성화
ALTER INSTANCE DISABLE INNODB REDO_LOG;

-- 리두 로그 활성화
ALTER INSTANCE ENABLE INNODB REDO_LOG;

데이터의 지속성과 원자성을 보장하기 위해 다시 활성화 시켜주는 것을 잊지 말자.

 

버퍼 풀의 크기 설정

innodb_buffer_pool_size 시스템 변수로 크기를 동적으로 설정할 수 있다.

따로 설정하지 않았다면 기본적으로 128 MB로 할당된다.

Byte로 표현된 Value

 

이 버퍼 풀의 크기는 시스템 메모리로 부터 할당받는다.

따라서 버퍼 풀 크기의 조절이 필요하다면 너무 큰 메모리를 할당하는 것은 서버 자체에 부담을 주고 OOM까지 발생할 수 있다.

정확한 버퍼 풀 사이즈를 처음부터 설정하긴 무리가 있어 시스템 메모리의 10%에서 점차 동적으로 늘려가면서

시스템 메모리 사용률, 버퍼 풀 히트율, 쿼리 응답 시간을 모니터링해 적절한 크기를 찾아가야한다.

 

버퍼 풀 인스턴스 분할

innodb_buffer_pool_instance 시스템 변수로 버퍼 풀을 여러 인스턴스로 분리해 병렬적으로 관리할 수도 있다.

기본적으로는 1개의 인스턴스를 가지며, 최대 64개의 인스턴스까지 설정할 수 있다.

전제 조건은 innodb_buffer_pool_size의 크기가 1GB 이상일 때만 분할할 수 있다.

 

크기 분배는 innodb_buffer_pool_size의 크키가 8GB이고 innodb_buffer_pool_instance이 8이라면

8 / 8 = 1로 각각의 버퍼 풀 인스턴스는 1GB의 크기를 할당받게 된다.

 

버퍼 풀 상태 백업 및 복구

디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업(Warming Up) 상태라고 표현한다.

워밍업 상태에서는 몇십 배의 빠른 쿼리 처리 속도를 보여준다.

 

MySQL 서버를 재시작할 경우 종료하기 전에 다음과 같이

innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.

-- 기본적으로 활성화 되어 있음.
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;
SET PERSIST

그리고 다시 시작하면 백업된 버퍼 풀의 상태로 워밍업 상태가 된다.

 

InnoDB 스토리지 엔진은 이런 버퍼 풀 백업/복구 자동화 기능을 제공해준다.

 

Double Write Buffer (이중 쓰기 버퍼)

InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.

이로 인해 더티 페이지를 디스크 파일로 기록 할 때 일부만 기록되는 문제(Partial Page 또는 Torn Page)를 방지하기 위해

데이터를 데이터 파일에 쓰기 전에 중간 버퍼(Double Write buffer)에 먼저 기록하는 방식을 사용한다.

 

리두 로그만 사용했을 때 문제 상황

16KB의 페이지를 리두 로그로 기록하는 과정에서 8KB까지만 리두 로그로 기록하고 나머지는 시스템 크래시로 실패했다고 해보자.

로그 버퍼는 메모리 영역에 있기 때문에 어디 까지 데이터가 변경이 완료됐는지 알수가 없다.

즉, 부분적으로만 데이터가 기록되어 데이터 정합성이 깨지는 문제가 발생한다.

 

Double Write buffer는 실제 데이터 파일 쓰기에 대한 안정 장치로 

디스크에 데이터를 쓰기가 결정된 시점에 버퍼 풀에서 바로 디스크의 데이터 파일에 쓰기 작업을 하지 않고

디스크의 Double Write buffer에 모았다가 한번에 디스크의 데이터 파일에 쓰게 된다.

 

Double Write buffer에는 이러한 시스템 크래시 상황을 대비해 온전한 상태의 데이터들을 백업해두고

비정상 종료 후 재시작하더라도 Double Write buffer와 데이터 파일의 페이지를 비교해 온전한 페이지를 가져올 수 있다.

 

리두 로그와 Duuble Write Buffer 기록 과정

그렇다면 어떠한 과정으로 기록하는지 아래 상황을 통해 한번 알아보자.

1. 데이터가 변경 요청이 들어오면 버퍼 풀에서 데이터 페이지를 변경하고 동시에 로그 버퍼에 추가한다.

2. 트랜잭션 커밋 시 로그 버퍼의 데이터를 리두 로그에 쓴다.

3. 데이터 파일에 쓰기 전에 Double Write Buffer에 변경된 데이터 페이지를 추가한다.

4. 체크포인트 발생, 버퍼 풀 공간 필요, 설정값 초과 시 Double Write Buffer에 있는 데이터를 순차적으로 데이터 파일에 기록한다.

 

이처럼 InnoDB 스토리지 엔진에서는 리두 로그를 사용한 변경 사항의 영속성을

Double Write Buffer는 데이터 쓰기의 안전성을 보장하기 때문에 두 설정은 세트로 사용되야한다.

 

Change Buffer (체인지 버퍼)

RDBMS에서는 데이터 파일 변경 뿐 아니라 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.

체인지 버퍼란 세컨더리 인덱스의 변경 사항을 저장하는 임시 메모리 영역이다.

 

체인지 버퍼 미사용 시

1. 데이터 입력
2. 인덱스 페이지 읽기
3. 인덱스 업데이트
4. 다음 데이터 입력 → 모든 과정이 순차적, 느림

 

체인지 버퍼 사용 시

1. 데이터 입력
2. 체인지 버퍼에 세컨더리 인덱스와 관련된 데이터 페이지가 없다면?
   • 변경사항 체인지 버퍼에 저장
3. 체인지 버퍼에 세컨더리 인덱스와 관련된 데이터 페이지가 있다면?
   • 인덱스 페이지를 읽으면서 변경사항 병합
4. 다음 데이터 입력 → 빠르게 처리하고, 실제 인덱스 업데이트는 나중에 필요할 때(읽기 작업 시) 처리

즉, 체인지 버퍼는 "지금 당장 하지 않아도 되는 일은 나중으로 미루자"는 개념으로 동작한다.

특히 많은 데이터를 한꺼번에 입력하거나 수정할 때 한번에 모아 처리하므로

성능 향상에 큰 도움되어 디스크 I/O를 효율적으로 가져갈 수 있다.

 

단, Unique와 클러스터링 인덱스에는 사용이 불가하다.

 

체인지 버퍼는 시스템 변수로 활성화/비활성화 할 수 있다. (기본 값은 none)

SHOW VARIABLES LIKE 'innodb_change_buffering';

 

기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정돼있다.

(최대 50%까지 늘릴 수 있음)

너무 빈번한 쓰기 작업이 발생한다면 아래 체인지 버퍼 풀 크기를 늘리는 것을 고려할 수 있다.

SET GLOBAL innodb_change_buffer_max_size = 50;

 

아래 명령으로 체인지 버퍼가 사용중인 메모리 공간 크기도 확인 가능하다.

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME='memory/innodb/ibuf0ibuf';

 

체인지 버퍼가 엄청 사기 처럼 보이긴하지만

결국 메모리를 추가로 사용하는 것이고 과거의 디스크의 경우에는 성능 향상이 있었지만

요즘은 랜덤 I/O에 최적화된 SSD를 주로 사용하기 때문에 좋은 성능의 SSD를 사용한다면 성능 향상이 유의미 하진 않다고 한다.

 

언두 스페이스와 롤백 세그먼트 그리고 동시 처리 가능한 트랜잭션의 개수

언두 로그가 저장되는 공간을 언두 테이블 스페이스라고 한다.

언두 테이블 스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며

롤백 세그먼트는 언두 스페이스를 관리하는 단위로

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

 

이 언두 테이블 스페이스와 롤백 세그먼트를 가지고 아래와 같이 동시 처리 가능한 트랜잭션의 개수를 구할 수 있다.

-- 기본 InnoDB 페이지 크기 : 16384 bytes = 16 KB
SELECT @@innodb_page_size;

-- 기본 롤백 세그먼트 개수 : 128개
SELECT @@innodb_rollback_segments;

-- 언두 슬롯 개수 : 16384 / 16 = 1024 슬롯
-- 전체 언두 슬롯 개수 : 128 * 1024

-- 언두 테이블 스페이스 개수 : 2개
SELECT @@innodb_undo_tablespaces;

-- 기본값을 기준으로 최대 동시 처리 가능한 트랜잭션 수 : 131072
(InnnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블 스페이스 개수)

현재 필자의 MySQL 서버 기준으로는 131072을 처리할 수 있다.

 

일반적인 서비스에서 131072개의 트랜잭션을 동시 처리할 일을 많이 없으니

지금 처럼 기본값을 사용해도 무방할 것이다.

 

하지만 언두 로그 슬롯이 부족할 경우에는 트랜잭션을 시작할 수 없는 문제가 발생할 수 있다.

이 상황에서는 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수를 설정해야한다.

 

또한 불필요한 공간을 반환해 공간을 효율적으로 사용할 수 있는데

언두 테이블 스페이스 공간을 필요한 만큼 남기고 불필요한 공간을 운영체제로 반납하는 것을 ‘Undo tablesapce truncate’라고 한다.

불필요한 공간을 잘라내는 방법은 자동과 수동 방법이 있는데 아래 시스템 변수로 확인/변경할 수 있다.

-- Undo tablesapce truncate 자동 모드 기본값 : ON
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';

 

Adaptive Hash Index (어댑티브 해시 인덱스)

일반적으로 인덱스는 B-Tree 인덱스를 생각할 것이다.

이는 사용자가 직접 테이블에 생성해둔 인덱스이고

어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아닌

InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

 

어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능으로 이름 그대로 해시 자료 구조 기반 인덱스이다.

자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고,

필요할 때마다 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 해준다.

즉, B-Tree를 루트 노드로부터 리프 노드까지 찾아가는 비용이 없어져 쿼리 성능이 빨라진다.

CPU 사용량도 감소되어 더 많은 쿼리를 동시에 처리할 수 있게 된다.

 

어댑티브 해시 인덱스는 버퍼 풀로부터 메모리 자원을 할당받고

버퍼 풀의 페이지를 가르키는 주소값을 저장해 버퍼 풀에 올라간 데이터에 대해서만 관리한다.

따라서 버퍼 풀의 생명주기와 동일하게 버퍼 풀의 페이지가 삭제되면 관련된 해시 인덱스도 삭제되고

새로운 페이지가 버퍼 풀에 올라오면 패턴을 학습하고 해시 인덱스를 생성한다.

 

이건 무조건 써야해! 할 수 있지만 꼭 그렇지만은 않다.

어댑티브 해시 인덱스가 도움이 되지 않는 상황

• 버퍼 풀이 아닌 디스크 읽기 작업이 많은 경우
• 순차 읽기의 경우 (해시 검색의 장점 X)
• 인덱스를 타지 않는 경우 (인덱스 키로 해시 인덱스를 만들기 때문)
• 삭제 작업이 많은 경우 (해당 데이터와 연관된 모든 어댑티브 해시 인덱스를 찾고 제거 후 해시 테이블을 재구성해야하기 때문)
• 특정 패턴의 쿼리가 없는 경우 (JOIN, LIKE, RANGE 검색의 경우는 검색 패턴이 매번 달라 해시 인덱스가 패턴을 학습하기 어렵다.)

 

어댑티브 해시 인덱스가 도움이 되는 상황

• 디스크의 크기와 버퍼 풀 크기가 비슷한 경우 (버퍼 풀에서 자주 읽어 오기 때문)
• 동등 비교 조건 작업이 많은 경우 (예측 가능한 접근 패턴)
• 쿼리가 일부 데이터에만 집중되는 경우 (자주 접근하는 데이터에 대한 패턴 학습 )

 

이처럼 어댑티브 해시 인덱스는 상황에 따라 좋을 수도 좋지 않을 수도 있기 때문에 활성화/비활성화 여부를 잘 생각해서 적절히 적용해야한다.

아래 시스템 변수로 어댑티브 해시 인덱스를 활성화/비활성화 할 수 있다. (Default : OFF)

innodb_adaptive_hash_index

 

하지만 이 모든 상황을 우리가 다 예측하는 것은 힘들 것이다.

따라서 어댑티브 해시 인덱스 적용 전 후로 CPU 사용량, 메모리 사용량을 모니터링하여 판단하는 것도 좋은 방법이라 생각된다.

---

 

참고 자료

• Real MySQL 8.0