How to run scripts to terminate all idle connections in Tanzu Greenplum cluster
Article ID: 295177
Updated On:
Products
Issue/Introduction
This article describes how to kill all idle connections in Tanzu Greenplum cluster.
Tanzu Greenplum users may open connections to the database through clients such as PSQL, and then leave those connections open and idle for long periods of time without logging off.
Depending on the number of users, this could consume system resources and claim most or all of the available connections.
For example, in a system using the default setting for the server parameter max_connections (25 connections on the master), 24 idle connections would leave only one available connection before reaching the maximum.
Note: Verify the script mentioned in this article on a test cluster before running it on production or important clusters/or database. These scripts are for education purpose only.
Resolution
When client sessions terminates or exits, Greenplum Database (GPDB) session cleanup will happen automatically.
Copy the following script onto a file name idleconn.sh to terminate or kill idle connections on the GPDB master instance.
#!/bin/sh
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 issue a kill for them:
# 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]
Usage:
idleconn.sh -c -t 3000 # Sets time to 3000 seconds
idleconn.sh -c # Uses default time of 2700 seconds
'
}
source "$GPHOME/greenplum_path.sh"
LOGFILE="${HOME}/gpAdminLogs/gp_idle_$(date '+%Y%m%d').log"
#
# Function to log the idle connections on the system
#
logging() {
# Logging all the connections which were idle for more than 2700s
# If query_start is null, the user connected but ran no query. So check the backend_start time.
psql -t -c "SELECT * FROM pg_stat_activity WHERE query_start IS NULL AND now() - backend_start > '${TIME}s'" template1 >> "$LOGFILE"
# If query_start is not null, the user ran a query at some stage. So check the query_start time rather than the backend_start.
psql -t -c "SELECT * FROM pg_stat_activity WHERE query_start IS NOT NULL AND now() - query_start > '${TIME}s'" template1 >> "$LOGFILE"
}
#
# Function to kill the idle connections
#
idle_conn() {
# Generating pid's for connections opened for more than 45 minutes:
# If query_start is null, the user connected but ran no query. So check the backend_start time.
psql -A -t -c "SELECT 'kill ' || pid FROM pg_stat_activity WHERE query_start IS NULL AND now() - backend_start > '${TIME}s' AND query = ''" template1 | bash
# If query_start is not null, the user ran a query at some stage. So check the query_start time rather than the backend_start.
psql -A -t -c "SELECT 'kill ' || pid FROM pg_stat_activity WHERE query_start IS NOT NULL AND now() - query_start > '${TIME}s' AND query = ''" template1 | bash
}
#
# Main program
#
if [ $# -ne 0 ]; then
while getopts "ct:h" opts; do
case "$opts" in
c)
conn=1
;;
t)
TIME=$OPTARG
;;
h)
HELP=1
;;
esac
done
fi
echo "$(date) -- Start" >> "$LOGFILE"
if [ $HELP -eq 1 ] || [ $conn -eq 0 ]; then
help
fi
if [ $conn -eq 1 ]; then
logging
idle_conn
fi
echo "$(date) -- End" >> "$LOGFILE"
========================================================================================
Command Usage:
#Remove any old log file...
find ${HOME}/gpAdminLogs -name gp_idle\*.log -mtime +60 -exec rm {} \;
To execute the script, use
/bin/sh idleconn.sh -c
To execute the script to terminate IDLE connection running longer than 3000 sec use
/bin/sh idleconn.sh -c -t 3000
By default the -t is set to 2700 seconds.
Note: In Tanzu Greenplum 4.x, the GUC, gp_vmem_idle_resource_timeout, was introduced to free up idle session resources (only segment connections, not master sessions).
Feedback
