[TIL] AWS Redshift에서 block이 걸려서 쿼리가 지연될 때 확인하고 강제 종료하는 방법 221017

💡 Today I Learned 요약 (26회차)
- Redshift에서는 세션 보호를 위해 특정 쿼리 동작 중에 추가 쿼리 동작을 잠글 수 있다.
- 쿼리를 통해 잠금 처리된 쿼리를 확인하고 종료시켜 롤백할 수 있다.

 

AWS Redshift에서 block이 걸려서 쿼리가 지연될 때 확인하고 종료하는 방법

Redshift에서 풀스캔 쿼리가 아닌데 이유 없이 돌고 있을 경우 잠금(block) 처리된 것을 의심할 수 있다. 보통은 block을 유발한 쿼리가 완료되면 block이 풀리고 후속 쿼리가 실행이 되지만 데이터 전처리 도중 다른 쿼리가 동작하여 block이 발생할 경우 시간에 쫓기게 될 수 있다. 그럴 때 확인 방법 및 종료, 쿼리 확인 방법에 대해 정리한다.

 

redshift 문서에서 정의하는 쿼리 잠금의 종류는 아래와 같다.

1. AccessExclusiveLock : UNLOAD, SELECT, UPDATE 또는 DELETE 작업 중에 획득

2. AccessShareLock : UNLOAD, SELECT, UPDATE 또는 DELETE 작업 중에 획득

3. ShareRowExclusiveLock : COPY, INSERT, UPDATE 또는 DELETE 작업 중에 획득

 

block 상태를 확인하기 위해서는 Redhisft에서 아래 쿼리를 입력하여 blocking_pid를 확인한다.

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a 
left join (select pid,relation,granted from pg_locks group by 1,2,3) b 
on a.relation=b.relation and a.granted='f' and b.granted='t' 
left join (select * from stv_tbl_perm where slice=0) c 
on a.relation=c.id 
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

해당되는 pid를 확인했으면 아래 쿼리로 해당 프로세스를 강제로 종료할 수 있다. 아래 쿼리가 실행되면 동작중인 쿼리의 트랜잭션은 롤백된다.

select pg_terminate_backend({PID});

해당 PID의 쿼리를 확인하고 싶다면 아래 쿼리를 입력하면 된다.

select query, trim(querytxt) as sqlquery, *
from stl_query where pid = {PID}
order by query desc;

---

#aws 관련 리서치 내역:

• Amazon Redshift에서 잠금을 감지하고 해제하려면 어떻게 해야 합니까?