MySQL Undo Log (Undo Tablespace) 알아보기

- 목차

 
 

함께 보면 좋은 글

https://westlife0615.tistory.com/16
 

MySQL Page 알아보기

소개.

MySQL 은 Undo Log 라는 데이터 저장 영역이 있습니다. 
Undo 란 revert, rollback 과 같이 직전에 수행한 동작을 되돌리는 행위입니다. 
MySQL 같은 데이터데이스 영역에서의 Undo 란 Write Query (DML Query) 를 되돌려 원래 상태로 복구함을 뜻하는데요. 
Insert 는 Delete 로 
Delete 는 Insert 로 
Update 는 반대되는 조건으로 Update 해야합니다. 
그래서 Undo Log 는 이름 그대로 Undo 를 위한 MySQL 의 데이터 영역입니다. 

Undo Log 는 구체적으로 Transaction 에서 사용됩니다. 
Transaction Begin 을 시작으로해서
Commit 또는 Rollback 으로 마무리되는 Transaction 영역에서 수행되는 Write Query 들이 Undo Log 가 다루는 대상입니다. 

Undo Log 는 Transaction 내부에서 변경하고자하는 값의 원본 데이터를 저장합니다. 
Transaction 내부에서 Delete 가 수행되는 경우,
삭제될 원본 데이터가 Undo Log 에 저장됩니다. 
그리고 Transaction 이 Rollback 될 떄, Undo Log 에 존재하는 원본 데이터를 사용하게 되죠. 
 
예를 하나 들어보도록 하겠습니다.
 
<test_table create>
별 의미없는 테이블입니다.
많은 양의 데이터를 추가하기 위해서 만든 Schema 입니다.

create table test_table (
	a int not null auto_increment primary key, 
	b varchar(256) default (UUID()), c varchar(256) default (UUID()), d varchar(256) default (UUID()),
	e varchar(256) default (UUID()), f varchar(256) default (UUID()), g varchar(256) default (UUID()),
	h varchar(256) default (UUID()), i varchar(256) default (UUID()), j varchar(256) default (UUID()),
	k varchar(256) default (UUID()), l varchar(256) default (UUID()), m varchar(256) default (UUID()),
	n varchar(256) default (UUID()), o varchar(256) default (UUID()), p varchar(256) default (UUID()),
	q varchar(256) default (UUID()), r varchar(256) default (UUID()), s varchar(256) default (UUID()),
	t varchar(256) default (UUID()), u varchar(256) default (UUID()), v varchar(256) default (UUID()),
	x varchar(256) default (UUID()), y varchar(256) default (UUID()), z varchar(256) default (UUID())		
);

 
< 1백만개의 row 를 insert 하는 procedure >

DELIMITER $$
    DROP PROCEDURE IF EXISTS insert_bulk_data;
	CREATE PROCEDURE insert_bulk_data ()
	BEGIN
		DECLARE X INT;
		SET X=1;
		WHILE X <= 1000000 DO
			INSERT INTO test_table() values();
		SET X=X+1;
		END WHILE;
	END$$
DELIMITER ;
CALL insert_bulk_data();
SELECT count(*) from test_table;

 
< 1백만개 row 조회 >

SELECT count(*) from test_table;
+----------+
| count(*) |
+----------+
|  1170947 |
+----------+

 
< 1백만개 delete  >
Delete 쿼리의 실행 이후에 Undo log 파일의 사이즈가 증가합니다.
16mb 에서 48mb 로 증가합니다.

delete from test_table where a > 0;
Query OK, 1170947 rows affected (56.53 sec)

 
 
< 초기 undo log file size >

 
< delete 이후의 undo log file size >

 
delete 쿼리의 결과로 undo log 파일의 사이즈가 증가합니다.
왜냐하면 Transaction 의 Rollback 을 대비해서 Delete 쿼리의 대상인 Row 들의 원본 데이터들이 Undo log 파일에 저장되기 때문입니다.

이어지는 내용에서 Undo log 에 대해서 상세히 알아보도록 하겠습니다.

Undo log 란 ?

Undo log 는 DML 쿼리에 대한 트랜잭션을 보조합니다.
Insert, Update, Delete 에 해당하는 DML 쿼리들은 데이터를 변경하게 되는데요.
데이터의 변경이 Rollback 되는 상황을 대비하여 변경 데이터의 원본 데이터를 Undo log 에 보관합니다.
Undo Log Entry 하는 형태로 기록이 되구요.
Transaction Rollback 상황 또는 시스템적으로 DML 쿼리가 실패하게 되면 Undo log의 원본 데이터를 통해서 변경된 데이터를 복원합니다.

클라이언트의 요청으로 DML 쿼리가 실행되면 일차적으로 메모리 영역의 Buffer Pool 에 존재하는 데이터들이 변경됩니다.
그리고 Checkpoint 시점에 Flush 된다면 disk 의 Tablespace 의 정보들 또한 수정됩니다.
이 상황에서 Rollback 을 해야하는 경우가 생길 수 있겠죠?
Rollback Case 에서 MySQL 은 Undo log 를 활용합니다.
Rollback 한 Transaction id 를 가지는 Undo Log Entry 들을 통해 데이터 복원을 수행하죠.

즉, 요약하자면 DML 쿼리 수행시에 변경 대상이 되는 모든 데이터들은 Undo Log 에 저장된다고 생각하시면 됩니다.
Undo Log 의 대상은 테이블 데이터와 인덱스 데이터 모두 포함됩니다.
( Write Query 는 테이블의 데이터 뿐만 아니라 인덱스도 변경하니까요. )

Undo Tablespace.

Undo Tablespace 는 disk 영역에 저장되는 Undo Log 의 다른 이름입니다.
Undo Log 는 메모리와 디스크 모두에 존재합니다. (In-memory Undo Log , On-disk Undo Log)
메모리 영역의 Undo Log 는 버퍼로써 동작하게 되구요.
Transaction 로 인해서 변경되는 데이터의 범위가 크지 않은 경우에는
메모리 상에 위치하는 Undo Log 버퍼에 저장된 원본 데이터들이 사용됩니다.
하지만 Transaction 의 실행 시간이 길어지거나 Transaction 에 의해서 변경되는 데이터의 크기가 굉장히 커지는 경우에는 Undo Tablespace 영역이 사용됩니다.
즉, Flush 를 마친 경우인거죠.

In-memory 의 Undo Log Buffer 가 Flush 되면 Undo Tablespace 로 Undo Log Entry 들이 저장됩니다.
위에서 살펴보았듯이, undo_001, undo_002 와 같은 이름의 파일로 저장이 됩니다.

 
Undo Tablespace 가 저장되는 위치를 알아보겠습니다.
 
<innodb_undo_directory>
innodb_undo_directory 시스템 변수에 Undo Tablespace 의 위치가 설정됩니다.

show variables like '%innodb_undo_directory%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | ./    |
+-----------------------+-------+
1 row in set (0.02 sec)

또는 

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

 
절대경로로 표시되는 케이스와 상대경로로 표시되는 케이스가 있습니다.
만약 ./ 와 같이 상대경로로 표시가 된다면, datadir 환경변수를 조회해야합니다.
 
<datadir>

show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

 
datadir 인 /var/lib/mysql/ 하위 영역에 Undo Tablespace 가 위치하게 됩니다.
 
< Undo Tablespace >
innodb_undo_directory 의 위치를 조회해보면 아래와 같이 2개의 파일이 확인됩니다.
파일이 2개인 이유는 기본값으로 2개의 Undo Tablespace 를 사용하기 때문입니다.
 

ls -al /var/lib/mysql | grep undo
-rw-r----- 1 mysql mysql 16777216 Oct 19 06:32 undo_001
-rw-r----- 1 mysql mysql 16777216 Oct 19 06:27 undo_002

 
Undo Tablespace 의 갯수를 변경하고자한다면 ,
innodb_undo_tablespaces 의 값이 수정되어야 합니다.
 
<innodb_undo_tablespaces>

show variables like '%innodb_undo_tablespaces%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2     |
+-------------------------+-------+
1 row in set (0.03 sec)

 
 

Transaction Isolation.

Undo Log 는 Repeatable Read Isolation 에서 사용됩니다.
Repeatable Read Isolation 은 각 데이터에서 발생하는 팬텀 읽기와 같은 문제를 해결하기 위해서 사용됩니다.
팬덤 리드란 Read Committed Isolation Level 에서 발생하는 이슈로,
현재 Transaction 에서 변경한 적이 없는 데이터가 조회되는 현상입니다.
 
Undo Log 는 Transaction 마다 Undo Log 영역을 생성하며,
각 Transaction 은 Transaction 시작 시점의 데이터를 유지합니다.
그래서 다른 Transaction 에서 Commit 된 결과가 현재 Transaction 에서 조회되지 않습니다.

 

Read-Committed Isolation.

 
Read-Committed Isolation Level 은 Undo Log 를 사용하지 않습니다.
따라서 아무리 많은 Transaction 이 발생하더라도 Undo Log 가 사용되지 않죠.
아래는 하나의 실험입니다.
Read-Committed Isolation Level 2백만개 가량의 데이터를 추가하였을 때,
Undo Log 의 변화를 살펴봅니다.
 
< Read-Committed Isolation Level >

test_table 테이블에 2백만개의 데이터가 생성되었습니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)

show variables like '%ISOLATION%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.05 sec)


SELECT count(*) from test_table;
+----------+
| count(*) |
+----------+
|  2446200 |
+----------+
1 row in set (18.98 sec)

 
<Undo Tablespace>
Undo Tablespace 의 상태튼 16MB 로 초기 상태와 비슷합니다.

 

Repeatable-Read Isolation.

결론부터 말씀드리면,
아래와 같이 Undo Log 는 두드러진 변화를 보입니다.

 
 
< REPEATABLE READ ISOLATION LEVEL >

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%ISOLATION%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

SELECT count(*) from test_table;
+----------+
| count(*) |
+----------+
|  2430000 |
+----------+
1 row in set (14.26 sec)

 
 
 

정리하며...

Undo Log 는 Transaction 을 위한 데이터 영역입니다.
Transaction 의 실패 또는 Rollback 을 위해서 원본 데이터를 저장하는 영역이죠.
Transaction Isolation Level 중에서 Repeatable Read 를 위해서 주로 사용됩니다.
Transaction 마다 고유의 Undo Log 영역이 보장되며,
각 Transaction 을 자신의 데이터 스냅샷을 유지할 수 있습니다.