---

1️⃣ 상황 요약 (Situation)

환경: InnoDB 기반 운영 DB / 수천만 Row
증상: 쿼리 대기 폭증, 세션 정체, 서비스 응답 급락
영향: 전체 트랜잭션 처리량 급락, 장애 전조 발생

DELETE FROM CQ_SHORT_INFO;

---

2️⃣ 즉시 확인 (Quick Check)

SHOW ENGINE INNODB STATUS;

-- MySQL 5.7 / MariaDB
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;

---

3️⃣ 원인 분석 (Root Cause)

DELETE는 단순 삭제가 아니다.
대규모 Undo Log + Redo I/O + Row-Level Exclusive Lock이 동시에 발생하는 부하가 많이 걸리는 작업이다.

---

4️⃣ 기본 조치 (Resolution — Standard Pattern)

대량 DELETE는 반드시 배치로 나눈다.

운영 기준

• 배치 사이즈: 1,000 ~ 10,000
• 단일 트랜잭션 대량 DELETE 금지

Replication 주의

• binlog_format = STATEMENT → DELETE ... LIMIT
  비결정적 위험
  => Master와 Slave가 서로 다른 데이터를 지울 수 있음
• ROW / MIXED 사용 권장

Foreign Key 사전 점검

• ON DELETE CASCADE 존재 시
  배치 사이즈 ≤ 1,000

---

5️⃣ 심화 전략 (Advanced Strategies)

🅐 Copy & Swap 패턴 (대량 삭제의 정석)

지우는 비용보다 새로 만드는 비용이 싸다.

절차

1. 동일 스키마 NEW_CQ_SHORT_INFO 생성
2. [필수 안전 장치 — 트랜잭션 격리 수준 변경]

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

[필독]
기본 REPEATABLE READ에서는
복사 중 원본 테이블에 S-Lock 발생 → 쓰기 차단 발생 :

위험: 기본 설정(REPEATABLE READ)에서는 복사 중 원본 테이블에 S-Lock이 걸려 모든 쓰기(INSERT/UPDATE/DELETE)가 전면 차단된다.

해결: 위 명령어로 격리 수준을 READ COMMITTED로 변경하면, Lock 없이(Non-Locking) 데이터를 읽어오기 때문에 서비스 중단 없이 안전하게 복사가 가능하다.

3. 데이터 복사

INSERT INTO NEW_CQ_SHORT_INFO
SELECT * FROM CQ_SHORT_INFO
WHERE <보존 조건>;

4. 교체 직전 점검

SHOW PROCESSLIST; -- 장기 SELECT 존재 여부 확인 (Metadata Lock 방지)

5. RENAME → DROP

효과

• Undo Log 0
• 단편화 제거
• 다운타임은 RENAME 순간뿐

---

🅑 pt-archiver — 운영 자동화 최종 병기(Percona Toolkit)

pt-archiver \
  --source DSN=... \
  --where "create_date < '2025-01-01'" \
  --limit 1000 \
  --commit-each \
  --sleep 1 \
  --purge \
  --primary-key-only \
  --check-slave-lag DSN=...

관련 글 :

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

---

6️⃣ 고급 최적화 — PK Walking Iterator

DELETE FROM CQ_SHORT_INFO
WHERE id >= v_current_id
  AND id < v_current_id + p_batch;

---

7️⃣ 재발 방지 (Prevention)

• 단일 대량 DELETE 금지
• 부분 삭제 → Batch / pt-archiver
• 대량 삭제 → Copy & Swap
• Foreign Key / Isolation Level / Metadata Lock 사전 점검

---

8️⃣ 한 줄 요약

대량 DELETE는 DB 장애의 지름길이다.
조금 지울 땐 배치로, 많이 지울 땐 새로 만들어라.

---

📎 Appendix — Stored Procedure 기반 안전한 Batch Delete 구현

부분 삭제 상황에서 가장 안정적인 배치 처리 방식이다.

DELIMITER $$

CREATE PROCEDURE purge_cq_short_info(IN p_batch INT, IN p_sleep_ms INT)
BEGIN
  DECLARE v_rows INT DEFAULT 1;

  WHILE v_rows > 0 DO
    START TRANSACTION;

    DELETE FROM CQ_SHORT_INFO
    WHERE id IS NOT NULL
    ORDER BY id
    LIMIT p_batch;

    SET v_rows = ROW_COUNT();

    COMMIT;

    IF v_rows > 0 AND p_sleep_ms > 0 THEN
      DO SLEEP(p_sleep_ms / 1000);
    END IF;
  END WHILE;
END$$

DELIMITER ;

CALL purge_cq_short_info(10000, 50);

🛠 마지막 수정일: 2025.12.30