Amazon RDS PostgreSQL 또는 Aurora PostgreSQL을 실행하는 DB 인스턴스에서 쿼리를 차단하는 항목을 식별하려면 어떻게 해야 합니까?

최종 업데이트 날짜: 2019년 11월 7일

Amazon Relational Database Service(Amazon RDS) PostgreSQL 또는 Amazon Aurora PostgreSQL을 실행하는 DB 인스턴스에서 쿼리를 실행하려고 했습니다. 하지만 다른 쿼리가 동시에 실행되고 있지 않은데도 쿼리가 차단되었습니다. 쿼리가 차단된 이유는 무엇이며 이 문제를 해결하려면 어떻게 해야 합니까?

​해결 방법

대부분 차단된 쿼리는 커밋되지 않은 트랜잭션으로 인해 발생합니다. 커밋되지 않은 트랜잭션으로 인해 새 쿼리는 차단되고, 유휴 상태가 되며, 잠금 대기 시간 초과 또는 문 제한 시간을 초과하면 결국 실패할 수 있습니다. 이 문제를 해결하려면 먼저 차단 트랜잭션을 식별한 다음 차단 트랜잭션을 중지합니다.

1.    pg_stat_activity 테이블에 대해 다음 쿼리를 실행하여 차단된 트랜잭션의 현재 상태를 식별합니다.

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

참고: TABLE NAME을 자체 테이블 이름 또는 조건으로 바꿉니다.

wait_event_type 열의 값이 Lock인 경우 다른 트랜잭션 또는 쿼리에 의해 쿼리가 차단됩니다. wait_event_type 열에 다른 값이 있으면 CPU, 스토리지 또는 네트워크 용량과 같은 리소스에 성능 병목 현상이 발생합니다. 성능 병목 현상을 해결하려면 인덱스를 추가하거나, 쿼리를 다시 작성하거나, vacuum 및 분석을 실행하여 데이터베이스의 성능을 튜닝합니다. 자세한 내용은 PostgreSQL로 작업하기 위한 모범 사례를 참조하십시오.

DB 인스턴스에서 성능 개선 도우미를 활성화한 경우 대기 이벤트, 호스트, SQL 쿼리 또는 사용자별로 그룹화된 DB 로드를 확인하여 차단된 트랜잭션을 식별할 수도 있습니다. 자세한 내용은 Amazon RDS 성능 개선 도우미 사용을 참조하십시오.

2.    wait_event_type 열의 값이 Lock인 경우 다음을 실행하여 차단된 트랜잭션의 원인을 식별할 수 있습니다.

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocked_activity.client_addr as blocked_client_addr,
       blocked_activity.client_hostname as blocked_client_hostname,
       blocked_activity.client_port as blocked_client_port,
       blocked_activity.application_name as blocked_application_name,
       blocked_activity.wait_event_type as blocked_wait_event_type,
       blocked_activity.wait_event as blocked_wait_event,
       blocked_activity.query    AS blocked_statement,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocking_activity.client_addr as blocking_user_addr,
       blocking_activity.client_hostname as blocking_client_hostname,
       blocking_activity.client_port as blocking_client_port,
       blocking_activity.application_name as blocking_application_name,
       blocking_activity.wait_event_type as blocking_wait_event_type,
       blocking_activity.wait_event as blocking_wait_event,
       blocking_activity.query   AS current_statement_in_blocking_process
 FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

3.    blocking 접두사가 있는 열을 검토합니다. 이 쿼리에 의해 생성된 다음 예제 테이블에서 차단된 트랜잭션이 27.0.3.146 호스트에서 실행되고 psql을 사용하는 것을 확인할 수 있습니다.

blocked_pid                           | 9069
blocked_user                          | master
blocked_client_addr                   | 27.0.3.146
blocked_client_hostname               |
blocked_client_port                   | 50035
blocked_application_name              | psql
blocked_wait_event_type               | Lock
blocked_wait_event                    | transactionid
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;
blocking_pid                          | 8740
blocking_user                         | master
blocking_user_addr                    | 27.0.3.146
blocking_client_hostname              |
blocking_client_port                  | 26259
blocking_application_name             | psql
blocking_wait_event_type              | Client
blocking_wait_event                   | ClientRead
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

팁: blocking_user, blocking_user_addrblocking_client_port를 사용하여 트랜잭션을 차단하는 세션을 식별할 수 있습니다.

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

4.    각 트랜잭션의 잠재적 영향을 검토한 후 다음 쿼리를 실행하여 트랜잭션을 중지합니다.

SELECT pg_terminate_backend(PID);

참고: PID를 3단계에서 확인한 프로세스의 blocking_pid로 바꿉니다.


잠금 보기에 대한 PostgreSQL 설명서

서버 신호 함수에 대한 PostgreSQL 설명서

wait_event 설명에 대한 PostgreSQL 설명서

잠금 모니터링에 대한 PostgreSQL Wiki

Amazon Aurora PostgreSQL 이벤트

이 문서가 도움이 되었습니까?

AWS에서 개선해야 할 부분이 있습니까?


도움이 필요하십니까?