InnoDB 스토리지 아키텍처

2023.10.10 복습 리팩토링 시작

InnoDB 스토리지 아키텍처

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며,

그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. 

InnoDB 구조

이제 InnoDB 스토리지 엔진의 주요 특징들과 함께 아키텍처를 살펴보자.

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

• InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다.
• 즉 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며,
  모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
• 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
• 결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높음)된다.
• MyISAM 스토리지 엔진은 클러스터링 키를 지원하지 않는다.
• 그래서 MyISAM은 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

외래 키 지원

• 외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM과 MEMORY 테이블에서는 사용할 수 없다.
• 외래 키는 DB 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데,
  그렇게 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.
• InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고,
  변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고,
  그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.
• 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다.
• 이런 경우에는 foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
• 외래 키 작업을 일시적으로 멈추면 대략 레코드 적재나 삭제 등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.
• 외래 키 작업을 일시적으로 해체했다고 해서 부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지해도 된다는 것을 의미하지는 않는다. 항상 부모 테이블과 자식 테이블의 일관성을 유지해야 한다.
• 그리고 foreign_key_checks가 비활성화되면 외래 키 관계의 부모 테이블에 대한 작업 (CASCADE 옵션)도 무시하게 된다.

SET foreign_key_checks=OFF;

// 작업 실행

SET foreign_key_checks=ON;

MVCC (Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며,

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

InnoDB는 언두 로그를 이용해 이 기능을 구현한다.

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

• 예제는 과거 스프링 DB 강의에서 진행한 것과 유사했다. 현재 격리 수준 READ_COMMITTED.
• 예제 데이터를 Update 한다. 커밋 명령어를 입력하지 않았으므로 디스크에 저장된 것은 아님. 자동 커밋 설정도 아님.
• Update 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDb 버퍼 풀은 새로운 값으로 업데이트된다.
• 이제 실습하는 데이터 값을 조회하면 어떤 값이 나올까? 답은 격리 수준에 따라 다르다.
• READ_UNCIMMITTED인 경우에는 변경한 데이터가 나온다.
• READ_COMMITTED 이상의 격리 수준이면 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다. 
• 즉 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보이는지 여러 가지 상황에 따라 달라지는 구조다.

정리하면 Update 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고, 기존 데이터는 언두 영역으로 복사된다.

이 상태에서 COMMIT 명령어를 실행하면 InnoDB는 더 이상 변경 작업 없이 지금의 상태를 영구적인 데이터로 만든다.

하지만 ROLLBACK 명령어를 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고,

언두 영역의 내용을 삭제한다. 커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다.

이 언두 영역을 필요로 하는 트랜잭션이 없을 때 비로소 삭제된다.

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

• InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
• 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
• 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED, REPEATABLE_READ 수준인 경우 INSERT와  연결되지 않은
  순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
• 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.
• 이를 '잠금 없는 일관된 읽기'라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

자동 데드락 감지

• InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기목록을 그래프 형태로 관리한다.
• InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.
• 이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.
• 트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다.
• InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수 있다.
• innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 같이할 수 있게 된다. 웬만하면 해당 시스템 변수를 활성화하자.

문제점

• 동시 스레드가 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
• 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다.
• 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악역향을 미치게 된다.
• 이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수 있다.

해결방식

• 이런 문제점을 해결하기 위해서는 innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드가 작동하지 않는다.
• 만약 데드락 감지 스레드가 작동하지 않으면 스토리지 엔진 내부에서 2개 이상의 트랜잭션에서 트랜잭션이 발생해도 누군가가 중재하지 않기 때문에 무한 대기에 빠진다.
• 하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환한다. 해당 변수는 초 단위로 설정할 수 있다.
• 데드락 스레드가 부담되어 innodb_deadlock_detect를 off로 비활성화 한다면, innodb_lock_wait_timeout을 기본 값인 50초보다 훨씬 낮은 시간으로 변경해서 사용하자.

자동화된 장애 복구

• InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 다양한 메커니즘이 있는데, MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
• InnoDB 스토리지 엔진은 매우 견고해 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않는다.
• 하지만 MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못 하는 경우도 발생할 수 있는데, 일단 한 번 문제가 생기면 복구하기가 쉽지 않다.
• InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다.
• 이 단계에서 자동으로 복구 될 수 없는 손상이 있다면, 복구를 멈추고 MySQL 서버는 종료된다.
• 이때는 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다. 
• 1부터 6까지 값을 넣을 수 있으며 숫자가 커질 수록 심각한 상황이라는 뜻이다.
• 6 값을 넣어도 MySQL 서버가 시작되지 않으면 백업이 존재한다면 백업으로 새로운 데이터베이스를 구축하고,
  바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구해야 한다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다.

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

일반적인 애플리케이션에서는 CUD 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다.

하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크의 작업을 줄일 수 있다.

버퍼 풀의 크기 설정

• 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.
• 레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하는데,
  커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수도 있다.
• MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없으며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라 결정된다. 또한 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수가 없다.
• MySQL 5.7 버전 부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선했다.
• 운영체제의 전체 메모리 공간이 8GB 미만이라면 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보해주는 것이 좋다.
• 메모리 공간이 8GB 이상이라면 InnoDB 버퍼 풀의 크기를 전체 메모리에서 50%에서 시작해서 조금씩 올려가면서 최적점을 찾는다.
• 메모리 공간이 50GB 이상이면 대략 15GB ~ 30GB만 다른 응용 프로그램에 사용하고 나머지는 버퍼 풀로 할당하자.
• InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기가 설정 가능하며, 동적으로 버퍼풀의 크기를 확장할 수 있다.
• 중요한 변경이므로 MySQL 서버가 한가할 때 진행하자.
• 버퍼풀을 확장하는 작업보다 줄이는 작업은 매우 위험하므로 가능하면 줄이지 말자.
• InnoDB 버퍼 풀은 버퍼풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발했는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선했다.
• innodb_buffer_pool_instance 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 인스턴스라고 표현한다.
• 메모리 공간이 40GB 이하면 기본 값인 8을 유지. 이상이면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋다. 만약 메모리가 1GB 이하면 인스턴스는 1개만 생성.

버퍼풀의 구조

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

• 버퍼 풀의 페이지 크기 조각을 관리하기 위해 스토리지 엔진은 크게 LRU 리스트, Flush 리스트, Free 리스트라는 3개의 자료 구조를 관리한다.
• 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
• LRU는 아래와 같은 구조를 가지는데, LRU(Old List)와 MRU(New List) 리스트가 합쳐진 상태다.

 

버퍼풀 관리를 위한 LRU 리스트 구조

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

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
   1. InnoDB 어댑티브 해시 인덱스를 사용해 페이지를 검색
   2. 해당 테이블의 인덱스를 이용해 버퍼 풀에서 페이지를 검색
   3. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가.
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동( 대량 읽기의 경우는 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지 않을 수도 있으므로, 이런 경우는 MRU로 이동되지 않음)
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고 결국 해당 페이지는 버퍼 풀에서 제거 된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 덞어지고 MRU 헤더 부분으로 옮겨진다.
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.

그래서 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 살아남게 되고,

반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것이다.

 

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

• 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않는다.
• 그러나 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록해야 한다.
• 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
• 그래서 리두 로그의 엔트리는 특정 데이터 페이지와 연결된다.
• 하지만 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다.
• InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다.
• 체크 포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할을 한다.

버퍼 풀과 리두 로그

• InnoDB 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺는다.
• InnoDB 버퍼 풀은 서버의 메모리를 허용하는 만큼 크게 설정하면 할 수록 쿼리의 성능이 빨라진다.
• 단 디스크의 모든 데이터가 버퍼 풀에 적재되는 상황은 제외한다. 성능이 더 좋아질 수 없다.
• InnoDB 버퍼 풀은 DB 서버 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상 시키는 것이다.
• InnoDB 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지와 함께 CUD 명령으로 데이터를 가진 더티 페이지도 가지고 있다.
• 더티 페이지는 디스크와 메모리의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 한다.
• 하지만 더티 페이지는 버퍼 풀에 무한으로 머무를 수 있는 건 아니다.
• InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.
• 즉 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어쓰인다.
• 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그라고 한다.
• 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN(Log Sequence Number)라고 한다.
• InnoDB 스토리지 엔진은 주기적으로 체크 포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화한다.
• 이렇게 발생한 체크포인트 중 가장 최근 체크 포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.
• 하지만 활성 리두 로그 공간의 마지막은 게속해서 증가하기 때문에 체크 포인트와 무관하다.
• 그리고 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지라고 한다.
• 즉 체크포인트 에이지는 활성 리두 로그 공간의 크기를 일컫는다.
• InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 한다.
• 물론 당연히 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화돼야 한다.
• 처음부터 리두 로그 파일의 전체 크기를 적절히 선택하기 어렵다면 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 대략 5 ~ 10 GB 수준으로 선택하고 필요할 때마다 조금씩 늘려가면서 최적 값을 선택하는 것이 좋다.

버퍼 풀 플러시

과거 5.6 버전까지는 디스크 폭증 현상이 종종 발생했다.

MySQL 8.0 부터는 대부분의 서비스에서 더티 페이지를 디스크에 동기화하는 부분에서 디스크 쓰기 폭증 현상이 발생하지 않았다.

InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 시스템 설정은 특별히 서비스 운영 시에 문제가 없다면 이 시스템 변수는 조정할 필요가 없다고 한다.

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해

아래와 같은 2개의 플러시 기능을 백그라운드로 실행한다.

• 플러시 리스트 플러시
  • InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다.
  • 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
  • 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동화시키는 작업을 수행한다.
  • 이때 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.
  • 이를 위해 InnoDB 스토리지 엔진은 다양한 시스템 변수들을 제공한다. (ex. innodb_page_cleaners, innodb_max_dirty_pages_pct_lwm 등)
• LRU 리스트 플러시
  • InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데 이를 위해 LRU 리스트 플러시 함수가 사용된다.

버퍼 풀 상태 백업 및 복구

• InnoDB 서버의 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결돼 있다.
• 쿼리 요청이 매우 빈번한 서버를 셧다운했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안되는 경우가 대부분일 것이다.
• 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로 디스크에서 데이터를 읽지  않아도 쿼리처리가 될 수 있기 때문이다.
• 이렇게 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업이라고 한다.
• MySQL 5.6 부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다.
• 서버 점검이나 기타 작업을 위해 MySQL 서버를 재시작해야 하는 경우 MySQL 서버를 셧다운하기 전에 다음과 같이 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.
• 그리고 서버를 다시 시작하면 innodb_buffer_pool_load_now 변수를 사용해 워밍업 상태로 만들 수 있다.
• 백업된 내용은 적재된 데이터의 메타 정보만 가져와 저장하므로 매우 가볍다.
• 그러므로 복구하는 과정은 디스크에서 데이터 페이지를 다시 불러오므로 오래 걸릴 수 있다.
• MySQL은 풀의 백업과 복구를 자동화하는 기능도 제공한다.
• 버퍼풀의 적재 내용을 확인하기 위해 MySQL 8.0 버전부터는 information_schema 데이터베이스에 innodb_cached_indexes 테이블을 제공한다.
• 이 테이블을 이용해 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다.

Double Writer Buffer

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

InnoDB 스토리지 엔진은 이런 문제를 막기 위해 Double-Write 기법을 이용한다.

예시를 확인해보자.

1. A,B,C,D,E라는 더티 페이지를 디스크로 플러시할 것이다.
2. 그럼 더티 페이지를 시스템 테이블 스페이스의 DoubleWrite 버퍼에 기록한다.
3. 이후 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.
4. 만약  A ~ E까지 디스크에 정상적으로 플러쉬 되면 DoubleWrite 버퍼 공간에 기록한 내용은 쓸모가 없어진다.
5. 만약 D 페이지가 기록되는 도중에 OS가 비정상적으로 종료됐다고 가정하자. 
6. 그러면 InnoDB 스토리지 엔진은 재시작될 때 항상 Double Write 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 Double Write 버퍼의 내용을 데이터 파일의 페이지로 복사한다.

DoubleWirte 기능을 사용할지 여부는 innodb_doublewrite 시스템 변수로 제어할 수 있다.

데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋다.

언두 로그

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

이렇게 백업된 데이터를 언두 로그라고 한다. 언두 로그는 아래와 같을 때 사용한다.

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

MySQL 5.5 버전까지는 언두 로그의 사용 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하지 않는 한 언두 로그 공간을 줄일 수가 없었다.

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

 

다행히 MySQL 5.7과 8.0으로 업데이트되면서 이 문제는 해결됐다. 

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

 

아래와 같은 명령어로 언두 로그가 얼마나 증가했는지 모니터링이 가능하다. MySQL 8.0 버전에서 가능한 SQL.

SELECT count 
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

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

체인지 버퍼

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

그런데 인덱스를 업데이트하는 작업은 랜덤 하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다.

그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트를 해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상하게 되는데,

이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

• 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.
• 체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있다. 필요하다면 50%도 가능.
• 체인지 버퍼가 너무 많은 공간을 사용하지 못하도록 사용하거나 더 많은 체인지 버퍼 풀을 사용하고자 한다면 innodb_change_buffer_amx_size 시스템 변수 비율을 설정하면 된다.

리두 로그 및 로그 버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID 중에서 D (Durable)에 해당하는 영속성과 가장 밀접하게 연관돼 있다.

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

• MySQL 서버를 포함한 대부분 DB 서버는 데이터 변경 내용을 로그로 먼저 기록한다.
• 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하다. 그러므로 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요하다.
• 이로 인한 성능 저하를 막기 위해 DB 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며,
• 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구한다.

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

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

첫 번째 경우는 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하면 된다.

하지만 2번의 경우는 리두 로그로는 해결할 수 없다.

이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하며 된다.

그렇다고 해서 2번의 경우 리두 로그가 전혀 필요하지 않은 것은 아니다.

최소 그 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 확인하기 위해서라도 리두 로그가 필요하다.

 

DB 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다.

하지만 이렇게 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발한다.

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

위 변수는 0, 1, 2 값을 가질 수 있다

 

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

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

만약 TEXT, BLOB 같은 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것이 좋다고 한다.

어댑티브 해시 인덱스

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

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

사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화 할 수 있다.

 

B-Tree 인덱스에서 특정 값을 찾는 과정은 매우 빠르게 처리된다고 사람들이 생각하지만, 이는 상대적인것이다.

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

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

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

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

 

해시 인덱스는 '인덱스 키 값'과 해당 인덱스 키 값이  저장된 '데이터 페이지 주소'의 쌍으로 관리되는데,

인덱스 키 값은 'B-Tree 인덱스의 고유 번호(Id)와 B-Tree 인덱스의 실제 키 값' 조합으로 생성된다.

어댑티브 해시 인덱스의 키 값에 'B-Tree 인덱스의 고유번호'가 포함되는 이유는 InnoDB 스토리지 엔진에서

어댑티브 해시 인덱스는 하나만 존재하기 때문이다.

즉, 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며,

특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문이다.

 

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

그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 페이지가 없어지면

어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다.

 

책에서는 성능 향상의 예시까지 보여주며 어댑티브 해시 인덱스가 만능인 것처럼 말하지만 성능 향상에 크게 도움이 되지 않을 때도 있다고 한다.

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

아래와 같은 경우에는 성능 향상에 도움이 된다.

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

어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이다.

따라서 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에는 도움이 되지 않는다.

해시 인덱스의 효율이 없는 경우에도 InnoDB는 계속 해시 인덱스를 사용해야 한다.

또한 어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업은 치명적인 작업이 된다.

 

스토리지 엔진으로는 결국 InnoDB를 사용하는 것이 좋다고 한다.

 

이상으로 포스팅을 마칩니다. 감사합니다.

 

참고

 REAL MySQL 8.0 1권

 

2023.10.11 복습 리팩토링 마무리