[MySQL] InnoDB 스토리지 엔진 아키텍처

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

InnoDB 스토리지 엔진 아키텍처

---

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

1. PK에 의한 클러스터링

---

InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링 되어 저장된다. (PK 키 값의 순서대로 디스크에 저장된다는 뜻)

• 모든 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용한다.
• 이러한 이유로 PK을 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.

2. 외래 키 지원

---

MyISAM, MEMORY 테이블과 다르게 InnoDB 스토리지 엔진 레벨에서는 외래 키를 지원한다.

• 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 그렇다 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.
• InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블에 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.

3. MVCC (Multi Version Concurrency Control)

---

MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다.

• InnoDB는 언두 로그를 이용해 이 기능을 구현한다.
• 하나의 레코드에 대해 2개의 버전이 유지된다.

Undo 로그

UPDATE, DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳.

UPDATE member SET name = '강승호' WHERE member_id = 1;

다음 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 '강승호'로 변경된다.
그리고 변경되기 전의 값이 'Will'였다면 언두 영역에는 'Will'라는 값이 백업되는 것이다.
이 상태에서 만약에 사용자가 커밋하게 되면 현재 상태가 그대로 유지되고, 롤백하게 되면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.
다음 상황에서 아직 COMMIT 이나 ROLLBACK 되지 않은 상태에서 다른 사용자가 해당 레코드를 조회하면 어디에 있는 데이터를 조회할까?

SELECT * FROM member WHERE member_id = 1;

이에 대한 답은 MySQL 서버의 시스템 변수(transactioon_isolation)에 설정된 격리 수준에 따라 다르다.

• 격리 수준이 READ_UNCOMMITED인 경우에는 InnoDB 버퍼 풀이나 데이터 파일로부터 변경되지 않은 데이터를 읽어서 반환한다. (Dirty Read)
• READ_COMMITED 이상의 격리 수준에서는 아직 커밋되지 않았기 때문에, InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

이 상태에서 COMMIT 명령 (UPDATE 쿼리)을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버린다. 하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다. (정확히는 커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것이 아니라, 언두 영역이 필요하는 트랜잭션이 더 이상 없을 경우 삭제한다)

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

---

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

• 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
• 격리 수준이 SERIALIZABLE이 아닌 격리수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.

5. 자동 데드락 감지

---

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

• InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.
• 어느 트랜젹션을 먼저 강제 종료할 것인지에 대한 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. (언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다)

cf) 참고로 InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데, innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 된다. (특별한 이유가 없다면 innodb_table_locks 시스템 변수를 활성화하자)

6. 자동화된 장애 복구

---

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

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

7. InnoDB 버퍼 풀

---

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

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

• INSERT, UPDATE, DELETE 처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤 한 디스크 작업을 유발하는데, 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤 한 디스크 작업의 횟수를 줄일 수 있다.

버퍼 풀의 구조

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

버퍼 풀의 크기 조각을 관리하기 위해 크게 LRU(Least Recently Used) 리스트, 플러시 리스트, 프리 리스트라는 3개의 자료 구조를 관리한다.

프리 리스트

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

LRU 리스트

• LRU + MRU 리스트가 결합된 형태.
• LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것.
• 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 MRU 영역에서 계속 살아남게 되고, 반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것이다.

플러시 리스트

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

버퍼 풀과 리두 로그

버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링의 용도가 존재.

• 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지와 변경된 데이터를 가진 더티 페이지를 가지고 있음.
• InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.
• 즉 데이터 변경이 계속 발생하면 리두 로그 파일에 기록했던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰인다.
• 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록될 때 마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN이라고 한다.
• InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.

버퍼 풀 플러시

플러시 리스트 플러시

• 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다.
• 이때 오래된 리두 로그 공간이 지워지려면 반드시 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
• 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다.

클리너 스레드

• 더티 페이지를 디스크로 동기화하는 스레드를 클리너 스레드라고 하는데, innodb_page_cleaners로 클리너 스레드의 개수를 조정할 수 있게 해 준다.

버퍼 풀 상태 백업 및 복구

버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결돼 있다.

쿼리 요청이 매우 빈번한 서버를 셧다운 했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안 되는 경우가 대부분일 것이다.

• MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입돼서, 서버 점검이나 기타 작업을 위해 MySQL 서버를 재시작해야 하는 경우 MySQL 서버를 셧다운 하기 전에 다음과 같이 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다. 그리고 MySQL 서버를 다시 시작하면 innodb_buffer_pool_load_now 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

SET GLOBAL innodb_buffer_pool_load_dump_now=ON; // MySQL 서버 셧다운 전에 버퍼 풀의 상태 박업

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

버퍼 풀의 적재 내용 확인

MySQL 8.0 버전에서는 information_schema 데이터베이스에 inodb_cached_indexes 테이블이 새로 추가됐다. 이 테이블을 이용하면 테이블의 인덱스 별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다.

8. Double Write Buffer

---

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

• 이로 인해 InnoDB 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시 할 때 일부분만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다.
• 이렇게 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 한다. (이런 현상은 하드웨어 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다.)

이러한 문제를 막기 위해 Double-Write 기법을 이용한다.

실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.

그리고 각 더티 페이지를 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.

• 실제 데이터 파일에 더티 페이지가 정상적으로 기록되면 더 이상 필요가 없어진다. (DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다)
• A, B 페이지는 정상적으로 기록됐지만 C 페이지가 기록되는 도중 운영체제가 비정상적으로 종료됐다고 가정하면, InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지를 복사한다.

9.  언두 로그

---

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이렇게 백업된 데이터를 언두 로그라고 한다.

용도

• 트랜잭션 보장
  • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 한든데, 이때 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구한다.
• 격리 수준 보장
  • 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
  • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 있음

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

10. 체인지 버퍼

---

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

• 그런데 인덱스를 업데이트하는 작업은 랜덤 하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 작업을 소모하게 된다.
• 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상하게 되는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

단, 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

• 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다.

11. 리두 로그 및 로그 버퍼

---

리두 로그는 트랜잭션 ACID 성질 중에서 D에 해당하는 영속성과 가장 밀접하게 연관되어 있다.

리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안정장치이다.

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

• 리두 로그는 쓰기 비용이 낮은 자료구조를 가졌으며, 비정상 종료가 발생하면 리두 로그 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구한다.

12. 어댑티브 해시 인덱스

---

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

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

• B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다.