RealMySQL - InnoDB 스토리지 엔진 아키텍처

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

• InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
• 프라이머리 키 값의 순서대로 디스크에 저장된다.
• MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다.

외래 키 지원

• 외래 키에 대한 지원은 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
• foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
• 체크가 비활성화되면 외래키 관계의 부모 테이블에 대한 작업(ON DELETE CASCADE와 UPDATE CASCADE 옵션)도 무시하게 된다.

MVCC(Multi Version Concurrency Control)

• MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다.
• InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다.

예) 2명이서 동시에 신청 했을 때, 1명만 되야 한다.
이런 상황에서 DBMS가 사용하는 공통적인 방법이 잠금(lock)이다.

• 정리하자면, 잠금은 트랜잭션 처리의 순차성을 보장하기 위한 방법이다.

Undo log는 단일 트랜잭션과 관련된 Undo log 레코드의 집합
Undo log 레코드에는 클러스터드 인덱스 레코드의 트랜잭션에 의한 제일 최근의 변경사항의 변경 전 데이터에 대한 정보를 포함하고 있다.

• 만약 다른 트랜잭션이 변경 전 데이터를 읽기를 원한다면 undo log 레코드에서 변경 전 데이터를 읽는다.

• Update 쿼리 실행 중 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 SELECT 조회 시 어디에 있는 데이터를 조회 할까? (버퍼 풀, 언두 로그, 데이터 파일(디스크))

• MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다.

• 격리 수준이 READ_UNCOMMITTED인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.

• READ_COMMITED, REPEATABLE_READ, SERIALIZABLE 인 경우에는 언두 영역의 데이터를 반환

• 이러한 과정을 DBMS에서는 MVCC라고 표현한다.

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

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

• 다른 트랙잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능

• 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를 잠금없는 일관된 읽기 라고 표현

• InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두로그를 사용한다.

자동 데드락 감지

• 데드락 감지 스레드 : 교착상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료

• 언두 로그 레코드를 적게가진 트랜잭션이 일반적으로 롤백의 대상

자동화된 장애 복구

• InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 자동으로 복구될 수 없는 손상이 있다면 자도 복구를 멈추고 MySQL 서버는 종료돼 버린다.

• MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다.

• 어떤 부분이 문제인지 알 수 없다면 설정값을 1~6까지 변경하면서 MySQL을 재시작해 본다.

• 값이 커질수록 그만큼 심각한 상황

InnoDB 버퍼 풀

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

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

버퍼 풀의 구조

• LRU(Least Recently Used), 플러시(Flush), 프리(Free) 리스트라는 3개의 자료 구조를 관리

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

• LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 것

• 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고(Aging) 결국 해당 페이지는 버퍼 풀에서 제거된다.

• 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.

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

버퍼 풀과 리두 로그

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

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

• InnoDB 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리한다.

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

버퍼 풀 플러시

버퍼풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.

• 플러시 리스트 플러시
  • 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다.
  • 버퍼풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
  • 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스클에 동기화 하는 작업 수행
  • 이때 언제부터 얼마나 많은 더티 페이지를 한번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.

• LRU 리스트 플러시
  • LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데 이를 위해 LRU 리스트 플러시 함수가 사용된다.

버퍼 풀 상태 백업 및 복구

• MySQL 5.6버전부터는 버퍼 풀 덤프 및 적재 기능이 도입
• InnoDB 버퍼 풀의 백업은 ib_buffer_pool 파일에 기록
• 해당 파일에서 데이터 페이지의 목록을 가져와서 실제 존재하는 데이터 페이지면 버퍼풀로 적재하지만 그렇지 않은 경우에는 조용히 무시한다.

Double Write Buffer

• 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다. 이 문제를 막기 위해 InnoDB 스토리지 엔진은 Double-write 기법을 사용한다.

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

• DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래 목적으로 사용된다. 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다. 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite를 사용하면 좋다.

언두 로그

• 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전의 데이터를 별도로 백업하는데, 이를 언두 로그라고 한다.

• 트랜잭션 보장

  • 트랜잭션이 롤백 됐을 때, 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구한다.

• 격리 수준 보장

  • 트랜잭션 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 읽기도 한다.

언두 로그 모니터링

mysql> UPDATE member SET name='홍길동' WHERE member_id=1;

• 위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터에 내용이 반영된다. 만약 변경되기 전의 값이 벽계수 였다면 언두 영역에는 벽계수 라는 값이 백업되는 것이다.

• 이상태에서 사용자가 커밋을 하면 현재 상태가 그대로 유지되고, 롤백을 하면 언두 영역에 저장된 데이터를 다시 복구한다.

• 언두 로그의 데이터는 두 가지 용도로 사용

  • 트랜잭션의 롤백 대비용
  • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공

언두 로그의 문제점

• 만약 1억 건의 레코드가 저장된 100GB 테이블을 DELETE 로 삭제한다고 가정
• MySQL 서버는 두로그에 데이터를 저장
• 이렇게 1억건의 레코드가 테이블에서는 삭제되지만 언두 로그에 복사가 되야한다. 즉, 테이블의 크기만큼 언두 로그의 공간 사용량이 늘어난다.

• 트랜잭션이 오랜 시간 실행될 때도 언두 로그의 양이 급격히 증가한다.
• 트랜잭션이 완료됬다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것이 아니다.

• 트랜잭션 B, C는 각각 UPDATE, DELETE를 실행했으므로 변경 이전의 데이터를 언두 로그에 백업했을 것이다.

• 하지만 먼저 시작된 A 트랜잭션이 아직 활성 상태이기 때문에 B, C 트랜잭션의 완료 여부와 관계없이 B, C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않는다.

• 이렇게 누적된 언두 로그로 인해 디스크의 사용량이 증가하는 것은 상대적으로 큰 문제가 아니다.

• 하지만 그동안 빈번하게 변경되는 레코드를 조회하는 쿼리가 자주 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 급격히 저하된다.

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

• 하지만 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않다.

• 서버 별로 안정적인 시점의 언두 로그 건수를 확인해 이를 기준으로 언두 로그의 급증 여부를 모니터링 하는 것이 좋다.

언두 테이블스페이스 관리

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

체인지 버퍼

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

• 변경해야 하는 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크에서 읽어와야 한다면 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태를 통해 성능을 향상 시키는데, 이 때 사용하는 임시 메모리를 체인지 버퍼(Change Buffer)라 한다.

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

리두 로그 및 로그 버퍼

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

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

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

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

• 1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.

• 2번의 경우는 리두 로그로는 해결이 불가능하다. 이 때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하며 된다.

• 그렇다고 해서 2번의 경우 리두 로그가 전혀 필요하지 않은 것은 아니다. 최소한 그 변경이 커밋, 롤백 또는 트랜잭션 실행 중 상태였는지를 확인하기 위해서라도 리두 로그가 필요하다.

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

• 적절히 변경된 내용을 버퍼 풀에 모았다가 한 번에 디스크로 기록해야 하므로 리두 로그 파일의 전체 크기가 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택되어야 한다.

• 하지만 사용량(특히 변경이 잦은 작업)이 매우 많은 DBMS의 경우는 이 리두 로그 기록 작업이 큰 문제가 되는데, 이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링 한다.

• 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼이다. 로그 버퍼의 크기는 데이터를 자주 변경하는 경우에는 크게 설정하는 것이 좋고 일반적인 상황에서는 기본 값 수준에서 설정하는 것이 적합하다.

리두 로그 아카이빙

• MySQL 서버에 유입되는 데이터 변경이 너무 많으면 이에 따라 리두 로그가 매우 빠르게 증가하여 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰일 수 있다.

• 이렇게 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어서 백업이 실패하게 된다.

• MySQL 8.0의 리두 로그 아카이빙기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다 하더라도 백업이 실패하지 않게 해준다.

리두 로그 활성화 및 비활성화

• MySQL 서버에서 트랜잭션이 커밋되어도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록된다.

• 리두 로그를 비활성화하고 필요한 작업(대용량 데이터 적재 등)을 완료했다면 반드시 리두 로그를 재활성화하자.

• 리두 로그가 비활성화된 상태에서 MySQL 서버가 비정상적으로 종료되면 MySQL 서버의 마지막 체크포인트 이후 시점의 데이터는 모두 복구할 수 없게 되기 때문이다.

어댑티브 해시 인덱스

• 사용자가 수동으로 생성하는 인덱스가 아닌 InnoDB 스토리지 엔진에서 사용자가 요청 하는 데이터에 대해 자동으로 생성하는 인덱스이다.

• B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다.

• 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에, 데이터 페이지를 디스크에서 읽어오는 경우가 잦은 경우에는 비활성화하는 것이 좋다.

InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

• MySQL 8.0 부터는 MySQL 서버의 모든 시스템들이 InnoDB 스토리지 엔진으로 교체되었고 공간 좌표 검색이나 전문 검색 기능들 또한 모두 InnoDB 스토리지 엔진을 지원하도록 개선되었다.

• 즉, MySQL의 모든 서버 기능들을 InnoDB 스토리지 엔진으로만 구현할 수 있게 된 것이다. 따라서, 추후 MyISAM 스토리지 엔진은 없어질 것으로 예상 된다.