์ƒ์„ธ ์ปจํ…์ธ 

๋ณธ๋ฌธ ์ œ๋ชฉ

[TIL] AWS Redshift์—์„œ block์ด ๊ฑธ๋ ค์„œ ์ฟผ๋ฆฌ๊ฐ€ ์ง€์—ฐ๋  ๋•Œ ํ™•์ธํ•˜๊ณ  ๊ฐ•์ œ ์ข…๋ฃŒํ•˜๋Š” ๋ฐฉ๋ฒ• 221017

CLOUD/AWS

by ์—์Šคํ”„๋ฆฌํ„ฐ 2022. 10. 17. 23:04

๋ณธ๋ฌธ

728x90
๐Ÿ’ก 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 ๊ด€๋ จ ๋ฆฌ์„œ์น˜ ๋‚ด์—ญ:

๊ด€๋ จ๊ธ€ ๋”๋ณด๊ธฐ

๋Œ“๊ธ€ ์˜์—ญ