Amazon RDS 또는 Aurora PostgreSQL DB 인스턴스에 대해 실행 중인 쿼리를 확인하고 리소스 소비 문제를 진단하려면 어떻게 해야 하나요?

해결 방법

실행 중인 쿼리 확인

RDS for PostgreSQL 또는 Aurora PostgreSQL의 DB 인스턴스에서 실행 중인 모든 프로세스를 확인하려면 사용자 계정에 rds_superuser 역할을 부여해야 합니다. 그렇지 않으면, pg_stat_activity에서는 자체 프로세스에 대해 실행 중인 쿼리만 표시합니다. 자세한 내용은 PostgreSQL 설명서에서 The Statistics Collector를 참조하세요.

1.    PostgreSQL 또는 Aurora PostgreSQL을 실행하는 DB 인스턴스에 연결합니다.

2.    다음 명령을 실행합니다.

SELECT * FROM pg_stat_activity ORDER BY pid;

연결을 설정한 시점을 기준으로 정렬된, 실행 중인 쿼리 목록을 보도록 이 명령을 수정할 수도 있습니다.

SELECT * FROM pg_stat_activity ORDER BY backend_start;

열 값이 null이면 해당 세션에 열린 트랜잭션이 없습니다.

SELECT * FROM pg_stat_activity ORDER BY xact_start;

또는 마지막 쿼리를 실행한 시점을 기준으로 정렬된, 동일한 실행 중인 쿼리 목록을 표시합니다.

SELECT * FROM pg_stat_activity ORDER BY query_start;

대기 이벤트의 집계 보기(있는 경우)에 대해서는 다음 명령을 실행합니다.

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

리소스 소비 진단

pg_stat_activity를 사용하고 확장 모니터링을 활성화하여 많은 시스템 리소스를 소비하는 쿼리/프로세스를 식별할 수 있습니다. 확장 모니터링을 활성화한 후에 문제를 진단하는 데 필요한 정보를 확인하기에 세부 수준 세트가 충분한지 확인합니다. 그런 다음, pg_stat_activity를 확인하여 데이터베이스의 현재 활동과 해당 시점에 확장 모니터링 지표를 볼 수 있습니다.

1.    OS 프로세스 목록 지표를 확인하여 리소스를 소비하는 쿼리를 식별합니다. 다음 예제에서는 RDS DB 인스턴스에서 프로세스가 CPU 시간의 약 95%를 소비합니다. 프로세스의 ID(pid)는 14431이고 프로세스는 SELECT문을 실행합니다. MEM%를 확인하여 시스템 메모리 사용량을 확인할 수도 있습니다.

NAME	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: master postgres 27.0.3.145(52003) SELECT [14431]	457.66 MB	27.7 MB	95.15	2.78	unlimited

2.    PostgreSQL 또는 Aurora PostgreSQL을 실행하는 DB 인스턴스에 연결합니다.

3.    다음 명령을 실행하여 세션의 현재 활동을 식별합니다.

SELECT * FROM pg_stat_activity WHERE pid = PID;

참고: PID를 1단계에서 식별한 pid로 바꿉니다.

4.    명령의 결과를 확인합니다.

datid            | 14008
datname          | postgres
pid              | 14431
usesysid         | 16394
usename          | master
application_name | psql
client_addr      | 27.0.3.145
client_hostname  |
client_port      | 52003
backend_start    | 2020-03-11 23:08:55.786031+00
xact_start       | 2020-03-11 23:12:16.960942+00
query_start      | 2020-03-11 23:12:16.960942+00
state_change     | 2020-03-11 23:12:16.960945+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 812
query            | SELECT COUNT(*) FROM columns c1, columns c2, columns c3, columns c4, columns c5;
backend_type     | client backend

쿼리를 실행하는 프로세스를 중지하려면 다른 세션에서 다음 쿼리를 호출합니다. PID를 3단계에서 식별한 프로세스의 pid로 바꿉니다.

SELECT pg_terminate_backend(PID);

중요: 트랜잭션을 종료하기 전에 각 트랜잭션이 데이터베이스와 애플리케이션의 상태에 미치는 잠재적 영향을 평가합니다.

---

관련 정보

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

역할 생성

Enhanced Monitoring

PostgreSQL 설명서의 psql

PostgreSQL 설명서의 pg_stat_activity