Transactional Outbox message-relay 개선하기

문제 상황

• Transactional Outbox 기반 message-relay 운영 중 메시지 적체와 동시 insert로 인해 select, delete, select for update의 latency가 간헐적으로 급증
• processed_message 테이블이 커지며 nested loop anti join 성능 저하 발생

원인 분석

• left join과 anti join 구조에서 processed_message 누적이 조회 비용 증가로 이어짐
• locking read, update, delete가 scan한 index record 전체에 lock을 설정해 index full scan 시 lock wait가 확대
• LIMIT 없이 IN 절만 사용한 쿼리가 full scan으로 이어져 불필요한 lock 경합 발생

해결 방법

• processed_message 삭제를 동기 처리하고 message 테이블 단일화로 조회 구조 단순화
• MySQL 8.0 NOWAIT로 lock wait 없이 node 간 경합 방지, Redis pessimistic lock 제거
• order by와 limit 조합으로 index range scan 유도, select와 delete를 분할해 마지막 row는 다음 루프로 넘김

성능/운영 포인트

• 테이블 크기 증가와 무관하게 일관된 select 성능 확보
• lock wait 감소로 deadlock과 DB 부하 완화
• 모니터링 결과 쿼리 latency 변동 폭 축소