목차
- InnoDB 스토리지 엔진 아키텍처 개요
- InnoDB 버퍼 풀
- Double Write Buffer
- 언두 로그
- 체인지 버퍼
- 리두 로그 및 로그 버퍼
- 어댑티브 해시 인덱스
InnoDB 스토리지 엔진 아키텍처 개요
아래 그림은 앞에서 InnoDB 스토리지 엔진의 아키텍처라고 살펴봤던 그림입니다. 이제 InnoDB를 이루는 구성요소 하나하나에 대해서 살펴보겠습니다.
InnoDB 버퍼 풀
InnoDB 버퍼 풀은 InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터의 파일이나 인덱스 정보를 메모리에 캐시해두는 공간입니다. 또한 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 합니다.
버퍼 풀의 크기 변경
버퍼 풀의 크기는 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 합니다. MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 합니다.
- 레코드 버퍼 : 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간
- 커넥션이 많고 사용하는 테이블이 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 많이 필요할 수 있습니다.
- 레코드 버퍼 공간은 별도로 설정할 수 없고, 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수 없습니다.
MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 되어서, 처음에는 적절히 작은 값으로 시작하고 상황을 고려해서 차츰차츰 증가시키거나 줄이는 방향으로 최적의 크기를 찾으면 됩니다.
InnoDB의 버퍼 풀 크기를 변경할 때에는, MySQL 메뉴얼을 참고해서 변경하기를 권장합니다.
InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)로 인해 내부 잠금 경합을 유발했는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었습니다. 여기서 각 버퍼 풀을 버퍼 인스턴스라고 합니다.
버퍼 풀의 구조
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장합니다.
페이지란, 데이터를 저장하는 데 사용되는 단위를 의미합니다. 즉, 디스크에서 읽거나 쓸 수 있는 최소한의 데이터 양을 의미하며 일반적으로 4KB에서 64KB의 크기를 가집니다.
이런 페이지 크기 조각을 관리하기 위해, InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트, 플러시(Flush) 리스트, 프리(Free) 리스트 라는 3개의 자료 구조를 사용합니다.
LRU 리스트
엄밀하게는 LRU 자료구조와 MRU(Most Recently Used) 자료구조 가 결합된 형태입니다. 위 그림에서의 Old 서브리스트 영역은 LRU에 해당하고, New 서브리스트 영역은 MRU에 해당합니다.
LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것입니다.
플러시 리스트
플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리합니다.
데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영합니다. 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결됩니다.
- 단, 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않습니다.
그리고 반대의 경우(데이터 페이지가 디스크로 기록이 돼도, 리두 로그에 기록되지 않는 경우)도 발생할 수 있는데, 이럴 때에는 InnoDB 스토리지 엔진은 체크포인트를 발생시켜 리두로그와 데이터 페이지의 상태를 동기화하게 됩니다.
체크포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할을 합니다.
프리 리스트
프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록입니다. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 찾을 때 사용됩니다.
InnoDB 스토리지 엔진에서 데이터를 찾는 과정
필요한 레코드가 저장된 데이터 페이지가버퍼 풀에 있는지 검사- InnoDB
어댑티브 해시 인덱스를 이용해 페이지를 검색 해당 테이블의 인덱스를 이용해 버퍼 풀에서 페이지를 검색- 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
- InnoDB
- 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
- 버퍼 풀의 LRU 헤더 부분에 적재된 페이지가 실제로 읽히면, MRU 헤더 부분으로 이동
- 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라
나이(Age)가 부여되고 오랫동안 사용되지 않으면 나이가 오래 되고(Aging), 결국 해당 페이지는 버퍼 풀에서 제거됨 - 필요한 데이터가 자주 접근됐다면,
해당 페이지의 인덱스 키를어댑티브 해시 인덱스에 추가
버퍼 풀과 리두 로그의 관계
InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있습니다. 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리 성능이 빨라집니다.
단, 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데 버퍼 풀의 메모리 공간만 단순히 늘리면, 데이터 캐시 기능만 향상시키는 것입니다.
쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 이해해야 합니다.
InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 함께 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)도 가지고 있습니다.
또한 InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용합니다.
즉, 데이터 변경이 게속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰입니다. 그래서 InnoDB는 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야합니다.
그래서 InnoDB 스토리지 엔진은 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log)라고 합니다. 위의 그림에서 화살표를 가진 엔트리들이 활성 리두 로그 공간인 것입니다.
그리고, 리두 로그 파일의 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 가지는데 이 값을 LSN(Log Sequence Number)라고 합니다.
또한 InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜서 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화시키는데 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 공간의 시작점이 됩니다.
그리고 가장 최근 체크포인트 지점의 LSN과 마지막 리두 로그 엔트리의 LSN 차이가 체크포인트 에이지(Checkpoint Age)라고 합니다. 즉, 체크포인트 에이지는 활성 리두 공간의 크기를 의미합니다.
시나리오 가정하며 이해해보기
- InnoDB 버퍼 풀은 100GB이며, 리두 로그 파일의 전체 크기는 100MB인 경우
- 평균 리두 로그 엔트리가 4KB라면, 25600개(100MB / 4KB)의 더티페이지만 버퍼 풀에 보관 가능
- 위의 가정에 추가로 데이터 페이지 크기가 16KB라고 가정하면, 허용 가능한 전체 더티 페이지 크기는 400KB
- 리두 로그 파일의 크기가 100MB이므로, 체크포인트 에이지가 최대 100MB 까지만 허용됨
- 즉, 쓰기 버퍼링 기능을 위한 효과는 거의 못보는 상황
- InnoDB 버퍼 풀은 100GB이며, 리두 로그 파일의 전체 크기는 100MB인 경우
- 평균 리두 로그 엔트리가 4KB이고, 데이터 페이지 크기가 16KB라면, 400GB 정도의 더티 페이지를 가질 수 있다. (100GB / 4KB)
- 하지만 버퍼 풀의 크기가 100MB 이기 때문에 최대 허용 가능한 더티 페이지는 100MB
정리해보자면, 리두 로그 파일의 크기를 키우면 쓰기 지연 기능을 최대화할 수 있지만, 그에 따라 성능 저하도 야기됩니다. 왜? 너무 많은 더티 페이지를 디스크에 동기화하려면 굉장히 많은 시간이 소모될 수 있기 때문입니다.
단, 리두 로그 파일의 크기가 너무 적으면 쓰기 지연 기능을 활용하기 힘듭니다. 그렇기에 처음에는 리두 로그 파일의 크기를 적게 잡고, 조금씩 늘려가며 최적값을 선택하는 것이 좋습니다.
버퍼 풀 플러시 (Buffer Pool Flush)
InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행합니다.
플러시 리스트 플러시LRU 리스트 플러시
MySQL 5.6버전까지는 더티 페이지들을 모아서 디스크와 동기화하기 위해 디스크 기록 작업이 한번에 몰려 서비스의 성능에 악영향을 미쳤다고 합니다.
플러시 리스트 플러시
InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워줘야 합니다. 리두 로그 엔트리를 지우려면, 더티 페이지가 먼저 디스크로 동기화가 되어야 합니다.
이를 위해, InnoDB는 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행합니다.
더티 페이지를 디스크로 동기화하는 스레드를 클리너 스레드(Cleaner Thread)라고 하는데, innodb_page_cleaners 시스템 변수로 클리너 스레드의 개수를 조정할 수 있습니다.
innodb_page_cleaners값이 버퍼 풀 인스턴스 개수보다 많으면, 버퍼 풀 인스턴스 개수(innodb_buffer_pool_instances) 값으로 자동 변경됩니다.innodb_page_cleaners값이 버퍼 풀 인스턴스 개수보다 적으면, 하나의 클리너 스레드가 여러 개의 버퍼 풀 인스턴스를 처리합니다.
InnoDB의 버퍼 풀은 한계가 있기 때문에 무한정 더티 페이지를 그대로 유지할 수 없습니다. 그래서 InnoDB 버퍼 풀은 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과(쓰기 지연)를 극대화할 수 있습니다.
하지만, InnoDB의 버퍼 풀에 더티 페이지가 많아질수록 디스크 쓰기 폭발 현상(Disk IO Burst)이 발생할 가능성이 높아진다는 문제점이 있습니다.
InnoDB는 버퍼 풀의 더티 페이지 비율이 90%를 넘기면, 더티 페이지를 디스크로 기록해야 한다고 판단하고, 급작스럽게 디스크 쓰기가 폭증하는 현상(디스크 쓰기 폭발 현상(Disk IO Burst))이 발생합니다.
그렇기에 innodb_max_dirty_pages_pct_lwm 이라는 시스템 설정 변수를 이용해 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 됩니다.
- 디폴트값은 10% 입니다.
- 단, 디스크 쓰기가 너무 많이 발생한다면
innodb_max_dirty_pages_pct_lwm값을 높이면 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있습니다.
어댑티브 플러시(Adaptive Flush)
그리고, InnoDB는 innodb_io_capacity 와 innodb_io_capacity_max 시스템 변수를 통해 MySQL 서버의 디스크가 어느 정도의 요청까지 무리없이 처리할 수 있는지를 지정해줄 수 있습니다.
하지만 관리해야할 MySQL 서버가 많다면, 일일이 서버의 트래픽을 봐가면서 innodb_io_capacity 와 innodb_io_capacity_max 값을 적절히 수정해주는 것은 상당히 번거롭습니다.
그래서 InnoDB는 어댑티브 플러시(Adaptive Flush)라는 기능을 제공합니다. 이는 innodb_adaptive_flushing 변수로 켜고 끌 수 있는데, 디폴트값은 켜는 것입니다.
어댑티브 플러시 기능이 활성화되면 InnoDB는 앞선 시스템 변수(innodb_io_capacity 와 innodb_io_capacity_max)에 의존하지 않고 새로운 알고리즘을 사용합니다.
해당 알고리즘은 어느 정도 속도로 더티 페이지들이 생성되는지를 분석합니다. 이는 리두 로그의 증가 속도를 분석하는 것과 같습니다. 즉, 리두 로그의 증가속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행합니다.
LRU 리스트 플러시
InnoDB는 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 합니다. 이를 위해 LRU 리스트(LRU_list) 플러시 함수가 사용됩니다.
LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 변수에 설정된 개수만큼의 페이지들을 스캔합니다. InnoDB는 이 때 스캔하면서 더티 페이지는 디스크에 동기화하게 되며, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮깁니다.
Double Write Buffer
리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록합니다. 이로 인해 InnoDB에서는 더티 페이지를 디스크 파일로 플러시할 때, 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구하지 못할 수도 있습니다.
이렇게 페이지의 일부만 기록되는 현상을 파셜 페이지(Partial-Page) 또는 톤 페이지(Torn-Page) 라고 합니다. 이런 현상은 하드웨어의 오작동이나 비정상 종료 등으로 발생할 수 있습니다.
어떻게 파셜 페이지 문제를 막을 수 있는가?
InnoDB는 파셜 페이지 문제를 막기 위해, Double-write 기법을 활용합니다. 아래의 그림이 Double-write 기법이 동작하는 방식입니다.
위처럼 버퍼 풀에 있는 A~E 까지의 더티 페이지를 디스크로 플러시한다는 상황을 가정해보겠습니다. 이 같은 상황에서 Double Write Buffer는 다음과 같이 동작합니다.
- 우선 실제 데이터 파일에 변경 내용을 기록하기 전에 A~E 까지의 더티 페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의
Double Write Buffer에 기록합니다. - 그리고 InnoDB 스토리지 엔진은 각 더티 페이지를 디스크의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행합니다.
시스템 테이블스페이스의 Double Write Buffer 공간에 기록된 변경 내용은 실제 데이터 파일에 A~E 더티 페이지가 정상적으로 기록되면 더이상 필요가 없어집니다.
즉, Double Write Buffer 의 목적은 실제 데이터 파일의 쓰기가 중간에 실패할 때의 문제를 대비하기 위한 목적으로 사용됩니다.
시나리오 가정으로 이해해보기
A,B 페이지는 정상적으로 디스크에 기록됐지만, C 페이지가 기록되는 도중에 운영체제가 비정상적으로 종료됐다고 가정해보겠습니다.
그럼, InnoDB 스토리지 엔진은 재시작될 때 항상 Double Write Buffer 의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 Double Write Buffer 의 내용을 데이터 파일의 페이지에 복사합니다.
데이터의 무결성이 중요한 서비스에서는 Double Wirte 활성화를 고려하는 것이 좋습니다. Double Write Buffer를 제어하려면, innodb_doublewrite 시스템 변수로 설정할 수 있습니다.
언두 로그(Undo log)
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE) 로 변경되기 이전 버전의 데이터를 별도로 백업합니다. 이렇게 백업된 데이터를 언두 로그 라고 합니다.
트랜잭션 보장- 트랜잭션이 롤백되면, 변경된 데이터를 변경 전 데이터로 복구해야하는데, 이 때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구합니다.
격리수준 보장- 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 레코드 값을 반환합니다. (
READ_UNCOMMITED만 변경된 데이터 반환, 이외의 격리 수준은 언두 로그의 데이터 반환)
- 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 레코드 값을 반환합니다. (
참고!
언두 로그는 별도의 데이터 파일, 즉 디스크에 저장됩니다.
언두 로그 모니터링
트랜잭션을 커밋하지 않아도 실제 데이터 파일의 내용은 변경됩니다. 단, 변경되기 전 값을 언두 로그에 저장하고, 만약 커밋되면 현재 상태를 유지하고, 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파이로 복구합니다.
MySQL 5.5 이전 버전은 한 번 증가한 언두 로그 공간은 줄어들지 않습니다. 이전 버전을 사용하시는 분들은 주의하시기 바랍니다.
대용량의 데이터를 처리하는 트랜잭션 뿐만 아니라, 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있습니다.
예시로, A 트랜잭션이 실행됐는데 하루 정도 방치되었다면, A 트랜잭션 이후 하루 동안 발생한 데이터 변경 내역들은 모두 언두 로그에 기록되고, 언두 로그의 기록은 A 트랜잭션이 계속 살아있기 때문에 삭제되지 않습니다.
즉, InnoDB 엔진의 언두 로그는 하루치 데이터 변경을 모두 저장하고 디스크의 언두 로그 저장 공간은 계속 증가합니다.
언두 로그가 늘어나면, 디스크 사용량 뿐만 아니라, 매번 백업할 때도 그만큼 더 복사해야한다는 문제점이 발생합니다. 하지만, MySQL 8.0 버전부터는 언두 로그 공간을 자동으로 변경해주기도 하고, 수동으로 줄일 수 있습니다.
다시 본론으로 돌아와서, 언두 로그를 모니터링은 다음 명령어를 통해 가능합니다.
-- // MySQL 서버의 모든 버전에서 사용가능
mysql> SHOW ENGINE INNODB STATUS \GINSERT로 인한 언두로그와 UPDATE(DELETE 포함)로 인한 언두로그는 별도로 관리됩니다.
UPDATE(DELETE 포함)로 인한 언두로그는 MVCC 와 롤백, 데이터 복구 모두에 사용됩니다.
하지만INSERT 명령으로 인한 언두로그는 MVCC 를 위해서는 사용되지 않고, 롤백이나 데이터 복구만을 위해서 사용됩니다.
언두 테이블스페이스 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 합니다.
- MySQL 5.6 버전 이전
- 언두로그가
시스템 테이블스페이스(ibdata.ibd)에 저장 - 시스템 테이블스페이스는 MySQL 서버 초기화될 때 생성되기에 확장의 한계가 존재
- 언두로그가
- MySQL 8.0 버전 이후
- 언두 로그는 항상
시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선
- 언두 로그는 항상
언두 테이블 스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo slot)을 가집니다.
// 언두 테이블스페이스 구조 롤백 세그먼트1 - 언두 슬롯1, 2, 3.... 롤백 세그먼트2 - 언두 슬롯1, 2, 3.... ...
하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가집니다. 예를 들어 InnoDB의 페이지 크기가 16KB 라면, 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 가집니다.
- 하나의 트랜잭션은 쿼리의 특성에 따라 최대 4개까지 사용할 수 있습니다.
최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
언두 로그 공간이 남는 것은 크게 문제되지 않지만, 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생합니다. 그렇기에 언두 로그 관련 시스템 변수를 변경해야 한다면 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수를 설정해야 합니다.
언두 테이블스페이스 공간을 필요한 만큼 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate라고 합니다. Undo tablespace truncate 하는 방법은 자동과 수동으로 2가지 입니다.
- 자동
- 트랜잭션이 커밋돼면
언두 로그에 복사된 이전 값은 필요없어지므로, InnoDB의퍼지 스레드(Purge thread)가 주기적으로 동작하며 불필요한 언두 로그를 삭제하는 작업을 실행합니다. - 이를
언두 퍼지(Undo purge)라고 합니다.
- 트랜잭션이 커밋돼면
- 수동
- 언두 테이블스페이스를
비활성화해서 언두 테이블스페이스가 더이상 사용되지 않도록 설정하면퍼지 스레드는 비활성 상태의 언두 테이블 스페이스를 찾아서 불필요한 공간을 잘라내고 운영체제로 공간을 반납합니다. - 비활성화 :
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE; - 활성화 :
ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;
- 언두 테이블스페이스를
체인지 버퍼
RDBMS에서 레코드가 INSERT 되거나 UPDATE, DELETE 될 때 데이터 파일을 변경하는 작업 뿐만 아니라, 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다.
그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로, 테이블에 인덱스가 많다면 상당히 많은 자원을 소모하게 됩니다.
랜덤하게 디스크를 읽는다? 랜덤 디스크 액세스?
랜덤 디스크 액세스는 디스크에 접근할 때 순차적이지 않은 방식으로 블록에 접근하는 것을 의미합니다. 디스크는 블록 단위로 데이터를 저장하고, 블록은 디스크 상에 랜덤한 위치에 저장됩니다.
예를 들어, 데이터 베이스에서 특정 인덱스를 검색할 때, 해당 인덱스 블록의 물리적인 위치를 알아도 디스크의 헤드를 자주 이동시켜서 I/O 성능이 저하되는 문제를 야기합니다.
즉, 인덱스를 통해 원하는 노드의 위치를 알게 되는 과정까지 거치는 노드들도 모두 순차적으로 저장된 것이 아니라, 랜덤하게 저장되어 있어서 디스크의 헤드가 많이 움직이게 됩니다. 그래서랜덤 디스크 액세스작업이 많이 일어나게 되면 최악의 디스크 I/O 작업이 발생하게 됩니다.
단, PK는 예외입니다. PK는 클러스터링 인덱스로 데이터 파일과 물리적으로 동일한 순서로 정렬되어 있습니다. 그래서클러스터링 인덱스(PK) 업데이트 작업은순차적으로 디스크에 액세스할 수 있습니다.
InnoDB는 변경해야할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고, 임시 공간 에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 됩니다.
이 때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 합니다.
사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다.
체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread)라고 합니다.
innodb_change_buffering 이라는 시스템 변수가 새로 도입되어 작업의 종류별로 체인지 버퍼를 활성화할 수 있으며, 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선됐습니다.
- all : 모든 인덱스 관련 작업 버퍼링
- none : 버퍼링X
- inserts : 인덱스에 새로운 아이템 추가하는 작업만 버퍼링
- deletes : 인덱스에서 기존 아이템을 삭제하는 작업만 버퍼링
- changes : 인덱스에 추가하고 삭제하는 작업(inserts + deletes)만 버퍼링
- purges : 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링
체인지 버퍼는 InnoDB 버퍼 풀로 설정된 메모리 공간을 사용합니다. 기본적으로 InnoDB 버퍼 풀의 25% 까지 사용할 수 있게 설정되어 있습니다.
리두 로그 및 로그 버퍼
리두 로그(Redo log)는 서버가 비정상적으로 종료됐을 때, 데이터 파일에 기록하지 못한 데이터를 잃지 않게 해주는 안전장치입니다.
- 그래서
리두 로그(Redo log)는 트랜잭션의 4가지 요소인 ACID 중 D(Durable)에 해당하는 영속성과 가장 밀접하게 관련되어 있습니다.
MySQL 서버를 포함한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록합니다. 거의 모든 DBMS는 쓰기 보다 읽기 성능을 고려해서 구축되기에 파일 쓰기는 디스크의 랜덤 액세스가 필요합니다.
- 그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요합니다.
이로 인한 성능 저하를 막기 위해서, 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구합니다.
시나리오로 이해해보기
MySQL 서버가 비정상 종료되는 경우, InnoDB 스토리지 엔진의 데이터 파일은 다음과 같은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있습니다.
- 커밋됐지만, 데이터 파일에 기록되지 않은 데이터
- 롤백됐지만, 데이터 파일에 이미 기록된 데이터
첫 번째 경우는, 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됩니다.
하지만 두 번째 경우는 리두 로그보다는 언두 로그를 통해 해결합니다. 어떻게? 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 되기 때문입니다.
그렇다고 해서 두 번째 경우에 리두 로그가 전혀 필요없는 건 아닙니다. 최소한 그 변경이 커밋됐는지, 롤백됐는지, 아니면 중간상태였는지를 확인하기 위해서라도 리두 로그(Redo log)가 필요합니다.
리두로그의 동작방식
기본적으로 리두 로그는 트랜잭션이 커밋되면, 즉시 디스크로 기록합니다.
그렇게 되어있어야만 서버가 비정상적으로 종료됐을 때, 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 그 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능해집니다.
하지만 이처럼 트랜잭션이 커밋될 때마다, 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발합니다. 그래서 InnoDB 스토리지 엔진은 리두 로그를 어느 주기로 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공합니다. 해당 시스템 변수는 다음과 같은 값들을 가질 수 있습니다.
innodb_flush_log_at_trx_commit=0- 1초에 한 번씩 리두 로그를 디스크로
기록(write)하고,동기화(sync)실행
- 1초에 한 번씩 리두 로그를 디스크로
innodb_flush_log_at_trx_commit=1- 매번 트랜잭션이 커밋될 때마다 디스크로
기록(write)되고동기화(sync)까지 수행 - 그래서, 트랜잭션이 일단 커밋되면,
리두 로그에 해당 트랜잭션에서 변경한 데이터 삭제
- 매번 트랜잭션이 커밋될 때마다 디스크로
innodb_flush_log_at_trx_commit=2- 매번 트랜잭션이 커밋될 때마다 디스크로
기록(write)는 되지만, 실질적인동기화(sync)는 1초에 한 번씩 실행 - 일단 트랜잭션이 커밋되면, 변경 내용이 운영체제의 메모리 버퍼로 기록되는 것이 보장됩니다.
- 그래서 MySQL 서버가 비정상 종료되어도 운영체제가 정상적으로 작동한다면, 해당 트랜잭션의 데이터는 사라지지 않습니다.
- 단, MySQL 서버와 운영체제 모두 비정상 종료된다면, 최근 1초 동안의 트랜잭션 데이터는 사라질 수 있습니다.
- 매번 트랜잭션이 커밋될 때마다 디스크로
InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진의 버퍼 풀의 효율성을 결정하기 때문에 신중히 결정해야 합니다.
왜 리두 로그 파일들의 전체 크기는 버퍼 풀의 효율성을 결정할까?
위에서 언급한 내용을 참고하시면 됩니다.
그래도 조금 더 상세히 정리해보겠습니다. 리두 로그 파일들의 전체크기는(innodb_log_file_size) * (innodb_log_files_in_group)이 됩니다.
만약 리두 로그 파일들의 전체 크기가 매우 작다면, 디스크에 쓰기 작업이 빈번하게 일어나며 성능 저하를 야기할 수 있습니다.
하지만 리두 로그 파일의 크기가 크다면, 디스크에 쓰기 작업이 적게 일어날 것입니다. 다만, 써야할 데이터가 너무 많아서 버퍼 풀의 90% 이상이 더티 페이지로 차버리면,디스크 쓰기 폭발 현상(Disk IO Burst)현상이 발생하게 됩니다.
즉, 버퍼 풀의 크기를 고려해서 적절한 리두 로그 파일들의 전체 크기를 결정해야 합니다.
로그 버퍼(log buffer)
사용량(특히 변경 작업)이 매우 많은 DBMS 서버의 경우에는 이 리두 로그의 기록 작업이 큰 문제가 됩니다.
이러한 문제를 보완하기 위해, 최대한 ACID 속성을 보장하는 수준에서 버퍼링합니다. 이러한 리두 로그 버퍼링에서 사용되는 공간이 로그 버퍼입니다.
ACID속성
- A : Atomic, 원자성, 트랜잭션은 원자성 작업이어야 합니다.
- C : Consistent, 일관성
- I : Isolated, 격리성
- D : Durable, 영속성, 한 번 저장된 데이터는 지속적으로 유지돼야 함을 의미합니다.
ACID의 일관성과 격리성은 쉽게 정의내리기 힘들지만, 이 두가지 속성은 서로 다른 두 개의 트랜잭션에서 동일 데이터를 조회하고 변경하는 경우에도 상호 간섭이 없어야 한다는 것을 의미합니다.
리두 로그 아카이빙
MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아키이빙할 수 있는 기능이 추가됐습니다. MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 데이터 파일을 복사하는 동안 InnoDB 스토리지 엔진의 리두 로그에 쌓인 내용을 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사합니다.
그런데 MySQL 서버에 유입되는 데이터 변경이 너무 많으면, 리두 로그가 매우 빠르게 증가하고, 백업 툴이 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰일 수도 있습니다.
- 리두 로그는
순환되어 사용되는 구조이기 때문!
그래서 MySQL 8.0의 리두 로그 아카이빙 기능은 데이터 변경이 많아서, 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해줍니다.
- 어떻게?
리두 로그 파일이 로테이션될 때 복사되는 것이 아니라,리두 로그 파일에 로그 엔트리가 추가될 때 함께 기록되는 방식을 사용하고 있어서 데이터 변경이 발생하면 즉시 아카이빙된 로그 파일의 크기가 조금씩 늘어납니다. - 마치
LinkedList구조!
리두 로그 활성화 및 비활성화
MySQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 동기화됩니다.
- 물론
innodb_flush_log_at_trx_commit값이 0 또는 2라면 커밋돼도 리두 로그가 즉시 동기화되지 않을 수 있습니다.
위에서 말하는 데이터 파일은 테이블 데이터 파일, 인덱스 파일, 언두 로그 등을 포함합니다.
그래서 MySQL 8.0 버전부터는 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 아래 명령어와 같이 리두 로그를 비활성화해서 데이터의 적재 시간을 단축시킬 수 있습니다.
-- // 리두 로그 비활성화
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
-- // .. (대량의 데이터 적재 작업 중) ..
-- // 리두 로그 활성화
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;이처럼 리두 로그를 비활성화하고, 데이터 적재 작업을 실행했다면 리두 로그를 다시 활성화하는 것을 잊으면 안됩니다.
만약 리두 로그가 비활성화된 상태에서, MySQL 서버가 비정상적으로 종료되면, MySQL 서버의 마지막 체크포인트 이후 시점의 데이터는 모두 복구할 수 없게됩니다. 더 심각한 문제는 MySQL 서버의 데이터가 마지막 체크포인트 시점의 일관된 상태가 아닐 수 있다는 것입니다.
앞에서도 나왔지만
체크포인트란, MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 의미합니다.
그러므로 데이터가 일부 손실돼도 괜찮다면 리두 로그를 비활성화하는 것보다 innodb_flush_log_at_trx_commit 을 1이 아닌 0 또는 2로 설정해서 사용하는 것을 권장합니다.
어댑티브 해시 인덱스
어댑티브 해시 인덱스(Adaptive Hash Index)는 개발자가 수동으로 생성하는 인덱스가 아니라, InnoDB 스토리지 엔진에서 개발자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.
innodb_adaptive_hash_index시스템 변수로 활성화/비활성화 선택 가능합니다. (디폴트 : 활성화)
인덱스와 관련된 오해
사람들은 인덱스(B-Tree, B+Tree) 에서 특정 값을 찾는 과정은 매우 빠르게 처리된다고 알고 있습니다.
- 예시로, B-Tree 인덱스에서 특정 값을 찾으려면, 루트 노드 > 브랜치 노드 > 리프 노드까지 찾아가야 원하는 레코드를 읽을 수 있습니다.
하지만, 데이터베이스 서버가 얼마나 많은 일을 하냐에 따라 찾는 과정은 느려질 수도 빨라질 수도 있습니다.
낮은 사양의 컴퓨터에서 동시에 몇 천개의 스레드가 인덱스를 이용해 검색한다면 CPU는 엄청난 스케줄링을 하게 되고 자연히 쿼리의 성능은 떨어집니다.
어댑티브 해시 인덱스의 쓰임새 및 구조
아래 설명에서, 일반 인덱스는 B-Tree 인덱스라고 가정하고 설명하겠습니다.
어댑티브 해시 인덱스는 위의 상황처럼 인덱스의 검색 시간을 줄여주기 위해 도입된 기능입니다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있습니다.
어댑티브 해시 인덱스의 구조는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리됩니다.
- 즉,
어댑티브 해시 인덱스=인덱스 키 값:데이터 페이지 주소인덱스 키 값은 B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키 값 조합을 가집니다.- 즉,
인덱스 키 값 = 인덱스 고유번호 + 실제 B-Tree 인덱스의 실제 키 값
왜 해시 인덱스의 키 값에 "B-Tree 인덱스의 고유번호"가 주어질까? 그 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하기 때문입니다.
최근 버전에선, 해시 인덱스가 파티션된다.
즉, 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문입니다.
그리고 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미합니다.
즉, 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없으면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라집니다.
어댑티브 해시 인덱스 성능
어댑티브 해시 인덱스를 활용하니, CPU 사용률도 줄고, 요청 가능 쿼리 수도 증가했습니다.
어댑티브 해시 인덱스의 단점들
There is no silver bullet...
어댑티브 해시 인덱스의 경합 (MySQL 8.0에서 해결)
예전 버전까지는 어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로, 어댑티브 해시 인덱스의 경합이 심했습니다.
그래서 MySQL 8.0부터는 내부 잠금(세마포어) 경합을 줄이기 위해, 어댑티브 해시 인덱스의 파티션 기능을 제공합니다.
innodb_adaptive_hash_index_parts변수로 파티션 개수 지정 가능- 디폴트 : 8개
어댑티브 해시 인덱스가 도움되지 않는 상황
단순히 어댑티브 해시 인덱스가 도움될지 안될지 판단하는 것은 쉽지 않습니다.
그래도 한가지 확실한 것은 어댑티브 해시 인덱스는 데이터 페이지를 버퍼 풀(메모리) 내에서 접근하는 것을 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않습니다.
또한, 어댑티브 해시 인덱스 또한 일반 인덱스처럼 별도의 저장공간인 메모리를 사용하고 때로는 상당히 큰 메모리를 사용하기도 합니다.
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
반대로,
어댑티브 해시 인덱스가 도움되는 상황 은 다음과 같습니다.
- 디스크의 데이터가 InnoDB 버퍼 풀의 크기와 비슷해 디스크 읽기가 많지 않은 경우
- 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
참고로, 어댑티브 해시 인덱스의 도움을 많은 테이블은 테이블의 삭제, 변경 작업(DDL)은 치명적인 작업이 됩니다. 이는 어댑티브 해시 인덱스 를 사용할 때에 꼭 주의해야할 점입니다.
- 상황가정) A 테이블의 인덱스가
어댑티브 해시 인덱스에 적재되어 있다고 가정해보겠습니다. - 이제 A 테이블을 삭제(DROP)이나 변경(ALTER)하려면, InnoDB 스토리지 엔진은 A 테이블이 가진 모든 데이터 페이지의 내용을
어댑티브 해시 인덱스에서 제거해야 합니다. - 이로 인해 테이블이 삭제되거나 스키마가 변경되는 동안 상당히 많은 CPU 자원을 사용하고, 그만큼 데이터베이스 서버의 성능이 느려집니다.
Reference
Real MySQL 8.0.1
https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-buffer.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html
https://tech.kakao.com/2016/04/07/innodb-adaptive-hash-index/
