Real MySQL 8.0 4장(2) - InnoDB의 구조

InnoDB 주요 특징

레코드 기반의 잠금지원, 높은 동시성 처리 가능, 안정성이 뛰어남

 

1. 프라이머리키로 데이터가 클러스터링 됨
2. 외래키를 지원함
3. MVCC 를 통해 잠금 없이 일관된 읽기를 지원함
4. 자동 데드락 감지기능이 들어가있음
5. 자동으로 장애도 복구할수 있음
6. 랜덤 디스크 쓰기 작업을 줄이기 위해 쓰기작업을 한번에 모아서 처리하는 InnoDB 버퍼풀 존재
7. Partial-page 문제를 방지하기 위한 Double Write Buffer 방식
8. 언두로그
9. 체인지 버퍼
10. 리두로그 및 로그 버퍼
11. 어댑티브 해시 인덱스

 

1. InnoDB는 프라이머리키로 데이터가 클러스터링 된다.

클러스터링이란?

비슷한 데이터끼리 묶어서 보관하는것

InnoDB는 그 비슷한 기준을 정할때 프라이머리키를 기준으로 생각한다.

 

InnoDB에서는 모든 데이터는 프라이머리 키를 기준으로 정렬된 형태로 실제 데이터가 저장되어있으며,

세컨더리 인덱스의 리프노드에 실제 데이터의 주소 역할을 하는 프라이머리 키를 데이터로 가지고있다.

 

InnoDB에서는 모든 데이터는 프라이머리 키를 기준으로 정렬된 형태로 저장되어 있기 때문에 인덱스 레인지 스캔을 활용하면 데이터를 빨리 읽을 수 있다.

 

MyISAM 같은 경우에는 이러한 프라이머리키와 세컨더리 인덱스 구분이 없고 리프노드에 실제 데이터의 물리적인 주소값을 갖는다. 즉 MyISAM에서 프라이머리 키는 그냥 유니크 제약을 가진 세컨더리 인덱스일 뿐이다.

(8장 인덱스에서 더 자세하게 나옴)

 

2. 외래 키 지원

외래 키의 경우 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로, MyISAM이나 MEMORY 스토리지엔진에서는 지원하지 않는 기능이다.

외래키의 단점

InnoDB에서 외래 키는 부모테이블과 자식테이블 모두 해당 컬럼에 인덱스 생성이 필요하다.

데이터 변경 시에는 반드시 부모테이블이나 자식테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블에 전파되어 데드락이 발생할 때가 많다.

⇒ 무엇보다 외래키가 없어도 JOIN이 가능하고, 위와 같은 이유에서 실무에서 외래 키를 사용하지 않는 경우도 있다.

외래 키 체크 기능 끄기

수동으로 데이터를 넣거나 지우는 경우, 부모테이블이나 자식테이블에 데이터가 있는지 체크하는 작업때문에 문제가 생길 수 있다.

이때 급하게 데이터를 처리해야 하는 경우 외래 키 체크 기능을 끌 수 있다.

SET foreign_key_checks=OFF;

왜래 키 체크 키능을 끄고 작업을 수행 한 후 일관성이 맞춰졌다면, 다시 체크 기능을 켜주어야 한다.

SET foreign_key_checks=ON;

 

3. MVCC(Multi Version Concurrency Control)

하나의 레코드에 대해 여러 버전이 존재하고, 필요에따라 데이터가 어떻게 보여질지 달라지는 구조

잠금 없이 일관된 읽기를 지원하기 위해 MVCC 구조를 채택

잠금 없이 일관된 읽기 란?

트랜잭션 격리수준이 READ_UNCOMMITED 인 예시

 

트랜잭션 격리수준이 READ_UNCOMMITED 인 예시

특정 사용자가 레코드를 변경하고 아직 커밋하지 않았다고 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.

언두로그에 대해서는 아래에서 다시 이야기 하겠다.

 

트랜잭션 격리 수준

READ_UNCOMMITED ⇒ 락이 걸려있어도 아직 커밋되지 않은 데이터도 읽겠다.
READ_COMMITED ⇒ 락이 걸려있어도 이전 커밋된 상태 까지의 데이터만 읽겠다.
REPEATABLE_READ ⇒ 자신보다 이전 트랜잭션ID 에서 변경까지만 읽겠다. Inno DB의 기본값
SERIALIZABLE ⇒ 락이 걸려있으면 락이 회수 될때까지 기다렸다가 읽겠다.

⇒ 5장에서 자세히 다룸

 

4. 자동 데드락 감지

InnoDB 스토리지 엔진에서는 내부적으로 잠금이 교착상태에 빠졌는지 체크하기 위해 잠금 대기 상황을 그래프 형태로 관리한다.

데드락 감지 스레드에서 주기적으로 그래프를 검사해 교착상태를 감지한다.

데드락에 걸린 트랜잭션 중 어느 트랜잭션을 먼저 종료할지는 언두 로그를 더 적게 가져 롤백 할때 처리할 정보가 적은 트랜잭션을 롤백처리 해버린다.

 

5. 자동화된 장애 복구

MySQL이 갑자기 종료되어 완료되지 못한 트랜잭션이나 디스크에 일부만 써진 데이터(Partial Write)가 존재한다면 InnoDB 는 서버를 재시작 할 때 이러한 부분을 자동으로 복구한다.

자동화된 장애 복구가 안될 경우

디스크나 서버 하드웨어 이슈로 서버를 재시작 할때 자동화된 복구가 수행되지 않을 수 있다. 이 경우 자동복구를 멈추고 MySQL 서버는 종료되어 버린다.

이때는 innodb_force_recovery 시스템 변수를 설정한 후 MySQL 서버를 재시작 해야한다.

• 로그 파일이 손상 되었을 경우, innodb_force_recovery를 6으로 설정 후 재시작
• 테이블 데이터 파일이 손상 되었을 경우, innodb_force_recovery를 1로 설정 후 재시작
• 어떤 부분이 손상되었는지 모를 경우에는 1부터 6까지 변경하면서 재시작
• 그래도 재시작 되지 않으면, 백업을 이용해 DB를 다시 구축하는 방법 뿐이다…

 

6. InnoDB의 버퍼 풀(for 데이터 캐시, 쓰기 버퍼링)

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

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

쓰기 작업을 일괄처리 할수 있는 버퍼 역할도 한다. ⇒ INSERT, UPDATE, DELETE 와 같은 쓰기 작업을 모아서처리하면 랜덤 디스크 작업 횟수를 감소할 수 있다.

InnoDB의 버퍼 풀 사이즈 정하기

MySQL 5.7버전 부터는 버퍼 풀 사이즈를 동적으로 런타임 중에 변경 할 수 있기 때문에 처음에 적절히 적은 값으로 설정 한 후 상황을 봐가면서 증가시키는 방법이 최선이다.

보통 처음에 운영체제의 전체 메모리의 50% 정도에서 시작해서 올려나가면서 최적점을 찾는다.

버퍼 풀의 크기는 innodb_buffer_pool_size 시스템 변수로 변경할 수 있고, MySQL 서버가 한가한 시점에 작업을 진행하는것이 좋다.

전체 버퍼 풀의 크기를 늘리거나 줄일때 128MB 단위로 처리된다.

 

버퍼 풀의 구조 1 -NRU 방식

버퍼풀의 경우 innodb_page_size 시스템 변수의 설정된 크기의 여러 페이지들로 쪼개어 저장 된다.

버퍼가 가득 찼을 경우, 버퍼를 비워주어야 하는데 이때 LRU 교체 알고리즘을 적용한다.

LRU 알고리즘 ⇒ 가장 오랫동안 사용되지 않은 페이지가 먼저 교체됨

정확히는 LRU 알고리즘으로 작동하는 Old 서브리스트 와 MRU 알고리즘으로 작동하는 New 서브리스트 두개의 리스트로 작동한다.

각 페이지는 얼마나 최근에 접근했는지에 따라 Age 값이 부여됨.

새로운 페이지는 Old 리스트의 헤드 부분(5/8 지점)에 추가

한번 읽힌 페이지는 Age가 초기화 되고 New 리스트 의 헤드쪽으로 이동(MRU)

사용하지 않는 페이지는 Old 리스트의 꼬리쪽으로 이동(NRU)

 

 🤔  왜 이렇게 New Old 두개의 리스트로 나눈걸까?

⇒ 일회성으로 많은 페이지가 캐싱될 경우 생길 수 있는 문제 방지
하나의 리스트로 단순히 LRU 교체 방식만 적용되어있다면, Dump와 Where 절 없는 Select 등으로 한번에 많이 일회성 페이지들이 추가 될 경우, 자주 사용되던 페이지도 밀려서 제거 될 가능성이 있기 때문에 리스트를 두개로 나누고, 일단 새로운 페이지를 두 리스트 중간에 삽입하고, 삽입된 데이터가 또 한번 참조 될 경우 위로 점점 승격 시키는 방식을 채택

 

버퍼 풀의 구조 2 -클린페이지, 더티페이지

만약 버퍼 풀에 캐시되어있는 상태에서 데이터가 INSERT, UPDATE, DELETE 명령으로 변경되는 경우에는 어떻게 될까?

 

클린페이지 : 디스크에서 버퍼 풀로 데이터를 가져온 후로 변경되지 않은 데이터를 가진 페이지

더티페이지 : 디스크에서 버퍼 풀로 데이터를 가져온 후에 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 페이지

 

더티페이지의 변경된 데이터들은 언젠가는 버퍼풀에서 나가서 디스크에 다시 기록되어야 한다. 이때 Redo Log를 활용하여 더티페이지가 버퍼풀에 무한정 머무르지 않도록 관리한다.

 

🤔 Redo Log 란?

데이터베이스에서 일어난 모든 변화를 저장하는 메모리 공간.
사용자의 INSERT, DELETE, UPDATE 작업 으로 인한 데이터의 변화가 아직 디스크에는 적용되지 않은 상태로 에러가 발생하여 사용자의 작업을 다시 디스크에 반영시키고 싶은 경우, 이 Redo Log를 활용해 다시 디스크에 반영하는 작업을 수행한다.

Redo Log는 다음 그림과 같이 여러개의 고정된 크기의 ib_logfile 라는 이름의 파일들로 저장되며 각 파일은 원형 버퍼로 연결 되어 관리 된다. 

책에 나온 설명을 토대로 그림을 그려보았다.

체크 포인트가 발생하면, 체크포인트의 LSN(Long Sequence Number) 보다 작은 부분에 해당하는 Redo log와 관련된 더티 페이지는 모두 디스크로 동기화 되야 한다.

 

버퍼 풀의 구조 3 - 버퍼 풀 플러시

InnoDB 스토리지 엔진은 버퍼 풀 위에서 아직 디스크에 기록되지 않은 더티 페이지들을 디스크에 동기화 하기 위해 2개의 플러시 기능을 백그라운드에서 실행한다.

(특별히 서비스를 운형할 때 성능 문제가 발생하지 않는 상태라면 굳이 이 부분을 조정 할 필요는 없다.)

플러시 리스트 플러시

오래된 더티페이지들을 모아서 디스크에 동기화 하는 작업

리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화 되어야 한다.

더티페이지를 디스크로 동기화 하는 작업은 백그라운드에서 실행되는데 이때 사용되는 스레드를 클리너 스레드라고 한다.

LRU 리스트 플러시

버퍼풀 내 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해 새로운 페이지들을 읽어올 공간을 만드는 작업

이때, 클린 페이지의 경우에는 즉시 프리(Free) 리스트로 옮겨지고, 더티페이지의 경우에는 디스크에 동기화 된다.

 

버퍼 풀의 구조 4 - 버퍼 풀 워밍업

서버를 셧다운 했다가 다시 시작하는 경우, 버퍼 풀에 캐시된 페이지들이 없어지기 때문에 성능이 평상시의 1/10도 안나올 경우가 있다.

버퍼풀 내에 디스크의 데이터가 잘 캐시되어 있는 상태를 워밍업이라고 표현한다.

잘 워밍업 되어 있는 경우 몇십배의 성능 향상이 있을 수 있다.

MySQL 5.6 버전 부터는 버퍼풀 덤프 및 적재 기능이 도입되었다.

수동 버퍼 풀 백업하기

SET GLOBAL innodb_buffer_pool_dump_now=ON;

버퍼 풀의 크기는 보통 크다고 하더라도 몇십 MB 이하 이기 때문에 이 작업은 금방 걸린다.

수동 버퍼 풀 복구 하기

// 버퍼풀 복구 시작
SET GLOBAL innodb_buffer_pool_load_now=ON;

// 버퍼풀 복구 상태 확인 (CLI에서 마지막에 \\G 붙이면 더 상세한 정보를 보여줌)
SHOW STATUS LIKE 'innodb_buffe_pool_dumps_status'\\G

// 버퍼풀 복구 중지
SET GLOBAL innodb_buffer_pool_load_abort=ON;

디스크에서 다시 데이터를 가져와야 하기 때문에 복구 작업에 시간이 오래 걸릴 수 있다.

자동 버퍼풀 백업 및 복구

서버를 종료하기 전에 수동으로 버퍼풀을 백업 해두었다가, 버퍼 풀을 복구 할 수도 있지만 이 작업을 매번 서버를 재시작 할 때마다 수동으로 하기는 쉽지 않다.

⇒ InnoDB에서 innodb_buffer_pool_dump_at_shutdown과, innodb_buffer_pool_load_at_startup 설정을 MySQL 서버의 설정파일에 추가하면, 자동으로도 버퍼풀 백업 및 복구하는 기능을 켤 수 있다.

 

7. Double Write Buffer

더티페이지를 디스크로 플러시 할 때, 중간에 일부만 기록되는 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page) 문제를 방지하기 위해 사용하는 버퍼

엔진은 실제 디스크 상의 데이터 파일에 위 그림상 A~E 까지 더티페이지 내용을 기록하기 전에 double write buffer 에 먼저 기록하고, 쓰기 작업이 중간에 실패 할 경우 버퍼 내 내용과 디스크 내 데이터 파일의 내용을 비교해 동기화 작업을 완료 시킨다.

 

8. 언두로그

트랜잭션 격리수준이 READ_UNCOMMITED 인 예시

 

InnoDB에서 롤백과, 트랜잭션 격리수준을 보장하기 위해

INSERT, UPDATE, DELETE로 변경되기 이전의 데이터를 별도 백업해두는 공간

트랜잭션 롤백을 위해 사용된다.

트랜잭션이 롤백 되면 변경 이전 데이터로 복구해야 하는데 이때 언두로그에 백업해둔 데이터를 이용한다.

격리 수준 보장을 위해 사용된다.

위 그림과 같이 트랜잭션의 격리 수준에 따른 동작을 지원하기 위해 사용된다. ⇒ 트랜잭션 격리 수준에 대해서는 5장에서 더 자세히 다룸

 

9. 체인지 버퍼

RDBMS 에서 레코드가 INSERT, UPDATE 될 경우 인덱스를 업데이트하는 작업도 발생하는데 이 작업의 경우, 랜덤하게 디스크를 읽는 작업이 필요하므로 많은 자원을 소비한다.

이렇게 인덱스를 업데이트 하는 경우, 업데이트의 결과를 메모리 상 버퍼에 담아두었다가, 메모리상에서 먼저 처리하고 사용자에게 결과를 반환하기 위해 사용되는 버퍼

 

10. 리두로그

트랜잭션의 ACID 특징 중 D에 해당하는 영속성과 관련이 있다.

트랜잭션 중간에 서버가 비정상적으로 종료 되도 하려던 작업을 이어서 하기 위한 로그

다음 두 과정에서 리두로그가 사용된다.

• 커밋됐지만, 테이터 파일에 아직 기록되지 않은 데이터 처리
• 롤백됐지만, 데이터 파일에 이미 기록되어 버린 데이터 처리

롤백됐지만, 데이터 파일에 이미 기록되어 버린 데이터 처리의 경우 리두로그가 아니라 언두로그가 사용될 것 같지만, 변경사항이 커밋됐는지, 롤백됐는지 판단하는 단계에서 리두로그가 공통으로 사용된다.

 

11. 어댑티드 해시 인덱스

자주 사용되는 컬럼을 해시로 정의하여, B-Tree 인덱스를 타지 않고 바로 데이터에 접근할 수 있는 기능

초당 쿼리 처리 수는 늘었지만, 오히려 CPU 사용량은 더 줄일 수 있다.

 

하지만, 어댑티드 해시 인덱스는 “어댑티드”라는 말처럼 자주 사용 되는 데이터를 메모리 풀에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 새로운 데이터를 버퍼풀이아니라 디스크에서 직접 읽어와야 하는 작업이 많은 경우에는 도움이 되지 않고 오히려 어댑티드 해시 인덱스를 생성, 삭제하고, 메모리를 할당하고 하는 작업들이 오버헤드로 작동 할 수 있다.

 

대부분의 쿼리가 일부 특정한 데이터를 빈번하게 읽는 경우이면서, B-Tree 인덱스를 타는 비용을 줄일 때 효과가 좋다.

 

출처

[MySQL] Innodb Buffer Pool 구조 및 캐시 전략

 

[MySQL] 4. 아키텍처(2)

 

MySQL - 아키텍처 정리 (3) - Real MySQL 8.0

 

InnoDB Flushing in Action for Percona Server for MySQL - Percona Database Performance Blog

 

MySQL InnoDB의 Adaptive Hash Index 활용