AW: killing idle_connections

["Dischner, Anton" <Anton.Dischner@xxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxx>
Gesendet: Dienstag, 1. Dezember 2020 03:50
An: pgsql-admin@xxxxxxxxxxxxxx
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