PgBouncer not able to terminate idle connections

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Sometimes PgBouncer does not clean up idle connections even if server_idle_timeout and client_idle_timeout reach their limit.

Environment

Cause

PgBouncer is a connection pooler. Any target application can be connected to PgBouncer as if it were a Greenplum server. PgBouncer will create a connection to the actual server, or it will reuse one of its existing connections.

The aim of PgBouncer is to lower the performance impact of opening new connections to Greenplum.

The life of the server connections and client connections are controlled by the parameters listed below:

server_idle_timeout

If a server connection has been idle more than the time (seconds) set by this parameter, it will be dropped. If it is 0, the timeout is disabled.

client_idle_timeout

Client connections idling longer than the time (seconds) set by this parameter are closed. 

Resolution

There is another parameter that affects this behavior. This comes into effect from PgBouncer version 1.6.x and above. 

min_pool_size

This parameter adds more server connections to the pool if the connections are below this number. This improves behavior when the usual load comes back suddenly after a period of total inactivity. The default is 0 (disabled).

From PgBouncer versions 1.6.x above, the PgBouncer application disables server_idle_timeout when the server count gets below the min_pool_size.

Altering the value of min_pool_size and setting it according to the environmental requirements helps in resolving this issue.