DB에 임시적으로 생성되는 레코드들이 있고, 이 레코드들은 처리가 된 이후에 다시 쓰이지 않게 되는 성격이라 디스크 용량 확보를 위해 지워야 했다. 하루에 지워야할 데이터 양이 약 1.5백만개 정도로 잡았더니 생각할 점들이 있었다.

  • 속도가 중요한가?
  • 서버에 얼마나 부담을 줄 것인가?
  • 한 번에 몇 개씩 지울 것인가?

우선 속도보다는 서버에서 안정적(리소스를 많이 쓰지 않고)으로 레코드들을 지우는게 필요했고, 그 답들을 찾으려다 보니 아래의 문서들을 찾았고, 번역을 해 봤다.

Stackoverflow 요약

Delete는 InnoDB에서 가장 비용이 많이 드는 작업이고, 쿼리 자체는 실행 플랜에 따라 최적화될 것이기 때문에 문제가 되지 않는다.

InnoDB의 특성을 통해 왜 Delete가 가장 느린 작업인지 이해해보자. InnoDB는 transactional storage engine이기 때문에, 프로세스가 중간에 중단되면, 마치 아무일도 일어나지 않은 것처럼 레코드들은 그대로 저장돼있을 것이다. 작업이 완료되면, 같은 인스턴스에 있는 모든 레코드가 삭제될 것이다. Delete가 일어나는 동안에는 서버에 접속한 다른 클라이언트들은 delete가 완료될 때까지 레코드를 볼 수 있을 것이다.

이러한 기능을 취하기 위해, InnoDB는 MVCC (Multi Version Concurrency Control)라는 기술을 이용한다. 이 기술이 하는 일은 기본적으로 첫 트랜잭션이 시작할 때의 전체 데이터베이스의 스냅샷을 각 커넥션에 제공하는 것이다. 이것을 수행하기 위해, InnoDB의 모든 레코드는 내부적으로 다중의 값(각 스냅샷에 대한)을 가질 수 있다. InnoDB의 Count 가 시간이 걸리는 이유가 이 때문이기도 하다. - 그 시점에 보고 있는 스냅샷 상태에 달려있다.

Delete 트랜잭션을 위해, 쿼리 조건을 통해 식별된 모든 레코드에 표시를 한다. 다른 클라이언트들이 동시에 데이터에 접근할 지도 모르기 때문에 테이블에서 즉시 삭제될 수는 없다, 왜냐하면 삭제의 원자성(automacity)를 보장하기 위해 각각의 스냅샷을 봐야하기 때문이다.

모든 레코드들에 삭제를 위한 표시가 완료되면, 트랜잭션이 성공적으로 수행된다. 하지만 delete트랜잭션 이전에 작업한 모든 트랜잭션이 끝나기 전까지 데이터들은 실제 데이터 페이지에서 바로 삭제될 수 없다.

트랜잭션에 안전한 방법으로 데이터를 삭제하는 것 치고 걸리는 시간 3분(지우는 데이터 2.2만개)은 그리 오래 걸리는 것이 아니다. 성능을 향상시키기 위해서 InnoDB의 버퍼 풀 사이즈를 늘이고 삭제 작업을 하는 동안에 다른 작업을 제한시킬 수 있다.

Explain을 활용한 쿼리 최적화

Explain을 통해 정보를 얻을 수 있는 쿼리들: SELECT, DELETE, INSERT, REPLACE, UPDATE

Explain이 설명 가능한 쿼리(Explainable statement)에 쓰이면, 쿼리 실행 플랜에 대한 정보를 옵티마이저로부터 가져와 보여준다. Explain의 출력 형식에 대해서는 참조.

Explain의 도움을 통해, 인덱스를 이용하여 쿼리문을 빠르게 수행하기 위해 테이블 어디에 인덱스를 추가해야할지 볼 수 있다. 또한, 옵티마이저가 최적의 순서로 테이블들을 조인하고 있는지 확인할 수 있다. SELECT 구문에 명명된 테이블 순서로 조인되도록 옵티마이저에게 힌트를 주기 위해서, JOIN 대신에 STRAIGHT_JOIN을 사용한다. 하지만, STRAIGHT_JOIN이 semi-join 변환을 불가하게 하기 때문에, STRAIGHT_JOIN이 인덱스가 사용되는 것을 막을 수도 있다.

만약 사용되야 한다고 생각하는 인덱스가 그렇지 않을 때, ANALYZE TABLE 을 실행하여 테이블 통계를 갱신하여 키의 카디널리티 같은 옵티마이저의 결정에 영향을 주는 요소들을 확인한다.

Transaction Cost

트랜잭션은 하나의 일관된 상태에서 다른 일관된 상태로 데이터베이스를 이동시키는 것이 필요하다. 전형적인 예로 두 계좌 사이에서 돈을 이체시키는 것이 있다: 한 계좌는 debit하고, 나머지 하나는 credit한다. 두 작업을 수행하는 사이에 데이터베이스는 일관되지 않게 된다. 하지만 트랜잭션 마지막에 데이터베이스는 다시 일관된 상태가 된다. 트랜잭션은 두 작업의 수행 중에 클라이언트가 일관되지 않은 데이터베이스를 보는 것으로부터 보호해준다. 그리고 충돌 상황에서 데이터베이스가 일관된 데이터를 유지하도록 한다.

트랜잭션 범위는 요청 범위에 대해 orthogonal하다. (번역은 직교하다라는 뜻인데, 둘 사이에 correlation이 없다고 생각하면 된다) 하나의 프로시저에 모든 작업을 넣을 필요가 없다.

따라서 트랜잭션은 중대한 프로시저 오버헤드를 초래하지 않아야 한다. 사실 프로시저는 트랜잭션에 관심이 없을 것이다. 잘 쓰여진 프로시저는 트랜잭션 내에서 잘 호출되어야 할 뿐만 아니라 트랜잭션이 있건 없건 제대로 작동해야 한다. 예외 처리 및 중첩 트랜잭션에서 트랜잭션 및 예외가 있는 상황에서 적절히 작동하는 프로시저를 위한 템플릿을 확인해보자.

결론적으로, 트랜잭션의 실제 비용은 얼마일까? 트랜잭션은 데이터를 쓰고Write(Read는 정말 트랜잭션이 필요 없다.) 수정된 레코드에 락을 건다. 블록된 레코드를 읽으려고 시도하면 보통 Read를 블록한다. 트랜잭션이 끝나면, 레코드에 걸린 락들도 해제된다. 이 때 해결방법은 스냅샷을 읽는 것이다. 스냅샷 읽기는 마법의 가루와 같아서, 블록된 레코드들에 방해받지 않고 읽기를 가능하게 한다. 왜냐하면 업데이트 이전 레코드의 버전을 항상 읽을 수 있기 때문이다. 열 버전 기반 분리 레벨에 대해서 읽어보자.

그래서 결론은 간단하다: 트랜잭션은 비용이 없다.(정말?) 왜냐하면 트랜잭션의 대체가 없기 때문이다. ‘X가 Y보다 느리다’거나 ‘X가 유일한 올바른 대체이다’ 따위의 논점이 아니다. (다른 트랜잭션의 대체재와 비교하여 비용을 측정할 수 없기 때문에 비용이 없다는 뜻으로)

Reference
Stackoverflow
Quora
Mysql reference
Stackoverflow