1 분 소요

이슈 현상

  • A 테이블 삭제 배치가 도는 중에 사용자로부터 단건 씩 삭제 요청이 오면 lock이 발생 → 단 건 삭제 요청 처리가 지연됨

성능 이슈 원인

  • 배치 delete 범위가 너무 넓음 (한번에 25000 건씩 삭제함) → 그만큼 lock이 걸리는 범위가 넓어짐.
  • 따라서 한번 delete 돌때 잡는 lock의 범위를 줄임으로써 문제 해결

이전 쿼리 (튜닝 전)

delete from A
where id in (
	select top (25000)  
		id
	from A with (NOLOCK)
	where sendDate < dateadd(day, -14 , convert(datetime, convert(char(23), getDate(), 23) + '00:00:00.000'))
	and dataType = #{type, jdbcType=CHAR}
	order by id asc
)

1차 튜닝

<!-- 조건을 만족하는 msgId 리스트 25000건 가져오기-->
select top (25000)  
	id
from A with (NOLOCK)
where sendDate < dateadd(day, -14 , convert(datetime, convert(char(23), getDate(), 23) + '00:00:00.000'))
and dataType = #{type, jdbcType=CHAR}

<!-- 앞서 조회한 25000개의 msgId를 5개씩 나눠서 FOR문으로 삭제 처리 -->
delete from A
where id in 
<foreach item="id" collection="pklist" open="(" separator="," close=")"> // 5건
	#{id}
</foreach>
  • order by 제거
    • top N 쿼리 + (where 조건으로) index 사용가능 → 부분 범위 처리 가능
    • 하지만 인덱스에 존재하지 않는 컬럼으로 정렬 (order by)를 해버리면 정렬을 위해 조건을 만족하는 모든 데이터가 일단 PGA에 올라와야함 (데이터가 상당히 많기 때문에 Temp 저장소를 사용할 것임) → I/O 과다 발생 + 부분 범위 처리 불가능
  • 한 번에 삭제하는 크기를 줄임 → lock 범위를 줄임
    • 25000건을 한번에 삭제하는데 걸리는 시간 : 5~7초
    • 위 작업을 실행하는 동안 단건 삭제 요청이 오면 (그리고 락 범위가 일치하면) 배치 삭제가 완료되어 lock이 해제될 때까지 기다려야 함 → 단건 삭제 요청을 처리하는 시간이 지연됨
    • 따라서 한 번에 삭제하는 양을 1000건으로 줄임 → 처리 시간 1초 미만

2차 튜닝

delete top (1000)     
from A
where sendDate < dateadd(day, -14 , convert(datetime, convert(char(23), getDate(), 23) + '00:00:00.000'))
and dataType = #{type, jdbcType=CHAR}
  • 쿼리에서 IN-LIST 제거
    • IN-LIST가 FILTER 조건으로 풀릴 경우 → 모든 인덱스 리프 블록을 다 탐색해야함
    • IN-LIST가 UNION ALL로 풀릴 경우 → 인덱스 수직적 탐색 횟수가 in-list 개수 만큼 발생. ( IN 조건 안에 데이터가 N건이면 수직적 탐색 N번 수행)
  • TOP N 쿼리 이용
    • 해당 테이블에는 (sendDate + type) 인덱스가 존재한다.
    • 따라서 Delete 구문에서 TOP N 쿼리를 사용 → Index Range Scan → 부분범위 처리 가능

    +) 배치 삭제에서 삭제할 레코드의 id를 우선적으로 조회 → 삭제를 진행하기 전에 사용자가 해당 레코드를 삭제해버렸을 경우 → 오류 위험 존재

  • 튜닝 결과 :
    • INDEX RANGE SCAN + 부분 범위 처리로 효율 증대
    • 단건 삭제 처리 시간 10초 → 1초로 단축

참고