[데이터베이스] InnoDB 스토리지 엔진 아키텍처 딥다이브

 

InnoDB 스토리지 엔진 아키텍처

MySQL 은 클라이언트의 쿼리를 해석하는 MySQL 엔진과 해석한 쿼리를 실행하기 위한 데이터를 읽고 쓰기 위한 핸들러 역할을 하는 스토리지 엔진으로 구성된다.

InnoDB는 현재 MySQL 기본 스토리지 엔진이고 제일 많이 쓰는 MySQL 스토리지 엔진이다.

Primary 키에 의한 클러스터링

InnoDB에서 모든 테이블은 PK에 의해 클러스터링 되어 있다.

→ PK 값의 순서대로 디스크에 저장된다는 의미

그리고 Secondary index 는 레코드의 물리 주소가 아닌 PK 를 가진다.

따라서 InnoDB는 pk에 의한 레인지 스캔은 상당히 빨리 처리될 수 있다.

오라클 DBMS 의 IOT와 동일하게 구성

 

💡
PK 기준으로 클러스터링 되기 때문에 PK에 의한 Range 스캔이 빠르다는 건 실제 데이터가 PK 순으로 연속되어 저장되어 있기 때문
하지만 클러스터링 때문에 쓰기 성능 저하
쓰기 성능 저하의 원인은 pk 기준으로 클러스터링 되어 있어 데이터가 추가되면 저장 되어야 할 데이터 위치를 찾아야함
만약 UUID 같은 pk로 설정되어 있다면 랜덤하게 삽입되어 만약 해당 페이지에 공간이 부족할 경우 페이지 분할이 발생할 수 있어 성능저하가 추가적으로 발생할 수 있음
InnoDB는 PK를 지정하지 않으면 자동으로 PK를 생성 하지만 사용자는 해당 PK를 사용할 수 없음 따라서 InnoDB의 PK를 지정하는 것을 추천

 

 

하지만 MyISAM 은 PK 와 세컨더리 인덱스랑 차이가 없다.

PK는 유니크 제약 조건을 가진 세컨더리 인덱스이다.

키를 포함한 모든 인덱스는 레코드의 물리 주소를 값으로 가진다.

외래 키 지원

InnoDB는 외래 키를 스토리지 엔진 레밸에서 지원한다.

다른 스토리지 엔진은 지원안 할 수 있다.

사담

외래키는 하나의 제약 조건인데, 수동으로 데이터를 삽입하거나 테이블 스키마를 변경할 때 외래키 제약 조건에 의해 데드락이나, 복잡한 외래키 관계에 의한 락의 전파 때문에 여러가지 에러를 발생할 수 있는데,

변수 설정을 통해 외래키 체크를 임시적으로 꺼둘 수 있는데, 반드시 특정 목적을 이루면 외래키 체크를 다시 활성화 해야한다

→ 별로 비추천하는 방식일듯 하다.

MVCC (Multi-Version Concurrency Control)

MVCC 의 주된 목적은 트랜잭션의 ACID 를 지키는 것과 잠금 없이 일관된 읽기를 제공하는 것이다.

다음은 MVCC 의 과정 중 하나이다.

만약 다음과 같은 쿼리문이 실행되고 commit 이 실행된다면

버퍼 풀과 디스크에 데이터가 입력이 될 것이다.

Copy

INCERT INTO memver (m_id, m_name , m_area) VALUES (12, '홍길동', '서울');

그 다음 m_area 를 경기로 update 하는 쿼리를 날리면 commit 여부와 상관없이

버퍼 풀에 update 되고 언두 로그에 이전 데이터를 복사해 놓는다.

(디스크에는 update가 될 수도 있고 안될 수도 있다 이는 checkpoint 나 Write 스레드에 의해 될 수도 있고 안될 수도 있다는 의미 하지만 InnoDB는 ACID 를 보장하기 때문에 버퍼 풀에 데이터와 디스크에 데이터를 같다고 봐도 무방하다.)

그리고 만약 commit 되지 않은 상태에서 읽기 쿼리가 실행된다면 isolation 레밸 변수 설정에 따라 달라진다.

이를 같은 데이터에 대해 2가지 이상의 버전이 생겨서 멀티 버전이라 하며 롤백이 되면 언두로그 데이터를 다시 반영하고 삭제되며,

commit이 된다고 해도 언두로그의 데이터는 없어질 수도 안없어질 수도 있다. (해당 언두로그 데이터를 원하는 트랜잭션이 끝나야한다)

버전의 생존 주기는 긴 기간동안 유효한 트랜잭션이 있으면 오랫동안 관리해야한다.

장기간의 트랜잭션은 DB 서버의 성능 저하를 유발할 수 있음

💡
PK 기준으로 클러스터링 되기 때문에 PK에 의한 Range 스캔이 빠르다는 건 실제 데이터가 PK 순으로 연속되어 저장되어 있기 때문
하지만 클러스터링 때문에 쓰기 성능 저하
쓰기 성능 저하의 원인은 pk 기준으로 클러스터링 되어 있어 데이터가 추가되면 저장 되어야 할 데이터 위치를 찾아야함
만약 UUID 같은 pk로 설정되어 있다면 랜덤하게 삽입되어 만약 해당 페이지에 공간이 부족할 경우 페이지 분할이 발생할 수 있어 성능저하가 추가적으로 발생할 수 있음
InnoDB는 PK를 지정하지 않으면 자동으로 PK를 생성 하지만 사용자는 해당 PK를 사용할 수 없음 따라서 InnoDB의 PK를 지정하는 것을 추천

데드락 감지 및 해결

• InnoDB는 잠금 대기 그래프를 통해 데드락을 감지하고, 감지된 트랜잭션 중 하나를 강제 종료.
• 데드락 감지 쓰레드가 부담이 될 경우, INNODB_DEADLOCK_DETECT 시스템 변수를 비활성화하거나 LOCK_WAIT_TIMEOUT 시스템 변수를 통해 우회적으로 대기 시간을 설정할 수 있음.
• 데드락 감지 또한 하나의 스레드에서 잠금 대기 그래프를 잠금을 하고 수행한다.
• 데드락 감지는 on/off 가 가능하며 만약 off 하면 몇 초 이상 응답이 없을 시 에러를 반환하는 설정을 하고는 한다.
• 그리고 mysql 엔진에서 락을 관리하는 테이블 락은 InnoDB에서 데드락 감지를 할 수 없는데 이것 또한 설정으로 할 수 있게 가능하다.

Copy

SHOW VARIABLES LIKE 'innodb_deadlock_detect';
// 기본은 on

Copy

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
// 만약 off 로 한다면 트랜잭션 응답이 몇초 이상 걸린다면 자동으로 롤백하는 설정을 잘 해야한다

자동화된 장애 복구

일반적으로 InnoDB는 데이터 파일이 손상되거나 서버가 시작되지 못하는 경우는 거의 없다.

기본적으로 MySQL 서버가 시작을 할 때 완료되지 못한 트랜잭션 작업이나 일부만 기록된 디스크의 데이터 페이지등에 대한 일련의 복구 작업을 자동으로 진행한다.

하지만 HW 이슈로 자동으로 복구하지 못하는 경우도 있음

이럴 땐 innodb_force_recovery 시스템 변수를 설정하여 검사 과정을 선별적으로 진행하도록 할 수 있다.

InnoDB 버퍼 풀

InnoDB 버퍼 풀은 InnoDB 스토리지 엔진에서 핵심적인 가장 핵심적인 부분이다.

디스크의 데이터 파일이나 인덱스 정보를 캐싱 해두는 공간이다.

쓰기 작업을 지연 시켜서 쓰기 작업에 대한 디스크 I/O 작업을 최소화하는 역할도 담당한다.

버퍼 풀의 크기를 조정?

버퍼 풀은 디스크 I/O 작업을 최소화 하는데 유용하기 때문에 버퍼 풀의 크기가 크면 클 수록 좋다.

하지만 버퍼 풀의 크기를 무지막지하게 크게 잡으면 안된다

왜냐하면 레코드 버퍼 같은 클라이언트 세션에서 사용할 메모리 영역도 서비스 마다 많이 필요할 수 있기 때문이다.

따라서 버퍼 풀의 크기를 작은 크기부터 점점 늘리는 것을 추천

버퍼 풀의 인스턴스 수?

또한 버퍼 풀도 모든 스레드에서 접근 가능하기 때문에 락을 사용한다.

하지만 락에서 유발된 잠금 경합이 문제가 되곤 했는데 MySQL 8.0 부터는 버퍼 풀도 여러가지 인스턴스를 만들 수있어서 이를 완화 했고, innodb_buffer_pool_size 변수 설정을 통해서 변경할 수 있다.

하지만 크리티컬한 변경이기 때문에 왠만해서는 변경하지 말자

버퍼 풀의 구조

InnoDB의 버퍼 풀은 거대한 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 데이터 페이지를 읽어와서 기록한다.

이러한 버퍼풀의 페이지를 관리하기 위해 3가지의 자료구조를 사용한다.

• LRU 리스트
• 플러시(Flush) 리스트
• 프리(Free) 리스트

LRU 리스트

LRU 리스트는 정확히 MRU 리스트와 LRU 리스트를 합친 자료구조이다.

InnoDB에서 버퍼 풀의 데이터를 검색하는 과정은 다음과 같다.

1. 필요한 데이터를 찾는다.
2. 만약 버퍼 풀에 없다면 데이터 페이지를 버퍼 풀에 적재하고 적제된 페이지의 포인터를 LRU 헤더 부분에 추가
3. 데이터를 실제로 읽으면 MRU 방향으로 승격(Read Ahead 와 같이 대량의 읽기에서 읽힌 페이지 중에 안읽힌 것들은 승격 안됨)
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 Age 를 부여하고 사용되면 Age를 초기화하고 MRU 방향으로 승격한다.
5. 필요한 데이터가 자주 접근했다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.

플러시 (flush) 리스트

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

디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만 변경점이 존재한다면 플러시 리스트에 의해 관리된다.

InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.

그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.

리두 로그 혹은 버퍼 풀에 기록을 한다고 디스크와 항상 동기화 되지는 않는다.

InnoDB는 주기적으로 체크포인트 같은 이벤트를 발생시켜서 동기화 한다.

버퍼풀과 리두 로그

버퍼풀이 데이터 캐싱하는 역할과 지연된 쓰기 기능을 동시에 지원하니까 단순히 버퍼 풀의 메모리 크기만 증가 시키면 읽기 성능과 쓰기 성능이 더 향상할거라고 생각할 수 있다.

하지만 쓰기 성능 향상은 리두 로그와 버퍼 풀의 관계 부터 알아야 정확하게 적용할 수 있다.

디스크의 데이터와 동기화 되지 않은 데이터 페이지를 더티 페이지라고 말하는데, 리두 로그는 고정된 크기의 더티 페이지를 연결해서 순환 고리를 형성한다.

그리고 리두 로그 중에 더티 페이지와 연결된 리두 로그 즉 재사용 불가능한 리두 로그 영역을 활성 리두 로그라고 부른다.

💡
리두 로그가 순환 고리 형태라는 의미는 리두 로그 파일은 고정된 크기를 사용하며 이 로그가 꽉 차면 처음부터 다시 덮어쓰는 방식으로 되어 있는데 이를 순환 고리 형태라 하고현재 버퍼 풀에 더티 페이지와 연결된 리두 로그는 재사용 불가능하니까 활성 리두 로그라고 구분한다.

더티 페이지를 디스크에 동기화하기 위해 체크 포인트 이벤트를 발생하는데, 체크 포인트 기준은 LSN(Log Sequence Number) 라는 일련 번호를 기준으로 한다.

리두 로그는 생길 때마다 LSN을 증가 시키고 체크 포인트 이벤트가 발생하면 다음 읽을 LSN 보다 작은 리두 로그 범위를 읽는다.

따라서 체크 포인트의 마지막 LSN 과 로그 엔트리의 마지막 LSN은 체크포인트 age라고 부르며 체크포인트 에이지는 활성 리두 로그 영역의 크기를 일컫는다.

리두 로그와 버퍼 풀 크기에 관한 최적 수치는 많은 고민이 필요하다.

버퍼 풀 플러시

MySQL 5.6 버전까지는 InnoDB 스토리지 더티 페이지 플러시 기능이 그다지 부드럽게 처리되지 않았다.

급작스럽게 디스크 기록이 폭증해서 MySQL 서버의 사용자 쿼리 처리 성능에 영향을 받는 경우가 많았는데, 8.0 버전은 더티 페이지를 동기화하는 부분(플러시)에서 예전과 같은 디스크 폭증 현상은 발생하지 않았다.

다음은 폭증을 해결하기 위해 변수 몇가지를 살펴보지만 운영시 성능 문제가 생기는게 아니라면 굳이 바꿀 필요 없는 값들이다.

성능에 문제없이 디스크에 동기화하기 위해 다음 2개의 플러시 기능을 백그라운드로 실행한다.

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

디스크 폭증 현상을 줄이기 위해서는 버퍼 풀에 더티 페이지 데이터를 많이 쌓아 놓으면 안된다.

 

플러시 리스트 플러시

리두 로그를 줄이기 위해서는 우선 버퍼 풀에 더티 페이지가 동기화 되어야 한다.

클리너 쓰레드와 버퍼 풀 인스턴스는 보통 1대1이고

더티 페이지의 비율이 특정 비율이 쌓이기 전에 동기화를 시키는게 관건이고

해당 비율은 수동으로 설정해줘도 되고

어뎁티브 플러시로 리두 로그 비율을 참고하여 알고리즘을 적용하여 동기화도 가능하다 (보통 디폴트 설정이고 10퍼 이상 쌓이면 알고리즘 검사가 시작된다.)

LRU 리스트 플러시

LRU 리스트를 주기적으로 스캔해서 많이 않쓰이는 데이터 페이지는 Free 리스토로 페이지를 이동시킨다.

버퍼 풀 상태 백업 및 복구

버퍼 풀은 쿼리의 성능에 직관되는 캐싱 기능을 가지고 있다.

즉, 버퍼 풀의 데이터가 있냐 없냐에 따라 쿼리 성능이 수십배 혹은 그 이상 차이가 날 수 있다.

그럼 만약

MySQL 5.6 이전까지는 재시작을 한다면 주요 페이지 및 인덱스를 스캔하였다.

하지만 이후에는 버퍼풀의 정보를 따로 적재해놓고 재시작할 때 적재한 데이터로 재시작한다.

하지만 버퍼풀의 데이터 전부가 아니라 정보만을 적재하기 때문에 데이터 페이지를 디스크에서 읽어오는데 시간이 소요된다.

따라서 재시작할 때 서비스를 바로 시작하는 것은 추천하지 않는다.

Double Write Buffer

InnoDB 의 Redo 로그는 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.

만약 Dirty Page를 디스크로 플러시할 때 일부만 기록되는 문제 (하드웨어의 오작동 및 시스템 강제종료 등등으로 유발 되는) 가 생기면 더티 페이지를 복구할 수 없을지도 모른다.

이런 페이지를 Partial-Page 또는 Torn-Page라고 부른다.

이러한 데이터 안정성을 위해 MySQL은 Double Write Buffer 라는 기능을 제공하는데

만약 A~E 까지 데이터 페이지를 기록한다면 Double Write Buffer 에 한번에 기록하고, 디스크에 하나씩 기록한다.

만약 디스크에 기록하는 중간에 강제 종료 때문에 특정 데이터가 유실된다면 MySQL 은 재시작할 때 Double Write Buffer 에 데이터와 데이터 파일과 비교하여 오차를 확인하여 동기화한다.

(이것은 데이터 안정성이 중요한 도메인에서 사용한다.)

Copy

SHOW VARIABLES LIKE 'innodb_doublewrite';

Undo 로그

Undo 로그는 아주 중요한 역할을 한다.

데이터 일관성을 위해 락과 같은 동시성 저하 작업에서 높은 동시성 처리를 가능하게 한다.

중요한 역할을 하지만 관리 비용도 많이 필요함

Undo 로그 레코드 모니터링

• MySQL 5.5 이전엔 한 번 증가한 Undo 로그 공간은 다시 작아지지 않았음.
• MySQL 5.7과 MySQL 8.0 부터 이 문제는 해결됨
• 그럼에도 활성 상태의 트랜잭션이 장기간 유지되는 것은 좋진 않음.

Redo 로그 및 로그 버퍼

Redo 로그는 HW/SW 문제로 인해 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되 못한 데이터를 유지시켜주는 안전장치이다.

대부분의 DBMS는 디스크 형태나 구조들이 읽기를 위한 자료구조로 되어 있다.

따라서 쓰기라는 작업은 상당한 비용인데 이를 위해 버퍼 풀 같은 방법으로 쓰기를 지연한다.

하지만 이러한 구조로 메모리와 실제 영속화된 디스크 영역의 데이터 정합성이 달라지게 되고 이러한 상황에서 HW/SW 문제로 인해 서버가 비정상적으로 종료되면 ACID의 D를 보장 못하게 한다.

이러한 상황에서 MySQL의 안정성을 위한 장치가 리두 로그이고

Redo 로그는 insert 나 update delete 같은 데이터가 변하는 작업은 모두 남긴다. (버퍼 풀 보다 먼저)

Redo 로그도 디스크에 남기기 때문에 로그를 버퍼링 할 수 있는 로그 버퍼 또한 존재한다.

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

위와 같은 상황에서 모두 리두 로그가 사용되며 2번째 상황에서 사용하기 위해 리두로그는 롤백했는지 커밋했는지 같은 정보도 담고 있다.

리두 로그는 커밋 되자마자 디스크에 기록되는 것이 바람직한데 리두 로그가 데이터 파일에 비해 비용이 덜 드는거지 디스크IO 작업이라 비용 부담이 든다.

따라서 리두 로그도 버퍼를 제공하는데, 이 버퍼에 로그를 리두 로그에 기록하는 순간도 잘 정해야한다.

만약 BLOB 같은 큰 데이터면 주기를 좀 늘려 보는 것도 추천한다.

Copy

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

• 0: 매 초마다 리두 로그를 디스크에 기록한다. (리두 로그는 메모리에 유지되지만, 데이터베이스 크래시가 발생할 경우 손실될 수 있다.)
• 1: 각 트랜잭션 커밋 시 리두 로그를 디스크에 기록한다. (가장 안전하지만 성능에 영향을 미칠 수 있다.)
• 2: 각 트랜잭션 커밋 시 리두 로그를 버퍼에 기록하고, 매 초마다 디스크에 기록한다. (안전성과 성능의 균형을 이룬다.)

Adaptive Hash Index

Adaptive Hash Index 는 버퍼 풀을 빠르게 인덱싱하기 위한 자료구조이다.

우리가 알고 있는 index 는 우리가 지정한 index 이다.

하지만 Adaptive Hash Index의 인덱스는 mysql 서버가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, 활성 비활성이 가능하다.

B-Tree 인덱스에서 특정 값을 찾지만 이또한 상대적으로 리프 노드까지 내려가야 찾을 수 있다.

Adaptive Hash Index는 사용자가 자주 쓰는 인덱스를 따로 자료구조 형태로 저장하는 것이고 해당 인덱스에 대한 데이터는 B-Tree 로 리프까지 찾지 않아도 된다.

Adaptive Hash Index는 단순하게 버퍼 풀에서 데이터를 더 빠르게 찾기 위함이고 Adaptive Hash Index에 데이터가 없더라고 해당 자료구조를 스캔해야하는 연산이 필요하다.

따라서 성능 향상에 도움될 수는 있지만 모든 상황에서는 아니며, 보통 디스크 작업이 많이 안일어나는 즉, 버퍼풀에서 많이 데이터가 읽는 상황에서 효율적인 기능이다.

출처

책 : Real MySQL 8.0 1 저자 : 이성욱