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

InnoDB 스토리지 엔진 아키텍처

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

InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링되어 저장됩니다. 따라서 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용합니다.

쿼리의 실행 계획에서 클러스터링 된 프라이머리 키는 기본적으로 세컨더리 인덱스에 비해 비중이 높게 설정됩니다.

외래 키 지원

InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파됩니다. 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의해야 합니다.

외래 키가 복잡하게 얽혀있는 경우 데이터 적재, 스키마 변경 등의 작업이 어려울 수 있습니다. 이 경우 foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 체크 작업을 일시적으로 멈출 수 있습니다. 이후 다시 외래 키 체크 기능을 활성화하려는 경우에는 반드시 부모, 자식 테이블 간에 일관성을 맞춰 주어야 합니다.

MVCC(Multi Version Concurrency Control)

MVCC는 하나의 레코드에 대해 여러 개의 버전을 동시에 관리하는 기능을 제공합니다. 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 그 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있습니다.

InnoDB는 언두 로그(Undo log)를 이용해 이를 구현합니다. 데이터를 변경하고 COMMIT 명령을 실행하면 버퍼 풀의 변경 사항이 영구적으로 반영됩니다. 하지만 ROLLBACK 명령을 실행하면 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제합니다.

InnoDB 버퍼 풀의 변경 내용은 InnoDB 스토리지 엔진의 백그라운드 스레드에 의해서 기록됩니다. 따라서 버퍼 풀의 변경 내용이 디스크의 데이터 파일에 기록됐는지 여부는 시점에 따라 다를 수 있습니다.

커밋이 된다고 언두 영역의 백업 데이터가 바로 삭제되지는 않으며, 언두 영역을 필요로 하는 트랜잭션이 더이상 없을 때 삭제됩니다.

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

InnoDB 스토리지 엔진은 MVCC를 이용해 잠금을 걸지 않고 읽기 작업을 수행합니다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능합니다.

SERIALIZABLE을 제외한 격리 수준에서 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행됩니다.

오랜 시간 동안 활성 상태인 트랜잭션이 존재하는 경우 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속해서 유지해야 할 수 있습니다. 이는 성능 저하나 문제를 야기할 수 있으므로 트랜잭션이 시작되었다면 최대한 빨리 완료하는 것이 좋습니다.

자동 데드락 감지

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

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

innodb_table_locks 시스템 변수를 활성화하면 레코드 잠금 뿐만 아니라 MySQL 엔진이 관리하는 테이블 레벨의 잠금까지 감지할 수 있어 특별한 이유가 없다면 활성화하는 것이 좋습니다.

동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려집니다.

• 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트에 새로운 잠금을 걸고 데드락 스레드를 탐색
• 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향이 발생
  
  

이러한 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, 이를 OFF로 설정하면 데드락 감지 스레드는 작동하지 않습니다. 따라서, 데드락 상황이 발생하면 무한정 대기가 발생할 것입니다. 하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 데드락 상황에서 일정 시간 후 요청이 실패하고 에러 메시지를 반환합니다. 책에서는 데드락 감지 스레드가 부담되어 innode_deadlock_detect를 OFF 한다면 innodb_lock_wait_timeout의 설정값을 적절하게 조절하라고 말합니다.

자동화된 장애 복구

InnoDB는 손상, 장애로부터 데이터를 복구하는 여러 메커니즘을 가지고 있습니다. MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업이 자동으로 진행됩니다.

단, 하드웨어의 문제로 자동 복구가 불가한 경우 innodb_force_recovery 시스템 변수를 이용할 수 있습니다. 해당 설정값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 합니다.

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 합니다. 이를 통해 랜던한 디스크 작업의 횟수를 줄일 수 있습니다.

버퍼 풀의 크기 설정

• MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절 가능
• 책에서는 작은 값부터 시작하여 적절한 값을 찾아가는 방법을 권장
• 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리되며, 이는 버퍼 풀의 크기를 줄이거나 늘리기 위한 단위 크기로 사용
  
  

버퍼 풀의 구조

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

버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 프리(Free) 리스트라는 3개의 자료 구조를 관리합니다.

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

  
  

• LRU 리스트
  LRU 리스트는 LRU와 MRU(Most R.U)가 결합된 리스트 형태
  디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화

InnoDB 엔진에서 데이터를 찾는 과정은 다음과 같습니다.

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

• 플러시 리스트
  디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리
  변경이 발생하면 변경된 데이터 페이지는 플러시 리스트에 의해 관리되고 특정 시점에 디스크로 기록
  데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영
  
  

버퍼 풀과 리두 로그

InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라집니다.

InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 늘리는 것은 데이터 캐시 기능만 향상시키는 것입니다.

버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page) 와 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page) 를 가지고 있습니다.

• InnoDB에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용

• 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 다시 새로운 로그 엔트리로 덮어 쓰임

• 때문에 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리가 필요

• 재사용 불가능한 공간을 활성 리두 로그라 함

  
  

• 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록될때마다 로그 포지션은 계속 증가된 값을 가지게 되며 이를 LSN(Log Sequence Number)라고 함

• InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화

• 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점

  
  

InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN 보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 합니다.

버퍼 풀 플러시

InnoDB는 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행합니다.

• 플러시 리스트(Flush_list) 플러시
  InnoDB는 리두 로그 공간의 재활용을 위해 오래된 리두 로그 엔트리가 사용하는 공간을 비워주기 위한 플러시 리스트 플러시 함수를 주기적으로 호출
  오래전에 변경된 데이터 페이지 순서대로 디스크 동기화 작업을 수행
  얼마나 많은 더티 페이지를 한 번에 기록하느냐에 따라 사용자 쿼리 처리 성능에 영향을 미침

  
  

• LRU 리스트(LRU_list) 플러시
  LRU 리스트의 끝부분부터 최대 innodb_lru_scan_depth 시스템 변수만큼 페이지들을 스캔
  더티 페이지는 디스크에 동기화, 크린 페이지는 즉시 프리 리스트로 페이지를 이동

  
  

버퍼 풀 상태 백업 및 복구

디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업(Warming Up)이라고 표현합니다. 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 훨씬 높은 처리 속도를 보여줍니다.

MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능을 통해 셧다운 전 서버의 버퍼풀 상태를 백업하고 복구할 수 있습니다. 수동 작업 뿐만 아니라 InnoDB는 자동화 설정 또한 제공하고 있습니다.

리두 로그 및 로그 버퍼

리두 로그(Redo Log)는 트랜잭션의 4가지 요소인 ACID 중 D(Durable)에 해당하는 영속성과 밀접하게 연관되어 있습니다. 하드웨어나 소프트웨어 등의 문제로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치입니다.

대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록합니다. 또한, 데이터 파일은 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요합니다. 이로 인한 성능 저하를 막기 위해 쓰기 비용이 낮은 자료 구조를 가진 것이 리두 로그입니다.

변경 작업이 많은 서버의 경우 리두 로그의 기록 작업이 문제가 될 수 있으며, 이를 보완하기 위해 ACID를 보장하는 수준에서 버퍼링합니다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼입니다.

Double Write Buffer

InnoDB의 리두 로그는 공간 낭비를 막기 위해 페이지의 변경된 내용만 기록합니다. 이로 인해 하드웨어 오작동 등의 이유로 더티 페이지의 일부만 디스크 파일에 기록되는 문제가 발생하면 해당 페이지의 내용은 복구 하지 못할 수도 있습니다.

이를 막기 위한 방식의 Double-Write 기법입니다. InnoDB는 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록합니다. 그 후, 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행합니다.

DoubleWrite 버퍼에 기록된 내용은 실제 데이터 파일의 쓰기가 중간에 실패한 경우에 버퍼의 내용을 데이터 파일의 페이지로 복사합니다.

언두 로그

InnoDB는 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업합니다. 이렇게 백업된 데이터를 언두 로그(Undo Log)라고 합니다. 언두 로그는 다음과 같은 역할을 합니다.

• 트랜잭션 보장
  트랜잭션이 롤백되었을 때, 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 데이터를 복구
• 격리 수준 보장
  어떤 커넥션에서 데이터를 변경하던 도중 다른 커넥션이 데이터를 조회하면 격리 수준에 따라 언두 로그에 백업해둔 데이터를 읽어서 반환
  
  

언두 로그의 양은 대용량의 데이터를 처리하거나 트랜잭션이 오랜 시간 동안 수행될 때 급격하게 증가할 수 있습니다.

트랜잭션이 완료되지 않고 장시간 활성화되는 경우, 트랜잭션의 시작 시점부터 생성된 언두 로그의 양을 계속 보존하느라 디스크의 언두 로그 저장 공간도 계속 증가합니다. 만일 빈번하게 변경된 레코드를 조회하려는 경우 InnoDB는 언두 로그의 이력을 그만큼 많이 조회해야 하기 때문에 쿼리 성능이 전반적으로 떨어지게 됩니다.

체인지 버퍼

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

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있다면 바로 업데이트를 수행하지만 디스크로부터 읽어와서 업데이트해야 한다면 즉시 실행하는 대신 임시 공간에 저장해 두고 사용자에게 결과를 반환하는 형태로 성능을 향상시킵니다. 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 합니다. 단, 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다.

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되며, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread)라고 합니다.

어댑티브 해시 인덱스

어댑티브 해시 인덱스(Adaptive Hash Index)는 InnoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.

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

해시 인덱스는 'B-Tree 인덱스의 고유번호(Id) 와 B-Tree 인덱스의 실제 키 값'을 조합으로 한 인덱스 키와 '데이터 페이지 주소' 쌍으로 관리됩니다. 키 값의 경우 InnoDB에서 어댑티브 해시 인덱스는 단 하나만 존재하기 때문에 인덱스의 고유번호가 포함됩니다. 데이터 페이지 주소의 경우 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미합니다.

어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 인덱스에서도 해당 페이지의 정보는 사라집니다.

어댑티브 해시 인덱스가 도움이 되지 않는 경우도 있습니다.

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

다음과 같은 경우에는 성능 향상에 도움이 됩니다.

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

어댑티브 해시 인덱스가 활성화되면 InnoDB는 키 값이 해시 인덱스에 있든 없든 검색해봐야 합니다. 즉, 해시 인덱스의 효율이 없는 경우에도 InnoDB는 계속 해시 인덱스를 사용하게 됩니다.

테이블의 삭제 작업에도 영향을 끼칩니다. 테이블을 삭제하거나 변경하려 한다면 해당 테이블이 가진 모든 데이터 페이지의 내용을 해시 인덱스에서 제거해야합니다. 이로 인해 작업이 이루어지는 동안 상당히 많은 CPU 자원을 사용하고, 그만큼 데이터베이스 서버의 처리 성능이 느려집니다.

참고

Real MySQL 8.0 4장 아키텍처_4.2 InnoDB 스토리지 엔진 아키텍처