Postgres Pro
Downloads
Home   >   mailing lists

Thread: killing idle_connections

killing idle_connections

From
Walters Che Ndoh
Date:
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


Re: killing idle_connections

From
"David G. Johnston"
Date:
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.

AW: killing idle_connections

From
"Dischner, Anton"
Date:

Hi all,

 

do you have running something like app armor which cancels tcp connections with a typical limit of 500 ?

 

If yes disable and test again,

 

best,

 

Anton

 

Von: Walters Che Ndoh <chendohw@gmail.com>
Gesendet: Dienstag, 1. Dezember 2020 03:50
An: pgsql-admin@postgresql.org
Betreff: killing idle_connections

 

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

 

Re: killing idle_connections

From
hubert depesz lubaczewski
Date:
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