[Deep Dive] MySQL 대용량 데이터 무중단 삭제 전략: PK Walking vs pt-archiver

운영 중인 데이터베이스, 특히 수천만 건 이상의 Row가 적재된 테이블에서 데이터를 삭제하는 행위는 INSERT보다 훨씬 높은 시스템 비용을 요구한다. 단일 트랜잭션으로 대량의 DELETE를 실행할 경우, Row-Level Lock 경합과 Replication Lag(복제 지연), 그리고 Buffer Pool 효율 저하로 인한 서비스 성능 저하가 발생할 가능성이 매우 높다.

이전 트러블슈팅 로그에서 장애 대응 관점의 해결책을 다뤘다면, 본 글에서는 이러한 문제를 아키텍처 레벨에서 제어하고 시스템 부하를 최소화하는 두 가지 삭제 전략, PK Walking Iterator와 pt-archiver를 심층 분석한다.

관련 글 :

1. RDBMS에서 대량 삭제가 위험한 이유

InnoDB 엔진에서 삭제는 단순히 데이터를 지우는 행위가 아니다. 시스템 내부에서는 다음과 같은 고비용 작업이 동반된다.

1. Undo Log 폭증: 트랜잭션 롤백과 MVCC(Multi-Version Concurrency Control) 보장을 위해 삭제 전 데이터를 모두 Undo Log에 기록한다. 이는 막대한 Disk I/O를 유발한다.
2. Lock 경합 증가: 삭제 대상 Row에 배타적 잠금(Exclusive Lock)을 건다. 일반적인 스냅샷 읽기(Consistent Read)는 영향받지 않지만, SELECT ... FOR UPDATE, 외래키 검사, DDL 등이 얽히면 연쇄적인 대기(Lock Wait)가 발생할 수 있다.
3. Buffer Pool 효율 저하: 삭제할 데이터를 찾기 위해 디스크의 데이터 페이지를 메모리(Buffer Pool)로 올리고 변경(Dirty Page)한다. 이 과정에서 정작 서비스에 필요한 ‘자주 조회되는 데이터(Working Set)’가 메모리에서 밀려나는 현상이 발생한다. 이는 Flush Pressure(플러시 압력) 증가와 Buffer Pool Hit Ratio(적중률) 저하로 직결된다.

따라서 대용량 삭제는 **”한 번에”**가 아니라, “잘게 쪼개서(Batch)”, “시스템 부하를 체크하며(Throttling)” 수행해야 한다.

2. 안티 패턴: LIMIT & OFFSET

가장 흔하게 범하는 실수는 LIMIT와 OFFSET을 사용하는 방식이다.

-- 절대 피해야 할 패턴: 뒤로 갈수록 느려짐
DELETE FROM target_table WHERE created_at < '2025-01-01' LIMIT 1000 OFFSET 100000;

초반에는 빠르지만, OFFSET이 커질수록 성능은 급격히 저하된다. 데이터베이스는 100,000번째 데이터를 찾기 위해 앞의 100,000건을 모두 읽고 버리는 스캔 작업을 수행해야 한다. 대용량 테이블에서 이 방식은 시스템 리소스를 낭비하는 주범이다.

3. 전략 A: PK Walking Iterator (Stored Procedure)

OFFSET의 비효율을 제거하기 위해 인덱스(PK)를 타고 범위를 명확히 좁혀가는 PK Walking 기법이다. 핵심은 OFFSET 없이 **”직전에 처리한 마지막 ID”**를 기준으로 다음 범위를 탐색하는 것이다.

[Code] 안전한 PK Walking 구현 (권장)

이 방식은 외부 도구 설치가 제한적이거나, 복잡한 비즈니스 로직이 포함된 삭제가 필요할 때 가장 강력한 대안이다.

⚠️ 인덱스 성능 주의: 이 로직의 성능은 (id, created_at) 또는 (created_at, id) 복합 인덱스의 유무와 실행 계획(EXPLAIN)에 따라 크게 달라진다. 최소한 created_at 단독 인덱스는 필수이며, 대량 삭제 전 반드시 EXPLAIN으로 인덱스 사용 여부를 확인해야 한다.

아래 예시 템플릿은 로직(알고리즘)은 그대로 따르되, 변수명은 본인 상황에 맞게 변경

DELIMITER $$

CREATE PROCEDURE safe_purge_data(IN p_batch_size INT)
BEGIN
  -- 초기값을 -1로 설정하여 0 또는 양수 ID 모두 커버
  DECLARE v_current_id BIGINT DEFAULT -1;
  DECLARE v_end_id BIGINT;
  DECLARE v_affected_rows INT DEFAULT 1;
  
  -- 루프 시작
  WHILE v_affected_rows > 0 DO
    
    -- 1. 이번 배치에서 삭제할 대상의 '마지막 ID'를 구한다. (서브쿼리 최적화)
    -- OFFSET을 쓰지 않고, v_current_id보다 큰 ID 중 p_batch_size만큼만 가져와서 MAX를 구함
    SELECT MAX(id) INTO v_end_id
    FROM (
      SELECT id
      FROM target_table
      WHERE id > v_current_id
        AND created_at < '2025-01-01'
      ORDER BY id ASC
      LIMIT p_batch_size
    ) AS sub;
    
    -- 삭제할 대상이 더 이상 없으면 루프 종료
    IF v_end_id IS NULL THEN
      SET v_affected_rows = 0;
    ELSE
      START TRANSACTION;

      -- 2. 범위 기반 삭제 (Range Delete)
      DELETE FROM target_table
      WHERE id > v_current_id 
        AND id <= v_end_id
        AND created_at < '2025-01-01';

      SET v_affected_rows = ROW_COUNT();
      
      COMMIT;

      -- 3. 포인터 이동
      SET v_current_id = v_end_id;
      
      -- 4. 시스템 부하 분산을 위한 강제 휴식 (Throttling)
      -- 실제 삭제가 일어났을 때만 휴식 (불필요한 대기 방지)
      IF v_affected_rows > 0 THEN
        DO SLEEP(0.1); 
      END IF;
    END IF;
    
  END WHILE;
END$$

DELIMITER ;

[Tip] 멱등성 (Idempotency) 이 프로시저는 중간에 실패하여 재실행하더라도, 이미 삭제된 데이터는 조건(WHERE id > v_current_id)에 의해 자연스럽게 건너뛰어지므로 안전하게 동일한 결과를 얻을 수 있다. (단, Replication 환경에서는 binlog_format이 ROW 또는 MIXED여야 데이터 불일치 위험이 없다.)

4. 전략 B: 운영 자동화의 표준, pt-archiver

DBA와 SRE 엔지니어들이 가장 신뢰하는 도구, Percona Toolkit의 pt-archiver이다. 이 도구의 핵심 가치는 **”서비스 영향 통제(Impact Control)”**에 있다.

핵심 기능: Replication Lag 감지

자체 구현한 스크립트와 가장 큰 차이점은 복제 지연 모니터링이다. 대량 삭제 시 Master DB는 괜찮아도 Slave DB에서 지연(Lag)이 발생하여 데이터 불일치나 조회 지연이 발생할 수 있다. pt-archiver는 이를 실시간으로 감지하여 스스로 멈춘다.

실전 운영 명령어

단순 데이터 삭제(--purge)를 위한 최적화된 명령어 셋이다.

pt-archiver \
  --source h=127.0.0.1,u=admin,p=pass,D=mydb,t=target_table \
  --where "created_at < '2025-01-01'" \
  --limit 1000 \
  --txn-size 1000 \
  --check-slave-lag h=192.168.0.50 \
  --max-lag 1 \
  --sleep 1 \
  --purge \
  --primary-key-only

옵션 상세 분석

• --primary-key-only: 가장 효율적인 PK 인덱스만 타고 데이터를 스캔한다. (단, WHERE 조건절의 인덱스가 PK보다 훨씬 효율적인 경우 제외 가능)
• --check-slave-lag h=... --max-lag 1: 지정된 Slave DB의 지연 시간이 1초를 넘어가면 작업을 즉시 일시 중단하고, 지연이 해소될 때까지 대기한다. 운영 안정성을 보장하는 핵심 옵션이다.
  • (참고: h=... 부분에는 환경에 따라 u=user,p=pass 등 접속 정보가 추가로 필요할 수 있다.)
• --limit / --txn-size: 한 번에 가져올 Row 수와 트랜잭션 크기를 지정한다. 보통 동일하게 설정하여 하나의 배치 트랜잭션으로 처리한다.
• --sleep 1: 배치 작업 사이마다 1초간 강제로 쉰다. CPU 자원을 서비스 트래픽에 양보한다.
• pt-archiver 옵션은 Percona Toolkit 버전에 따라 다를 수 있으므로, 실행 전 pt-archiver --help로 옵션 존재 여부를 확인한다.

5. 결론: 전략의 선택

상황에 따라 적절한 도구를 선택해야 한다.

1. pt-archiver 권장:
   • 쉘(Shell) 접근이 가능한 대규모 운영 환경.
   • Replication Lag에 민감한 서비스 (가장 안전).
   • 단순 기간 만료 데이터 삭제 등 정형화된 작업.
2. PK Walking 권장:
   • 외부 도구 설치가 불가능한 환경 (예: 완전 관리형 PaaS의 제약).
   • 삭제 로직에 복잡한 비즈니스 요건이 포함된 경우.
3. Copy & Swap 권장 (데이터 50% 이상 삭제 시):
   • DELETE보다 **새 테이블을 만들어 필요한 데이터만 옮기고(INSERT SELECT), 테이블을 교체(RENAME)**하는 것이 훨씬 빠르다.
   • [안전장치 필수]
     • INSERT SELECT 실행 시 격리 수준을 READ COMMITTED로 설정하여 원본 테이블의 쓰기 차단(Locking) 위험을 낮춰야 한다.
     • RENAME 실행 전 SHOW PROCESSLIST를 통해 장기 실행 중인 트랜잭션이 없는지 확인해야 한다(Metadata Lock 대기 방지). 가급적 트래픽이 적은 유지보수 시간대에 수행하는 것을 권장한다.

데이터의 생명주기(DLM)를 관리하는 것은 데이터를 적재하는 것만큼 중요하다. 무계획적인 DELETE 실행은 장애의 지름길이다. 위 전략들을 통해 시스템 부하를 제어 가능한 범위 내에 두는 것이 엔지니어링의 핵심이다.

🛠 마지막 수정일: 2025.12.30