How to auto terminate IDLE connection (gp_vmem_idle_resource_timeout) in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article applies to all versions of Tanzu Greenplum and describes how to automatically terminate IDLE connections.

Resolution

In Tanzu Greenplum, you can automatically terminate IDLE connections through the parameter "gp_vmem_idle_resource_timeout". The role of this parameter is to automatically terminate all idle connections after a specified amount of time.

By default the value is 18s. If the session is idle after 18s the session is terminated.

flightdata=# show gp_vmem_idle_resource_timeout ;
 gp_vmem_idle_resource_timeout
-------------------------------
 18s
(1 row) 

Note: The parameter only works for a session that is running on segments. Segments do most of the work and consume the max amount of memory. It will not terminate any IDLE connection on the master.
 

Example

The session information on the cluster is:

flightdata=# select procpid,sess_id  from pg_stat_activity ;
 procpid | sess_id
---------+---------
    1863 |      24
(1 row)
-- Start and end a transaction

flightdata=# begin;
BEGIN
flightdata=# rollback;
ROLLBACK 

Once the transaction ends, connections enter the IDLE stage.

Checking the connection status with gpssh on all the hosts shows that after 18 seconds, the session is no longer a part of the output. 

[gpadmin@mdw2 faisal]$ while true; do gpssh -f hostfile "ps -ef | grep postgres|grep con24"; done
[...]
[...]
[sdw3] gpadmin   2320 23546  0 03:32 ?        00:00:00 postgres: port 42702, gpadmin flightdata 172.28.8.250(33959) con24 seg1 idle
[sdw5] gpadmin  19834  1829  0 03:32 ?        00:00:00 postgres: port 42701, gpadmin flightdata 172.28.8.250(43642) con24 seg2 idle
[...]
[...]
[sdw5] gpadmin  19836  1828  0 03:32 ?        00:00:00 postgres: port 42702, gpadmin flightdata 172.28.8.250(33526) con24 seg3 idle
[sdw3] gpadmin   2318 23548  0 03:32 ?        00:00:00 postgres: port 42701, gpadmin flightdata 172.28.8.250(63256) con24 seg0 idle
[sdw5]
[sdw3]
[sdw5]
[sdw3] 

A session on master segment remains open. It expects a new set of queries to be executed. If a new query is received by the master, the master will again spawn a new session on its segments.

The session on the master will cleared when the user exits the database.

Additional Information