1. 언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업하는데, 이렇게 백업된 데이터를 언두 로그(Undo Log)라고 합니다.
1.1. 언두 로그 레코드 모니터링
언두 영역은 DML 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳입니다. 언두 로그의 데이터는 크게 두 가지 용도로 사용됩니다.
트랜잭션의 롤백 대비용: 변경 이전 데이터를 백업
트랜잭션의 격리 수준 보장: 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지 결정
대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있습니다. 또한 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것도 아닙니다.
언두 로그의 저장 공간이 계속 증가하여 디스크의 사용량이 증가하는 문제는 큰 문제가 아닐 수도 있습니다. 하지만 그동안 빈번하게 레코드를 조회하는 쿼리가 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어지게 됩니다.
그래서 MySQL 서버별로 안정적인 시점의 언두 로그 레코드 건수를 확인해 이를 기준으로 언두 로그의 급증 여부를 모니터링 하는 것이 좋습니다. 다음과 같이 MySQL 서버의 언두 로그 레코드 건수를 확인할 수 있습니다.
-- // MySQL 서버의 모든 버전에서 사용 가능한 명령
mysql> SHOW ENGINE INNODB STATUS\G
-- // MySQL 8.0 버전에서 사용 가능한 명령
mysql> SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
1.2. 언두 테이블스페이스 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 합니다. 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가집니다.
하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가집니다. 예를 들면, InnoDB의 페이지 크기가 16KB라면 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 갖게 되고, 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 DML 문장의 특성에 따라 최대 4개가 됩니다. 일반적으로는 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정하면 됩니다.
최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 수) * (언두 테이블 스페이스 개수)
언두 로그 관련 시스템 변수를 변경해야 한다면, 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수를 설정해야 합니다. 물론 일반적인 서비스에서 기본값으로 해서 크게 문제될 건 없으므로 가능하면 기본값을 유지해도 좋습니다.
언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 'Undo tablespace truncate'라고 합니다. 이 반납(Truncate) 방법은 자동과 수동 두 가지 방법을 모두 지원합니다. MySQL 서버의 시스템 변수 innodb_undo_log_truncate ON으로 설정되면 자동, OFF으로 설정되면 수동이며 기본값은 OFF입니다.
2. 체인지 버퍼
RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다.
InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있다면 바로 업데이트를 수행하지만, 그렇지 않다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킵니다.
이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 합니다.
3. 리두 로그 및 로그 버퍼
거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요합니다.
그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요한데, 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있습니다.
비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구합니다.
3.1. 리두 로그 아카이빙
MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가됐습니다. MySQL 서버에 유입되는 데이터 변경이 너무 많으면 엔터프라이즈 백업이나 Xtrabackup 툴이 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰일 수 있습니다.
리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해줍니다.
3.2. 리두 로그 활성화 및 비활성화
리두 로그는 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화돼있습니다.
또한 MySQL 8.0부터는 수동으로 리두 로그를 활성화하거나 비활성화할 수 있게 됐습니다. 다음과 같이 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 리두 로그를 비활성화해서 데이터의 적재 시간을 단축시킬 수 있습니다.
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
-- // 리두 로그를 비활성화한 후 대량 데이터 적재를 실행
mysql> LOAD DATA...
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
데이터가 중요하지 않다 하더라도 서비스 도중에는 리두 로그를 활성화해서 MySQL 서버가 비정상적으로 종료돼도 특정 시점의 일관된 데이터를 가질 수 있게 하는 것을 권장합니다.
4. 어댑티브 해시 인덱스
InnoDB의 대표적인 트리 구조는 B+Tree입니다. 데이터는 Primary Key 순으로 정렬되어 관리되고, Secondrary Key는 인덱스키+PK를 조합으로 정렬이 되어 있습니다.
즉, 특정 데이터를 찾기 위해서는 Secondrary Key에서 PK를 찾고, 그 PK를 통해 다시 원하는 데이터로 찾아가는 형태로 데이터가 처리됩니다. 트리의 가장 큰 강점은 데이터 접근 퍼포먼스가 데이터 증가량에 따라서도 결코 선형적으로 증가하지 않다는 점에 있습니다.
참고로 PK 접근 시 데이터 접근에 소요되는 비용은 O(logN)이고, 트리에 두 번 접근하는 Secondary Key에 소요되는 비용은 2 * O(logN)입니다.
(이미지 출처: tech.kakao.com/2016/04/07/innodb-adaptive-h..)
어댑티브 해시 인덱스(Adaptive Hash Index)는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다. 이를 이용하여 이용하여 트리 검색 시간을 줄일 수 있습니다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 트리를 타지 않고 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 됩니다. “Adaptive”라는 단어에서 예상할 수 있겠지만, 모든 값들이 해시로 생성이 되는 것이 아니라 자주 사용되는 데이터 값만 내부적으로 판단하여 상황에 맞게 해시 값을 생성합니다.
어댑티브 해시 인덱스가 항상 도움이 되는 것은 아닙니다. 해시 인덱스 유지 자체에 대한 오버헤드가 많이 추가되고, 버퍼 풀에서 메모리를 빼앗아갈 수도 있습니다. AHI가 성능 향상에 크게 도움이 되지 않는 경우는 다음과 같습니다.
디스크 읽기가 많은 경우
특정 패턴의 쿼리가 많은 경우(JOIN이나 LIKE 패턴 검색)
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
그리고 다음과 같은 경우에는 성능 향상에 많은 도움이 됩니다.
디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
AHI가 우리 서비스에 맞게 도움이 되는지 판단할 수 있는 가장 쉬운 방법은 MySQL 서버의 상태 값들을 살펴보는 것입니다.
mysql> SHOW ENGINE INNODB STATUS\G
AHI의 효율은 검색 횟수가 아니라 해시 인덱스 히트율과 AHI가 사용 중인 메모리 공간, 그리고 서버의 CPU 사용량을 종합해서 판단해야 합니다.
몇 가지 상황에 대한 AHI의 퍼포먼스는 여기를 참고해주세요.
🎯정리
언두 로그는 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업된 데이터입니다.
언두 테이블스페이스와 롤백 세그먼트의 개수는 필요한 동시 트랜잭션의 개수에 맞게 적절히 설정하여야 합니다.
Undo tablespace truncate는 언두 테이블 스페이스 공간을 필요한 만큼만 남기고 운영체제로 반납하는 기능입니다.
체인지 버퍼는 변경해야 할 인덱스 페이지가 버퍼 풀에 없을 때 변경 사항을 캐시하는 임시 메모리 공간입니다.
리두 로그는 장애가 발생한 트랜잭션에 의해 쓰여진 데이터를 수정하기 위해 복구 중에 사용되는 데이터입니다.
어탭티브 해시 인덱스(AHI)는 클라이언트가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.