infoqoch's 개발 블로그

mysql, InnoDB 스토리지 엔진의 구조와 동작 방식

• • •

InnoDB 스토리지 엔진

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

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

  • InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉 프라이머리 키 값의 순서대로 디스크에 저장된다. 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.

외래 키 지원

  • 외래 키는 InnoDB만 지원.
  • 외래 키는
    • 부모 테이블과 자식 테이블에 외래키 칼럼에 대한 인덱스가 필요.
    • 변경 시 부모 테이블이나 자식 테이블의 데이터 존재 유무를 체크하여 잠금이 전파되어 데드락 발생 가능.
  • 위와 같은 이유로 서비스 환경에서는 사용하지 않더라도 개발 환경에서는 가이드 용으로 사용.
  • 수동으로 데이터를 적재하거나 변경 할 때, foregin_key_checks 변수를 세션에 한정하여 변경하여 외래 키에 대한 확인 절차를 정지하여, 빠르게 처리할 수 있음.

잠금 없는 일관된 읽기와 MVCC

  • InnoDB는 잠금 없는 일관된 읽기(Non-Locking Consistent Read) 제공.
    • Serializable 이하의 격리 수준에서는 순수한 읽기 작업에 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.
  • 잠금 없는 일관된 읽기는 MVCC(Multi Version Concurrency Control)를 활용하여 여러 개의 버전을 동시에 관리하기 때문에 가능
  • MVCC는 언두 로그를 활용
  • 오랜 시간 동안 활성 상태인 트랜잭션이 있을 경우, 해당 트랜잭션의 일관된 읽기를 보장하기 위해 언두 로그를 삭제하지 못함. 이로 인한 성능 및 메모리 문제가 발생할 수 있으므로 트랜잭션은 롤백이나 커밋을 통해 빠르게 완료하자!

자동 데드락 감지

  • 잠금의 교착 상태를 확인하기 위하여 잠금 대기 목록을 그래프(Wait for List) 형태로 관리한다.
  • 데드락 감지 스레드는 주기적으로 잠금 대기 그래프를 검사하여 교착 상태에 빠진 트랜잭션을 찾고 그 중 하나를 강제 종료 한다.
  • 강제 종료 대상을 선택하는 기준은 언두 로그의 양이 적은 쪽이다. 왜나하면 강제 롤백으로 인한 부하가 적기 때문이다.
  • InnoDB 엔진은 상위 엔진인 MySQL 엔진의 테이블 잠금을 확인하지 못하여 데드락 감지가 정확하지 않을 수 있다. InnoDB가 MySQL 엔진의 테이블 잠금을 감지할 수 있도록 innodb_table_lock 시스템 변수를 활성화 하자.
  • 동시 처리 스레드가 많아질 수록 데드락 감지 스레드의 성능이 낮아진다. 왜냐하면 잠금 목록을 검사할 때 새로운 잠금을 걸고 처리하기 때문이다. 그러므로 자동 데드락 감지는 필요에 따라 innodb_deadlock_detect를 off로 한다. 교착 상태가 발생할 경우 타임아웃으로 처리하며 이는 innodb_lock_wait_timeout 시스템 변수를 활성화 한다. 다만, 기본 값은 50초로 너무 길기 때문에 이보다 훨씬 낮은 시간으로 변경해서 사용한다.

자동화된 장애 복구

  • InnoDB 스토리지 엔진은 매우 견고하여 데이터 파일이 손상되는 문제는 거의 발생하지 않는다.
  • 어떤 이유로 장애가 발생한 채 서비스가 종료된 경우, MySQL 서버는 재시작할 때 장애를 자동으로 파악하고 복구를 시도한다. 만약 복구를 실패할 경우 서버는 시작되지 않는다. 이런 경우
    • MySQL 서버를 백업한 후,
    • innodb_force_recovery를 낮은 레벨(1)부터 높은 레벨(6)까지 변경하며 재시작을 통해 자동 복구를 유도한다.
    • 모두 실패한 경우 백업으로 데이터베이스를 새로 구축하고 바이너리로그로 최대한 장애 시점까지 복구한다.

InnoDB 버퍼풀

  • InnoDB의 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간. 읽기 작업은 디스크가 아닌 메모리를 참조하여 빠르게 처리하고 쓰기 작업은 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할 수행.

버퍼 풀의 구조

  • 버퍼 풀은 페이지(innodb_page_size)의 조각으로 나누어 관리한다.
  • LRU 리스트, 플러시 리스트, 프리 리스트라는 3개의 자료구조로 페이지를 관리한다.
  • LRU 리스트:
    • 한 번 읽은 페이지를 최대한 오랫동안 유지하여 디스크 읽기를 최소화하기 위한 알고리즘.
    • 자주 사용하는 페이지는 상위로 이동하고 그렇지 않은 페이지는 하위로 밀리다가 제거되는 방식.
    • 자주 접근하는 데이터는 어댑티브 해시 인덱스에 추가.
  • 플러시 리스트:
    • 디스크로 동기화 되지 않은 데이터를 가진 데이터 페이지(더티 페이지)를 관리.
    • 데이터가 변경되면 해당 페이지와 리두로그에 반영되고 페이지와 리두 로그는 연결된다.
  • 프리 리스트: 데이터가 채워지지 않은 페이지 목록.

더티 페이지와 리두 로그

  • 더티 페이지는 리두 로그와 연결되어 있다.
  • 주기적으로 체크포인트 이벤트를 발생하며 리두 로그와 더티 페이지를 디스크로 동기화한다. 이때 체크포인트가 발생한 특정 리두 로그(LSN) 이하의 더티 페이지는 반드시 디스크에 동기화 된다.
  • 버퍼풀과 리두로그의 크기는 적절하게 설정해야 한다.
    • 만약 리두로그가 너무 작을 경우, 리두 로그가 너무 빨리 차서 플러시를 해야 한다. 그래서 버퍼 풀을 쓰기 지연으로 사용할 수 없어 효과적이지 않다.
    • 반대로 리두로그가 극단적으로 클 경우, 리두 로그를 채우기 전에 버퍼풀에 더티 페이지가 가득 차서 리두 로그에 사용하지 않는 메모리가 발생한다. 리두로그가 허용 범위라 하더라도 과도하게 큰 것은 위험한데, 너무 많은 더티페이지를 허용할 경우 플러시할 때 급작스러운 디스크 쓰기가 발생할 수 있기 때문이다.
  • 버퍼 풀이 100gb 인 경우 리두 로그는 5-10gb 정도를 저자는 권장한다.

버퍼 풀 플러시(Buffer Pool Flush)

  • 리두 로그 공간을 재활용하기 위해서는 주기적으로 버퍼 풀의 더티 페이지를 디스크로 동기화 해야 한다. 주기적으로 플러시 리스트를 사용하여 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화(플러시)한다.
  • MySQL 8.0 이후 더티 페이지에 대한 디스크 동기화 과정에서 쓰기 폭증 현상(Disk IO Burst)이 많이 개선되었다. 그러므로 플러시 관련한 기능은 있는 그대로 사용하되, 이로 인한 장애 및 성능 문제가 발생할 때 관리해도 충분하다.
  • 성능 문제는 쓰기 폭증과 더불어 그 반대 상황으로 불필요하게 자주 쓰기 작업을 하는 경우도 포함한다. 적절한 중간 값을 찾아야 한다.
    • 기본적으로 전체 버퍼 풀 중 90%까지 더티 페이지를 가질 수 있는데 이를 조정할 수 있다.
    • 일정 비율 이상 더티페이지가 있을 경우 조금씩 더티 페이지를 디스크로 기록하는데 이 비율을 조정할 수 있다.
    • 디스크 쓰기 성능에 따라 더티 페이지의 양을 결정할 수 있다. 이를 세세하게 조정할 수도 있고 어댑티브 플러시(Adaptive flush) 알고리즘을 제공하여 리두 로그의 증가 속도를 기준으로 디스크 쓰기 성능을 파악하고 실행을 얼마나 할지 자동으로 결정할 수도 있다.

버퍼 풀 상태 백업 및 복구

  • MySQL 서버가 종료했다 재실행하는 경우, 버퍼 풀이 비어 있으므로 디스크 읽기부터 시작한다. 이로 인한 성능 문제가 발생한다. 그러므로 서버를 재실행 한 경우 워밍업을 위하여 주요 테이블과 인덱스를 풀 스캔을 수행하였다.
  • MySQL 5.6 이후는 버퍼 풀을 덤프 (innodb_buffer_pool_load_now)하여 재실행 시 버퍼풀을 복구할 수 있다.
  • 버퍼풀 덤프의 용량은 크지 않은데, 데이터 페이지의 메타 데이터만 가지고 있기 때문이다. 메타 데이터를 기반으로 디스크 데이터를 호출하기 때문에 서버 시작이 오래 걸릴 수 있다. 이 과정을 생략하고 바로 서비스를 재개할 수 있다(innodb_buffer_pool_load_abort).

버퍼 풀의 크기 설정

  • 버퍼 풀의 크기 설정은 크기(innodb_buffer_pool_size)는 서버 실행 도중 동적으로 조절 가능하다.
  • 적은 값으로 설정해서 조금씩 늘린다. 대체로 램이 8GB 이하라면 50%, 그 이상이라면 50%에서 조금씩 올려준다.
  • 버퍼 풀은 잠금(세마포어)으로 관리되어 이로 인한 내부 경합이 발생한다. 이를 해소하기 위하여 작은 버퍼풀로 쪼갠 후 작은 버퍼풀(버퍼 풀 인스턴스)에 대한 개별 잠금으로 분산 시켰다. 이 경우 버퍼 풀의 크기가 8GB 이하라면 1개, 40GB 이상이라면 기본값인 8개로 쪼갠다.

Double Write Buffer

  • 더티 페이지를 디스크에 반영할 때, 어떤 장애로 인하여 일부만 기록될 수 있다. 이를 복구하고 디스크 반영을 완전하게 처리하기 위한 기능이다.
  • 더티 페이지를 플러시를 할 때, 먼저 처리하는 더티 페이지 묶음을 DoubleWrite 버퍼에 보관한다. 만약 플러시가 제대로 되지 않은 상태에서 MySQL이 종료 될 경우, MySQL 서버가 재실행 되면 해당 버퍼의 내용에 따라 디스크에 반영 되었는지 확인한다. 그렇지 않을 경우 해당 버퍼의 데이터로 디스크에 반영한다.
  • 데이터 무결성을 위하여 가능하면 활성화한다. 만약 무결성이 중요하지 않은 서비스라면 리두 로그와 더불어 비활성화 할 수 있다.

언두 로그

  • 트랜잭션과 격리 수준에 따른 일관된 읽기를 보장하기 위하여 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이를 언두 로그라 한다.
  • 트랜잭션 보장: 롤백 될 경우, 언두 로그에 백업해 둔 이전 데이터로 복구한다.
  • 격리 수준 보장: 다른 커넥션이 변경 중인 데이터에 접근할 경우 격리 수준에 따라 언두 로그의 데이터를 반환할 수 있다.

언두 로그의 동작

  • update로 데이터 변경하며 동시에 아직 완료(커밋 혹은 롤백)되지 않은 경우?
    • 버퍼 풀의 데이터를 변경하고 & 언두 로그에 변경 전 값을 복사한다.
      • 변경된 버퍼 풀의 데이터는 상황에 따라 디스크에 동기화 되어 있을 수도 있다.
    • read commit 이상의 격리 수준을 가진 트랜잭션이 해당 데이터를 호출할 경우, 해당 변경 사항은 commit 되지 않았으므로 언두 영역의 데이터를 반환한다. 즉 하나의 레코드에 대해 여러 개의 버전이 유지되고, 필요에 따라 반환하는 데이터가 달라진다.
  • 롤백 될 경우? 언두 영역의 데이터로 버퍼 풀(혹은 디스크)을 이전 데이터로 복구하고 언두 영역을 삭제한다.
  • 커밋 될 경우? 언두 영역이 필요로 하는 트랜잭션 전체가 제거될 때, 비로소 삭제된다.

언두 로그의 공간 문제

  • 언두 로그의 공간은 무한정 커질 수 있다.
    • 어떤 레코드를 삭제하면, 삭제한 레코드를 언두 로그에 보관해야 한다. 언두로그가 정리된다 하더라도 늘어난 공간은 유지된다.
    • MySQL 8.0 이후부터 언두 로그 공간을 자동으로 줄여준다.
  • 어떤 트랜잭션이 오랫동안 유지될 경우 해당 트랜잭션의 일관된 읽기를 보장을 위하여 언두 로그의 데이터가 제거되지 않는다.
    • 트랜잭션을 장시간 유지하지 말자.

체인지 버퍼(Change Buffer)

  • DML로 인해 변경되어야 할 인덱스 페이지가 만약 버퍼 풀에 있으면 바로 업데이트를 수행한다. 만약 버퍼 풀에 없을 경우 디스크로부터 읽어야 하지만 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상킨다. 이를 위한 임시 메모리 공간을 체인지 버퍼라고 한다.
  • 다만, 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.
  • 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은, 백그라운드 스레드에 의해 병합되는데, 이를 체인지 버퍼 머지 스레드라고 한다.

리두 로그 및 로그 버퍼

  • 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
  • MySQL 서버를 포함한 대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다. 이는 다음과 같은 장점을 가진다.
    • 디스크 쓰기는 랜덤 엑세스가 필요하며 이로 인한 성능 문제가 발생한다. 쓰기 비용을 최소화하기 위해 미리 로그에 저장하여 빠르게 안정성을 확보한다.
    • 비정상 종료가 발생하더라도 리두 로그에 따라 순서에 맞춰 복구할 수 있으므로 더 안전하다.
  • MySQL 서버가 비정상 종료 된 경우, 다음과 같은 상황으로 일관되지 않은 데이터를 가진다.
    • 커밋됐지만 데이터 파일에 기록되지 않은 데이터
    • 롤백됐지만 데이터 파일에 이미 기록된 데이터
  • 전자의 경우 리두 로그로 복구하면 된다.

  • 후자의 경우 언두 로그로 복구한다. 다만, 커밋됐는지, 롤백됐는지, 트랜잭션의 어떤 상태였는지는 리두로그를 통해서만 알 수 있다.

  • 리두 로그의 저장 과정은 다음과 같다. 리두 로그 발생 -> 리두 버퍼 저장 -> OS 메모리 저장 -> 디스크 쓰기. 이 과정을 어떻게 하느냐에 따라 쓰기 성능에 영향을 미친다. 기본값(innodb_flush_log_at_trx_commit)은 1로서 리두 로그가 즉각 디스크로 반영된다. 0과 2는 디스크 동기화까지 기본 1초의 시간이 걸리며 이 사이에 문제가 발생할 경우 데이터는 유실되나 성능이 빠르다. 실제 테스트 결과는 아래 블로그를 참고하자.

  • https://yunhyeonglee.tistory.com/41

리두 로그 아카이빙

  • MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 복사된 데이터 백업 파일의 일관성 보장을 위하여 리두 로그 엔트리를 함께 복사한다.
  • 다만, MySQL 서버의 데이터 변경이 많은 경우 리두 로그가 매우 빠르게 증가하고 이로 인하여 리두 로그를 백업하기 전에 덮어 쓰일 수가 있다. 앞선 백업 툴은 리두 로그가 덮어 쓰여진다 하더라도 백업을 보장한다.

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

  • 장애에 대비하여 MySQL는 리두 로그를 항상 활성화한다. 트랜잭션이 커밋된 이후 해당 변경 내용이 디스크에 바로 반영되지 않더라도 리두 로그는 항상 디스크로 기록된다(innodb_flush_log_at_trx_commit=1).
  • MySQL 8.0 이후 리두 로그를 비활성화하여 데이터 적재 시간을 단축할 수 있다.
    • ALTER INSTANCE DISABLE INNODB REDO_LOG;
    • 모든 작업이 종료되면 반드시 리두 로그를 활성화해야 한다.

어댑티브 해시 인덱스

  • 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스로서, innodb_adaptive_hash_index 변수를 통해 비활성화 가능하다.
  • 인덱스는 B-Tree를 사용하며 빠르지만 많은 스레드에서 요청이 있을 경우 느릴 수 있다. 이를 보완하기 위한 기능이다.
  • 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색하여 레코드가 저장된 데이터 페이지를 즉시 찾는다. B-tree의 검색 시간을 생략하여 매우 빠른 성능을 제공한다.
  • 해시 인덱스는 버퍼풀에 올려진 데이터 페이지에 대해서만 관리된다.
  • 해시 인덱스가 활성화 될 경우 다음과 같은 이유로 성능 저하가 발생할 수 있다.
    • 페이지를 찾을 때, 우선적으로 해시 인덱스를 통해 검색한다.
    • 해시 인덱스로 인한 메모리 공간이 필요하다.
    • 삭제 및 변경 과정에서 어댑티브 해시 인덱스를 함께 제거해야 한다.
  • 해시 인덱스는 다음 상황에서 불리하다.
    • 디스크 읽기가 많은 경우
    • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
    • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  • 그리고 다음과 같은 경우에는 성능 향상에 많은 도움이 된다.
    • 디스크의 데이터가 버퍼 풀의 크기와 비슷하여 디스크 읽기가 많지 않은 경우
    • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

(추가) MyISAM 스토리지 엔진 아키텍처

  • MyISAM은 InnoDB의 버퍼풀과 같은 메모리 공간으로서 키 캐시를 가진다. 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 한정하여 버퍼링 역할을 수행한다.
  • 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업을 통해 동작한다. 운영체제의 캐시나 버퍼링 매커니즘에 성능이 의존한다. 그래서 운영체제는 MyISAM의 데이터 처리를 위한 메모리 공간이 필요하며 일반적으로 키 캐시는 물리 메모리의 40% 이상을 넘기지 않는다.
  • MyISAM의 프라이머리 키에 대한 클러스터링 없이 INSERT 되는 순서대로 디스크에 저장된다. 프라이머리키와 세컨더리 인덱스는 데이터 파일의 물리 주소값인 ROWID 값을 포인터로 가진다.
  • MyISAM의 경우 프라이머리키에 대한 클러스터링 키를 지원하지 않아, 세컨더리 인덱스와 구조적으로 차이를 가지지 않는다.
  • MySQL 8.0 이후, MyISAM과 InnoDB로 MEMORY에서 TempTable로 사실상 세대 교체가 되었다. 메이저 버전이 변경되면 MyISAM과 MEMORY는 제거될 것으로 예상된다.

RealMySQL 8.0, 4장을 참고하여 작성하였습니다.