배치작업시간대의 Aurora PostgreSQL 의 CPU 급증 문제 해결기

서론

채팅방 순위 업데이트 배치 작업 중 DB CPU 사용량 급증하는 문제가 있었다. 성능개선 도우미를 확인하니 IO:xactsync 가 Average Activie Session 중 많은 비중을 차지하여, 데이터베이스 부하에 큰 영향을 주는 것을 확인했다. 코드를 보니 단일 update 쿼리를 100개씩 잘라 Promise all 로 처리하고 있었다. 만약 배치 사이즈를 줄이면 해결할 수 있을까?

 

본론

우선 개념과 정의를 정리해보았다.

• PostgreSQL은 WAL(Write Ahead Logging) 을 사용하여 데이터 무결성을 보장한다. 즉 디스크에 로그를 기록한 뒤 커밋을 처리하는 것이다.
• Aurora PostgreSQL은 WAL 에 로컬 디스크가 아니라 네트워크로 연결된 스토리지를 사용한다.
• IO:xactsync 이벤트는 데이터베이스가 Aurora 스토리지 하위 시스템이 트랜잭션의 커밋을 승인할 때까지 대기 중일 때 발생한다고 한다.

위 정리한 항목을 참고하여, 최근 채팅방이 늘어난 것으로 인한 단일 업데이트문이 커밋이 증가하였고, 이를 기록하기 위한 네트워크를 통한 스토리지 쓰기 요청이 병목이라고 판단하였다. 즉 배치 사이즈를 줄이더라도 전체 커밋 개수가 줄어들지는 않기에 해결될 수 없었다. 

 

1차 해결: Bulk Update로 커밋 수 감소

커밋 횟수 자체를 줄이기 위해 PostgreSQL의 bulk update 문법(UPDATE ... FROM VALUES 패턴)을 적용했다. 기존에는 row마다 개별 UPDATE = 개별 커밋이었지만, FROM VALUES로 500개 row를 하나의 문으로 처리하면 커밋 1회로 줄어든다. 기존 대비 커밋 수가 대폭 감소한 셈이다.

500개 단위 chunk로 나눈 이유는 배치 서버와 데이터베이스의 OOM 방지를 위해서다. 하나의 explicit transaction으로 전체를 묶는 방법도 있었지만, 트랜잭션 타임아웃 리스크를 고려하여 chunk 단위 auto-commit 방식을 선택했다. 또한 대량 UPDATE로 인한 dead tuple 증가에 대비하여 autovacuum 관련 파라미터도 조정했다.

 

쿼리 길이 제한에 대한 공식 문서는 없지만, 링크를 통해서 쿼리를 파싱할 때 사용 가능한 버퍼 크기가 최대 1GB임을 유추해볼 수도 있을 것이다. 

 

2차 분석: 병목이 이동했다

Bulk update 적용 후 Performance Insights를 다시 확인했다. IO:xactsync의 비중은 줄어들었지만, CPU 사용량 90%는 여전했다. AAS의 주요 원인이 IO:xactsync에서 CPU로 바뀌어 있었다.

커밋 병목은 해소되었지만, 5만 건 전체를 매번 재계산하고 UPDATE하는 연산 자체의 CPU 부하가 남아 있는 것이다. 네트워크 I/O 병목을 제거하니 CPU 연산이 드러난 셈이다.

 

다음 단계 (검토 중)

 

의심중인 유력한 원인은 순위 컬럼의 인덱스 재구성이다.

PostgreSQL에서 UPDATE는 내부적으로 "기존 row를 dead tuple로 마킹 + 새 row 삽입"인데, 이때 해당 row가 포함된 모든 인덱스의 엔트리도 삭제 + 재삽입되는데, 5만 건을 전부 UPDATE하면 5만 건의 인덱스 B-tree 재구성이 일어나는 것이고, 이건 순수 CPU 연산이기 때문이다.

 

Redis Sorted Set를 이용하거나 lexorank를 이용할 수 있을 것 같다.

 

참고

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/apg-waits.xactsync.html