Redshift でロック待ちを解消する方法

• ロック待ちしているクエリを調査する

SELECT
  a.txn_start,
  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,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(trim(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid,
  e.query_id,
  e.query_type,
  e.query_text
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 SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
LEFT JOIN SYS_QUERY_HISTORY e
  ON e.transaction_id = a.xid
WHERE
  a.relation IS NOT NULL;

• 実行中のクエリをユーザー名、クエリタイプ、ユーザー名で集計する

SELECT
username
,query_type
,username
,COUNT(*)
FROM SYS_QUERY_HISTORY
WHERE status IN ('planning','queued','running')
GROUP BY
username
,query_type
,username
;

• セッションを kill するクエリを生成する

select 'select pg_terminate_backend(' || session_id || ');'
FROM SYS_QUERY_HISTORY
WHERE status IN ('planning','queued','running')
AND — 追加条件
;

参考

repost.aws