AnswerBun.com

Opening DB connections to Postgres taking long

Stack Overflow Asked on January 3, 2022

Some of our applications are facing issues with the connection pool. I run one of them. A JEE application on Payara 4.1 which uses PostgreSQL 9.5.8.

I have as good as no problems when running the application localy with local db instance. When running on the remote environment I have seen issues happening every 10 minutes that the application was unresponsive (well, it actually responded everything with HTTP status 503). Guessing it was related to opening connections taking long, we have set the parameter idleTimeoutInSeconds="0" in jdbc-resource. Now we have the same issues about 4 times a day which is an improvement, but – well – neighbour systems are still complaining.

We usually run with 5 steady connection allowing maximum connections of 30. Our application usually uses 1 up to 2 to handle traffic. With TCP dump I have seen, that at a certain point in time the connection pool tries to open many connections (the pool realizes the connections it holds have been closed by the DB without any information like TCP FIN, opening each connections takes about 1 second). During this time of about 30 seconds not all requests can be safely queued and some 503 happen.

Locally everything is fine. Opening a connection takes ~50ms and everyone is happy. Our postgres team is not helping at all and I am stuck with a problem. As I don’t see any improvement possibility with the connection pool in JEE, I have radical ideas going in the direction of:

  1. Refreshing the connections myself. All the time. Constantly. (Which would be hard to implement in JEE where I can not simply look into the connection pool and tell each connection to be refreshed just in case).

  2. Replacing the not-helping-at-all JEE implementation of connection pool with something that works better. (Future generations of developers maintaining our app will hate me…)

  3. Replacing the DB with something managed by myself. (Even dumber idea)

Does anyone:

  1. Has any idea how I could perform 1 or 2 above?
  2. Has any other ideas what could help?

Here my current JDBC resource definition if needed:

<jdbc-resource poolName="<poolName>" jndiName="<jndiName>" isConnectionValidationRequired="true"
connectionValidationMethod="table" validationTableName="version()" maxPoolSize="30" 
validateAtmostOncePeriodInSeconds="30" statementTimeoutInSeconds="30" isTimerPool="true" steadyPoolSize="5" 
idleTimeoutInSeconds="0" connectionCreationRetryAttempts="100000" connectionCreationRetryIntervalInSeconds="30" 
maxWaitTimeInMillis="2000">

Add your own answers!

Related Questions

Substring instead of Left

2  Asked on January 5, 2021

   

R: Multi-type Point Pattern scan.test

1  Asked on January 5, 2021 by clemson

       

Pandas: filter rows with loc on anonymous dataframe

2  Asked on January 5, 2021 by user3579222

   

Why are the spacings in the decipher incorrect?

2  Asked on January 4, 2021 by iliketocode123465

   

How to play more than one stream at once using soundio?

1  Asked on January 3, 2021 by 010110110101

 

Units and Measurements in Swift

1  Asked on January 3, 2021 by tick_hoges

     

Count files in directory (python)

0  Asked on January 3, 2021 by user14606934

 

How to set the “Resource root URL” in Jenkins

1  Asked on January 3, 2021 by hephaestus

 

Write Pandas DataFrame to chronologic excel sheet

0  Asked on January 3, 2021 by anna-bleha

     

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP