CloudWatch Database Insights 활용: Aurora PostgreSQL 실행 계획 분석 및 성능 최적화

1. 개요

• 본 문서는 Amazon Aurora PostgreSQL 클러스터에서 **CloudWatch Database Insights (Advanced 모드)**를 사용하여 SQL 실행 계획을 분석하고, 데이터베이스 성능 문제를 해결하며 최적화하는 방법을 설명합니다.
• 핵심: PostgreSQL 쿼리 옵티마이저와 쿼리 액세스 플랜 이해를 바탕으로 성능 병목 현상 식별 및 해결.

2. 솔루션 개요 및 사전 조건

2.1. CloudWatch Database Insights

• Aurora, RDS 등 다양한 데이터베이스 엔진 지원 포괄적 모니터링 도구.
• Advanced 모드에서 Aurora PostgreSQL 실행 계획 분석 기능 제공.
• DevOps 엔지니어, 개발자, DBA가 성능 문제 신속 식별 및 해결 지원.

2.2. PostgreSQL 쿼리 옵티마이저 및 실행 계획

• 옵티마이저: SQL 쿼리 실행을 위한 최적의 전략 결정.
• 실행 계획 (Query Access Plan): 옵티마이저가 선택한 단계별 실행 전략 (순차 스캔, 인덱스 스캔, 조인 등).
• EXPLAIN 명령어: 쿼리 플랜 분석에 활용.

2.3. 사전 조건

• CloudWatch Database Insights Advanced 모드 활성화 및 Aurora PostgreSQL DB 인스턴스 구성.
• 필요한 IAM 권한 및 데이터베이스 엔진 버전 충족.

3. SQL 실행 계획 분석 및 비교

3.1. CloudWatch 콘솔 활용

• CloudWatch 콘솔 → Database Insights → Database Views → Aurora PostgreSQL DB 인스턴스 → Top SQL 탭에서 성능 지표 확인.
• Plans Count: 각 쿼리에 대해 수집된 계획 수 표시.

3.2. 실행 계획 분석 예시

• 시나리오: 전자상거래 애플리케이션의 특정 SQL 문 실행 계획 비교.
• 비교: 순차 스캔 vs. 인덱스 스캔 방식 비교를 통해 효율성 평가.
• 결과: 두 번째 실행 계획(인덱스 스캔)이 더 우수한 성능을 보임.

3.3. 성능 저하 문제 해결

• 원인: 인덱스 삭제로 인한 최적화되지 않은 실행 계획 생성 및 쿼리 속도 저하.
• 분석: CloudWatch Database Insights를 통해 시간 경과에 따른 실행 계획 비교.
• 결과: 초기 상태(인덱스 사용)와 성능 저하 후 상태(순차 스캔) 비교를 통해 문제 원인(인덱스 삭제) 명확히 식별.

4. 쿼리 성능 최적화

4.1. 실행 계획 분석을 통한 병목 현상 식별

• 핵심 요소 검토: 조인 유형, 스캔 방법, 정렬 기법, 예상/실제 행 수, 연산자 비용.
• 문제점 발견: Sort Method: external merge Disk: 61216kB (디스크 기반 정렬).

4.2. work_mem 파라미터 튜닝

• 원인: work_mem 기본값(4MB) 부족으로 인한 디스크 스필 발생.
• 해결: work_mem을 256MB로 증가시켜 인메모리 정렬 사용.
• 결과: 쿼리 실행 계획에서 디스크 기반 정렬이 사라지고 메모리 기반 정렬로 최적화됨.

5. 결론

• CloudWatch Database Insights는 Aurora PostgreSQL의 실행 계획 분석을 통해 성능 병목 현상을 효과적으로 식별하고 최적화할 수 있게 지원.
• 핵심: 인덱스 관리, 쿼리 튜닝, 파라미터 설정을 통해 데이터베이스 성능 향상 및 운영 효율성 증대.