How to kill all idle connections in Greenplum database cluster

Post date: Nov 08, 2012 5:41:2 PM

In Greenplum 4.x, Greenplum introduced GUC "gp_vmem_idle_resource_timeout" to free up idle session resources. For more details referGreenplum Database 4.x Administrator's Guide on Powerlink at this location:

Home > Support > Technical Documentation and Advisories > Software ~ E-I ~ Documentation > Greenplum Family > 4.1.x

GUC Description: If a database session is idle for longer than the time specified, the session will free system resources (such as shared memory), but remain connected to the database. This allows more concurrent connections to the database at one time.

Lower version of Greenplum

When client sessions terminates / exit, Greenplum session cleanup will happen automatically.

Running the following script will kill idle connections to the Greenplum Database master:

#!/bin/bash

conn=0

TIME=2700

HELP=0

################################################################################################

# Objective : To identify the CONNECTIONS IDLE for more than 45 minutes

# Summary : This will identify the pid's and will issues a kill for then:

# Logging : All logs will be stored in $HOME/gpAdminLogs/gp_idle.log

# Consequences : Connections killed will get the below error messages and they would need to reconnect

# FATAL: terminating connection due to administrator command

# server closed the connection unexpectedly

# This probably means the server terminated abnormally

# before or while processing the request.

# The connection to the server was lost. Attempting reset: Succeeded.

################################################################################################

help() {

echo '

-h : Display help

-c : kill idle connections

-t : age of query and connection [default: 2700 seconds ]

idleconn.sh -c -t 3000 [default: 2700 values in seconds]

idleconn.sh -c [default: 2700 values in seconds]

}

source $GPHOME/Greenplum_path.sh

loging (){

# Logging all the connections which were idle for more than 2700s

psql -t -c "select * from pg_stat_activity where now()-backend_start > '${TIME}s'" template1 >> $HOME/gpAdminLogs/gp_idle.log

}

idle_conn () {

# Generating pid's for connections opened for more then 45 minutes :

psql -A -t -c "SELECT 'kill '||procpid from pg_stat_activity where now()-backend_start > '${TIME}s' or current_query ='<IDLE>' " template1 | bash

}

if [ $# -ne 0 ];then

while getopts "ct:h" opts;do

case "$opts" in

conn=1

;;

TIME=$OPTARG;

;;

HELP=1

;;

esac

done

#else

#help

date >> $HOME/gpAdminLogs/gp_idle.log

if [ $HELP -eq 1 ] || [ $conn -eq 0 ];then

help

if [ $conn -eq 1 ];then

loging

idle_conn

date >> $HOME/gpAdminLogs/gp_idle.log