Pivotal Knowledge Base

Follow

How to auto terminate IDLE connection (gp_vmem_idle_resource_timeout)

Purpose

How to automatically terminate the IDLE connections ?

Solution

In greenplum this can be achieved via a parameter "gp_vmem_idle_resource_timeout", whose role is to automatically terminate all idle connection after a specified amount of time.

By default the value is 18s, i.e 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 the session that is running on segments (which does most of the work and consume the max amount of memory) and it will not terminate any IDLE connection on master.

Example

-- My 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 its goes into IDLE stage

-- Checking the connection status via gpssh on all my hosts shows after 18s 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]

-- But the session remains open on master segments, as it expects you would execute a new set of query, if a new query is received by the master, master again spawn a new session on its segments.

-- The session on master would be cleared when the user exits from the database.

Comments

  • Avatar
    Gurupreet Singh Bhatia

    is there any way to terminate idle session from master also ??

  • Avatar
    Faisal Ali

    Hi,

    IDLE connection on master will only get terminated when the session that has connection on the database disconnects.

    If the session doesnt disconnects then you can use the pg_terminate_backend, please refer to the below article for some help.

    https://support.pivotal.io/hc/en-us/articles/202032703
    https://support.pivotal.io/hc/en-us/articles/202645963

    If you dont want to kill the session all the time then you may look at the pgbouncer from postgres ( not a greenplum product ) which can help in connection pooling.

    Hope that helps.

    Thanks

  • Avatar
    Gurupreet Singh Bhatia

    Thanks Faisal

Powered by Zendesk