쿼리 튜닝 - 플랜 분석 예시 (memory 부족)

쿼리 redistribute motion 1:48 (slice4; segments: 1) (cost=0.00...)(actual time=.. rows=4530430 loops=1)   hash key: "outer".?column?     ->Result (..)       ->WindowAgg (cost..)          Partition by: (CASHE WHEN ..)          -> Sort (..              Sort Key: (CASE ..              Sort Method: external sort Disk: 2059808kB            -> Result..              ->GroupAggregate ...                  ->Hash JOin                     Hash Cond:                     Join Filter                     Extra Text:hash chain length 1.3 avg, 3 max, using 340 of 262144 buckets. slice0 executor memory:140k bytes avg x 48 workers, 163k bytes max (seg33) slice1 executor memory:36036k bytes avg x 48 workers, 57700k bytes max (seg22). work_mem: 55289k bytes max. slice2 executor memory: 43794k bytes avg x 48 workers, 80948k bytes max(seg0). work_mem:55289k bytes max. slice3 executor memory:52676k bytes avg x 48 workers, 162868k bytes max (seg10). work_mem:104441k bytes max *slice4 executor memory: 471454k bytes(seg46). work_mem:248830k bytes max, 3532266k bytes wanted memory used 532480kb memory wanted 60050417kb optimizer:pivotal optimizer (gporca) planning time:184.813ms execution time:392449.904ms

 

1. Redistribute Motion 단계 (데이터 재분배)

sql

redistribute motion 1:48 (slice4; segments: 1) (actual time=.. rows=4530430 loops=1) hash key: "outer".?column?

• Redistribute Motion: Greenplum은 MPP(Massively Parallel Processing) 구조라서, 여러 세그먼트(Worker 노드)에서 데이터를 처리합니다. redistribute motion은 데이터를 해시 키를 기준으로 세그먼트 간에 재분배하는 단계입니다.
• rows=4,530,430: 약 453만 개의 행이 재분배되었습니다.
• hash key: 해시 키 기준으로 데이터가 분산되고 있습니다. 해시 키는 조인 조건(Hash Join 단계)에서 사용되는 키입니다.

메모리 문제 원인 힌트: Redistribute Motion 단계는 메모리 사용량을 크게 증가시킬 수 있습니다.

---

2. WindowAgg 단계 (윈도우 함수 연산)

->WindowAgg Partition by: (CASE WHEN ..) -> Sort Sort Key: (CASE ..) Sort Method: external sort Disk: 2059808kB

• WindowAgg: 윈도우 함수(OVER(PARTITION BY ...))를 수행 중입니다.
• Partition by: 조건에 따라 파티션을 나누어 윈도우 함수 작업을 수행합니다.
• Sort Method: external sort:
  • 정렬이 메모리에 다 담기지 못해서 디스크에 스와핑(external sort)되었습니다.
  • 디스크 사용량: 약 2GB(2059808kB)
  • 이는 **work_mem**이 부족하다는 신호입니다.

문제점: work_mem을 충분히 할당하지 않으면 정렬 시 디스크 I/O가 증가해 성능 저하가 발생합니다.

---

3. Hash Join 단계 (해시 조인)

 

->Hash Join Hash Cond: Join Filter Extra Text: hash chain length 1.3 avg, 3 max, using 340 of 262144 buckets.

• Hash Join: 조인 시 해시 테이블을 사용했습니다.
• hash chain length:
  • 평균 해시 체인 길이: 1.3
  • 최대 해시 체인 길이: 3
  • 버킷 사용률: 340개 사용 / 262,144개 총 버킷
• 해시 테이블의 충돌(체인)이 적어 해시 품질은 양호합니다.

문제점은 아님: 해시 조인 자체는 성능에 큰 문제가 없어 보입니다.

---

4. 메모리 사용량 분석 (슬라이스별 메모리)

 

slice0 executor memory: 140k avg x48, 163k max

slice1 executor memory: 36036k avg x48, 57700k max. work_mem: 55289k max.

slice2 executor memory: 43794k avg x48, 80948k max. work_mem: 55289k max.

slice3 executor memory: 52676k avg x48, 162868k max. work_mem: 104441k max.

slice4 executor memory: 471454k (seg46). work_mem:248830k max, 3532266k wanted

• Greenplum은 쿼리를 슬라이스(Slice) 단위로 병렬 처리하며, 각 슬라이스는 여러 세그먼트(Segment)에서 실행됩니다.
• Slice4 메모리 초과 문제:
  • 평균 사용량: 471,454kB (~460MB)
  • work_mem 할당: 248,830kB (~243MB)
  • 실제 요구량: 3,532,266kB (~3.5GB)
  • 요구 메모리(wanted)가 설정 메모리보다 약 14배 초과했습니다.

메모리 병목 원인: Slice4에서 work_mem 부족으로 디스크 스와핑이 발생해 실행 속도가 느려졌습니다.

---

5. 메모리 총합 및 부족 현황

 

memory used: 532480kB (~520MB) memory wanted: 60050417kB (~60GB)

• 전체 메모리 사용: 약 520MB
• 전체 메모리 요구량: 약 60GB
• 차이: 약 59.5GB 부족

핵심 문제: work_mem과 gp_vmem_protect_limit 등의 메모리 설정이 현재 쿼리에 비해 현저히 낮습니다.

---

6. 옵티마이저 종류

 

optimizer: pivotal optimizer (gporca)

• 현재 **Pivotal 옵티마이저(GPORCA)**를 사용 중입니다.
• 복잡한 쿼리에서는 ORCA보다 PostgreSQL 기반의 **Planner(legacy optimizer)**가 더 나은 성능을 보일 수 있습니다.

---

7. 실행 시간 및 계획 시간

 

planning time: 184.813ms execution time: 392449.904ms (~392초, 약 6분 32초)

• 플랜 생성: 184ms (정상)
• 실행 시간: 6분 32초 (비정상적으로 느림)
• 주된 원인은 Slice4의 메모리 부족 및 디스크 I/O입니다.

---

🚨 문제 요약 (원인):

1. work_mem 부족: WindowAgg 및 Hash Join 단계에서 디스크 스와핑 발생.
2. Slice4의 메모리 초과: 요구 메모리가 설정치보다 14배 높음.
3. Redistribute Motion으로 데이터 재분배: 네트워크 및 메모리 오버헤드 증가.
4. GPORCA 옵티마이저 문제 가능성: Planner 대비 비효율적인 계획 수립 가능성.

---

✅ 해결 방안 (튜닝 가이드):

1. work_mem 증가:
   SET work_mem = '512MB'; -- 최소 512MB 이상으로 증가
2. gp_vmem_protect_limit 증가 (DBA 권한 필요):
    

   ALTER SYSTEM SET gp_vmem_protect_limit = '16384MB'; -- 예시: 16GB
3. 플래너 사용 (GPORCA 비활성화):
    

   SET optimizer = off; -- Planner 사용
4. Redistribute 단계 최적화:
   • 조인 키(hash key)를 데이터 분산도가 좋은 칼럼으로 수정
5. 쿼리 리팩토링:
   • 윈도우 함수 및 중간 정렬 단계 쿼리를 서브쿼리나 CTE(Common Table Expression)로 분할

---