이번 시간에는 InnoDB의 undo log가 어떻게 쓰이는지를 SAVEPOINT로 일부만 되돌리는 과정, 버전 체인이 길어질 때의 비용, 그리고 긴 트랜잭션 때문에 undo 테이블스페이스(undo 기록을 담는 별도 파일)가 커지는 상황까지 직접 확인하며 배워가는 시간입니다.
예를 들어서 계좌 잔액을 100에서 90으로 바꾸는 UPDATE를 실행한 뒤 ROLLBACK한다고 해볼게요. 그러면 잔액은 다시 100이 돼요.
그런데 테이블의 행은 이미 90으로 바뀌었는데, 100이라는 값은 어디서 가져왔을까요?
InnoDB는 행을 바꾸기 전에 원래 값을 undo log에 남겨 둬요. ROLLBACK은 이 기록으로 행을 되돌리고, 다른 트랜잭션이 과거 시점의 데이터를 읽을 때도 이 기록을 씁니다.
Binlog, redo log와 함께 본 undo log의 기본 동작(ROLLBACK, REPEATABLE READ에서 과거 값 읽기, 크래시 뒤 롤백)이 더 궁금하면 세 로그를 비교한 글을 참고하세요.
실습 준비
이 글의 명령은 모두 실습 코드의 mysql-undo-log/ 디렉터리에서 실행합니다.
compose.yaml: MySQL 8.4 컨테이너setup.sql: 계좌 테이블, 카운터 테이블, 카운터 프로시저 생성logs.sql: 10만 행짜리bank.logs생성
Docker로 MySQL 8.4를 띄울게요. Error log를 자세히 남기는 --log-error-verbosity=3은 이 글에서는 쓰지 않아요. compose.yaml은 이렇습니다.
services:
mysql:
image: mysql:8.4
command: --log-error-verbosity=3
environment:
MYSQL_ROOT_PASSWORD: root
ports:
- "3421:3306"
setup.sql은 계좌 테이블 bank.accounts와 카운터 테이블 bank.counter, 그리고 카운터를 원하는 횟수만큼 1씩 올리는 프로시저 bank.bump를 만들어요. 프로시저 안의 UPDATE는 한 번 실행될 때마다 각각 커밋됩니다.
이제 컨테이너를 띄우고 SQL을 실행해 주세요. 자주 쓰는 명령은 셸 함수로 만들어 둘게요. MYSQL_PWD 환경 변수로 비밀번호를 넘기는 방식은 실습에서만 쓰셔야 해요.
아래 블록은 파일로 만들지 말고 실습 디렉터리에서 연 터미널에 그대로 붙여 넣어 주세요. 이후 명령은 모두 이 터미널에서 실행하고, 터미널을 새로 열면 다시 붙여 넣으시면 됩니다.
docker compose up -d
sql() { docker compose exec -T -e MYSQL_PWD=root mysql mysql -uroot "$@"; }
hll() { sql -e "SHOW ENGINE INNODB STATUS\G" | grep "History list length"; }
until sql -e "SELECT 1" > /dev/null 2>&1; do sleep 2; done
sql < setup.sql
여러 트랜잭션이 동시에 움직이는 모습을 보려면 세션이 두세 개 필요해요.
터미널을 따로 열어 각각 mysql 클라이언트로 접속해 주세요. 앞으로 이 세션들을 세션 A, B, C라고 부를게요.
docker compose exec mysql mysql -uroot -proot
실습 1: ROLLBACK은 undo를 거꾸로 따라갑니다
ROLLBACK하면 InnoDB는 트랜잭션이 남긴 undo 기록을 가장 최근 것부터 거꾸로 적용해요.
그런데 트랜잭션 전체가 아니라 중간 지점까지만 되돌리고 싶을 때도 있죠. 그럴 때 SAVEPOINT를 씁니다.
세션 A에서 1번 계좌를 90으로 바꾸고 저장점(SAVEPOINT)을 만든 뒤, 2번과 3번 계좌를 바꿔 볼게요.
BEGIN;
UPDATE bank.accounts SET balance = 90 WHERE id = 1;
SAVEPOINT after_withdraw;
UPDATE bank.accounts SET balance = 80 WHERE id = 2;
UPDATE bank.accounts SET balance = 70 WHERE id = 3;
SHOW ENGINE INNODB STATUS에서 이 트랜잭션의 undo 기록 수가 보여요. 터미널에서 확인해 볼게요.
sql -e "SHOW ENGINE INNODB STATUS\G" | grep "undo log entries"
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 3
행 3개를 바꿨으니 undo 기록도 3개예요. 이제 세션 A에서 저장점까지 되돌려 볼게요.
ROLLBACK TO SAVEPOINT after_withdraw;
SELECT * FROM bank.accounts;
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
| 1 | kim | 90 |
| 2 | lee | 100 |
| 3 | park | 100 |
+----+-------+---------+
3 rows in set (0.00 sec)
저장점 뒤에 바꾼 2번, 3번 계좌만 100으로 돌아왔고, 저장점 앞에서 바꾼 1번 계좌는 90 그대로예요. 터미널에서 다시 봅니다.
sql -e "SHOW ENGINE INNODB STATUS\G" | grep "undo log entries"
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
출력을 보면 undo log entries가 3에서 1로 줄었어요. 저장점 뒤에 생긴 undo 기록 두 개를 거꾸로 적용하고 버린 거예요.
그런데 여기서 눈여겨볼 게 있어요. 3 row lock(s), 즉 2번, 3번 행까지 행 잠금 세 개는 그대로 남아 있습니다.
ROLLBACK TO SAVEPOINT는 저장점 뒤에 잡은 행 잠금을 풀지 않아요. 그래서 다른 트랜잭션이 2번, 3번 행을 바꾸려면 A가 커밋하거나 전체를 롤백할 때까지 기다려야 합니다.
세션 A에서 커밋해 둡니다.
COMMIT;
실습 2: 버전 체인이 길면 과거를 읽는 쪽이 느려집니다
다른 트랜잭션이 바꾸고 있거나 이미 바꾼 행을 읽을 때, InnoDB는 읽기 뷰(read view)를 기준으로 어느 버전을 보여 줄지 정해요. 읽기 뷰는 만들어진 순간에 어떤 트랜잭션이 이미 커밋됐고 어떤 트랜잭션이 아직 진행 중인지를 기록해 둔 것이라고 보시면 됩니다.
행을 읽을 때 InnoDB는 그 행을 마지막으로 바꾼 트랜잭션 ID인 DB_TRX_ID를 읽기 뷰와 비교해요. 보이지 않는 버전이면 행에 붙은 roll_ptr(이전 버전의 undo 기록을 가리키는 포인터)를 따라 undo의 이전 버전으로 내려가고, 보이는 버전이 나올 때까지 이를 반복해요.
이렇게 스냅숏 시점의 값을 돌려주는 읽기를 일관된 읽기(consistent read)라고 합니다. undo로 행의 여러 버전을 두고 읽는 이 방식을 MVCC(다중 버전 동시성 제어)라고 불러요.
스냅숏이 오래될수록, 돌려줄 버전을 찾으려고 따라가야 하는 체인도 길어져요. 트랜잭션을 시작하는 순간을 스냅숏 시점으로 삼는 START TRANSACTION WITH CONSISTENT SNAPSHOT으로 확인해 볼게요.
세션 A에서 스냅숏을 열고 카운터를 한 번 읽어 둘게요.
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT n FROM bank.counter WHERE id = 1;
+------+
| n |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
이제 세션 B에서 같은 행을 5만 번 UPDATE합니다. 한 번 바꿀 때마다 커밋되니까 버전이 5만 개 생겨요.
CALL bank.bump(50000);
Query OK, 1 row affected (1 min 0.18 sec)
새로 접속한 세션 C에서 같은 행을 읽어 볼게요.
SELECT n FROM bank.counter WHERE id = 1;
+-------+
| n |
+-------+
| 50000 |
+-------+
1 row in set (0.00 sec)
스냅숏을 연 세션 A에서도 읽어 볼게요.
SELECT n FROM bank.counter WHERE id = 1;
+------+
| n |
+------+
| 0 |
+------+
1 row in set (0.03 sec)
C는 행에 적힌 최신 버전을 그대로 읽어요. 반면 A는 5만 개의 undo 기록을 거슬러 내려가 0을 만들어 냅니다.
행 하나에서는 0.03초 차이지만, 쿼리가 이런 행을 많이 읽을수록 차이도 쌓여요. 이때 History list length도 같이 볼게요.
hll
History list length 50007
History list length는 커밋됐지만 아직 purge되지 않은 undo log의 수예요. purge는 더 이상 아무도 보지 않는 undo 기록과 삭제된 행을 실제로 치우는 백그라운드 작업이라고 보시면 됩니다.
커밋한 트랜잭션 하나가 하나씩 남기니까, 5만 번 커밋한 만큼 늘었어요. A의 스냅숏이 이 버전들을 모두 붙잡고 있는 거죠.
A를 끝내고 잠시 기다려 볼게요.
COMMIT;
SELECT n FROM bank.counter WHERE id = 1;
+-------+
| n |
+-------+
| 50000 |
+-------+
1 row in set (0.00 sec)
hll
History list length 1
A가 스냅숏을 닫자 purge가 쌓였던 undo 기록을 정리했어요.
실습 3: 긴 트랜잭션은 undo를 붙잡습니다
실습 2에서 오래 열린 스냅숏 하나가 undo 기록 5만 개를 붙잡았어요.
예를 들어서 운영 중인 서버에서 트랜잭션 하나가 몇 시간씩 열려 있다고 해볼게요. 그러면 그동안 생긴 모든 변경의 undo가 쌓이고, undo 테이블스페이스 파일도 커집니다.
undo 테이블스페이스 크기는 INNODB_TABLESPACES에서 보시면 돼요.
undo_size() { sql -t -e "SELECT NAME, FILE_SIZE, STATE FROM information_schema.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo'"; }
undo_size
+-----------------+-----------+--------+
| NAME | FILE_SIZE | STATE |
+-----------------+-----------+--------+
| innodb_undo_001 | 33554432 | active |
| innodb_undo_002 | 33554432 | active |
+-----------------+-----------+--------+
이 서버의 undo 테이블스페이스는 innodb_undo_001, innodb_undo_002 두 개예요(아래에서는 undo_002처럼 줄여 쓸게요).
처음 크기는 16MB인데, 실습 2에서 5만 번 UPDATE하는 동안 이미 32MB(33554432바이트)로 커졌어요. 이 글을 쓰며 잴 때는 이 사이에 다른 확인도 함께 돌렸어서, 따라 하실 때 시작 크기는 조금 다를 수 있어요. purge로 undo 기록을 지워도 파일은 줄지 않습니다.
logs.sql로 10만 행짜리 테이블 bank.logs(id, memo 100자)를 만들어 주세요.
sql < logs.sql
세션 A에서 스냅숏을 열어 둡니다.
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT COUNT(*) FROM bank.logs;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
터미널에서 10만 행의 memo를 세 번 바꾸고 undo 테이블스페이스 크기를 볼게요.
for c in b c d; do sql -e "UPDATE bank.logs SET memo = REPEAT('$c', 100)"; done
undo_size
hll
+-----------------+-----------+--------+
| NAME | FILE_SIZE | STATE |
+-----------------+-----------+--------+
| innodb_undo_001 | 33554432 | active |
| innodb_undo_002 | 50331648 | active |
+-----------------+-----------+--------+
History list length 5
undo_002가 32MB에서 48MB로 커졌어요.
그런데 History list length는 5뿐이에요. 이 값은 트랜잭션 수로 세기 때문이에요. UPDATE 한 번이 10만 행을 바꿨으니, 트랜잭션 하나가 남긴 undo 기록이 많은 거죠.
그래서 undo 양을 볼 때는 History list length와 함께 파일 크기도 확인해 주세요.
정리
범위: MySQL 8.4(mysql:8.4 이미지), 기본 격리 수준 REPEATABLE READ, 세션 두세 개.
ROLLBACK TO SAVEPOINT뒤undo log entries는 3에서 1로 줄었지만, 저장점 뒤에 잡은 2번, 3번 행 잠금은 그대로 남았어요. 저장점으로 되돌려도 잠금은 커밋이나 전체 롤백까지 남으니 트랜잭션을 짧게 끝내 주세요.- 같은 행을 5만 번 커밋한 뒤 오래된 스냅숏의 읽기는 0.03초, 최신 읽기는 0.00초였고 History list length는 50007이었어요. 스냅숏을 오래 열어 두지 마세요.
- 아무것도 바꾸지 않은 스냅숏 하나가 열린 동안 UPDATE 세 번으로
undo_002가 32MB에서 48MB로 커졌고, History list length는 5였어요. undo 양은 History list length와 함께 파일 크기도 보세요. - 이 글에 싣지 않은 추가 측정에서는 긴 트랜잭션을 끊고 쓰기를 조금 일으키자 두 undo 테이블스페이스가 10MB로 낮춘
innodb_max_undo_log_size기준으로 truncate(파일을 처음 크기로 줄이는 정리)돼 16MB로 돌아왔어요. 실제 서버에서는 기본값 1GB에서 시작해 파일 크기 추이를 보고 기준을 정하세요.