Long running queries

Ever wondered if you did not get a call in the middle of the night just for some stupid stuck query that was not supposed to run that long and blocking all the other queries making the database load rise up till its not even manageable?

I did and finally got something to address it. Below is a script that logs all long running queries and kills them with pg_cancel_backend. Finally sends out an email alert, you can modify it according to your production requirement and increase the time of the query that should be killed. Just put it in a cronjob “AFTER TESTING IT PROPERLY”. I am not responsible for any data loss here.

Make sure you have postfix installed and configured for your mail to work.

# The script must run as postgres, since only postgres user has control over every database.
if [ `whoami` != "postgres" ]; then
exit 0;
fi

# Selecting relevant columns, getting non-idle queries only where the query runs since at least 6 minutes.
psql -c "select procpid, client_addr, query_start, current_query from pg_stat_activity
where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now()
and substring(current_query, 1, 11) != 'autovacuum:'
order by query_start desc" > $LOGFILE

NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'`

if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then
# Getting the first column from the output discarding alfanumeric values (table elements in psql's output).
STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"`
for PID in $STUCK_PIDS; do
echo -n "Cancelling PID $PID ... " >> $LOGFILE

# "t" means the query is successfully cancelled.
SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"`
if [ $SUCCESS ]; then
SUCCESS="OK.";
else
SUCCESS="Failed.";
fi
echo $SUCCESS >> $LOGFILE
done

cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes." youremail@whatever.com;

fi

rm $LOGFILE
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: