[스터디] 06. InnoDB 스토리지 엔진 아키텍처

목차

 
이번에는 InnoDB 스토리지 엔진의 아키텍처의 주요 특징에 대해서 학습하려고 합니다.

4.2 InnoDB 스토리지 엔진 아키텍처

• 레코드 기반의 잠금을 제공함.
• 높은 동시성 처리가 가능함.
• 안정적이며 성능이 뛰어남.

▲ InnoDB 구조

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

• InnoDB의 모든 테이블은 프라이머리 키 값의 순서대로 디스크에 저장됨.
• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용함.
• 쿼리 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정됨.
• InnoDB 스토리지 엔진과 다르게 MyISAM 스토리지 엔진에서는 지원하지 않음. MyISAM 테이블의 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가짐.

4.2.2 외래 키 지원

• InnoDB 스토리지 엔진에서 지원함.
• 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요함.
• 변경 시에는 부모 및 자식 테이블에 데이터가 있는지 체크하기 위한 잠금으로 데드락이 발생할 수 있음.
• foreign_key_checks 시스템 변수를 OFF로 설정하면 외래키 체크 작업을 일시적으로 멈출 수 있음.
  • 외래 키 관계를 가진 부모 테이블의 레코드를 삭제했다면, 자식 테이블의 레코드도 삭제해서 일관성을 맞춰주고 다시 활성화해야 함.

SET foreign_key_checks = OFF;
SET foreign_key_checks = ON;

4.2.3 MVCC(Multi Version Concurrency Control)

• 잠금을 사용하지 않는 일관된 읽기를 제공함.
• InnoDB는 언두 로그(Undo log)를 이용해 MVCC를 구현함.
• 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조.

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

• InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 가능함. 변경되기 전 데이터를 읽기 위해 언두 로그를 사용함.
• 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED이나 READ_COMMITTED, REPEATABLE_READ 수준인 경우, INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 잠금을 대기하지 않고 실행함.
• 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않음(잠금 없는 일관된 읽기). 

4.2.5 자동 데드락 감지

• InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리함.
  • 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들 중 하나를 강제 종료함.
  • 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상임.
• InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴)은 볼 수 없어서 데드락 감지가 불확실할 수 있음.
  • innodb_table_locks 시스템 변수를 활성화하면 테이블 레벨의 잠금까지 감지할 수 있음.
• 동시 처리 스레드가 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려짐.
  1. 이 문제를 해결하기 위해 MySQL 서버에서 제공하는 innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드가 작동하지 않음. → 데드락 상황이 발생하면 무한정 대기
  2. innodb_lock_wait_timeout 시스템 변수를 활성화하면 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환함.(50초 이하 권장)

4.2.6 자동화된 장애 복구

• InnoDB 스토리지 엔진은 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않음.
• 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못하는 경우가 발생하면, InnoDB 데이터 파일은 MySQL 서버가 시작될 때 자동 복구를 수행함. 이때 자동 복구를 할 수 없는 손상이 있으면 멈추고 MySQL 서버가 종료됨.
• 이 문제를 해결하기 위해 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작함.
  • innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외의 INSERT, UPDATE, DELETE 같은 쿼리는 수행할 수 없음.
  • 아래 작업을 진행해도 MySQL 서버가 시작되지 않으면 백업을 이용해 재구축하는 방법밖에 없음.

값	명칭	장애 상황	해결 방법
1	SRV_FORCE_IGNORE_CORRUPT	테이블스페이스의 데이터나 인덱스 페이지에 대한 손상	손상된 부분을 무시하고 MySQL 서버를 시작함. 주로 읽기 전용 모드에서 데이터를 백업하거나 추출하는 데 사용함.
2	SRV_FORCE_NO_BACKGROUND	백그라운드 작업으로 인한 시스템 손상	모든 백그라운드 작업을 중단하고 MySQL 서버를 시작함.
3	SRV_FORCE_NO_TRX_UNDO	트랜잭션 롤백 시 발생하는 손상	커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 MySQL 서버를 시작함. 일부 데이터 불일치가 발생할 수 있음.
4	SRV_FORCE_NO_IBUF_MERGE	인서트(Insert) 버퍼에 기록된 내용의 병합(Merge) 과정 중 데이터 손상	인서트 버퍼의 내용을 무시하고 강제로 MySQL을 시작함. 테이블을 덤프한 후 데이터베이스를 구축하면 데이터의 손실 없이 복구 가능함. 성능 저하가 발생할 수 있음.
5	SRV_FORCE_NO_UNDO_LOG_SCAN	언두(Undo) 로그의 손상으로 인한 데이터 접근 문제	언두 로그를 무시하고 MySQL을 시작함. MySQL 서버가 종료된 시점에 커밋되지 않은 작업이 무시될 수 있음. 트랜잭션 복구 불가능함.
6	SRV_FORCE_NO_LOG_REDO	리두(Redo) 로그 적용 중 발생하는 문제로 인한 데이터 불일치	리두 로그를 무시하고 MySQL을 시작함. 커밋되었다고 해도 데이터 파일에 기록되지 않은 데이터는 무시됨.

4.2.7 InnoDB 버퍼 풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
• 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 하는 버퍼 역할도 수행함.
• 일반적인 애플리케이션에서 데이터 변경 작업(INSERT, UPDATE, DELETE)로 발생하는 랜덤한 디스크 작업의 횟수를 줄일 수 있음.

4.2.7.1 버퍼 풀의 크기 설정

• 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 함.
• 레코드 버퍼: 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간. 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 많이 필요해질 수도 있음.
• MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있음. InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 상황에 따라 증가시키는 방법이 최적임.
  • 운영체제의 전체 메모리 공간이 8GB 미만이라면 50% 정도만 InnoDB 버퍼 풀로 설정함. 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보해주는 것이 좋음.
  • 전체 메모리 공간이 그 이상이라면 InnoDB 버퍼 풀의 크기를 전체 메모리의 50%에서 시작함. 조금씩 올려가면서 최적점을 찾음.
  • 운영체제의 전체 메모리 공간이 50GB 이상이라면, 대략 15GB에서 30GB 정도를 운영체제와 다른 응용 프로그램을 위해서 남겨둠. 나머지를 InnoDB 버퍼 풀로 할당함.
• InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있음. 버퍼 풀의 크기 변경은 MySQL 서버가 한가한 시점에 진행하는 것이 좋음. InnoDB 버퍼 풀을 크게 변경하는 작업은 시스템 영향도가 크지 않지만, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 큼. 버퍼 풀의 크기를 줄이거나 늘릴 때는 128MB 단위로 처리됨.
• innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있음. 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현함.

4.2.7.2 버퍼 풀의 구조

• InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼갬. InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장함.
• 버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU(Least Recently Used) 리스트, 플러시(Flush) 리스트, 프리(Free) 리스트라는 3개의 자료 구조를 관리함.
  • 프리(Free) 리스트: InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됨.
  • LRU 리스트: 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것임.
  • 플러시 리스트: 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리함. 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록되어야 함.
• InnoDB 스토리지 엔진에서 데이터를 찾는 과정
  1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
     1. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
     2. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
     3. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
  2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
  3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있음. 이런 경우에 MRU로 이동되지 않음.)
  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고('Aging'이라고 함) 결국 해당 페이지는 버퍼 풀에서 제거됨. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨짐.
  5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
• 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 됨. 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거됨.

4.2.7.3 버퍼 풀과 리두 로그

• InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 쿼리의 성능이 빨라짐. InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것임.
• InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 함께 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)도 가지고 있음. 더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되어야 함. 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는 것은 아님.
• InnoDB 스토리지 엔진에서 리두 로그의 특징은 다음과 같음.
  • 리두 로그는 고정 크기의 파일 여러 개로 구성되며, 순환 고리처럼 사용됨.
  • 데이터 변경 시 이전 로그 엔트리는 새 로그 엔트리로 덮어 쓰여짐.
  • 리두 로그 파일은 재사용 가능한 공간과 재사용 불가능한 공간(Active Redo Log, 활성 리두 로그)으로 구분하여 관리함.
  • 활성 리두 로그의 시작점은 최근 체크포인트의 로그 시퀀스 번호(LSN: Log Sequence Number)로 정해지고, 마지막 부분은 계속 증가함.
  • 체크포인트 에이지(Checkpoint Age)는 활성 리두 로그 공간의 크기를 나타냄.
  • InnoDB는 더티 페이지와 리두 로그 엔트리가 연관되어 있어, 체크포인트 발생 시 LSN보다 작은 엔트리와 관련된 더티 페이지가 디스크로 동기화됨.
  • 리두 로그 파일의 크기가 작으면 체크포인트 에이지도 작아져 쓰기 버퍼링 효과를 제대로 볼 수 없음. 반면, 리두 로그 파일의 크기가 매우 크면 불필요한 디스크 쓰기가 많아져 비효율적일 수 있음.
  • 리두 로그의 적절한 크기 설정은 데이터 변경의 양을 고려하여 5~10GB 정도로 시작해 필요에 따라 조절하는 것이 좋음.

4.2.7.4 버퍼 풀 플러시(Buffer Pool Flush)

• MySQL 5.6 버전까지는 InnoDB 스토리지 더티 페이지 플러시 기능이 부드럽게 처리되지 않았음.
• MySQL 8.0 버전으로 업그레이드되면서 대부분의 서비스에서는 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않음.
• InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행함.
  1. 플러시 리스트(Flush_list) 플러시
  2. LRU 리스트(LRU_list) 플러시

4.2.7.4.1 플러시 리스트 플러시

• InnoDB는 오래된 리두 로그 엔트리를 재활용하기 위해 더티 페이지를 먼저 디스크로 동기화해야 함. 이 과정은 플러시 리스트(Flush_list)를 사용하여 순차적으로 데이터 페이지를 디스크에 동기화함.
• 더티 페이지가 많을수록 디스크 쓰기 폭발 현상의 위험이 커짐.
• 여러 시스템 변수들이 이 과정을 조절하며, 중요한 것으로는 innodb_page_cleaners, innodb_max_dirty_pages_pct, innodb_io_capacity 등이 있음.
  • innodb_page_cleaners
  • innodb_max_dirty_pages_pct_lwm
  • innodb_max_dirty_pages_pct
  • innodb_io_capacity
  • innodb_io_capacity_max
  • innodb_flush_neighbors
  • innodb_adaptive_flushing
  • innodb_adaptive_flushing_lwm
• innodb_page_cleaners는 클리너 스레드의 수를 정의하며, 버퍼 풀 인스턴스 수에 따라 자동 조절됨.
• innodb_max_dirty_pages_pct는 전체 버퍼 풀에서 더티 페이지가 차지할 수 있는 최대 비율을 설정함.
• IO 용량 관련 변수(innodb_io_capacity, innodb_io_capacity_max)는 디스크의 읽기 및 쓰기 성능을 설정함.
• 어댑티브 플러싱 기능(innodb_adaptive_flushing)은 리두 로그의 증가 속도에 따라 더티 페이지를 적절히 디스크에 기록하도록 조정함.
• innodb_flush_neighbors는 HDD에서 사용 시 인접한 더티 페이지를 함께 디스크에 기록하는 기능을 제어함, SSD 사용 시 비활성화 추천.

4.2.7.4.2 LRU 리스트 플러시

• InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 함. 이를 위해 LRU 리스트(LRU_list) 플러시 함수가 사용됨.
• InnoDB 스토리지 엔진은 LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지들을 스캔함. 이때, 더티 페이지는 디스크에 동기화되며, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮김.
  • InnoDB 버퍼 풀 인스턴스별로 최대 innodb_lru_scan_depth 개수만큼 스캔함.
  • 실질적으로 LRU 리스트의 스캔은 (innodb_buffer_pool_instances * innodb_lru_scan_depth) 수만큼 수행함.

4.2.7.5 버퍼 풀 상태 백업 및 복구

• InnoDB 서버에서 버퍼 풀은 쿼리 성능에 중요한 역할을 하며, 서버 재시작 후 즉시 이전 성능을 회복하기 어려움.
• 과거에는 서버 재시작 후 주요 테이블과 인덱스를 스캔하여 버퍼 풀을 워밍업했으나, 이는 시간이 많이 소요되는 방법임.
• MySQL 5.6부터는 버퍼 풀의 상태를 덤프하고 재시작 후 이를 적재할 수 있는 기능이 추가되었음.

-- // MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

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

• 백업 파일은 ib_buffer_pool이라는 이름으로 생성되며, 실제 크기는 상대적으로 작음.
• 백업된 데이터를 버퍼 풀에 복구하는 시간은 버퍼 풀 크기에 따라 다르며, 필요한 데이터 페이지를 디스크에서 읽어야 함.
• 복구 작업이 너무 길어질 경우 SET GLOBAL innodb_buffer_pool_load_abort=ON;을 사용하여 중단할 수 있음.
• 자동화 옵션
  • 서버 셧다운 시 자동 백업: innodb_buffer_pool_dump_at_shutdown=ON
  • 서버 시작 시 자동 복구: innodb_buffer_pool_load_at_startup=ON

4.2.7.6 버퍼 풀의 적재 내용 확인

• MySQL 5.6 버전부터 MySQL 서버의 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있음.
• InnoDB 버퍼 풀이 큰 경우에는 이 테이블 조회가 상당히 큰 부하를 일으키면서 서비스 쿼리가 많이 느려지는 문제가 있음. 그래서 실제 서비스용으로 사용되는 MySQL 서버에서는 버퍼 풀의 상태를 확인하는 것이 거의 불가능함.
• MySQL 8.0 버전에서는 information_schema 데이터베이스에 innodb_ cached_indexes 테이블이 새로 추가됨. 이 테이블을 이용하면 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있음.

-- MySQL 서버는 개별 인덱스별로 전체 페이지 개수가 몇 개인지는 사용자에게 알려주지 않음.
-- information_schema의 테이블을 이용해도 테이블의 인덱스별로 페이지가 InnoDB 버퍼 풀에 적재된 비율은 확인할 수가 없음.
-- 따라서 테이블 단위로 전체 데이터 페이지 개수와 InnoDB 버퍼 풀에 적재된 데이터 페이지 개수의 합을 조회함.
mysql> SELECT
	it.name table_name,
	ii.name index_name,
	ici.n_cached_pages n_cached_pages
    FROM information_schema.innodb_tables it
	INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it.table_id
	INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = ii.index_id
    WHERE it.name=CONCAT('employees','/','employees');

+---------------------+---------------------+----------------+
| table_name          | index_name          | n_cached_pages |
+---------------------+---------------------+----------------+
| employees/employees | PRIMARY             | 299            |
| employees/employees | ix_hiredate         | 8              |
| employees/employees | ix_gender_birthdate | 8              |
| employees/employees | ix_firstname        | 8              |
+---------------------+---------------------+----------------+

4.2.8 Double Write Buffer

• InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록함. InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있음.
• 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 함. 하드 웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있음.
• InnoDB 스토리지 엔진에서는 이런 문제를 막기 위해 Double-Write 기법을 이용함. 
• DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용됨. 페이지가 기록되는 도중에 운영체제가 비정상적으로 종료되면 InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사함.
• DoubleWrite 기능을 사용할지 여부는 innodb_doublewrite 시스템 변수로 제어할수 있음.
  DoubleWrite 버퍼는 HDD처럼 자기 원판(Platter)이 회전하는 저장 시스템에서는 한 번의 순차 디스크 쓰기를 하는 것이기 때문에 부담이 되지 않지만, SSD처럼 랜덤 IO나 순차 IO의 비용이 비슷한 저장 시스템에서는 부담스러움.

4.2.9 언두 로그

• InnoDB 스토리지 엔진은 트랜잭션 보장과 데이터 격리 수준을 유지하기 위해 DML(INSERT, UPDATE, DELETE) 연산 전 데이터를 언두 로그에 백업함.
• 언두 로그의 사용 목적
  • 트랜잭션 롤백: 롤백 시 언두 로그에 저장된 이전 데이터를 사용하여 변경 전 상태로 복구함.
  • 데이터 격리 보장: 다른 커넥션의 데이터 조회 시, 변경 중인 데이터 대신 언두 로그의 데이터를 제공하여 격리 수준을 유지함.
• 언두 로그는 공간 사용이 많고 관리 비용도 많이 필요하기 때문에 트랜잭션이 오래 실행될수록 언두 로그의 양이 급격히 증가할 수 있으며, 이는 성능 저하를 초래할 수 있음.

4.2.9.1 언두 로그 레코드 모니터링

• 언두 로그의 데이터 저장 및 사용 방식
  • 레코드 백업: 예를 들어 UPDATE 문을 실행할 때, 변경 전 데이터는 언두 영역에 백업됨.
  • 레코드 롤백: 롤백 시 백업된 데이터를 사용하여 데이터를 원래 상태로 복구함.
  • 격리 수준 관리: 다른 트랜잭션 동안 데이터를 조회할 때 변경 중인 데이터 대신 언두 로그의 데이터를 반환하여 데이터의 일관성을 보장함.
• MySQL 5.5 이전 버전에서는 언두 로그 공간이 한 번 증가하면 줄어들지 않음.
• MySQL 5.7 이상 버전에서는 언두 로그 공간을 돌아가면서 순차적으로 사용하여 디스크 공간을 줄일 수 있음.
• MySQL 8.0 버전에서는 필요에 따라 언두 로그 공간을 자동으로 줄이는 기능이 추가되어 성능 및 공간 효율성이 개선됨.

4.2.9.2 언두 테이블스페이스 관리

• MySQL 5.6 이전 버전에서는 모든 언두 로그가 시스템 테이블스페이스(ibdata.ibd)에 저장됨. 확장성에 한계가 있음.
• MySQL 5.6 버전에서는 innodb_undo_tablespaces 시스템 변수가 도입되어, 이 변수를 2보다 큰 값으로 설정하면 언두 로그를 시스템 테이블스페이스가 아닌 별도의 언두 로그 파일에 저장함. 유연한 언두 로그 관리가 가능해짐.
• MySQL 8.0.14 버전부터는 innodb_undo_tablespaces 시스템 변수를 통해 언두 테이블스페이스의 공간을 필요한 만큼만 사용하고, 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 'Undo tablespace truncate' 기능이 지원됨. 언두 로그 공간을 효율적으로 관리할 수 있음.
• 자동과 수동 모드
  • 자동 모드: 트랜잭션이 완료되면 불필요해진 언두 로그를 삭제하는 'Undo Purge' 작업이 실행됨. 필요시 언두 로그 파일에서 사용되지 않는 공간을 잘라내고 운영체제로 반납함. innodb_undo_log_truncate 시스템 변수가 ON일 때 주기적으로 실행됨.
  • 수동 모드: innodb_undo_log_truncate 시스템 변수가 OFF로 설정되어 있거나, 자동 모드가 충분히 효과적이지 않은 경우, 수동으로 언두 테이블스페이스를 비활성화하고 불필요한 공간을 잘라낼 수 있음. 언두 테이블스페이스가 최소 3개 이상일 때 작동함.

4.2.10.체인지 버퍼

• RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요함. 하지만 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모함.
• InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킴. 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 함.
• 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없음.
• 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread)라고 함.
• MySQL 5.5 이전 버전 까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능(MySQL 5.5 이전 버전까지는 이 버퍼를 인서트 버퍼라고 함)했는데, MySQL 5.5 버전부터 조금씩 개선되면서 MySQL 8.0 버전에서는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있음.
• MySQL 5.5 이전 버전에서는 별도의 시스템 변수 설정 없이 기본적으로 기능이 활성화됨. MySQL 5.5부터는 innodb_change_buffering이라는 시스템 변수가 새로 도입되어 작업의 종류별로 체인지 버퍼를 활성화할 수 있고, 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있음.
• innodb_change_buffering 시스템 변수에 설정할 수 있는 값
  • all: 모든 인덱스 관련 작업(inserts + deletes + purges)을 버퍼링
  • none: 버퍼링 안함
  • inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
  • deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
  • changes: 인덱스에 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
  • purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)
• 체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정됨. 필요하면 InnoDB 버퍼 풀의 50%까지 사용하게 설정할 수 있음.

4.2.11 리두 로그 및 로그 버퍼

• 리두 로그(Redo Log)는 트랜잭션의 4가지 요소인 ACID 중에서 D(Durable)에 해당하는 영속성과 연관됨. 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치임.
• 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요함. 이때 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있음. 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구함.
• 데이터베이스 서버는 ACID도 중요하지만 성능도 중요하기 때문에 데이터 파일뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있음.
• MySQL 서버가 비정상 종료되는 경우, InnoDB 스토리지 엔진의 데이터 파일은 다음과 같은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있음.
  1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
     • 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됨.
  2. 롤백됐지만 데이터 파일에 이미 기록된 데이터
     • 리두 로그로는 해결할 수 없는데, 이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 됨. 최소한 그 변경이 커밋됐는지, 롤백됐는지, 트랜잭션의 실행 중간 상태였는지를 확인하기 위해서 리두 로그는 필요함.
• 데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장함. 하지만 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발함. 그래서 InnoDB 스토리지 엔진에서 리두 로그를 어느 주기로 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공함.

4.2.11.1 리두 로그 아카이빙

• MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가됨.
• MySQL 엔터프라이즈 백업과 Xtrabackup 툴은 데이터 파일을 복사하는 동안 새로 추가되는 리두 로그 엔트리를 추적하여 함께 복사함. 데이터 파일과 리두 로그의 일관성을 유지하기 위해 필요함.
• 데이터 변경이 많을 경우 리두 로그가 빠르게 증가하고, 백업 중에 아직 복사하지 못한 리두 로그 엔트리가 덮어쓰여 백업이 실패할 수 있음.
• 리두 로그 아카이빙 기능은 백업 도중 리두 로그의 덮어쓰기가 발생하더라도 백업이 실패하지 않도록 보장함. 이를 통해 데이터의 일관성과 백업의 완전성이 유지됨.
• 아카이빙된 리두 로그를 저장할 디렉터리는 innodb_redo_log_archive_dirs 시스템 변수로 설정해야 하며, 해당 디렉터리는 MySQL 서버를 실행하는 운영체제 사용자(보통 mysql 사용자)만 접근 가능해야 함.

-- 디렉터리 생성 및 설정
linux> mkdir /var/log/mysql_redo_archive linux> cd /var/log/mysql_redo_archive
linux> mkdir 20200722
linux> chmod 700 20200722

mysql> SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive';

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

• InnoDB 스토리지 엔진의 리두 로그는 하드웨어나 소프트웨어 등의 문제점으로 MySQL 서버가 비정상적으로 종료됐을 때, 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화되어 있음.
• MySQL 서버에서 트랜잭션이 커밋되어도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록됨.
• MySQL 8.0 이전 버전까지는 수동으로 리두 로그를 비활성화할 수 없었음. MySQL 8.0 버전부터는 수동으로 리두 로그를 활성화하거나 비활성화할 수 있음. 그래서 MySQL 8.0 버전부터 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우, 다음과 같이 리두 로그를 비활성화 해서 데이터의 적재 시간을 단축시킬 수 있음,

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

-- // 리두 로그를 비활성화한 후 대량 데이터 적재를 실행
mysql> LOAD DATA ...

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

 

• ALTER INSTANCE [ENABLE | DISABLE] INNODB REDO_LOG 명령을 실행한 후, Innodb_redo_log_enabled 상태 변수를 살펴보면 리두 로그가 활성화되거나 비활성화됐는지 확인할 수 있음.

4.2.12 어댑티브 해시 인덱스

• 어댑티브 해시 인덱스(Adaptive Hash Index): InnoDB 스토리지 엔진에서 자주 요청되는 데이터에 대해 자동으로 생성되는 인덱스. 사용자가 직접 만들지 않고, 시스템이 데이터 접근 패턴을 분석하여 동적으로 생성함.
  • 일반적으로 우리가 알고 있는 인덱스는 사용자가 생성한 B-Tree 인덱스임. 하지만 어댑티브 해시 인덱스는 InnoDB에 의해 자동으로 생성되며, 이는 B-Tree 인덱스의 검색 효율을 높이기 위해 사용됨.
  • B-Tree 인덱스에서 데이터를 찾는 과정은 루트 노드부터 시작하여 브랜치 노드를 거쳐 리프 노드까지 접근해야 하며, 이는 많은 시간과 CPU 자원을 소모할 수 있음. 어댑티브 해시 인덱스는 이러한 검색 경로를 단축시켜 더 빠른 데이터 접근을 가능하게 함.
  • 해시 인덱스는 '인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 구성됨. 인덱스 키 값은 B-Tree 인덱스의 고유번호와 실제 키 값의 조합으로 생성됨.
  • 어댑티브 해시 인덱스는 버퍼 풀에 로딩된 데이터 페이지에 대해서만 관리됨. 버퍼 풀에서 데이터 페이지가 제거되면 관련된 해시 인덱스 정보도 사라짐.
  • 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용하여 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있음.

• 어댑티브 해시 인덱스를 의도적으로 비활성화하는 경우도 많음. 어댑티브 해시 인덱스가 성능 향상에 도움이 되지 않는 경우는 다음과 같음.
  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
• 다음과 같은 경우에는 성능 향상에 도움이 됨.
  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
  • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
• 어댑티브 해시 인덱스가 도움이 될지 아닐지를 판단하기는 쉽지 않음. 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않음.
• 어댑티브 해시 인덱스 또한 저장 공간인 메모리를 사용하며, 때로는 상당히 큰 메모리 공간을 사용할 수도 있음. 해시 인덱스의 효율이 없는 경우에도 InnoDB는 계속 해시 인덱스를 사용할 것임.

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

• MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았음.
  • MySQL 5.5 버전부터는 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택됐지만, MySQL 서버의 시스템 테이블은 여전히 MyISAM 테이블을 사용함. 전문 검색이나 공간 좌표 검색 기능은 MyISAM 테이블에서만 지원됨.
  • MySQL 8.0 버전부터는 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체됐고, 공간 좌표 검색이나 전문 검색 기능이 모두 InnoDB 스토리지 엔진을 지원함. MySQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 됨.
• MEMORY 스토리지 엔진이 이름 때문에 과대평가를 받지만, MEMORY 스토리지 엔진 또한 동시 처리 성능에 있어서 InnoDB 스토리지 엔진을 따라갈 수 없음. 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB보다 빠를 수 있지만, MySQL 서버는 일반적으로 온라인 트랜잭션 처리를 위한 목적으로 사용되며 온라인 트랜잭션 처리에서는 동시 처리 성능이 매우 중요함.
• MySQL 서버는 사용자의 쿼리를 처리하기 위해 내부적으로 임시 테이블을 사용할 수도 있음.
  • MySQL 5.7 버전까지 MEMORY 스토리지 엔진이 내부 임시 테이블의 용도로 사용됨. 하지만 MEMORY 스토리지 엔진은 가변 길이 타입의 칼럼을 지원하지 않음.
  • MySQL 8.0 부터는 TempTable 스토리지 엔진이 MEMORY 스토리지 엔진을 대체함.
  • internal_tmp_mem_storage_engine 시스템 변수를 이용해 내부 임시 테이블을 위해 TempTable 엔진을 사용할지 MEMORY 엔진을 사용할지 선택할 수 있음. 기본값은 TempTable임.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.