How to check for orphan connections in VMware Tanzu Greenplum
Article ID: 295266
Updated On:
Products
Issue/Introduction
This article applies to all versions of Tanzu Greenplum.
A orphan process or connection is only available on segments, resulting in locks at the segment level.
This article explains how to check the database and system for these orphan processes.
Resolution
Option 1: Through PSQL
Run the query below in PSQL to determine the connections that are not available on the 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: Confirm that the connections are not Workload Manager connections WLM (WLM connections are local).
In the following example, there is only one segment affected with an orphan process. It is the segment with no connection at the master and a 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
Option 2: Through the server process
Prerequisites
1. Create a file with the name "hostfile". The segment server entries in this file must be set to "only" and no master hostname such as the one below:
gpadmin:Fullrack@mdw $ cat hostfile sdw3 sdw4 sdw5
2. Execute the command below 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
For 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
Note: For information on more commands to detect the orphan process, refer to Scripts for detecting orphan process and lock.
Additional Information
If orphan processes are found then please contact Broadcom Greenplum support.
Please do not use the kill command on the processes as that may cause panics in the database.
A database restart is an alternative for these scenarios. A restart will cleanup the processes from all the segments.
Feedback
