AWS re:Post을(를) 사용하면 다음에 동의하게 됩니다. AWS re:Post 이용 약관
AWS re:Postre:Post

PostgreSQL용 RDS 또는 Aurora PostgreSQL 인스턴스에서 성능 문제와 느리게 실행되는 쿼리를 식별하고 해결하려면 어떻게 해야 합니까?

8분 분량
0

Amazon Relational Database Service(RDS) for PostgreSQL 인스턴스가 느립니다. 실행 속도가 느린 쿼리를 식별하고 문제를 해결하려고 합니다.

해결 방법

Amazon RDS for PostgreSQL 인스턴스의 성능은 다음과 같은 여러 가지 이유로 영향을 받을 수 있습니다.

  • 하드웨어 용량이 작음
  • 워크로드 변화
  • 트래픽 증가
  • 메모리 문제
  • 차선책 쿼리 계획

원인 파악

이러한 도구를 함께 사용하여 느리게 실행되는 쿼리의 원인을 식별합니다.

  • Amazon CloudWatch 지표
  • Enhanced Monitoring 지표
  • 성능 개선 도우미 지표
  • 데이터베이스 통계
  • 네이티브 데이터베이스 도구

CloudWatch 지표

리소스 부족으로 인한 성능 병목 현상을 식별하려면, Amazon RDS DB 인스턴스에 사용할 수 있는 일반적인 CloudWatch 측정치를 모니터링하십시오.

  • CPU 사용률 - 사용된 컴퓨터 처리 용량의 백분율
  • FreeableMemory - DB 인스턴스에서 사용 가능한 RAM(메가바이트 단위)
  • SwapUsage - DB 인스턴스에서 사용하는 스왑 공간(메가바이트 단위)

CPU 사용률의 비율이 높을수록 일반적으로 인스턴스의 활성 워크로드와 더 많은 CPU 리소스가 필요하다는 것을 나타냅니다. 스왑 공간 소비와 함께 메모리 사용률이 높으면 워크로드에 대한 메모리 가용성이 낮아 스와핑이 자주 발생한다는 의미입니다. 이는 인스턴스가 워크로드를 따라갈 수 없음을 의미할 수 있습니다. CPU 및 메모리 리소스의 높은 사용률은 일반적으로 쿼리가 오래 실행되거나, 트래픽이 갑자기 증가하거나, 많은 수의 유휴 연결이 원인입니다.

다음 명령을 실행하여 런타임으로 활성 쿼리를 봅니다.

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

다음 명령을 실행하여 데이터베이스에 있는 유휴 연결 수를 찾습니다.

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

그런 다음 이 명령을 실행하여 리소스를 소비하는 유휴 연결을 종료합니다.

참고: example-pid를 pg_stat_activity에서 가져온 유휴 연결의 PID로 변경해야 합니다.

SELECT pg_terminate_backend(example-pid);

다음 지표를 검토하여 RDS 인스턴스에서 원하는 네트워크 처리량을 달성했는지 확인합니다.

  • NetworkReceiveThroughput
  • NetworkTransmitThroughput

이러한 지표는 수신 및 발신 네트워크 트래픽을 바이트/초 단위로 나타냅니다. 크기가 작거나 비 Amazon EBS에 최적화되지 않은 인스턴스 클래스는 네트워크 처리량에 영향을 미쳐 인스턴스가 느려질 수 있습니다. 네트워크 처리량이 낮으면 데이터베이스 성능에 관계없이 모든 애플리케이션 요청에 대한 응답 속도가 느려질 수 있습니다.

다음 지표를 검토하여 I/O 성능을 평가합니다.

  • ReadIOPS 및 WriteIOPS - 초당 평균 디스크 읽기 또는 쓰기 작업 수
  • ReadLatency 및 WriteLatency - 읽기 또는 쓰기 작업에 소요된 평균 시간 (밀리초)
  • ReadThroughput 및 WriteThroughput - 디스크에서 읽거나 디스크에 쓰는 초당 평균 MB 수
  • DiskQueueDepth - 디스크에 쓰거나 디스크에서 읽기 위해 대기 중인 I/O 작업 수

자세한 정보는 Amazon RDS 인스턴스의 IOPS 병목 현상으로 인해 발생하는 Amazon EBS 볼륨의 대기 시간 문제를 해결하려면 어떻게 해야 하나요?를 참조하세요.

Enhanced Monitoring 지표

향상된 모니터링을 사용하면 운영 체제 수준의 메트릭과 높은 CPU 및 메모리를 사용하는 상위 100개 프로세스 목록을 볼 수 있습니다. 초당 향상된 모니터링을 활성화하여 RDS 인스턴스에서 간헐적인 성능 문제를 식별할 수 있습니다.

사용 가능한 운영 체제 메트릭을 평가하여 CPU, 워크로드, I/O, 메모리 및 네트워크와 관련될 수 있는 성능 문제를 진단할 수 있습니다.

공정 목록에서 CPU% 또는 Mem% 값이 높은 공정을 식별합니다. 그런 다음 데이터베이스에서 관련 연결을 찾습니다.

예를 들면 다음과 같습니다.

NAMEVIRTRESCPU%MEM%VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]250.66 MB27.7 MB85.932.21무제한

데이터베이스에 연결한 후 다음 쿼리를 실행하여 연결 및 쿼리 관련 정보를 찾습니다.

SELECT * FROM pg_stat_activity WHERE pid = 10322;

성능 개선 도우미 지표

Performance Insights를 사용하면 대기, SQL, 호스트 또는 사용자별로 구분된 데이터베이스 워크로드를 평가할 수 있습니다. 데이터베이스 및 SQL 레벨 측정 단위를 캡처할 수도 있습니다.

Performance Insights 대시보드의 상위 SQL 탭에는 DB 로드에 가장 많이 기여하는 SQL 문이 표시됩니다. 최대 vCPU 값보다 높은 DB 로드 또는 AAS(대기 부하)는 인스턴스 클래스에서 조절된 워크로드를 나타냅니다.

SQL 통계에서 호출당 평균 지연 시간은 쿼리의 평균 실행 시간을 제공합니다. 평균 실행 시간이 가장 높은 SQL과 다른 SQL이 최상위 DB 로드 기여자로 간주되는 것이 일반적입니다. 이는 최상위 SQL 목록이 총 실행 시간을 기반으로 하기 때문입니다.

데이터베이스 통계

다음 통계는 PostgreSQL에서 데이터베이스 성능을 평가하는 데 도움이 될 수 있습니다.

  • 데이터 분포 통계
  • 확장된 통계
  • 모니터링 통계

이러한 통계를 읽고 이해하는 방법에 대한 자세한 내용은 PostgreSQL의 통계 이해를 참조하세요.

네이티브 데이터베이스 도구

느린 쿼리를 식별하려면 네이티브 pgBadger 도구를 사용합니다. 자세한 내용은 네이티브 및 외부 도구를 기반으로 Amazon RDS for PostgreSQL에서 쿼리 최적화 및 조정을 참조하세요.

성능 최적화

메모리 설정 조정

PostgreSQL DB 서버는 읽기 및 쓰기 액세스를 개선하기 위해 데이터를 캐싱하기 위해 전체 수명 동안 특정 메모리 영역을 할당합니다. 이 메모리 영역을 공유 버퍼라고 합니다. 데이터베이스가 공유 메모리 버퍼에 사용하는 메모리 양은 shared_buffers 파라미터가 제어합니다.

공유 메모리 영역과는 별도로 각 백엔드 프로세스는 DB 서버 내에서 작업을 수행하기 위해 메모리를 사용합니다. 사용되는 메모리 양은 work_mem 및 maintenance_work_mem 파라미터에 설정된 값을 기반으로 합니다. 자세한 내용은 서버 구성에 대한 PostgreSQL 설명서를 참조하세요.

DB 인스턴스의 메모리 사용량이 계속 증가하면 이러한 파라미터의 값을 낮추는 것이 좋습니다. DB 인스턴스에 연결된 사용자 지정 파라미터 그룹에서 이러한 파라미터의 값을 낮출 수 있습니다.

Aurora PostgreSQL 쿼리 계획 관리

Amazon Aurora PostgreSQL 호환 에디션 쿼리 계획 관리를 사용하여 쿼리 실행 계획이 언제 어떻게 변경되는지 제어할 수 있습니다. 자세한 내용은 Aurora PostgreSQL 호환 쿼리 계획 관리 모범 사례를 참조하세요.

실행 속도가 느린 쿼리 문제 해결

일반적으로 인프라 문제가 발생했거나 전체 리소스 사용량이 많을 때 쿼리가 느리게 실행됩니다. 실행 속도가 느린 쿼리는 쿼리 플래너에 의한 최적이 아닌 쿼리 계획의 결과일 수도 있습니다. PostgreSQL 쿼리 플래너는 테이블에 대해 생성된 통계를 사용하여 쿼리 계획을 생성합니다. 스키마 변경 및 오래된 통계로 인해 이러한 계획이 영향을 받을 수 있습니다. 테이블과 인덱스를 부풀리면 쿼리 실행 속도가 느려질 수도 있습니다.

autovacuum 대몬(daemon)은 데드 튜플 임계값이 충족될 때마다, 테이블에서 데드 튜플을 제거하는 autovacuum 워커 프로세스를 생성합니다. 또한 autovacuum 대몬(daemon)은 특정 테이블에 대해 저장된 통계를 새로 고치는 ANALYZE 작업을 실행합니다.

다음 쿼리를 실행하여 관련 정보를 검색합니다.

  • 데드 튜플
  • autovacuum 또는 vacuum 작업 횟수
  • 자동 분석 또는 분석 실행 횟수
  • 이러한 작업이 마지막으로 실행된 시간
SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,
autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count 
FROM pg_stat_user_tables
ORDER BY 5 DESC;

pg_stat_activity 뷰를 사용하여 현재 활동과 관련된 데이터를 찾을 수 있습니다. 이 보기는 백엔드 pid, 쿼리 및 기타 세부 정보를 제공합니다. 장기 실행 쿼리를 찾으려면 다음 쿼리를 실행합니다.

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU' 
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

잠금을 기다리는 쿼리는 느릴 수 있습니다. 따라서 다음 쿼리를 실행하여 쿼리가 잠금을 기다리고 있는지 확인합니다.

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

PostgreSQL용 RDS 인스턴스를 사용하면 데이터베이스 내에서 pg_stat_statement 확장을 생성할 수 있습니다.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements를 사용하여 쿼리 통계를 볼 수 있습니다. 확장을 만들기 전 shared_preload_libraries에 pg_stat_statements 항목을 추가해야 합니다.

참고: 사용자 지정 파라미터 그룹이 DB 인스턴스에 연결된 경우에만 이 모듈의 파라미터를 수정할 수 있습니다.

이 쿼리를 사용하여 인스턴스의 성능에 영향을 주는 최상위 SQL 쿼리를 식별할 수 있습니다.

데이터베이스에서 더 많은 시간을 소비하는 쿼리를 찾으려면 PostgreSQL 버전 12 이하에 대해 다음 쿼리를 실행합니다.

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL 버전 13 이상에 대해 다음 쿼리를 실행합니다.

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

버퍼 캐시 적중률이 낮은 쿼리를 나열하려면 PostgreSQL 버전 12 이하에 대해 다음 쿼리를 실행합니다.

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time
DESC LIMIT 10;

PostgreSQL 버전 13 이상에 대해 다음 쿼리를 실행합니다.

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +
shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

인데이터베이스 오류 로그에 장기 실행 쿼리 또는 쿼리 계획을 캡처하려면, 인스턴스에 대해 log_min_duration_statement 파라미터를 적절히 설정한 다음 auto_explain 모듈을 사용합니다. log_min_duration_statement 파라미터를 사용하면 문이 최소한 지정된 시간 동안 실행된 경우 완료된 각 문의 기간이 기록됩니다. 예를 들어 이 파라미터를 250ms로 설정하면, 250ms 이상 실행되는 모든 SQL 문이 기록됩니다. auto_explain 모듈을 사용하면 데이터베이스에서 실행되는 쿼리의 실행 설명 계획을 캡처할 수 있습니다.

explain 및 explain analyze 명령을 사용하여 계획을 캡처할 수도 있습니다. 쿼리에 대한 auto_explain 모듈 또는 explain 명령에 기반한 쿼리 조정 기회를 식별합니다. 자세한 내용은 EXPLAIN 사용에 대한 PostgreSQL 설명서를 참조하십시오.

시스템이 제대로 튜닝되었는데도 여전히 리소스 제한이 발생하는 경우, 인스턴스 클래스를 상향 조정하는 것을 고려해 보십시오. 인스턴스 클래스를 상향 조정하여 더 많은 컴퓨팅 및 메모리 리소스로 DB 인스턴스를 할당합니다. 자세한 내용은 DB 인스턴스 클래스의 하드웨어 사양을 확인하세요.

관련 정보

Amazon RDS 또는 Amazon Aurora PostgreSQL-호환 가능 버전의 높은 CPU 사용률 문제를 해결하려면 어떻게 해야 하나요?

PostgreSQL DB 인스턴스용 RDS에서 파라미터를 사용한 작업

메모리가 충분한데도 Amazon RDS DB 인스턴스에서 스왑 메모리를 사용하는 이유는 무엇입니까?

주제
데이터베이스마이그레이션 및 현대화분석
태그
PostgreSQLAmazon Relational Database Service
언어
한국어
AWS 공식
AWS 공식업데이트됨 일 년 전
댓글 없음

관련 콘텐츠