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

간략한 설명

로드에서 CPU 사용률이 높으면 다음 도구를 조합해 사용하여 원인을 식별할 수 있습니다.

• Amazon CloudWatch 지표
• Enhanced Monitoring 지표
• 성능 개선 도우미 지표
• 네이티브 PostgreSQL 보기 및 카탈로그(예: pg_stat_statements, pg_stat_activity, pg_stat_user_tables)
• 데이터베이스의 유휴 연결
• Analyze 명령
• 장기 실행 쿼리, autovacuum, 잠금 대기, 연결, 연결 해제 요청을 로깅하기 위한 PostgreSQL 로깅 파라미터 자세한 내용은 Amazon RDS for PostgreSQL을 사용하여 쿼리 로깅을 활성화하려면 어떻게 해야 하나요?를 참조하세요.

해결 방법

Amazon CloudWatch 지표

CloudWatch 지표를 사용하여 장기간에 걸쳐 CPU 패턴을 식별할 수 있습니다. WriteIOPs, ReadIOPs, ReadThroughput, WriteThroughput 그래프를 CPU 사용률과 비교하여 워크로드에서 CPU를 많이 사용하는 시점을 알아냅니다.

시간대를 식별한 후에 DB 인스턴스에 연결된 Enhanced Monitoring 데이터를 검토할 수 있습니다. 1, 5, 10, 15, 30, 60초 간격으로 데이터를 수집하도록 Enhanced Monitoring을 설정할 수 있습니다. 이렇게 하면 CloudWatch보다 더욱 짧은 간격으로 데이터를 수집할 수 있습니다.

Enhanced Monitoring

Enhanced Monitoring은 운영 체제(OS) 수준에서 보기를 제공합니다. 이 보기는 높은 CPU 부하의 원인을 세분화된 수준에서 식별하는 데 도움이 될 수 있습니다. 예를 들어, 로드 평균, CPU 분산(system% 또는 nice%) 및 OS 프로세스 목록을 검토할 수 있습니다.

Enhanced Monitoring을 사용하면 1분, 5분, 15분 간격으로 loadAverageMinute 데이터를 확인할 수 있습니다. 로드 평균이 vCPU 수보다 크면 인스턴스에 로드가 많음을 나타냅니다. 또한 로드 평균이 DB 인스턴스 클래스의 vCPU 개수보다 적다면 CPU 제한이 애플리케이션에 지연 시간이 발생하는 원인이 아닐 수 있습니다. 로드 평균을 확인하여 CPU 사용률 원인을 진단할 때 거짓 긍정을 방지합니다.

예를 들어, CPU 제한에 도달하는 프로비저닝된 IOPS가 3000인 db.m5.2xlarge 인스턴스 클래스를 사용하는 DB 인스턴스가 있다고 가정해 보겠습니다. 그런 다음, 다음 예제 지표를 검토하여 높은 CPU 사용량의 근본 원인을 식별할 수 있습니다. 다음 예제에서는 인스턴스 클래스에 8개의 vCPU가 연결되어 있습니다. 동일한 로드 평균에서, 170을 초과하는 경우 측정된 시간대에 컴퓨터에 로드가 많음을 나타냅니다.

로드 평균 시간(분)

15	170.25
5	391.31
1	596.74

CPU 사용률

User(%)	0.71
System(%)	4.9
Nice(%)	93.92
Total(%)	99.97

참고: Enhanced Monitoring에서 **Nice%**는 데이터베이스에서 워크로드가 사용하고 있는 CPU 용량을 나타냅니다.

Enhanced Monitoring을 활성화한 후에 DB 인스턴스에 연결된 OS 프로세스 목록을 확인할 수도 있습니다. Enhanced Monitoring은 최대 100개의 프로세스를 보여줍니다. CPU와 메모리 사용량에 따라 어느 프로세스가 성능에 가장 큰 영향을 미치는지 파악하는 데 도움이 됩니다.

Enhanced Monitoring 결과와 pg_stat_activity 결과를 결합하면 쿼리의 리소스 사용률을 확인하는 데 도움이 됩니다.

성능 개선 도우미

Amazon RDS 성능 개선 도우미를 사용하여 데이터베이스 부하를 담당하는 쿼리를 식별할 수 있습니다. 특정 시간대에 해당하는 SQL 탭을 확인하여 이를 수행할 수 있습니다.

네이티브 PostgreSQL 보기 및 카탈로그

데이터베이스 엔진 수준에서, 실시간으로 문제가 발생하면 pg_stat_activity 또는 pg_stat_statements를 사용할 수 있습니다. 이를 통해 컴퓨터, 클라이언트 및 대부분의 트래픽을 전송하는 IP 주소를 그룹화할 수 있습니다. 또한 이 데이터를 사용하여 시간 경과에 따른 증가 또는 애플리케이션 서버의 증가를 확인할 수 있습니다. 또한 애플리케이션 서버에 세션 중단이나 잠금 문제가 있는지 확인할 수 있습니다. 자세한 내용은 pg_stat_activity와 pg_stat_statements에 대한 PostgreSQL 설명서를 참조하세요.

pg_stat_statements를 활성화하려면 기존의 사용자 지정 파라미터 그룹을 수정하여 다음 값을 설정합니다.

• pg_stat_statements를 shared_preload_libraries에 추가
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

[즉시 적용]을 선택하고 DB 인스턴스를 재부팅합니다. 그런 다음 모니터링하려는 데이터베이스에서 다음과 비슷한 명령을 실행합니다.

참고: 다음 예제에서는 "demo" 데이터베이스에서 확장 기능을 설치합니다.

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

pg_stat_statements를 설정한 후에 다음 방법 중 하나를 사용하여 출력을 모니터링할 수 있습니다.

total_time별로 쿼리를 나열하고 PostgreSQL 버전 12 이하의 데이터베이스에서 가장 많은 시간을 소비하는 쿼리를 확인합니다.

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 버전 13 이상의 경우:

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

버퍼 캐시 적중률이 낮은 쿼리를 나열하고 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 3 DESC LIMIT 10;

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_written
FROM 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 유휴 연결이 성능에 미치는 영향을 참조하세요. Enhanced Monitoring을 사용해 OS 프로세스 목록을 검토하여 유휴 연결을 확인할 수 있습니다. 그러나 이 목록에는 최대 100개의 프로세스가 표시됩니다.

데이터베이스 수준에서 몇 가지 쿼리를 실행하여 유휴 연결을 확인할 수 있습니다.

다음 쿼리를 실행하여 현재 세션의 유휴 및 활성 상태를 확인합니다.

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, query
FROM pg_stat_activity
WHERE 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_stat 시스템 카탈로그에 저장합니다. 그런 다음 쿼리 플래너는 이러한 통계를 사용하여 쿼리에 대한 가장 효율적인 실행 계획을 결정하는 데 도움을 줍니다. 데이터베이스의 테이블에서 Analyze를 자주 실행하지 않는 경우 쿼리에서 더 많은 컴퓨팅 리소스를 사용할 수도 있습니다. 이는 시스템에 액세스하는 관계에 대한 오래된 통계가 있기 때문입니다. 이러한 오류는 다음과 같은 조건에서 발생합니다.

• autovacuum이 자주 실행되지 않습니다.
• 메이저 버전 업그레이드 후 Analyze가 실행되지 않았습니다.

autovacuum이 실행되고 있지 않음: autovacuum은 VACUUM 및 ANALYZE 명령의 실행을 자동화하는 대몬(daemon)입니다. autovacuum은 데이터베이스에서 비대한 테이블을 확인하고 재사용을 위해 공간을 회수합니다. autovacuum 대몬(daemon)은 튜플의 설정된 임계값이 소진될 때마다 Analyze 작업을 실행하여 테이블 통계가 정기적으로 업데이트되도록 합니다. 이렇게 하면 쿼리 플래너가 최근 통계를 기반으로 가장 효율적인 쿼리 계획을 사용할 수 있습니다. autovacuum이 실행되고 있지 않으면 쿼리 플래너가 최적이 아닌 쿼리 계획을 생성하여 쿼리에서 리소스를 더 많이 사용할 수도 있습니다. 자세한 내용은 다음을 참조하세요.

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

다음 쿼리를 실행하여 테이블에서 autovacuum 및 autoanalyze가 마지막으로 실행된 시점에 대한 정보를 가져옵니다.

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

메이저 버전 업그레이드 후 Analyze가 실행되지 않음: PostgreSQL 데이터베이스는 일반적으로 메이저 엔진 버전 업그레이드 후 성능 문제가 발생합니다. 이러한 문제의 일반적인 원인은 pg_statistic 테이블을 새로 고치기 위해 업그레이드 후 Analyze 작업이 수행되지 않기 때문입니다. PostgreSQL DB 인스턴스용 RDS의 모든 데이터베이스에 대해 Analyze 작업을 실행해야 합니다. 메이저 버전 업그레이드 중에는 Optimizer 통계가 전송되지 않습니다. 따라서 리소스 사용률 증가로 인한 성능 문제를 방지하려면 모든 통계를 다시 생성해야 합니다.

메이저 버전 업그레이드 후 파라미터 없이 다음 명령을 실행하여 현재 데이터베이스의 모든 일반 테이블에 대한 통계를 생성합니다.

ANALYZE VERBOSE

PostgreSQL 로깅 파라미터

Amazon RDS for PostgreSQL을 사용하여 쿼리 로깅을 활성화합니다. 그런 다음, PostgreSQL 오류 로그에서 log_min_duration_statement와 log_statement 파라미터가 적절한 값으로 설정되었는지 확인합니다. 자세한 내용은 오류 보고 및 로깅에 대한 PostgreSQL 설명서를 참조하세요.

CPU 사용률 감소

CPU를 많이 사용하는 쿼리를 찾아낸 후, 다음의 방법을 사용하여 CPU 사용량을 더욱 낮출 수 있습니다.

1. 조정 가능성을 찾기 위해 EXPLAIN 및 EXPLAIN ANALYZE를 사용하여 주의 사항을 알아냅니다. 자세한 내용은 EXPLAIN에 대한 PostgreSQL 설명서를 참조하세요.
2. 반복해서 실행하는 쿼리가 있는 경우 준비된 문을 사용하여 CPU의 부담을 줄이는 것이 좋습니다. 준비된 문을 반복적으로 실행하면 쿼리 계획이 캐시됩니다. 따라서 계획이 이미 캐시되었으므로 추가 실행을 위한 계획 시간이 훨씬 단축됩니다.

---

관련 정보

PostgreSQL 작업 모범 사례