#!/bin/bash ################################################################################# # findlockblocks.sh # # Dead-stupid script that leverages existing RS queries and does a mashup that reports # the current running queries that are blocking others, sorted by time running. # # Nice, simple way to see if there's actually a problem or if RS is just swamped. # # v1.01 2019-06-14 rlbyrd Initial queries, usually sourced from psql CLI # v1.10 2019-09-27 rlbyrd Converted to shell script # # TODO: Utilize a sourced bash file with usernames and passwords, as with other scripts # in the library. # ################################################################################# RSUSER="vacasaroot" RSPASS="xxxxxxxxxx" read -r -d '' "stats_sql" << 'EOF' SELECT a.txn_owner, 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; EOF echo "$stats_sql" > /tmp/stats.sql BLOCKPIDS=`cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse | cut -d'|' -f9 | grep "\S" | egrep -v "blocking|rows|\-\-\-"| sed 's/ //g' | sort -u` #construct IN clause for PID in $BLOCKPIDS do PIDCLAUSE="$PIDCLAUSE,$PID" done PIDCLAUSE=" AND pid in (${PIDCLAUSE:1}) " stats_sql="select pid, duration/1000000 as seconds, trim(user_name) as user,substring(query,1,200) as querytxt from stv_recents where status = 'Running' and seconds >=1 ${PIDCLAUSE} order by seconds desc;" echo $stats_sql > /tmp/stats.sql echo " Current active Redshift queries that are blocking the execution of other queries (may or may not be critical) ============================================================================================================= " cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse