CODEWITHSUNDEEP
postgresqlpgpool
Tarikur Rahaman
Tarikur Rahaman

Reputation: 117

pgpool and postgresql lots of idle connection

I'm using postdock (https://github.com/paunin/PostDock)

Postgres version: 10 Pgpool version: 3.7

1 pgpool 1 master 1 slave 1 backup (barman)

My application is connecting with pgpool ( I'hv 1 databases and 7 user/app) and I'hv seen from background that in PostgreSQL has lots of IDLE connection that was running query DISCARD ALL.

I increased the postgresql max connection from 100 to 1500. because sometimes idle connection goes up to 850 and for that connection is impacting our services.

Now we're killing the idle connection manually every 5 min.

Our application is closing connection properly because in our existing DB system (without pgpool) it only opened 12 DB connection.

have anyone face this similar problem?

I'm using the same configuration that is provided with postdock.

Upvotes: 1

Views: 3552

Answers (2)

Faisal
Faisal

Reputation: 324

I had the same issue, the client_idle_limit in my case was turned off (default), setting it to 300 seconds (5 minutes) resulted in decreasing a lot of connections having the query DISCARD ALL.

From PgPool docs (4.5.5):

client_idle_limit (integer): Specifies the time in seconds to disconnect a client if it remains idle since the last query. This is useful for preventing the Pgpool-II children from being occupied by a lazy clients or broken TCP/IP connection between client and Pgpool-II. The default is 0, which turns off the feature.

The reason I believe is that PgPool will send those queries to reset the backend connection using reset_query_list when exiting the user session. But since client_idle_limit is turned off, it just decides to make the connection alive (idle) even if a query sent to terminate the session.

Upvotes: 0

jgj1018
jgj1018

Reputation: 67

I don't know much about postdock, but it seems this pgpool.conf(https://github.com/paunin/PostDock/blob/master/src/pgpool/configs/pgpool.conf) is used for settings. and you can see "connection_life_time = 0" connection_life_time is the time in seconds to terminate the cached connections to the PostgreSQL backend and 0 means connections will not be disconnected. you should specify certain specific number.

Upvotes: 1

Related Questions