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

간략한 설명

높은 CPU 사용량의 원인을 파악하려면 다음 도구를 사용하십시오.

• Amazon CloudWatch 지표
• 향상된 모니터링 지표
• Performance Insights 지표
• 네이티브 PostgreSQL 뷰 및 카탈로그(예: pg_stat_statements, pg_stat_activity 및 pg_stat_user_tables)
• 데이터베이스의 유휴 연결
• PostgreSQL 웹 사이트의 ANALYZE 명령
• 장기 실행 쿼리, autovacuum, 잠금 대기, 연결 및 연결 해제 요청을 기록하기 위한 PostgreSQL 로깅 파라미터

해결 방법

CloudWatch 지표 검토

CloudWatch 지표를 사용하여 장기간에 걸친 CPU 패턴을 식별할 수 있습니다. WriteIOPs, ReadIOPs, ReadThroughput 및 WriteThroughput 그래프를 CPU 사용률과 비교하여 워크로드로 인해 높은 CPU가 발생한 시간을 확인할 수 있습니다.

기간을 확인한 후 DB 인스턴스와 연결된 향상된 모니터링 데이터를 검토하십시오. 향상된 모니터링의 수집 간격을 1, 5, 10, 15, 30 또는 60초로 설정하여 보다 세분화된 수준에서 데이터를 수집할 수 있습니다.

향상된 모니터링 사용

향상된 모니터링은 운영 체제(OS) 수준의 뷰를 제공합니다. 예를 들어 평균 워크로드, CPU 분포(System% 또는 Nice%) 및 OS 프로세스 목록을 검토할 수 있습니다. 자세한 내용은 OS 모니터링을 참조하십시오.

1, 5, 15분 간격으로 loadAverageMinute 데이터를 확인할 수 있습니다. 평균 로드가 vCPU 수보다 크면 인스턴스에 과부하가 발생한 것입니다. 평균 로드가 DB 인스턴스 클래스의 vCPU 수보다 적을 경우 CPU 스로틀링으로 인해 애플리케이션 지연 시간이 발생하지 않을 수 있습니다. 높은 CPU 사용량의 원인을 해결할 때는 평균 로드를 확인하여 오탐이 발생하지 않도록 하십시오.

예를 들어, 프로비저닝된 IOPS가 3000인 db.m5.2xlarge 인스턴스 클래스를 사용하는 DB 인스턴스가 CPU 할당량에 도달했습니다. 8개의 vCPU가 인스턴스 클래스와 연결되어 있습니다. 동일한 평균 로드가 170을 초과하면 측정된 기간 동안 기계에 과부하가 발생한 것입니다.

평균 로드 시간(분):

• 15: 170.25
• 5: 391.31
• 1: 596.74

CPU 사용률:

• 사용자(%): 0.71
• 시스템(%): 4.9
• 양호함(%): 93.92
• 합계(%): 99.97

참고: 향상된 모니터링에서 **Nice%**는 워크로드가 데이터베이스에 대해 사용하는 CPU의 양입니다.

향상된 모니터링을 활성화한 후 DB 인스턴스와 연결된 OS 프로세스 목록도 확인할 수 있습니다. 향상된 모니터링을 통해 성능에 영향을 미치는 프로세스를 최대 100개까지 식별할 수 있습니다. 향상된 모니터링 결과를 pg_stat_activity 결과와 함께 사용하면 쿼리의 리소스 사용량을 식별하는 데 도움이 됩니다.

Performance Insights 사용

Amazon RDS Performance Insights를 사용하여 데이터베이스 로드의 원인이 되는 쿼리를 식별할 수 있습니다. 특정 기간에 해당하는 SQL 탭을 확인합니다.

네이티브 PostgreSQL 뷰 및 카탈로그 확인

데이터베이스 엔진 수준에서는 pg_stat_activity 및 pg_stat_statements를 사용할 수 있습니다. 문제가 실시간으로 발생하는 경우 pg_stat_activity 또는 pg_stat_statements를 사용하여 가장 많은 트래픽을 보내는 시스템, 클라이언트 및 IP 주소를 그룹화하십시오.

데이터를 사용하여 시간 경과에 따른 증가 또는 애플리케이션 서버의 증가를 확인할 수 있습니다. 애플리케이션 서버의 세션 중단이나 잠금 문제가 있는지도 확인할 수 있습니다. 자세한 내용은 PostgreSQL 웹 사이트의 pg_stat_activity 및 pg_stat_statements를 참조하십시오.

pg_stat_statements를 활성화하려면 다음 단계를 완료하십시오.

1. 기존 사용자 지정 DB 파라미터 그룹을 수정합니다.

2. pg_stat_statements를 shared_preload_libraries에 추가합니다.

3. track_activity_query_size를 4096으로 설정합니다.

4. pg_stat_statements.track을 ALL로 설정합니다.

5. pg_stat_statements.max를 10000으로 설정합니다.

6. 즉시 적용을 선택한 다음, DB 인스턴스를 재부팅합니다.

7. 모니터링하려는 데이터베이스에서 다음 명령을 실행합니다.

   demo=> select current_database();current_database------------------  
   demo  
   (1 row)  
   
   demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

   참고: 위 명령은 데모 데이터베이스에 확장 프로그램을 설치합니다.

pg_stat_statements를 설정한 후 다음 방법 중 하나를 사용하여 출력을 모니터링합니다. 데이터베이스에서 가장 많은 시간을 소비하거나, 버퍼 캐시 적중률이 낮거나, 실행별로 표시되는 쿼리를 볼 수 있습니다.

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

PostgreSQL 버전 12 이하:

SELECT total_time, queryFROM pg_stat_statements  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 버전 13 이상:

SELECT total_plan_time+total_exec_time as total_time, queryFROM pg_stat_statements  
ORDER BY 1 DESC LIMIT 10;

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

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_percentFROM pg_stat_statements   
ORDER BY 3 DESC LIMIT 10;

실행별로 쿼리를 나열하여 시간 경과에 따른 쿼리를 샘플링하려면 PostgreSQL 버전에 대해 다음 쿼리를 실행합니다.

PostgreSQL 버전 12 이하:

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_writtenFROM pg_stat_statements  
WHERE calls != 0  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 버전 13 이상:

SELECT query,calls,  
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,   
 rows/calls as avg_rows,  
temp_blks_read/calls as avg_tmp_read,  
 temp_blks_written/calls as avg_temp_written  
FROM pg_stat_statements  
WHERE calls != 0  
ORDER BY 3 DESC LIMIT 10;

데이터베이스의 유휴 연결 확인

데이터베이스의 유휴 연결은 메모리 및 CPU와 같은 컴퓨팅 리소스를 사용할 수 있습니다. 인스턴스의 CPU 사용률이 높으면 데이터베이스의 유휴 연결을 확인하십시오. 자세한 내용은 유휴 PostgreSQL 연결이 성능에 미치는 영향을 참조하십시오.

향상된 모니터링을 사용하여 유휴 연결에 대한 OS 프로세스 목록을 검토할 수 있습니다. 그러나 목록에는 최대 100개의 프로세스만 표시됩니다. 데이터베이스 수준에서 유휴 연결을 확인하려면 다음 쿼리를 실행합니다.

유휴 및 활성 상태인 현재 세션을 확인합니다.

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activityWHERE query != '<IDLE>  
'AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin'  
ORDER BY query_start desc;  
SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query  
FROM pg_stat_activity  
WHERE not pid=pg_backend_pid()  
AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin';

각 사용자 및 애플리케이션 이름의 연결 수를 가져옵니다.

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;    application_name    | count   
------------------------+-------  
 psql                   |     1  
 PostgreSQL JDBC Driver |     1   
                        |     5  
(3 rows)  
postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;  
 usename  | count  
----------+-------  
 master   |     4   
 user1    |     1  
 rdsadmin |     2  
(3 rows)

유휴 연결을 확인한 후 다음 쿼리 중 하나를 실행하여 연결을 종료합니다.

psql=> SELECT pg_terminate_backend(pid)   FROM pg_stat_activity  
   WHERE usename = 'example-username'  
   AND pid <> pg_backend_pid()  
   AND state in ('idle');

-또는-

SELECT pg_terminate_backend (example-pid);

애플리케이션으로 인해 연결이 너무 많으면 메모리 및 CPU 리소스가 이러한 연결을 관리하지 않도록 애플리케이션을 수정하십시오. 연결 수를 제한하거나 PgBouncer와 같은 연결 풀러를 사용할 수 있습니다. Amazon RDS Proxy를 사용하여 연결 풀을 설정할 수도 있습니다.

ANALYZE 명령 실행

ANALYZE 명령은 데이터베이스의 테이블 내용에 대한 통계를 수집하고 그 결과를 pg_statistic 시스템 카탈로그에 저장합니다. 그런 다음, 쿼리 플래너는 통계를 사용하여 쿼리의 가장 효율적인 실행 계획을 결정하는 데 도움을 줍니다. 데이터베이스의 테이블에서 ANALYZE를 자주 실행하지 않으면 시스템의 기한 경과 통계로 인해 쿼리가 더 많은 컴퓨팅 리소스를 사용할 수 있습니다.

기한 경과 통계는 다음과 같은 이유로 발생합니다.

• Autovacuum이 자주 실행되지 않습니다.
• 메이저 버전 업그레이드 후 ANALYZE 작업을 실행하지 않았습니다.

Autovacuum은 데이터베이스에서 비대해진 테이블을 검사하고 재사용을 위한 공간을 확보합니다. 테이블 통계가 정기적으로 업데이트되도록 하기 위해 autovacuum 대몬은 설정된 튜플 임계값이 만료되었을 때 ANALYZE 명령을 실행합니다.

자세한 내용은 다음 리소스를 참조하십시오.

• Amazon RDS for PostgreSQL 환경의 autovacuum 이해
• Amazon RDS for PostgreSQL에서 autovacuum 튜닝 사례 연구

테이블에서 autovacuum 및 autoanalyze를 마지막으로 실행한 시기를 확인하려면 다음 쿼리를 실행합니다.

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

메이저 엔진 버전 업그레이드 후 성능 문제를 방지하려면 ANALYZE 명령을 실행하여 pg_statistic 테이블을 새로 고칩니다. RDS for PostgreSQL DB 인스턴스의 모든 데이터베이스에 대해 ANALYZE 명령을 실행합니다.

리소스 사용률 증가로 인한 성능 문제를 방지하려면 모든 통계를 재생성하십시오. 메이저 버전 업그레이드 후 현재 데이터베이스의 모든 일반 테이블에 대한 통계를 생성하려면 파라미터 없이 다음 명령을 실행합니다.

ANALYZE VERBOSE

PostgreSQL 오류 로그 확인

Amazon RDS를 사용하여 PostgreSQL에 대한 쿼리 로깅을 활성화합니다. 그런 다음, PostgreSQL 오류 로그를 확인하여 log_min_duration_statement 및 log_statement 파라미터를 적절한 값으로 설정했는지 확인합니다. 자세한 내용은 PostgreSQL 웹 사이트의 오류 보고 및 로깅을 참조하십시오.

CPU 사용량 줄이기

높은 CPU를 유발하는 쿼리를 식별한 후 다음 방법을 사용하여 CPU 사용량을 더 줄이십시오.

• EXPLAIN 및 EXPLAIN ANALYZE를 사용하여 쿼리 계획을 조정하는 방법을 파악합니다. 자세한 내용은 PostgreSQL 웹사이트의 EXPLAIN 사용을 참조하십시오.
• 반복적으로 실행되는 쿼리가 있는 경우 준비된 명령문을 사용하여 CPU에 가해지는 부하를 줄입니다. 반복적으로 실행하는 준비된 명령문은 쿼리 계획을 캐시합니다. 계획이 추가 실행을 위해 이미 캐시에 저장되어 있으면 쿼리를 계획할 시간이 줄어듭니다.

관련 정보

PostgreSQL 작업 모범 사례