[DBA][MariaDB] 대용량 데이터 삭제 - 실패 경험과 작업 요령

Intro

안녕하세요. 초보 DBA 다뉴입니다.

오늘도 여러가지 업무를 진행하며, 뻘짓을 해가며 배운 것들에 대해 한번 기록을 남겨볼까합니다.

 

작업 내용은 간단합니다.

특정 테이블의 데이터 중 특정 날짜 이전의 데이터를 모두 삭제

 

얼마 전에도 같은 업무를 진행 했었는데, 잘 알지 못하고 무지성으로 작업했다가

MariaDB가 다운되는 초유의 사태를 겪고 시말서까지 썼었어서, 벌벌 떨며 작업하게 됐네요.

---

대용량 데이터 삭제 

첫 번째 작업 시도 - 실패의 경험

대용량 데이터를 삭제하기 위해 첫 번째로 했던 작업은 그냥 무지성 DELETE 였습니다.

네 이게 바로 저를 시말서 쓰게 만든 작업 방법이였는데요.

 

21억 row의 데이터가 들어있는 Table에 특정 날짜 이전의 row를 전부 삭제

 

그냥 아무 의심없이 바로 DELETE 문을 실행했습니다.

delete from [Table_A]
 where ins_dtm < '2023-01-01';

예시를 들자면,

테이블 [Table_A]에 ins_dtm (입력 일시)가 '2023-01-01' 보다 작은 모든 데이터 삭제

라는 쿼리를 던지게 되었는데요. 참고로 PK만 있고 ins_dtm에는 Index 조차 없었습니다.

 

쿼리를 던지고 나중에 대략적으로 다른 데이터와 비교해서 삭제 시간을 유추해 본 결과

삭제에 100시간 정도 걸릴 것으로 예상이 되었는데요...

 

21억의 데이터를 Full Table Scan을 해야 하니까 정말 오래걸리네요.

이때 이 DML을 Kill 했어야 하는데... 정말 후회되네요.

 

그렇게 대략 70~80시간 후 Undo Log가 너무나 많이 쌓이며 더이상 버티지 못하고 서버가 터지게 되었습니다...

 

이걸 복구하는데, 저희 팀장님이 30시간 넘게 고생하신걸 생각하니... 너무나 죄송하네요.

---

두 번째 작업 시도 - 이건 아닌거 같은데?

비슷한 작업을 또 하게 되었는데요.

요번에는 저번 처럼 무지성 DELETE 는 당연히 하지 않았습니다.

 

분할해서 DELETE 하는 방법을 채택했는데요.

 

요번 Table 은 대략 9억 row의 데이터를 가지고 있으며,

삭제 해야 할 건수를 보니 대략 8.7억 row의 데이터를 삭제 해야 했습니다.

 

요번 Table의 날짜 컬럼에도 Index는 없었습니다. 또 Full Table Scan을 해야하는 상황인데요.

 

10000건 씩 나눠서 DELETE를 하기위해 Procedure를 생성하였습니다.

10000건 씩 나누어 DELETE함으로써 Undo Log가 무지막지하게 쌓이는걸 방지할 목적이였습니다.

CREATE PROCEDURE sp_delete(
  in pi_s_dt date,
  in pi_e_dt date
)
BEGIN
  declare l_max_loop int;
  declare i int default 0;
  declare l_m_dt date;

  set l_m_dt = pi_s_dt;

  while (l_m_dt <= pi_e_dt) do
    set i = 0;
  
    select count(*) / 10000
      into l_max_loop
      from [Table_A]
     where ins_dtm >= l_m_dt 
       and ins_dtm <  l_m_dt + 1;
     
    while (i <= l_max_loop) do
    
      delete from [Table_A]
       where ins_dtm >= l_m_dt
         and ins_dtm <  l_m_dt + 1
       limit 10000;
     
      insert into TB_DEL_HST (TB_NM, DEL_DT, DEL_NO, INS_DTM)
      values ('[Table_A]', l_m_dt, i, current_timestamp);
    
      set i = i + 1;

    end while;
    
    set l_m_dt = l_m_dt + 1;
  
  end while; 
  
  END

입력한 시작 날짜 (pi_s_dt) / 종료 날짜 (pi_e_dt)에 대해서 하루 단위에 10000건 씩 삭제하는 쿼리를 작성해봤는데요.

 

TB_DEL_HST 테이블을 만들어, 데이터 삭제 Loop 작업의 Log를 확인하며, 작업을 하였습니다.

인덱스가 없으니... 역시나 엄청 오래걸립니다.

DELETE 문에 limit 10000로 10000건 단위의 제한을 걸었지만, 조회 자체는 Full Table Scan을 하니까요.

 

대략 1시간치의 데이터를 삭제하는데 1시간이 걸리니... 이대로라면 몇달동안 삭제를 해야했습니다.

그리고 실시간으로 쌓이고 있기도 하구요.

 

역시나 이 방법도 답이 아닌 것 같습니다. 

 

조회하는 컬럼에 Index가 없다지만, 매번 삭제 할 때 마다 Full Table Scan이라니...

다른 방법을 고민해보게 되었습니다.

---

세 번째 작업 시도 - PK를 활용해보자

9억 row의 데이터 중 8.7억 row의 데이터를 삭제

 

과연... 어떻게 하면 빨리 할 수 있을까.

위에 두 번째 시도했던 방법이 답이 아니란건 바로 알 수 있었는데요.

 

그나마 다행인건 작업할 Table에 Auto_Increment로 PK가 생성되어 있었습니다.

Full Table Scan은 절대 하면 안되겠다고 다시한번 생각하며 PK를 활용해봅니다.

---

CTAS(Create Table As Select)를 활용하여, 

날짜 조건이 맞는 PK 값을 담은 테이블 만들어줍니다.

create table XTMP_PK_NO as
select pk_no 
  from [Table_A];

CTAS로 작업하는 이유는 CTAS는 DML이 아닌 DDL인데요.

 

Undo Log로 한번 피본 적이 있기 때문에...

 

Create Table -> Insert Table 작업 시 쌓이게 될 Undo Log가 무서웠습니다.

Undo Log가 쌓이지 않는 DDL로 데이터를 생성하였습니다.

 

PK를 담은 테이블의 생성시간 만 1시간이 넘게 걸렸고, 테이블의 용량 22GB 정도로 생성되었습니다.

---

이렇게 생성한 테이블을 DELETE문에 PK 조회에 사용할 계획인데요.

delete a
  from [Table_A] a
 inner join XTMP_PK_NO b 
    on a.pk_no = b.pk_no 
 where a.ins_dtm < '2023-01-01'
 limit 10000;

이렇게 [Table_A] 에 10000건 씩 삭제를 하려고 했는데요.

PK를 통해 데이터를 조회하고, 조회한 데이터의 ins_dtm를 한번 더 확인 후 10000건 삭제 할 계획이였습니다.

이러면 Index(PK)를 활용 할 수 있으니까요.

 

근데 limit 절 부분에서 생각한 것 처럼 활용이 되지 않았는데요.

위 쿼리의 문법은 틀렸고, 아래와 같은 문법으로 사용해야 한다고 합니다.

delete a
  from [Table_A] a
 inner join (select pk_no 
               from XTMP_PK_NO 
              limit 10000)b 
    on a.pk_no = b.pk_no 
 where a.ins_dtm < '2023-01-01';

limit를 이렇게 사용하면 한가지 문제가 발생합니다.

 

[Table_A]에서만 10000건이 삭제되고, XTMP_PK_NO에서는 삭제가 되지않기 때문에

다음 Loop에서도 XTMP_PK_NO에서 또 똑같은 10000건을 가지고와 Join을 할텐데요.

그렇다면 Join 결과값이 Null이 될 것입니다. 삭제한 값과 삭제안한 값을 Join했을테니까요.

 

결국 XTMP_PK_NO에서도 같은 데이터를 삭제 하던가, 페이징 처리를 하던가 해야한다고 생각이 들었습니다.

저는 Offset을 활용한 페이징보다는 XTMP_PK_NO에서도 데이터를 함께 삭제하는게

더욱 확실 할 것 같다고 생각이 들었습니다. 속도에도 영향을 줄 것 같았구요.

 

XTMP_PK_NO 테이블에 대해 추가 작업을 위해,

원래는 그냥 CTAS로 테이블 생성 및 데이터 입력만했는데 PK를 생성할 필요성이 느껴졌습니다.

alter table XTMP_PK_NO add constraint primary key(pk_no);

어차피 XTMP_PK_NO 테이블의 데이터 전체를 다 쓸거라는 생각에 PK를 만들지 않았는데, 결국 이렇게 만드네요.

 

그럼 Loop에 사용 될, DELETE 쿼리를 한번 다시 수정해봅니다.

delete a
  from [Table_A] a
 inner join (select pk_no 
               from XTMP_PK_NO 
              order by pk_no 
              limit 10000)b 
    on a.pk_no = b.pk_no 
 where a.ins_dtm < '2023-01-01';
 
delete from XTMP_PK_NO 
 order by pk_no 
 limit 10000;

이렇게 PK를 활용하여 10000건 씩 삭제하는 DELETE문을 작성하였습니다.

 

이 DELETE문을 Loop 작업하기 위해 Procedure를 생성하였습니다.

CREATE PROCEDURE sp_delete_pk()
BEGIN
  declare l_max_loop int;
  declare i int default 0;

  select count(*)/10000 
    into l_max_loop
    from XTMP_PK_NO;

  while (i <= l_max_loop) do
  
    delete a
      from [Table_A] a
      join (select pk_no 
              from XTMP_PK_NO 
             order by pk_no 
             limit 10000) b 
        on a.pk_no = b.pk_no 
     where a.ins_dtm < '2023-01-01';
     
    delete from XTMP_PK_NO
     order by pk_no 
     limit 10000; 
   
    insert into TB_DEL_HST (TB_NM, DEL_DT, DEL_NO, INS_DTM)
    values ('[Table_A]', null, i, current_timestamp);
  
    set i = i + 1;

  end while;
  
END

최대 몇번 Loop를 돌지 l_max_loop 변수에 담아두고 l_max_loop 수 만큼 루프를 진행합니다.

 

저는 대략 87000번 Loop 돌라고 값이 나왔는데요.

사실 무서워서 100번, 1000번, 10000번... 고정값으로 변수에 넣어 조금씩 늘려가며 Loop 를 돌았고,

마지막에 l_max_loop 값을 사용하여 작업을 완료하였습니다. 

 

이렇게 작업하니 몇시간 안되서 8.7억 row의 데이터를 다 삭제하였습니다.

용량으로는 80GB의 테이블이 대략 3GB의 테이블이 되었습니다.

---

Outro

사실 PK(Index)를 사용하라는 방법은 저희 팀장님께서 조언을 해주셨는데요.

정말 답도없이 혼자 헤매고 있을뻔 했습니다.

 

대용량의 데이터를 다뤄볼 일이 많지는 않은데요.

 

대용량 데이터라하면, 분석이나 이관을 많이 할 것 같은데,

이렇게 많은 양의 데이터를 지워본 것도 좋은 경험 인 것 같습니다.

 

대용량 데이터를 작업 할 때는

Full Table Scan을 피할 수 있도록 노력하고, 분할 작업을 해야한다고 다시 한번 배웠습니다.

 

이상으로 초보 DBA 다뉴였습니다.

감사합니다.