Amazon RDS PostgreSQL 또는 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 열의 값이 잠금인 경우 쿼리는 다른 트랜잭션이나 쿼리에 의해 차단됩니다. wait\ _event\ _type 열이 다른 값인 경우 CPU, 스토리지 또는 네트워크 용량과 같은 리소스에 성능 병목 현상이 발생합니다. 성능 병목 현상을 해결하려면 데이터베이스의 성능을 조정해야 합니다. 예를 들어 인덱스를 추가하거나 쿼리를 다시 작성하거나 진공 및 분석 명령을 실행할 수 있습니다. 자세한 내용은 PostgreSQL 함수 사용 모범 사례를 참조하세요.

Performance Insights를 켠 경우 대기 이벤트, 호스트, SQL 쿼리 또는 사용자별로 그룹화된 DB 부하를 확인하여 차단된 트랜잭션을 식별할 수 있습니다. 자세한 내용은 Amazon RDS의 성능 통찰력을 통한 DB 로드 모니터링을 참조하세요.

2.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;

3.차단 접두사가 있는 열을 검토합니다. 다음 예제 표에서는 차단된 트랜잭션이 27.0.3.146 호스트에서 실행되고 psql을 사용하는 것을 확인할 수 있습니다. blocking_user, blocking_user_addr, 및 blocking_client_port을 사용하면 트랜잭션을 차단하는 세션을 식별하는 데 도움이 됩니다.

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;

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

4.다음 쿼리를 실행하여 트랜잭션을 중지합니다.

SELECT pg_terminate_backend(PID);

참고:****PID를 이전 단계에서 식별한 프로세스의 blocking_pid _pid로 바꿉니다.

관련 정보

잠금 보기를 위한 PostgreSQL 문서

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

wait_event description에 대한 PostgreSQL 설명서

잠금 모니터링을 위한 PostgreSQL 위키

Amazon Aurora PostgreSQL 대기 이벤트