트랜잭션 로그 / undo 비대화

증상

트랜잭션 로그 파일이나 undo/redo 영역이 빠르게 커진다. 'log full', 'ORA-01555 snapshot too old', 'unable to extend undo'.

1. 로그를 키우는 열린 트랜잭션을 찾는다

가장 오래된 활성 트랜잭션이 끝날 때까지 log/undo를 정리할 수 없다. 하나의 장시간 열린 트랜잭션이 로그 전체를 붙잡고 있을 수 있다.

Oracle

SELECT s.sid, s.username, t.start_time, t.used_ublk FROM v$transaction t JOIN v$session s ON t.ses_addr=s.saddr ORDER BY t.start_time;

PostgreSQL

SELECT pid, state, xact_start, now()-xact_start age, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

MySQL

SELECT trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started;

SQL Server

DBCC OPENTRAN;

2. 엔진별 근본 원인을 이해한다

MSSQL: FULL 복구 모델에서 로그 백업을 하지 않으면 로그가 무한히 커진다. Oracle: ORA-01555 = 부족한 undo retention/크기 + 긴 쿼리. PG: replication slot이 진행하지 않으면 WAL이 쌓이고, 긴 트랜잭션은 VACUUM을 막는다.

3. MSSQL 로그에 대한 올바른 조치

로그 백업(BACKUP LOG)을 수행한다 — 이렇게 하면 로그를 재사용할 수 있다. 복구 모델이 정말 SIMPLE이어야 한다면 변경한다. 그 후 필요하면(필요할 때만) 로그를 shrink한다.

SQL Server

BACKUP LOG [DB] TO DISK='C:\backup\DB_log.trn';
-- Then, if needed: DBCC SHRINKFILE (N'DB_log', 512);

4. PG WAL / replication slot 확인

진행하지 않는(inactive) replication slot은 WAL 누적을 일으킨다. 삭제하기 전에 slot의 소유자(slot_type, database, plugin)를 확인한다: 일시적으로 연결이 끊긴 standby이거나 논리 subscriber/CDC(예: Debezium)일 수 있다. slot을 삭제하면 연결된 standby는 재구축해야 하고 subscriber/CDC 측은 재동기화해야 한다. 정말 사용하지 않을 때만 삭제한다. 지속적인 보호를 위해 max_slot_wal_keep_size(PG13+)로 상한을 설정한다.

PostgreSQL

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) retained FROM pg_replication_slots ORDER BY 3 DESC;
-- If unneeded: SELECT pg_drop_replication_slot('slot_name');

5. undo/retention을 조정하고 재발을 방지한다

Oracle: UNDO 테이블스페이스를 늘리고, 긴 쿼리에 맞춰 undo_retention을 설정한다(또는 RETENTION GUARANTEE). 애플리케이션에서 장시간 열린 트랜잭션을 방지한다. 로그 백업 작업이 실행되는지 모니터링한다.