Pivotal Knowledge Base

Follow

How to check orphan connections in Pivotal Greenplum (GPDB) database

Goal

There are scenarios where a process/connection can only be available available on segments resulting in locks at segment level. You can check the database and system for these orphan process from below commands .

Check orphan processes

-- Through psql

Below query can be run in psql to get the connections those are not available on master and only exists on segments.

 SELECT DISTINCT connection 
FROM (SELECT
hostname ,
port ,
pl.gp_segment_id as segment ,
'con'||mppsessionid as connection ,
relation::oid::regclass , granted FROM
pg_locks pl ,
gp_segment_configuration gsc WHERE
pl.gp_segment_id=gsc.content AND gsc.role='p' AND mppsessionid NOT IN (SELECT sess_id FROM pg_stat_activity )
) as q1 ;

Note: Make sure the connections are not WLM ( WLM connections are local). 

Example

In the below example there was only one segment affected with orphan process having no connection at master and locking relation test:

template1=#  SELECT hostname , pl.gp_segment_id as segment ,'con'||mppsessionid as connection ,relation::oid::regclass ,  granted  
template1-#  FROM pg_locks pl ,gp_segment_configuration gsc 
template1-#  WHERE pl.gp_segment_id=gsc.content 
template1-#  AND gsc.role='p'
template1-#  AND mppsessionid NOT IN  (SELECT sess_id FROM pg_stat_activity ) ;
 hostname | segment | connection | relation | granted 
----------+---------+------------+----------+---------
 sdw3     |       3 | con224     | test     | t
 sdw3     |       3 | con224     |          | t

-- Through server process

Prerequisite

1) Create file with name "hostfile" that has all the segment server entries "only" and no master hostname like below :

gpadmin:Fullrack@mdw $ cat hostfile
sdw3
sdw4
sdw5

2) Run the below command from the same directory where "hostfile" has been created

 for i in `gpssh -f hostfile " ps -ef | grep postgres  | grep con |grep -v consumer  "  | awk '{print $15}' | sort -u`  ; do  echo "#Checking $i at Master " ; ps -ef | grep $i | grep -v grep > /dev/null  ; if [ $? -ne 0 ]; then echo "##Session $i not available at  Master <<<<<<<<<<<< "; else echo "##Session $i exists at Master##" ;  fi  ; done

Example

gpadmin:Fullrack@mdw $ for i in `gpssh -f hostfile " ps -ef | grep postgres  | grep con |grep -v consumer  "  | awk '{print $15}' | sort -u`  ; do  echo "#Checking $i at Master " ; ps -ef | grep $i | grep -v grep > /dev/null  ; if [ $? -ne 0 ]; then echo "##Session $i not available at  Master <<<<<<<<<<<< "; else echo "##Session $i exists at Master##" ;  fi  ; done
#Checking con340 at Master 
##Session con340 exists at Master##
#Checking con224 at Master 
##Session con224 not exists at Master

You may also refer the article for some more commands to detect the orphan process.

Next steps

If orphan processes are found then please contact pivotal support for clean up. Please do not use OS kill command on the processes. A database restart would be an alternative for these scenarios which will cleanup the processes from all the segments.

Comments

Powered by Zendesk