PostgreSQL: SQL Error [40001]

※ SQL Error [40001]: ERROR: canceling statement due to conflict with recovery.

 

안녕하세요. 듀스트림입니다.

 

PostgreSQL 사용 시 자주 발생하는 오류도 하나씩 포스팅하려고 해요.

---

SQL Error [40001]: ERROR: canceling statement due to conflict with recovery

이 오류는 standby 서버에서 실행 중인 쿼리와 primary 서버의 WAL replay 작업 간에 충돌이 발생할 때, 데이터 일관성을 보장하기 위해 해당 쿼리를 강제로 취소하면서 발생합니다.

---

1. 발생 원인

• WAL replay와의 충돌: standby 서버는 primary 서버의 WAL을 적용하며 최신 상태를 유지합니다. 이 과정에서, 장시간 실행되는 쿼리가 standby의 스냅샷과 충돌할 수 있습니다.

 

• Long-running Query: 장시간 실행되는 쿼리일수록 primary 서버의 데이터 변경(예: VACUUM, autovacuum, UPDATE, DELETE 등)과 충돌할 가능성이 높아집니다.

 

• Replication 설정 미조정: 기본 설정 상태에서는 standby 쿼리와 primary의 정리 작업이 충돌할 경우 standby 쿼리를 취소하게 됩니다.

---

2. 해결 방법

2.1. 쿼리 재실행

• 충돌로 인해 취소된 쿼리를 워크로드가 여유로울 때 다시 실행하는 방법이 있습니다.

---

2.2. Replication 파라미터 조정

 

2.2.1. hot_standby_feedback 활성화: standby 서버에서 실행 중인 쿼리 정보를 primary 서버에 전달하여, primary의 VACUUM 등 정리 작업을 지연시킬 수 있습니다.

-- 설정 예시
ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

 

+ hot_standby_feedback 활성화 시 주의 사항

• 테이블 Bloat 증가
  • 데이터 정리 지연: hot_standby_feedback이 활성화되면 standby 서버에서 실행 중인 쿼리의 정보를 primary 서버에 전달하여, 해당 쿼리가 참조하고 있는 오래된 데이터의 제거(VACUUM)를 지연시킵니다.
  • 불필요한 데이터 축적: 이로 인해 primary 서버에 불필요한 죽은(dead) 튜플이 오래 남아 테이블이 비대해질 수 있으므로, 정기적인 모니터링과 관리가 필요합니다.
• VACUUM 및 Autovacuum 작업 영향
  • 정리 작업의 지연: primary 서버의 VACUUM 작업이 지연되면, 업데이트나 삭제 후 남은 데이터가 제대로 정리되지 않아 성능 저하로 이어질 수 있습니다.
  • Autovacuum 부하: 자동 정리 프로세스(autovacuum)가 더욱 자주 작동하거나 추가적인 리소스를 소모할 가능성이 있으므로, 시스템 자원 사용률을 주시해야 합니다.
• Replication Lag Trade-off
  • 데이터 정합성과 최신성: standby 쿼리 보호를 위해 primary의 정리 작업이 지연되면, replication 자체에는 큰 문제가 없더라도, 장기적으로 데이터 정합성 유지 및 최신성 확보에 영향을 미칠 수 있습니다.
  • 복제 지연 우려: 특히, 데이터 변경이 빈번한 환경에서는 불필요하게 오래된 데이터가 유지됨으로써 replication 시스템 전체의 효율에 영향을 줄 수 있습니다.
• 디스크 사용량 증가
  • 저장 공간 관리: 오래된 튜플이 제거되지 않고 축적되면, 디스크 공간 사용량이 증가하게 되어 저장 공간 부족 문제를 야기할 수 있습니다.
  • 정기적인 클리닝 필요: 이와 관련하여, 주기적으로 테이블과 인덱스의 상태를 점검하고, 필요시 수동 VACUUM이나 재인덱싱 작업을 진행해야 합니다.
• 시스템 성능 및 리소스 모니터링 강화
  • 모니터링 도구 활용: pg_stat_database_conflicts, pg_stat_activity 등 시스템 뷰를 활용해 standby 쿼리와 관련된 충돌 상황 및 VACUUM 지연 현황을 주기적으로 모니터링해야 합니다.
  • 리소스 최적화: 시스템 전체의 CPU, 메모리, 디스크 I/O 사용량 등을 확인하며, hot_standby_feedback로 인한 부작용이 있는지 점검할 필요가 있습니다.
• 운영 환경에 따른 신중한 적용
  • 워크로드 분석: 데이터 분석, 리포트 생성 등 장시간 실행 쿼리가 많은 환경에서는 standby 쿼리 보호가 중요하지만, 동시에 primary 서버의 정리 작업이 지연되어 발생할 수 있는 부작용도 함께 고려해야 합니다.
  • 테스트 및 검증: 운영 환경에 적용하기 전에 테스트 환경에서 충분히 검증하여, hot_standby_feedback 활성화가 전체 시스템에 미치는 영향을 파악하는 것이 중요합니다.

---

2.2.2. max_standby_streaming_delay 값 조정: Standby 쿼리가 WAL replay와 충돌할 경우 대기할 수 있는 시간을 늘려, 쿼리가 취소되지 않도록 합니다.

-- 설정 예시
ALTER SYSTEM SET max_standby_streaming_delay = '120s';  -- 기본값은 30초
SELECT pg_reload_conf();

 

+ max_standby_streaming_delay 값 조정 시 주의 사항

• Replication Lag 증가 가능성
  • 대기 시간 연장에 따른 지연: 이 값을 높이면 standby에서 WAL 리플레이 작업이 더 오래 대기하게 되어, primary 서버의 변경사항이 적용되는 시점이 지연될 수 있습니다.
  • 데이터 최신성 저하: standby 노드를 읽기 전용으로 사용하는 경우, replication lag가 커지면 데이터의 최신성이 떨어질 수 있습니다.
• 장시간 실행 쿼리 보호와 시스템 동기화 간 균형
  
  • 쿼리 보호 효과: 높은 대기 시간은 장시간 실행되는 쿼리가 WAL 리플레이 충돌로 인해 취소되는 것을 방지할 수 있습니다.
  • 동기화 영향: 그러나 WAL 적용이 지연되면 standby의 데이터가 primary에 비해 뒤처질 수 있으므로, 시스템 전체의 데이터 동기화 요구사항을 고려해야 합니다.
• 환경 및 워크로드에 따른 값 설정 필요
  
  • 워크로드 분석: 시스템에서 실행되는 쿼리의 특성과 빈도, 그리고 데이터 변경 패턴을 분석하여 최적의 값을 결정해야 합니다.
  • 적절한 타협: 장시간 실행 쿼리가 많다면 값을 높여주는 것이 유리하지만, 빠른 복제 동기화가 필요한 환경이라면 낮은 값을 유지해야 합니다.
• 모니터링 및 후속 조치 강화
  
  • 모니터링 지표 확인: 설정 변경 후 pg_stat_replication, pg_stat_activity, pg_stat_database_conflicts 등 시스템 뷰를 통해 replication lag, 쿼리 실행 현황 및 충돌 발생 빈도를 지속적으로 모니터링해야 합니다.
  • 테스트와 검증: 운영 환경에 적용하기 전, 테스트 환경에서 충분한 검증을 통해 예상치 못한 부작용이 없는지 확인하는 것이 중요합니다.
• 운영 환경에서의 영향 고려
  
  • Failover 시 데이터 일관성: 높은 대기 시간 설정은 failover 상황에서 standby의 데이터 일관성에도 영향을 줄 수 있으므로, 장애 복구 시나리오를 충분히 고려해야 합니다.
  • 시스템 성능 전반에 미치는 영향: 설정 값 변경은 전체 시스템의 동작 방식에 영향을 미칠 수 있으므로, 다른 관련 파라미터와의 상호작용도 고려해야 합니다.

---

2.3. 쿼리 최적화

• 장시간 실행되는 쿼리를 최적화하여 실행 시간을 줄임으로써 충돌 가능성을 낮춥니다.

---

3. 예방 방법

3.1. Replication 파라미터 조정

• 2.2.2.에 작성된 파라미터들을 최적화 하는 방법이 있습니다.

---

3.2. 쿼리 실행 전략 변경
• 장시간 실행되는 리포트나 분석 쿼리는 HA로 구성된 노드가 아닌 Replica 노드에서 실행하거나, primary 서버의 부하가 낮은 시간대에 실행하는 방안을 고려합니다.

---

3.3. autovacuum 설정 조정
• primary 서버의 VACUUM 작업이 standby 쿼리와 충돌하지 않도록 autovacuum 설정을 조정합니다.

PostgreSQL: autovacuum 최적화

---

4. 모니터링 방법

4.1. 로그 모니터링
• PostgreSQL 로그에서 canceling statement due to conflict with recovery 메시지 발생 시점 및 빈도를 확인합니다.

---

4.2. 시스템 뷰 활용
• pg_stat_activity: standby 서버에서 실행 중인 쿼리 목록과 실행 시간을 모니터링합니다.
• pg_stat_replication: replication 상태와 지연(lag)을 점검합니다.

• pg_stat_database_conflicts: 충돌 관련 통계를 확인할 수 있습니다.

---

오늘은 여기까지~