Script to kill ALL IDLE Connection In postgreSQL
1.MORE I/O POSTGRESQL KILLING ALL IDLE CONNECTION FOR MONITORING PURPOSES:
This following script is used for killing all idle connection and you can use it as a monitoring puposes like how many idle query is running for a hour and how many 'idle', 'idle in transaction', 'idle in transaction (aborted)' and 'disabled' queries are running for a hour and when it killed and how many hours is taken,schedule this script every one hours don't schedule it every 2 minutes and 1 minutes because it will be taken more I/O(4 times) than following second script,if you want shedule this script every 1 or 2 minutes use second script it will take less I/O.
2.LESS I/O POSTGRESQL KILLING ALL IDLE CONNECTION:
How to schedule a job every 1,2,3 minutes using crontab linux utility?
Ans:
I given below some sample crontab sheduled time,Example for every 1 minutes,every 2 minutes and every 3 minutes want run "ALL_IDLE_CONN.sh" script.
CRONTAB SCHEDULER:
Ans:
All idle connection killing script:
#this following script is killed all idle connection ( 'idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled' ) status once idle connectin reached 5 minutes this following script want to run every 3 minutes once so i scheduled it using crontab,i adviced to scheduled it as per your environment transaction idle connection, use following script to find out idle connection timing
3.very Less I/O than second script:
This following script is used for killing all idle connection and you can use it as a monitoring puposes like how many idle query is running for a hour and how many 'idle', 'idle in transaction', 'idle in transaction (aborted)' and 'disabled' queries are running for a hour and when it killed and how many hours is taken,schedule this script every one hours don't schedule it every 2 minutes and 1 minutes because it will be taken more I/O(4 times) than following second script,if you want shedule this script every 1 or 2 minutes use second script it will take less I/O.
cat /home/postgres/idle.sh #!/bin/bash HOSTNAME=`hostname` PSQL="/opt/PostgreSQL/9.3/bin/psql" PORT=5432 HOST="localhost" DB="template1" USER="postgres" DATE2=`date +'%d%m%G'` DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log" touch $DETAIL2 IDLECONNTIME2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select now();"` MAXCONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "show max_connections;"` CONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select count(*) from pg_stat_activity;"` echo "ALL IDLE Connetions Start Time (script start Time before killing session) : $IDLECONNTIME2" | tee -a $DETAIL2 echo "Maximum Connections Defined : $MAXCONN2" | tee -a $DETAIL2 echo "Current Connection Count : $CONN2" | tee -a $DETAIL2 IDLECONN2=`$PSQL -d $DB -U $USER -p $PORT "select count(*) from pg_stat_activity WHERE pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '5' MINUTE;"` echo "Count of ALL IDLE connections more than 5min: $IDLECONN2" |tee -a $DETAIL2 echo "Connections ALL IDLE for more than 5 min" >> $DETAIL2 $PSQL -d $DB -U $USER -p $PORT -c " select datname, pid,now() - query_start as "runtime",state,state_change,query from pg_stat_activity WHERE pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2 $PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2 echo "ALL IDLE Connetions End Time (script End Time After session killed) : $IDLECONNTIME2" | tee -a $DETAIL2 exit 0;=========================second script==================================
2.LESS I/O POSTGRESQL KILLING ALL IDLE CONNECTION:
How to schedule a job every 1,2,3 minutes using crontab linux utility?
Ans:
I given below some sample crontab sheduled time,Example for every 1 minutes,every 2 minutes and every 3 minutes want run "ALL_IDLE_CONN.sh" script.
CRONTAB SCHEDULER:
crontab -e crontab every 1 minute is a commonly used cron schedule: * * * * * sh /home/postgres/ALL_IDLE_CONN.sh crontab every 2 minute is a commonly used cron schedule: */2 * * * * sh /home/postgres/ALL_IDLE_CONN.sh crontab every 3 minute is a commonly used cron schedule: */3 * * * * sh /home/postgres/ALL_IDLE_CONN.shHow to write a script for postgreSQL killing ALL IDLE connection ?
Ans:
All idle connection killing script:
#this following script is killed all idle connection ( 'idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled' ) status once idle connectin reached 5 minutes this following script want to run every 3 minutes once so i scheduled it using crontab,i adviced to scheduled it as per your environment transaction idle connection, use following script to find out idle connection timing
vi /home/postgres/ALL_IDLE_CONN.sh #!/bin/bash HOSTNAME=`hostname` PSQL="/opt/PostgreSQL/9.3/bin/psql" PORT=5432 HOST="localhost" DB="template1" USER="postgres" DATE2=`date +'%d%m%G'` DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log" touch $DETAIL2 # this is help to reduces the I/O not beffer sir $PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2 exit 0;=======================================================================
3.very Less I/O than second script:
vi /home/postgres/ALL_IDLE_CONN.sh #!/bin/bash HOSTNAME=`hostname` PSQL="/opt/PostgreSQL/9.3/bin/psql" PORT=5432 HOST="localhost" DB="template1" USER="postgres" # this is help to reduces the I/O not beffer sir $PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '5' MINUTE;" exit 0;
Comments
Post a Comment