Script for detecting orphan process and locks in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article pertains to all versions of Tanzu Greenplum and covers how to detect orphan process on the database or server. 

An orphan process is where there is a process (related to a gang) that is running on the segments and there is no corresponding process related to the same gang on the master or other segments.

This orphan process arises when there is query abort, process crash, server/segment crash, etc.

Resolution

Note: The queries or commands below might produce a misleading message. When the query is run, this query might be in the dispatch phase and not all segments have started the transaction. Therefore, it's always a good idea to run the query again after two minutes  ensure if the process remains the same or changed.

Detect all the orphan process through the Unix utility

1. The query below might not help if the orphan process is on the master server itself. In other words, the query is still running on the master but no connection on segments. Ignore the process that is IDLE.

2. The script below uses the connections on master as a reference. In case the connection exists between the master and a few segments but not the rest, the script below might not report it.

source /usr/local/greenplum-db/greenplum_path.sh ; ORPHAN="ps auwxx|grep postgres|grep con[0-9]|`echo "egrep -v \\\"wal|con0|gpsyncagent"`|`psql -R '|' -d template1 -Atc "select 'con'||sess_id::text from pg_stat_activity"`\""; gpssh `psql -R' ' -d template1 -Atc " select distinct '-h ' || hostname from gp_segment_configuration "` $ORPHAN

Check from the database end

Note: This works if the process is holding any lock on the table and its detected by pg_locks.

From the report below, total segments should be equal to total sessions. If this is not the case, then the session is orphan or missing one or more segments.

Greenplum 5x:

select
   'con'|| a.mppsessionid AS "Session ID",
   b.total_seg as "Total Segments",
   count(a.*) AS "Total Sessions"
from
  (select distinct mppsessionid,gp_segment_id 
   from pg_locks
   where mppsessionid not in (select sess_id from pg_stat_activity where procpid!=pg_backend_pid() OR current_query!='<IDLE>' OR waiting='t')
   and mppsessionid != 0
  ) a,
  (select count(*) as total_seg
   from gp_segment_configuration 
   where role='p'
  ) b
group by 1,2
having count(a.*) < b.total_seg
order by 3;

Greenplum 6x:

select
   'con'|| a.mppsessionid AS "Session ID",
   b.total_seg as "Total Segments",
   count(a.*) AS "Total Sessions"
from
  (select distinct mppsessionid,gp_segment_id 
   from pg_locks
   where mppsessionid not in (select sess_id from pg_stat_activity where pid!=pg_backend_pid() OR query!='<IDLE>' OR waiting='t')
   and mppsessionid != 0
  ) a,
  (select count(*) as total_seg
   from gp_segment_configuration 
   where role='p'
  ) b
group by 1,2
having count(a.*) < b.total_seg
order by 3;

Check if the orphan process is blocking any session/query

After running the query below, note the Blockers SessionID and match it with the content <sessionID> in the query above. If it's the same then this orphan process is blocking other running queries.

Greenplum 5x + 6x:

SELECT 
    w.relation::regclass AS "Table",
    w.mode               AS "Waiters Mode",
    w.pid                AS "Waiters PID",
    w.mppsessionid       AS "Waiters SessionID",
    b.mode               AS "Blockers Mode",
    b.pid                AS "Blockers PID",
    b.mppsessionid       AS "Blockers SessionID",
    (select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and role='p') AS "Blocking Segment"
FROM pg_catalog.pg_locks AS w, pg_catalog.pg_locks AS b 
Where ((w."database" = b."database" AND w.relation  = b.relation)
OR w.transactionid = b.transactionid)
AND w.granted='f'
AND b.granted='t'
AND w.mppsessionid <> b.mppsessionid
AND w.mppsessionid in (SELECT l.mppsessionid FROM pg_locks l WHERE l.granted = true AND relation in ( select relation from pg_locks where granted='f'))
AND w.gp_segment_id = b.gp_segment_id
ORDER BY 1;

For more information on the database locks, refer to the scripts discussed How to check for orphan connections in Tanzu Greenplum.

For few more scripts to check the orphan process, refer to Scripts for identifying locking session information in the database in Tanzu Greenplum.

 

Additional Information

• If there is a backup or restore (especially gp_dump/gp_restore) running, then take off the parallel feature. Each segment connects independently to its segments and issue COPY FROM/TO, so they are not orphan processes.
• If the orphan process that is detected on master is IDLE and there is no corresponding process on any segment for that con<sessionID>, it is prefectly fine. The segments IDLE process is terminated by the Pivotal Greenplum based on the parameter gp_vmem_idle_resource_timeout.