Killing Idle Sessions

A lot of users will open a connection to a database and then leave it open for days.  They will forget about this and then open more connections.  Eventually, they will hit the max_connections limit and then not be able to create any new connections to Greenplum.

An easy fix for this is to kill sessions programmatically that have been idle for more than an hour.  Of course, you can adjust this time period as appropriate.

Here is a simple SQL statement to identify the idle sessions:

SELECT procpid
FROM pg_stat_activity
WHERE current_query = '<IDLE>'
AND clock_timestamp() - query_start > interval '1 hour';

One way to kill these idle sessions is with a single command:

eval `psql -A -t -c "SELECT 'kill ' || procpid FROM pg_stat_activity WHERE current_query = '<IDLE>' AND clock_timestamp() - query_start > interval '1 hour'"`

Put that command into a shell script (be sure to source the greenplum_path.sh script first) and create a cron job that runs every 5 minutes.

If you want to track who you killed and what time, you can use this shell script and again, use a cron job to execute it every 5 minutes.

#!/bin/bash
source /usr/local/greenplum-db/greenplum_path.sh
for i in $( psql -A -t -c "SELECT procpid, usename FROM pg_stat_activity WHERE current_query = '<IDLE>' AND clock_timestamp() - query_start > interval '1 hour'" ); do
        procpid=$(echo $i | cut -d\| -f1)
        usename=$(echo $i | cut -d\| -f2)

        echo `date`\|$procpid\|$usename >> kill.log
        kill $procpid
done

One thought on “Killing Idle Sessions

  1. Jon Post author

    A quick change to the SQL and you can kill long running queries.

    eval `psql -A -t -c "SELECT 'kill ' || procpid FROM pg_stat_activity WHERE current_query <> '<IDLE>' AND clock_timestamp() - query_start > interval '1 hour'"`
    
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.