pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

killing idle_connections
3+ messages / 3 participants
[nested] [flat]

* killing idle_connections
@ 2020-12-01 02:49  Walters Che Ndoh <chendohw@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Walters Che Ndoh @ 2020-12-01 02:49 UTC (permalink / raw)
  To: pgsql-admin

I have two DB's in prod
- DB 1 has pg12 and pgbouncer for connection pooling
- DB 2 has pg10 and F5 for connection pooling

Both DB's have a max connection of 500.
During pick hours i always run out of connections, even though i have 3
superuser reserved, my connections still go beyond 500.

So when I try to check active, idle and idle_in_transaction, this is what I
get.

*Count         State*
   5
  24             active
  4               idle in transaction
  414           idle

most of the time *idle  connection *is at *400* plus or above *350.*
So i created a script to kill connections that have been idle for *20 secs* and
the *cron* run every *1 minute*. After a while, i started getting this
error from developer *"57P01: terminating connection due to administrator
command".*

I understand that connection pooling is designed to reuse idle connections
and if you kill them, you are actually or probably killing a connection.

Or maybe the time i set for the script to run is too aggressive?

Or should I not run the script in the first place since connection pooling
handles idle connections?

Does anyone have any suggestions to overcome my connection problems?

I have also done OS tuning and I am convinced they are okay.

Any ideas🙏

Many Thanks
Ndoh

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: killing idle_connections
@ 2020-12-01 02:59  David G. Johnston <david.g.johnston@gmail.com>
  parent: Walters Che Ndoh <chendohw@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: David G. Johnston @ 2020-12-01 02:59 UTC (permalink / raw)
  To: Walters Che Ndoh <chendohw@gmail.com>; +Cc: pgsql-admin

On Monday, November 30, 2020, Walters Che Ndoh <chendohw@gmail.com> wrote:
>
> Or should I not run the script in the first place since connection pooling
> handles idle connections?
>

That.  Combined with the developers correctly handling connection
management in their code.  The pooler doesn’t doesn’t fix bad code.  It
lets you properly disconnect and reconnect constantly in the application
while minimizing the pain of doing so.  Settings on the poolers came
surface the pain of bad usage more quickly, and should turn connection
failures into connection delays (which should eventually timeout).
Speaking off-the-cuff here...

David J.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: killing idle_connections
@ 2020-12-01 13:38  hubert depesz lubaczewski <depesz@depesz.com>
  parent: Walters Che Ndoh <chendohw@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: hubert depesz lubaczewski @ 2020-12-01 13:38 UTC (permalink / raw)
  To: Walters Che Ndoh <chendohw@gmail.com>; +Cc: pgsql-admin

On Mon, Nov 30, 2020 at 07:49:39PM -0700, Walters Che Ndoh wrote:
> I have two DB's in prod
> - DB 1 has pg12 and pgbouncer for connection pooling
> - DB 2 has pg10 and F5 for connection pooling
> 
> Both DB's have a max connection of 500.
> During pick hours i always run out of connections, even though i have 3
> superuser reserved, my connections still go beyond 500.

What is your pool_mode in pgbouncer?
If it's "session" - then it is the problem, and you should consider
reading https://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/

Please note that not all applications will work OK with transaction
pooling, but if they don't then (for me) it's a sign that they should
get fixed.

depesz






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2020-12-01 13:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-01 02:49 killing idle_connections Walters Che Ndoh <chendohw@gmail.com>
2020-12-01 02:59 ` David G. Johnston <david.g.johnston@gmail.com>
2020-12-01 13:38 ` hubert depesz lubaczewski <depesz@depesz.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox