내용으로 건너뛰기

Amazon RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 DB 인스턴스에서 쿼리가 차단된 원인을 파악하려면 어떻게 해야 합니까?

3분 분량
0

Amazon Relational Database Service(Amazon RDS) for PostgreSQL 또는 Amazon Aurora PostgreSQL 호환 버전 데이터베이스(DB) 인스턴스에서 차단된 쿼리 문제를 해결하려고 합니다.

해결 방법

커밋되지 않은 트랜잭션으로 인해 새 쿼리가 차단되거나 절전 모드로 전환되거나, 잠금 대기 제한 시간 또는 명령문 제한 시간을 초과하면 쿼리가 실패할 수 있습니다. 이 문제를 해결하려면 커밋되지 않은 트랜잭션을 식별하고 중지하십시오.

차단된 트랜잭션 식별

차단된 트랜잭션을 식별하려면 Performance Insights를 활성화하고 Database Insights를 사용하십시오. 데이터베이스 로드 차트를 사용하여 대기, 호스트, SQL 또는 사용자에 대한 데이터베이스 로드 차원을 확인합니다. 자세한 내용은 Amazon RDS의 Performance Insights를 통한 DB 로드 모니터링을 참조하십시오.

차단된 트랜잭션의 현재 상태를 확인하려면 다음 명령문을 실행합니다.

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

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

wait\ _event\ _type 열의 값이 잠금이 아닌 경우 CPU, 스토리지 또는 네트워크 용량과 같은 리소스에 성능 병목이 발생한 것입니다. 성능 병목을 해결하려면 데이터베이스의 성능을 조정하십시오. 예를 들어 인덱스를 추가하거나 쿼리를 다시 작성하거나 진공 및 분석 명령을 실행할 수 있습니다. 자세한 내용은 PostgreSQL 작업 모범 사례를 참조하십시오.

wait_event_type 열의 값이 잠금인 경우 다른 트랜잭션이나 쿼리가 해당 쿼리를 차단합니다. 차단된 트랜잭션의 원인을 파악하려면 다음 명령문을 실행합니다.

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;

트랜잭션을 차단하는 세션을 확인하려면 blocking_user, blocking_user_addr 및 blocking_client_port를 검토하십시오. 다음 예제 출력에서 마스터 사용자는 psql을 사용하여 27.0.3.146 호스트에서 실행 중인 트랜잭션을 차단합니다. blocking_pid는 8740입니다.

blocked_pid                           | 9069blocked_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;

트랜잭션 중지

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

트랜잭션을 중지하려면 다음 명령문을 실행합니다.

SELECT pg_terminate_backend(PID);

참고: PID를 트랜잭션 blocking_pid로 바꿉니다.

관련 정보

Amazon Aurora PostgreSQL 대기 이벤트

AWS 공식업데이트됨 일 년 전