[MySQL] 긴 트랜잭션이 Undo와 Purge를 지연시키는 과정 측정하기

728x90

데이터베이스를 다루다 보면 어느 날 갑자기 데이터 디렉터리의 undo 테이블스페이스 파일이 눈에 띄게 커져 있는 것을 발견할 때가 있습니다. 테이블 데이터는 그대로인데 디스크 사용량만 계속 늘어난다면, 대부분의 경우 범인은 어딘가에서 며칠째 커밋되지 않고 열려 있는 트랜잭션입니다. 이때 흔히 놓치는 사실은, 문제를 일으키는 트랜잭션이 반드시 대량의 쓰기를 하는 트랜잭션일 필요가 없다는 점입니다. 단순히 SELECT 한 번을 실행한 뒤 커밋도 롤백도 하지 않은 세션 하나가, 전혀 다른 테이블에서 다른 트랜잭션들이 계속 만들어내는 undo 레코드의 회수(Purge)를 통째로 막아버릴 수 있습니다. 이 글에서는 InnoDB의 Undo Log와 Purge가 어떻게 맞물려 동작하는지 기본 원리부터 짚고, History List Length(HLL)라는 지표가 무엇을 의미하는지, 그리고 실제로 긴 트랜잭션을 만들어 HLL과 Undo Tablespace 크기가 어떻게 변하는지를 information_schema를 이용해 직접 측정하는 절차까지 순서대로 살펴보겠습니다.

1. Undo Log와 MVCC: Purge가 필요한 이유

InnoDB는 잠금 없는 읽기 일관성을 제공하기 위해 다중 버전 동시성 제어(MVCC, Multi-Version Concurrency Control)를 사용합니다. 어떤 행이 UPDATE나 DELETE로 변경되면, InnoDB는 변경 전 상태를 복원할 수 있는 정보를 Undo Log에 기록합니다. 이 Undo 레코드는 두 가지 목적으로 사용됩니다. 하나는 트랜잭션이 롤백될 때 원래 값으로 되돌리기 위함이고, 다른 하나는 다른 트랜잭션이 그 행의 이전 버전(스냅샷)을 읽어야 할 때 제공하기 위함입니다.

따라서 어떤 트랜잭션이 특정 시점에 시작한 읽기 뷰(Read View)를 가지고 있다면, 그 시점 이후에 발생한 변경 사항에 대한 Undo 레코드는 아직 누군가에게 필요할 수 있으므로 즉시 지울 수 없습니다. 모든 활성 트랜잭션의 읽기 뷰보다 오래된 Undo 레코드만이 더 이상 필요 없다고 판단되어 백그라운드의 Purge 스레드에 의해 물리적으로 회수됩니다. 이 회수 대상 Undo 레코드의 대기열 길이를 나타내는 지표가 바로 History List Length입니다.

가장 중요한 것은, Purge를 막는 것이 반드시 undo를 생성한 그 트랜잭션일 필요는 없다는 점입니다. undo와 전혀 무관한 테이블만 조회하고 있는 오래된 SELECT 트랜잭션이라도, 그보다 나중에 만들어진 모든 undo 레코드의 회수를 지연시킵니다. 이는 초보 개발자뿐 아니라 숙련된 개발자도 자주 놓치는 함정입니다.

2. History List Length 확인 방법

History List Length는 두 가지 방법으로 확인할 수 있습니다.

SHOW ENGINE INNODB STATUS 결과의 TRANSACTIONS 섹션에서 History list length 값을 직접 눈으로 읽는 방법
information_schema.INNODB_METRICS에서 trx_rseg_history_len 카운터를 조회해 값을 프로그램적으로 수집하는 방법

SELECT NAME, COUNT, STATUS
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

이 카운터는 MySQL 8.4 기준으로 기본 활성화(enabled) 상태이므로 별도의 SET GLOBAL innodb_monitor_enable 설정 없이도 바로 조회할 수 있습니다. 실무에서는 이 값을 모니터링 시스템(Zabbix, Prometheus mysqld_exporter 등)에 주기적으로 수집하고, 절대값 자체보다 값이 계속 우상향하는지 여부를 알람 조건으로 두는 것이 더 안전합니다. 워크로드에 따라 정상적으로도 수만 단위까지 오르내릴 수 있기 때문에, 절대 임계치 하나만으로 판단하면 오탐이 잦아집니다.

3. Undo Tablespace 용량 확인 방법

History List Length가 늘어나는 것과 별개로, 실제 디스크 용량이 어떻게 소비되는지도 함께 봐야 합니다. MySQL 8.0부터는 기본적으로 undo 로그가 시스템 테이블스페이스가 아닌 별도의 undo 테이블스페이스 파일(기본 undo_001, undo_002)에 저장되며, 아래 쿼리로 파일 크기를 조회할 수 있습니다.

SELECT TABLESPACE_NAME, FILE_NAME, ROUND(FILE_SIZE/1024/1024, 2) AS size_mb
FROM information_schema.FILES
WHERE TABLESPACE_NAME LIKE 'innodb_undo%';

주의해야 할 점은, Purge가 진행되어 History List Length가 줄어들더라도 undo 테이블스페이스 파일 자체의 크기는 자동으로 줄어들지 않는다는 것입니다. 파일 크기를 실제로 줄이려면 innodb_undo_log_truncate가 켜져 있어야 하고(8.0 기본값은 ON), 해당 undo 테이블스페이스가 비활성화(inactive) 상태로 전환된 뒤 트렁케이트되는 별도의 백그라운드 과정을 거쳐야 합니다. 즉 HLL 감소와 파일 크기 감소 사이에는 시간차가 있다는 점을 기억해야 합니다.

4. 실측: 긴 트랜잭션이 HLL과 Undo 공간에 미치는 영향

이제 직접 재현해 보겠습니다. 아래는 스크래치 데이터베이스에서 그대로 실행할 수 있는 준비 절차입니다. 10만 건의 행을 가진 테이블을 만들고, 같은 규모의 UPDATE를 서로 다른 조건(자동 커밋, 커밋 전 대기, 커밋 직후, 커밋 후 일정 시간 경과)에서 실행하며 매번 History List Length와 Undo Tablespace 크기를 함께 측정합니다.

SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE undo_demo (
  id BIGINT PRIMARY KEY,
  val BIGINT NOT NULL,
  filler CHAR(200)
) ENGINE=InnoDB;
INSERT INTO undo_demo
SELECT n, n, REPEAT('a', 200)
FROM (
  WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
  )
  SELECT n FROM seq
) s;

측정에 사용하는 쿼리는 History List Length와 Undo Tablespace 크기(MB)를 한 번에 조회합니다.

SELECT
  (SELECT COUNT FROM information_schema.INNODB_METRICS
   WHERE NAME = 'trx_rseg_history_len') AS history_list_length,
  (SELECT ROUND(SUM(FILE_SIZE)/1024/1024, 2) FROM information_schema.FILES
   WHERE TABLESPACE_NAME LIKE 'innodb_undo%') AS undo_tablespace_mb;

아래 표는 위 준비 절차를 마친 상태에서, 서로 다른 시나리오마다 이 측정 쿼리를 실행한 결과를 정리한 것입니다. 셀의 실제 수치는 로컬 MySQL 8.4 인스턴스에서 직접 실행한 결과로 채워 넣어야 합니다.

이 결과에서 확인해야 할 흐름은 다음과 같습니다. 자동 커밋 상태에서는 각 UPDATE 문 자체가 하나의 짧은 트랜잭션이므로 커밋과 동시에 Purge 대상이 되어 History List Length가 비교적 빠르게 정리됩니다. 반면 START TRANSACTION 이후 커밋하지 않은 채 대량의 UPDATE를 실행한 시점에는, 그 트랜잭션 자신이 만든 undo 레코드조차 자신의 커밋 전까지는 회수 대상에서 제외되므로 History List Length가 계속 쌓입니다. 커밋 직후에는 값이 남아 있다가, 이후 대기 시간이 늘어날수록 백그라운드 Purge 스레드가 점진적으로 처리하면서 값이 줄어드는 것을 확인할 수 있습니다. 이 감소 속도 자체가 바로 "Purge 진행 속도"이며, 워크로드와 innodb_purge_threads, innodb_purge_batch_size 설정에 따라 달라집니다.

5. 다른 세션의 열린 트랜잭션이 무관한 테이블의 Purge까지 막는 경우

앞선 실측은 트랜잭션 자신이 만든 undo가 자신의 커밋 전까지 쌓이는 상황을 보여줬습니다. 하지만 실무에서 더 위험하고 흔히 발생하는 상황은, 전혀 관계없는 테이블을 조회하던 세션이 오래 열려 있는 바람에 다른 모든 세션이 만들어내는 undo의 회수까지 막아버리는 경우입니다. 이 상황은 하나의 커넥션만으로는 재현할 수 없으므로, 두 개의 MySQL 클라이언트 세션을 열어 직접 확인해야 합니다.

세션 A에서 아무 테이블이나 조회하며 트랜잭션을 열어 둔 채 방치합니다.

-- 세션 A
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT 1;
-- 여기서 COMMIT도 ROLLBACK도 하지 않고 세션을 그대로 둡니다.

세션 B에서 앞서 만든 undo_demo 테이블에 반복적으로 UPDATE와 COMMIT을 수행합니다.
```
-- 세션 B
UPDATE undo_demo SET val = val + 1;
SELECT (SELECT COUNT FROM information_schema.INNODB_METRICS
  WHERE NAME = 'trx_rseg_history_len') AS history_list_length;
```
이 과정을 몇 초 간격으로 반복 실행하면서 history_list_length 값이 세션 A가 열려 있는 동안에는 계속 우상향하기만 하고 좀처럼 줄어들지 않는 것을 관찰합니다.
세션 A에서 COMMIT; 또는 ROLLBACK;을 실행해 트랜잭션을 종료한 뒤, 다시 세션 B에서 같은 측정 쿼리를 몇 차례 실행하며 History List Length가 비로소 감소세로 돌아서는 것을 확인합니다.

운영 중인 서버에서 이런 세션을 찾아내려면 information_schema.INNODB_TRX를 활용할 수 있습니다.

SELECT trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec,
  trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

이 쿼리 결과를 WHERE 절로 필터링해 trx_age_sec이 특정 임계치(예: 300초)를 넘는 세션만 골라내면, HLL 급증의 원인이 되는 장기 트랜잭션을 빠르게 특정할 수 있습니다. WHERE 절 자체의 기본 문법과 조건 최적화는 [MySQL] WHERE 절 완전 정복: 기본 문법부터 8.0 최적화 팁까지 글에서 자세히 다루고 있으니 참고하시기 바랍니다.

6. 흔히 하는 실수와 예방 방법

실무에서 History List Length 급증을 유발하는 흔히 하는 실수는 대체로 다음 세 가지로 정리됩니다.

애플리케이션 연결 풀에서 트랜잭션을 명시적으로 닫지 않는 경우: ORM이나 커넥션 풀 설정에서 자동 커밋이 꺼져 있는 상태로 커넥션을 반환하면, 다음 요청이 그 커넥션을 재사용하기 전까지 트랜잭션이 열린 채로 유지됩니다. 커넥션 풀의 idle timeout과 트랜잭션 타임아웃을 반드시 함께 점검해야 합니다.
장시간 실행되는 배치 작업을 하나의 트랜잭션으로 묶는 경우: 수백만 건을 처리하는 배치를 커밋 없이 한 번에 실행하면, 그 배치 자신의 undo가 계속 쌓일 뿐 아니라 다른 세션들의 undo 회수까지 함께 지연시킵니다. 일정 건수 단위로 커밋을 나누어 실행하는 것이 더 안전합니다.
백업 도구가 사용하는 장기 트랜잭션: mysqldump --single-transaction이나 일부 논리 백업 도구는 백업이 끝날 때까지 하나의 트랜잭션을 유지합니다. 대용량 데이터베이스에서 백업이 오래 걸릴수록 그 시간 동안 Purge가 지연된다는 점을 감안해 백업 시간대와 배치 시간대를 겹치지 않게 조정해야 합니다.

SELECT * 처럼 필요 이상으로 넓은 결과 집합을 가져오는 습관도 간접적으로 영향을 줍니다. 조회 범위가 넓을수록 트랜잭션이 실제로 실행되는 시간이 길어지고, 그만큼 읽기 뷰가 오래 유지되는 시간도 늘어나기 때문입니다. 이와 관련된 실행 계획 분석은 [MySQL] 습관적으로 쓴 SELECT * 가 당신의 DB를 느리게 만드는 이유 (feat. 실행 계획 분석) 글에서 다루었으니 함께 참고하면 좋습니다.

7. 버전별 동작 차이와 모니터링 팁

MySQL 5.6까지는 undo 로그가 시스템 테이블스페이스 안에 저장되어, 한 번 늘어난 파일 크기가 사실상 줄어들지 않는 문제가 있었습니다. 5.7부터 별도 undo 테이블스페이스 분리와 온라인 트렁케이트 기능이 도입되었고, 8.0에서는 기본적으로 undo 테이블스페이스가 시스템 테이블스페이스와 분리되어 있으며 innodb_undo_log_truncate도 기본 활성화되어 있습니다. 다만 이 자동 트렁케이트는 어디까지나 Purge가 실제로 끝나고 해당 undo 테이블스페이스가 비활성 상태로 전환된 뒤에만 동작하므로, 긴 트랜잭션이 지속되는 동안에는 8.0에서도 파일 크기가 그대로 유지되거나 계속 늘어난다는 점은 동일합니다.

운영 환경에서 실행 계획 자체는 정상인데 특정 쿼리의 actual rows와 추정치 차이가 크게 벌어지거나 응답 시간이 들쭉날쭉하다면, 그 원인이 통계 정보 문제가 아니라 History List Length 급증으로 인한 undo 페이지 스캔 비용 증가일 수도 있습니다. 실행 계획을 해석하는 구체적인 방법은 [MySQL] EXPLAIN ANALYZE 해석법: 추정 행 수와 actual rows 오차 찾기 글에서 다루고 있으니, HLL이 비정상적으로 높은 시점에 함께 확인해 보는 것을 권장합니다.

정리해 드립니다

긴 트랜잭션은 그 자체로 잠금을 오래 붙잡는 문제뿐 아니라, InnoDB의 MVCC 구조상 자신뿐 아니라 다른 모든 세션이 만들어내는 undo 레코드의 회수까지 지연시킨다는 점에서 훨씬 넓은 범위에 영향을 줍니다. History List Length와 Undo Tablespace 크기를 information_schema.INNODB_METRICS와 information_schema.FILES로 주기적으로 수집하고, information_schema.INNODB_TRX로 오래 열린 트랜잭션을 조기에 찾아내는 모니터링 체계를 갖추는 것이 근본적인 예방책입니다. 특히 애플리케이션 커넥션 풀 설정과 배치 작업의 커밋 주기는 반드시 별도로 점검해야 할 항목입니다.