Amazon RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 DB 인스턴스에서 성능 문제 및 실행 속도가 느린 쿼리를 파악하고 문제를 해결하려면 어떻게 해야 합니까?

해결 방법

작은 크기의 하드웨어, 워크로드 변경, 트래픽 증가, 메모리 문제 또는 최적화되지 않은 쿼리는 DB 인스턴스 성능에 영향을 미칩니다. 성능 문제를 해결하려면 다음과 같은 조치를 취하십시오.

원인 파악

CloudWatch 지표 확인

리소스 부족으로 인한 성능 병목 지점을 파악하려면 CPUUtilization, FreaableMemory 및 SwapUsage Amazon CloudWatch 지표를 모니터링하십시오.

CPU 사용률이 높으면 DB 인스턴스의 활성 워크로드에 더 많은 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';

유휴 연결을 종료하려면 다음 명령을 실행하십시오.

SELECT pg_terminate_backend(example-pid);

참고: example-pid를 유휴 연결의 프로세스 ID로 바꾸십시오.

DB 인스턴스가 예상 네트워크 처리량에 도달했는지 확인하려면 NetworkReceiveThroughput 및 NetworkTransmitThroughput 지표를 확인하십시오. 크기가 작거나 최적화되지 않은 Amazon Elastic Block Service(Amazon EBS) 인스턴스 클래스는 네트워크 처리량에 영향을 주어 인스턴스 속도를 저하시킬 수 있습니다. 네트워크 처리량이 낮으면 데이터베이스 성능에 관계없이 모든 애플리케이션 요청에 대한 응답이 느려질 수 있습니다.

I/O 성능을 평가하려면 ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, ReadThroughput, WriteThroughput 및 DiskQueueDepth 지표를 확인하십시오. 자세한 내용은 Amazon RDS 인스턴스의 IOPS 병목 현상으로 인한 Amazon EBS 볼륨 지연 문제를 해결하려면 어떻게 해야 합니까?를 참조하십시오.

향상된 모니터링 사용

향상된 모니터링을 사용하여 운영 체제(OS) 수준에서 지표를 확인하고 높은 CPU 및 메모리를 사용하는 상위 100개 프로세스를 나열할 수 있습니다. 세부 수준을 1로 설정한 상태에서 향상된 모니터링을 활성화하여 DB 인스턴스에서 간헐적으로 발생하는 성능 문제를 파악하십시오.

사용 가능한 OS 지표를 평가하여 CPU, 워크로드, I/O, 메모리 및 네트워크와 관련된 성능 문제를 진단하십시오. 프로세스 목록에서 CPU% 또는 Mem% 값이 높은 프로세스를 확인합니다.

예:

이름	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250.66MB	27.7MB	85.93	2.21	무제한

데이터베이스에 연결한 후 다음 쿼리를 실행하여 데이터베이스에서 CPU 사용량이 높은 연결을 찾으십시오.

SELECT * FROM pg_stat_activity WHERE pid = 10322;

참고: 10322를 연결의 프로세스 ID로 바꾸십시오.

성능 개선 도우미 지표 확인

성능 개선 도우미를 사용하여 대기, SQL, 호스트 또는 사용자별로 데이터베이스 워크로드를 평가할 수 있습니다. 또한 데이터베이스 및 SQL 수준 지표를 가져올 수도 있습니다.

성능 개선 도우미 대시보드의 상위 SQL 탭을 사용하여 DB 로드에 가장 많이 기여하는 SQL 문을 볼 수 있습니다. DB 로드 또는 대기별 로드(AAS)가 최대 vCPU보다 높으면 DB 인스턴스 클래스의 워크로드가 제한됩니다.

SQL 통계의 호출당 평균 지연 시간을 사용하여 쿼리의 평균 실행 시간을 볼 수 있습니다. 상위 SQL은 총 실행 시간을 기반으로 합니다. 따라서 실행 시간이 가장 긴 SQL은 DB 로드 기여도가 가장 높은 SQL과 다른 경우가 많습니다.

데이터베이스 통계 확인

PostgreSQL에서 데이터베이스 성능을 평가하려면 데이터 분포 통계, 확장 통계 및 모니터링 통계를 확인하십시오. 통계에 대한 자세한 내용은 ](https://aws.amazon.com/blogs/database/understanding-statistics-in-postgresql/)PostgreSQL에서 통계에 대한 이해[를 참조하십시오.

기본 데이터베이스 도구 확인

느린 쿼리를 식별하려면 GitHub 웹사이트의 기본 pgbadger 도구를 사용하십시오. 자세한 내용은 기본 및 외부 도구를 기반으로 PostgreSQL용 Amazon RDS의 쿼리 최적화 및 조정을 참조하십시오.

성능 최적화

메모리 설정 조정

shared_buffers 파라미터를 쿼리 성능 향상에 도움이 되는 값으로 설정할 수 있습니다.

work_mem 및 maintenance_work_mem 파라미터는 백엔드 프로세스에 사용되는 메모리의 양을 정의합니다. 자세한 내용은 PostgreSQL 웹사이트에서 20.4 리소스 소비를 참조하십시오. DB 인스턴스의 메모리 사용량이 빈번하게 높아지는 경우 인스턴스에 연결된 사용자 지정 파라미터 그룹의 파라미터 값을 낮추십시오.

Aurora PostgreSQL 호환 쿼리 계획 관리 사용

Aurora PostgreSQL 호환 쿼리 계획 관리를 사용하여 쿼리 실행 계획 변경 방식과 시기를 제어할 수 있습니다. 자세한 내용은 Aurora PostgreSQL 쿼리 계획 관리 모범 사례를 참조하십시오.

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

인프라 문제, 최적화되지 않은 쿼리 계획 또는 높은 전체 리소스 사용량으로 인해 쿼리 실행 속도가 느려질 수 있습니다. PostgreSQL 쿼리 플래너는 테이블 통계를 사용하여 쿼리 계획을 생성합니다. 스키마 변경 및 이전 통계가 계획에 영향을 미칠 수 있습니다. 또한 테이블과 인덱스가 오버로드되면 쿼리 실행 속도가 느려질 수 있습니다.

테이블이 데드 튜플 임계값에 도달하면 autovacuum 데몬이 테이블에서 데드 튜플을 제거하는 autovacuum 워커 프로세스를 생성합니다. 또한 autovacuum 데몬은 테이블의 통계를 새로 고치는 ANALYZE 작업을 실행합니다.

다음 쿼리를 실행하여 데드 튜플과 autovacuum 또는 vacuum 작업 및 autoanalyze 또는 analyze 실행을 확인하십시오.

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 보기를 사용하여 백엔드 프로세스 ID 또는 쿼리 등의 현재 활동과 관련된 데이터를 찾을 수 있습니다. 장기 실행 쿼리를 찾으려면 다음 쿼리를 실행하십시오.

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;

쿼리 통계를 보려면 pg_stat_statements를 사용하십시오. pg_stat_statements 확장을 생성하기 전에 pg_stat_statements 항목을 shared_preload_libraries에 추가합니다. 데이터베이스 내에서 pg_stat_statements 확장을 생성하려면 다음 쿼리를 실행하십시오.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

참고: 사용자 지정 파라미터 그룹이 DB 인스턴스에 연결된 경우에만 pg_stats_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_percentFROM 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;

데이터베이스 오류 로그에서 장기 실행 쿼리 또는 쿼리 계획을 찾으려면 DB 인스턴스의 log_min_duration_statement 파라미터를 구성한 다음, auto_explain 모듈을 사용하십시오.

또한 explain 및 explain analyze 명령을 사용하여 쿼리 계획을 가져올 수도 있습니다. 쿼리 튜닝을 조정할 수 있는 방법을 확인하려면 auto_explain 모듈 또는 explain 명령을 사용하십시오. 자세한 내용은 PostgreSQL 웹사이트에서 14.1 EXPLAIN 사용 및 F3. auto_explain - 느린 쿼리의 로그 실행 계획을 참조하십시오.

시스템을 최적화했는데도 여전히 성능 문제가 발생하는 경우 DB 인스턴스 클래스를 스케일 업하는 것이 좋습니다. DB 인스턴스를 스케일 업하면 더 많은 컴퓨팅 및 메모리 리소스를 할당할 수 있습니다.

관련 정보

Amazon RDS 또는 Amazon Aurora PostgreSQL의 높은 CPU 사용률 문제를 해결하려면 어떻게 해야 합니까?

RDS for PostgreSQL DB 인스턴스에 대한 파라미터 작업

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