Last Updated on 1월 23, 2022 by Jade(정현호)
안녕하세요
이번 포스팅에서는 UNDO Tablespace 에 대한 전반적인 내용과 UNDO 테이블스페이스 truncate 관련된 내용에 대해서 확인 해보려고 합니다.
Contents
UNDO Tablespace
UNDO Tablespace (언두 테이블스페이스) 는 언두 로그를 저장하며, 언두 로그는 Clustered Index 레코드의 변경시 이전 데이터(Before Image) 를 저장하는 공간 영역 으로 수행된 트랜잭션에 대한 취소 하는 방법에 대한 정보가 포함 되어 있습니다
즉, 수행된 트랜잭션의 취소를 수행하기 위해서 저장 및 참조 되는 로그 이며, 저장되는 장소가 언두 테이블스페이스 입니다.
또한 이런 언두 로그를 통해서 다중 버전 동시성 제어(MVCC) 기능을 사용합니다.
Default(기본) undo tablespace는 SQL 문이 승인되기 전에 존재해야 하는 롤백 세그먼트의 위치를 제공하기 위해 인스턴스 초기화 시 생성됩니다.
Default undo tablespaces 의 수나 위치는 버전에 따라 차이가 있으며 5.7 버전까지는 innodb_undo_tablespaces 시스템 변수에 의해서 수와 위치가 결정되었습니다.
MySQL 5.7
MySQL 서버 5.7 에서 innodb_undo_tablespaces 시스템 변수의 기본값은 0 으로 시스템 변수를 별도로 설정하지 않으면 시스템 테이블스페이스에 같이 위치하게 됩니다.
각 언두 테이블스페이스는 최대 128개의 롤백 세그먼트를 개별적으로 지원합니다 innodb_rollback_segments 시스템 변수는 롤백 세그먼트의 수를 정의합니다.
롤백 세그먼트가 지원하는 트랜잭션 수는 롤백 세그먼트의 언두 슬롯 수와 각 트랜잭션에 필요한 언두 로그 수에 따라 달라지게 됩니다.
롤백 세그먼트의 언두 슬롯 수는 InnoDB 페이지 크기에 따라 달라집니다.
| InnoDB 페이지 크기 | 롤백 세그먼트의 언두 슬롯 수(InnoDB 페이지 크기 / 16) |
|---|---|
| 4096 (4KB) | 256 |
| 8192 (8KB) | 512 |
| 16384 (16KB) | 1024 |
| 32768 (32KB) | 2048 |
| 65536 (64KB) | 4096 |
하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 DML 문장의 특성에 따라서 최대 4개 까지 언두 슬롯을 사용하게 되며, 임시 테이블을 사용하지 않는 일반적인 환경에서는 하나의 트랜잭션은 2개 정도 언두 슬롯이 필요 하게 됩니다.
기본 16KB 사이즈 InnoDB 페이지를 사용하는 경우 초기의 언두 테이블스페이스 파일 크기는 10MB 가 됩니다.
4KB, 8KB, 32KB 및 64KB 페이지 크기의 경우 초기 실행 취소 테이블스페이스 파일 크기는 각각 7MB, 8MB, 20MB 및 40MB 가 되게 됩니다.
innodb_undo_tablespaces 시스템 변수는 MySQL 인스턴스를 초기화하기 전에만 설정 할 수 있습니다. 인스턴스가 생성 된 이후에는 변경 할 수 없습니다.
MySQL 5.7 버전 까지는 값을 지정하지 않으면 기본 설정인 0 을 사용하여 인스턴스가 초기화 되고 시스템 테이블스페이스에 위치하게 됩니다.
인스턴스가 초기화 된 이후 해당 시스템 변수를 변경해서 재시작하면 에러가 발생 됩니다.
[ERROR] InnoDB: Expected to open N undo tablespaces but was able to find only 0 undo tablespaces.
Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting
innodb_undo_directory 시스템 변수를 사용하여 언두 테이블스페이스의 디렉터리 위치를 지정 합니다.
디렉토리 위치를 지정하지 않으면 data directory 에 언두 테이블스페이스가 생성됩니다.
MySQL 8.0
MySQL 서버 8.0 부터는 기본 값으로 2개의 언두 테이블스페이스를 생성 하게 됩니다
MySQL 5.7 과 동일하게 innodb_undo_directory 시스템 변수에 설정에 의해서 파일 위치가 정의 되며, 설정 되어 있지 않을 경우 data directory 에 저장 됩니다.
파일이름은 undo_001, undo_002 와 같은 형식으로 생성 됩니다.
~# ls -alrt data | grep undo mysql mysql 10485760 Nov 27 10:34 undo_001 mysql mysql 10485760 Nov 27 10:34 undo_002
MySQL 8.0.14 이전에는 innodb_undo_tablespaces 설정을 늘리면 지정된 수의 언두 테이블스페이스가 생성되어 active undo tablespaces 목록에 추가되었습니다.
set global innodb_undo_tablespaces 명령어를 통해서 런타임 중에 변경이 가능하고 설정을 낮추면 활성 언두 테이블스페이스 목록에서 실행 취소 테이블스페이스가 제거됩니다.
하지만 활성 목록에서 제거된 언두 테이블스페이스라도 기존 트랜잭션에서 더 이상 사용 되지 않을 때까지 활성 상태로 유지됩니다.
그리고 MySQL 8.0.14 이전에는 비활성화된(deactivated) 언두 테이블스페이스를 제거할 수 없습니다.
언두 테이블스페이스 파일의 수동 제거는 Slow shutdown 후에 가능하지만 서버를 종료할 때 열려 있는 트랜잭션이 있는 경우 서버가 다시 시작된 후 일정 시간 동안 비활성화된 실행 취소 테이블스페이스에 활성 실행 취소 로그가 포함될 수 있으므로 권장하지 않습니다.
MySQL 8.0.14 부터 개선되어 DROP UNDO TABALESPACE 구문을 사용하여 언두 테이블스페이스를 삭제할 수 있게 되었습니다.
또한 MySQL 8.0.14 버전 부터 innodb_undo_tablespaces 시스템 변수 Deprecated 가 되었으며, 시스템 변수를 설정 하여도 더 이상 적용되지 않으며 인스턴스 초기화 이후 변경 하여도 에러는 발생되지 않습니다(변수 값 변경이 적용되지 않음 - 무시됨)
MySQL 서버 8.0.14 버전 부터는 Undo Tablespace 의 추가는 아래와 같이 명령어를 사용하도록 기능 추가가 되었습니다.
mysql> CREATE UNDO TABLESPACE 테이블스페이스_이름 ADD DATAFILE 'file_name.ibu';
UNDO Tablespace 의 확장자는 ibu 를 사용해야 하며 언두 테이블스페이스 파일 이름을 정의 할 때는 상대 경로를 지정할 수 없습니다.
FULL PATH 는 허용되지만 PATH 는 InnoDB 에서 알고 있어야 하며 innodb_directories 시스템 변수에 정의 된 PATH 이어야 합니다.
[MySQL 8.0 InnoDB 아키텍처]
그래서 이와 같이 별도로 언두 테이블스페이스를 추가 될 수 있는 부분이 MySQL 8.0 에서 기능 추가 되었으며 MySQL 8.0 의 InnoDB 아키텍처에서도 system 언두 테이블스페이스 2개 이외 추가적인 언두 테이블스페이스는 user-defined 라고 되어 있는 것을 확인 할 수 있습니다.
Online Truncate of InnoDB UNDO Tablespaces
MySQL 서버 5.7 버전에서는 용량이 커진 UNDO Tablespace 에 대해서 truncate 하는 기능이 추가 되었습니다
(5.7 버전에서는 기본값은 기능 비활성화(OFF) 입니다)
InnoDB UNDO 테이블스페이스는 데이터베이스의 변경과 관련된 롤백 정보(이전 페이지 정보)를 보유 하는 저장 공간으로, 이 정보는 트랜잭션을 롤백하고 다중 버전 동시성 제어(MVCC) 기능을 제공 하게 됩니다
트랜잭션이 COMMIT(커밋) 되면 InnoDB는 관련된 UNDO 로그 레코드를 버리게 됩니다.
업데이트 또는 삭제에 대한 언두 레코드는 이전 버전의 레코드에 엑세스 할 수 있는 활성화된 트랜잭션이 있는 한 계속 유지가 됩니다.
이러한 모든 활성화된 트랜잭션이 커밋되면(트랜잭션이 종료 되면) 연결된 UNDO 레코드를 삭제할 수 있게 됩니다.
현재의 InnoDB 의 퍼지 스레드(purge thread(s)) 는 오랜된 UNDO 로그 레코드가 포함된 UNDO 로그 페이지를 해제 하여 해당 페이지를 재사용 할 수 있도록 시도 합니다.
그러나 보존 기간(해당 UNDO 로그 레코드가 필요한 활성 트랜잭션에 의해 결정) 에 따라서 UNDO 테이블스페이스는 계속 해서 새 페이지를 할당 하면서 크기가 계속 커지게 됩니다.
그 때문에 여러개의 활성화된 오랜 시간 실행된 트랜잭션이 있으면 UNDO 테이블스페이스 크기가 계속해서 증가 하고 시간이 지남에 따라서 상당히 커질수도 있습니다.
공간 재확보를 위해서 필요 이상으로 커진 UNDO 테이블스페이스의 디스크 공간을 줄이기 위해서 UNDO 테이블스페이스의 Truncate 기능이 새롭게 추가 되었습니다
기능 활성화
UNDO 테이블스페이스의 truncate 기능 지원 여부는 innodb_undo_log_truncate 시스템 변수의 설정에 따르며 기본값은 OFF 입니다(MySQL 5.7 버전 기준)
기능을 사용하기 위해서는 아래와 같이 시스템 변수를 변경 해야 합니다.
mysql> SET GLOBAL innodb_undo_log_truncate=ON;
UNDO 테이블 스페이스의 truncate 를 사용하기 위해서는 같이 설정이 필요한 시스템 변수가 있습니다.
UNDO 로그가 UNDO 테이블스페이스를 사용하도록 구성된 경우에만 truncate 기능이 동작 합니다.
시스템 테이블스페이스 내에 저장된 UNDO 로그 공간은 자를 수 없습니다.
즉 innodb_undo_tablespaces 시스템 변수가 설정되어있어야 하며 또한 최소 2 이상으로 설정하여 UNDO 테이블스페이스는 최소 2개이상으로 구성되어 있어야 합니다.
위에서 설명된 내용과 같이 MySQL 5.7 에서는 인스턴스 초기화시 innodb_undo_tablespaces 시스템 변수 값을 지정하지 않아 기본값 0 으로 인스턴스가 초기화 되었다면 다시 인스턴스를 재생성 해야 합니다(초기화 이후 변경 불가)
추가로 innodb_undo_logs 시스템 변수는 35 이상으로 설정되어있어야 하며 기본값은 128 입니다.
해당 시스템 변수는 MySQL 5.7.19 버전에 부터 Deprecated 되었으며, innodb_rollback_segments 로 대체 되었습니다.
하나의 언두 세그먼트에 저장될 수 있는 트랜잭션 수를 설정 하는 시스템 변수 입니다.
InnoDB에서 사용하는 롤백 세그먼트의 수를 늘리거나 줄일 수 있지만 시스템에 물리적으로 존재하는 롤백 세그먼트의 수는 결코 줄어들지 않습니다.
따라서 낮은 값으로 시작하여 필요하지 않은 롤백 세그먼트를 할당하지 않도록 하고 점차적으로 값을 늘리는 방법을 고려 할 수 있을 것 같습니다.
innodb_rollback_segments 기본값 및 최대값은 128입니다.
How it Works
위에서 언급한 모든 조건이 충족되면 InnoDB 는 아래와 같이 truncate 를 시도 합니다.
1) 언두 테이블스페이스를 truncate 하기 위해서 Select/Mark 를 합니다. 편향 선택을 피하기 위해 라운드 로빈 방식으로 수행됩니다.
2) 선택한 UNDO 테이블스페이스에 있는 모든 롤백 세그먼트(rseg)를 비활성화합니다.
비활성은 롤백 세그먼트가 새 트랜잭션에 할당되지 않음을 의미합니다.
롤백 세그먼트를 사용하는 기존 트랜잭션은 영향을 받지 않고 계속 진행됩니다.
3) "purge system"은 더 이상 필요하지 않은 롤백 세그먼트를 계속 해제합니다. 롤백 세그먼트에 할당된 페이지를 여유 공간으로 표시하고 롤백 세그먼트의 논리적 크기를 줄입니다.
4) UNDO 테이블스페이스에 있는 모든 롤백 세그먼트가 해제되면 표시된 UNDO 테이블스페이스에서 실제 파일 시스템의 truncate 작업이 수행됩니다.
그런 다음 새로 생성될 때 설정된 기본 크기로 다시 초기화됩니다.
5) Truncated 롤백 세그먼트는 새 트랜잭션에 할당될 수 있도록 다시 활성화됩니다.
롤백 세그먼트 해제 가속화
Purge thread 는 더 이상 필요하지 않은 롤백 세그먼트를 해제하려고 시도합니다.
모든 롤백 세그먼트가 해제될 때까지 UNDO 테이블스페이스 truncate 를 진행할 수 없습니다.
이전에는 128개 롤백 세그먼트 마다 Purge thread 를 호출 하여 해제를 진행하였습니다.
이것은 MySQL 성능을 유지하기 위해서 수행되었으며, 이 작업은 리소스를 많이 사용하기 때문 입니다.
이 고정값 128은 innodb_purge_rseg_truncate_frequency 시스템 변수로 대체 되었으며 해제 의 수행 빈도를 조절 할 수 있게 됩니다.
innodb_purge_rseg_truncate_frequency 기본값은 128이며 최대 128 입니다.
truncate 가 호출되는 횟수와 관련되어 있으며, 기본값으로 128 롤백 세그먼트 마다 해제가 수행됩니다.
이 값을 줄이게 되면 Purge thread 가 롤백 세그먼트에 대해서 해제 하는 빈도가 증가 합니다.
mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency = 64;
사용자는 시스템 성능을 위해서 기본값인 128을 유지 할 수도 있고, 상황에 따라서 더 빠른 UNDO 세그먼트의 Purge/Truncate 를 위해서 이 변수를 줄여서 가속화도 할 수 있습니다.
Minimum Size of UNDO Tablespace to Truncate
UNDO 테이블 스페이스의 truncate 가 실행되는 최소 사이즈를 지정할 수 있습니다.
UNDO 테이블스페이스의 임계값 크기의 정의 이며, UNDO 테이블스페이스가 임계값을 초과하면 innodb_undo_log_truncate가 활성화될 때 truncation 으로 표시(marked) 됩니다.
innodb_max_undo_log_size 시스템 변수는 기본값은 1073741824 bytes (1024 MB) 입니다.
성능적 영향
MySQL 5.7 까지는 해당 기능을 활성화 하여 사용을 하면 성능에 약간의 영향이 있을 수 있습니다.
어플리케이션의 요청이 계속 진행 중인 시스템에서 온라인으로 truncate 를 수행한다는 점을 생각해본다면 사용자 트랜잭션에 성능적 영향이 있을 수 있습니다.
영향을 받는 부분은 어디서 오는지 확인 해 보도록 하겠습니다.
위에서 언급 한 내용과 같이 UNDO 테이블스페이스에 있는 롤백 세그먼트는 truncation 으로 표시(marked) 되고 비활성화가 됩니다.
이것은 관련된 사용 가능한 UNDO 로그 관리 리소스를 제한하게 됩니다.
예를 들어, 2개의 UNDO 테이블 스페이스에 128개의 UNDO 로그가 있고 이중에 95개가 해당 2개의 UNDO 테이블 스페이스에 있을 경우, truncate 를 하기 위해서 UNDO 테이블 스페이스 중 1개를 오프라인 으로 설정하게 됩니다.
오프라인으로 설정하면 트랜잭션 처리에 48 UNDO 로그만 트랜잭션에 사용할 수 밖에 없으므로 결과적으로 이전에 비해 UNDO 로그 처리 리소스가 절반으로 줄어들게 됩니다
이것이 truncate 작업이 실행되는 동안 트랜잭션 처리 속도(TPS)에 약간의 영향을 미치게 됩니다
다만 정확한 성능 영향은 다음과 같은 요인에 따라 달라지게 됩니다.
• UNDO 테이블스페이스 수
• UNDO 로그 수
• UNDO 테이블스페이스의 크기
• I/O 하위 시스템의 속도
• 장기 실행 트랜잭션이 있는지 여부
• 시스템에 부하가 많이 걸리는지 여부
MySQL Team 의 내부 테스트를 기반으로 innodb_undo_tablespaces=8, innnodb_undo_logs=128, innodb_max_undo_logs_size=100M에서 128 스레드에서 TPS의 sysbench OLTP_RW 로 테스트 하였을 때 TPS 성능 저하는 5% 미만 이었습니다.
truncate 작업이 완료되고 모든 롤백 세그먼트가 다시 활성화되면 더 많은 리소스를 사용할 수 있게 되면서 성능이 자연스럽게 다시 향상(또는 원래대로) 됩니다
이 기능은 전체 서버 부하가 적을 것으로 예상되는 주말 새벽 시간대에 스케줄러 설정으로 innodb_undo_log_truncate 시스템 변수를 동적으로 ON/OFF 를 변경하여 실행하는 방법도 고려 할 수 있습니다.
MySQL 8.0.21 에서 개선 사항
MySQL 서버 8.0.21 에서 Undo Truncation 의 개선 사항이 있습니다.
먼저 MySQL 8.0 버전에서는 UNDO 테이블 스페이스는 암시적(implicitly) 또는 명시적(explicitly)으로 truncate 할 수 있습니다.
두 방법 모두 동일한 매커니즘을 사용 합니다.
이 매커니즘은 UNDO 테이블스페이스의 truncate 수행이 완료 되는 동안 사용량이 많은 시스템에서 주기적으로 성능적 영향을 유발 할 수 있었습니다.
이러한 문제가 MySQL 8.0.21 버전에서 수정이 되었습니다.
MySQL 8.0 에서는 아래 2가지 파라미터의 기본값이 5.7과 다르게 변경되었으며, 그에 따라서 UNDO 테이블스페이스의 Truncate 가 기본으로 동작 하게 됩니다.
innodb_undo_tablespaces : 기본값 2
innodb_undo_log_truncate : 기본값 ON
Implicit Truncation
먼저 암시적(Implicit) 방법은 MySQL 8.0 에서 기본적으로 UNDO 테이블스페이스 truncate 기능이 ON 입니다.
이러한 기본 설정에 의해서 UNDO 테이블 스페이스가 1GB 보다 커지면 InnoDB의 백그라운드 Purge thread에 의해 오프라인으로 전환 됩니다.
UNDO 테이블스페이스의 UNDO 로그 와 관련된 모든 트랜잭션이 완료 되고 해당 공간의 모든 UNDO 로그가 데이터 무결성을 보호하기 위해 더이상 필요하지 않으면 테이블 스페이스의 truncate 가 준비가 완료 된 상태가 됩니다
그런 다음 테이블스페이스가 삭제되고 이를 대체할 새로운 UNDO 테이블스페이스가 생성되게 되며 완료 되면 다시 활성화 합니다
Explicit Truncation
다음으로 명시적(Explicit) 입니다. UNDO 테이블스페이스가 너무 커졌다고 생각이 될 경우 아래 명령어를 실행하여 truncate 프로세스를 명시적으로 활성화 할 수 있습니다.
mysql> ALTER UNDO TABLESPACE 테이블스페이스_이름 SET INACTIVE;
위와 같이 명령을 수행 후에 프로세스가 UNDO 로그가 불필요해질때까지 기다리고 있는지 또는 완료 되었는지를 아래 딕셔너리 뷰에서 확인 할 수 있습니다.
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'tablespace_name';
8.0.21 에서의 개선사항
1) flushing operation ware removed
MySQL Team 에서는 매우 바쁜 시스템의 경우 truncate 작업 수행하면 버퍼풀에서 old undo 테이블스페이스(trucated 된 UNDO 테이블스페이스) 의 모든 페이지를 Flush 하기 때문에 성능저하를 일으킬수 있음을 확인 하였습니다.
따라서 MySQL 8.0.21 에서는 이와 같은 flushing operation 이 사라졌으며 InnoDB는 이제 관련된 UNDO 데이터파일이 삭제된 후 해당 페이지를 버퍼 풀에 남겨둡니다.
InnoDB 는 이 페이지가 삭제된 테이블스페이스와 연관되어 있음을 알고 있습니다.
남은 페이지는 LRU 에 의해 해제 되게 되며 그런 다음 전체 Checkpoint 에서 나머지 버퍼를 해제하게 됩니다.
이러한 Internal buffer pool 관리 기능을 사용하면 UNDO 테이블스페이스의 truncate를 거의 즉각적으로 수행할 수 있게 됩니다
2) instead of flushed to disk during the truncate operation
새로운 UNDO 테이블스페이스의 초기 페이지는 이제 truncate 작업 동안 Redo logged 가 디스크에 플러시(flushed to disk)를 대신 합니다.
3) prevent potential issues
InnoDB는 Single UNDO 테이블스페이스에 대해 512개의 고유 테이블스페이스 ID 범위를 사용합니다
UNDO 테이블스페이스에 truncate 가 발생 할때 마다 라운드 로빈 방식으로 새 ID 를 할당 받게 됩니다.
매우 바쁜 시스템에서 innodb_max_undo_log_size 의 설정값이 작거나, 같은 UNDO 테이블스페이스에 대해서 명시적으로 truncate 를 반복할 경우 와 같이 상황이 엉망이 되면 두 체크포인트 사이에서 UNDO 테이블스페이스가 512번 이상 truncate 될 수 있음이 발견 되었습니다.
이 경우 버퍼 풀에 페이지가 있거나 동일한 UNDO 테이블스페이스의 512개 이상의 different incarnations 에 대한 리두 로그가 변경될 수 있습니다.
과도한 수의 UNDO 테이블스페이스 truncate 작업으로 인해 발생할 수 있는 잠재적인 문제를 방지하기 위해 체크포인트 간 동일한 실행 UNDO 테이블스페이스에 대한 자르기 작업이 이제 64개로 제한됩니다.
제한을 초과하면 UNDO 테이블스페이스를 계속 비활성화할 수 있습니다. 그러나 다음 체크포인트 이후까지 truncate 되지 않습니다.
이러한 개선점은 InnoDB 가 더 성능향상이 되는 또다른 방법 이 될 것 같습니다.
Reference
Reference Link
• mysql.com/blog/online-truncate-of-innodb-undo-tablespaces
• mysql.com/blog/improvements-to-undo-truncation-in-mysql-8-0-21
• mysql.com/5.7/innodb-undo-tablespaces
• mysql.com/8.0/innodb-undo-tablespaces
• mysql.com/8.0/innodb-add-undo-tablespaces
• mysql.com/8.0/innodb-undo-logs
• mysql.com/news-8-0-21
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
