[SQL Server] 운영 개선건 - 분기 단위 대량 작업의 마이크로 배치화

서비스에서 6개월 단위의 데이터만 남기는 작업을 6개월 ~ 1년 단위로 진행하고 있었다.

해당 테이블은 개발팀을 통해 무료 재화 개념의 데이터로 법적 보관 의무가 없어서 상시 6개월 데이터만 보관하여도 가능한 것 확인.

데이터 분포 파악 -- 일 평균 44만건 (최대 94만 최소 18만 건)

 

개선 방안

1. 파티션 테이블 검토
2. VIEW 생성하여 물리적인 파티션 개념 적용
3. 매일 6개월이 지난 데이터 삭제

1. 파티션 테이블 생성할 경우 결제 일자값으로 생성 테이블 자동 관리가 용이할 것으로 보임 다만 주요 조회 값은 결제번호로 조회가 되므로 성능 향상에 기대가 어려움

 

참고)

파티션 테이블 인덱스

비파티션  

- 파티션 키 칼럼이 조건절에 누락되면 여러 인덱스 파티션을 액세스해야 하므로 비효율적. 특히, OLTP 환경에서 성능에 미치는 영향이 크므로 비파티셔닝 전략이 유용할 수 있음
- NL Join에서 파티션 키에 대한 넓은 범위검색 조건을 가지고 Inner 테이블 액세스 용도로 인덱스 파티션이 사용된다면 비효율적 → 비파티션 인덱스 사용을 고려
- 파티션 인덱스를 이용하면 sort order by 대체 효과 상실. 소트 연산을 대체함으로써 부분범위 처리를 활용하고자 할 땐 비파티셔닝 전략이 유용
- 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

Global Prefixed  

- 인덱스 경합 분산에 효과적
- 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 대안으로 활용 가능
- 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

Local Prefixed  

- 관리적 측면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때
- 이력성 데이터를 주로 관리하는 DB 환경에 효과적
- 파티션 키 칼럼이 '=' 조건으로 사용될 때 유용
- 파티셔닝 칼럼에 대한 검색 조건이 없으면 인덱스 선두 칼럼이 조건절에 누락된 것이므로 정상적인 사용이 불가(Index Full Scan으로는 선택 가능)
- 파티션 키 칼럼(=인덱스 선두 칼럼)이 Like, Between, 부등호 같은 범위검색 조건일 때 불리

Local Non Prefixed  

- 관리적 측면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때
- 이력성 데이터를 주로 관리하는 DB 환경에 효과적
- 파티션 키 칼럼이 조건절에 사용될 때 유용
- 파티셔닝 칼럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있으므로 주의
- 파티션 키 칼럼이 범위검색 조건으로 자주 사용된다면 Local Prefixed 보다 Local NonPrefixes가 유리. 그렇다더라도 좁은 범위검색이어야 함

 

2. 테이블을 TABLE_01, TABLE_02 로 물리적으로 생성한 후

TABLE_01 TABLE_02 를 UNION ALL한 VIEW를 TABLE 이름으로 생성 INSERT는 _02 테이블에서만 발생시키고

6개월이 지난 후에 _01 테이블 삭제 _02 테이블 → 01 테이블로 RENAME, _02 테이블 생성 진행

→ 조회에 대한 성능 테스트 필요

(PK를 통한 조회를 할 경우 좋은 성능을 보이나 그 외에는 정렬이 필요한 경우 지연이 발생한다) 

 

3. 매일 6개월이 지난 데이터 삭제 

가장 간단한 방법, 6개월 데이터를 제외하고 일 배치로 데이터 삭제 처리

다만 건수가 많기 때문에 트랜잭션을 작게 나누어서 처리가 필요함

 

 

총 데이터 24억건 기준

소요시간(5회 평균)	삭제 건수	총 건수	LOCK TYPE
30MS	100	94만건	KEY
112MS	1000	94만건	KEY
584 MS	5000	93만건	KEY
985 MS	10000	91만건	TAB OR KEY

 

5천건, 1만건 비슷한 성능을 보이나 LOCK TYPE이 1만건의 경우 TAB으로 잡히는 경우가 종종 확인되어

5천건 단위로 삭제하는 것이 더 안정적임 (평균 데이터 기준 90회 삭제 처리)

 

 

TOP 2만건 삭제 

Page에 S LOCK TAB에 IS (조회 과정에서 잡히는 LOCK으로 보임)

 

데이터를 일괄 삭제한 7천만건 기준

소요시간(5회 평균)	삭제 건수	총 건수	Lock Type
8MS	100	46만건
35MS	1000	46만건	KEY,PAGE : U,IU,X TAB : IX
145 MS	5000	45만건	KEY,PAGE : U,IU,X TAB : IX,X
276 MS	10000	43만건	KEY,PAGE : U,IU,X TAB : IX,X
1200 MS	50000	38만건	TAB : X

 5,000 건씩 삭제 처리 모니터링 후 이슈 있을 경우 3,000 건으로 진행

 

해당 개선으로 인하여 분기 또는 년 단위 점검으로 진행하던 작업 미실시, 또한 작업 시간대에 서비스 지연 없는 것으로 파악(KST 기준 05시에 슬로우쿼리 0건)

다만 조회를 통하여 삭제가 진행되므로 (데이터 건수도 일 평균 44만건) GRAFANA 지표상 READ, WRITE 관련 지표가 상승하는 것을 파악할 수 있음.