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
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:
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).
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…)
Replacing the DB with something managed by myself. (Even dumber idea)
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">
0 Asked on January 5, 2021 by tu-ai-le
1 Asked on January 5, 2021 by clemson
1 Asked on January 5, 2021 by don-hatch
1 Asked on January 5, 2021 by benstackoverflow
2 Asked on January 5, 2021 by user3579222
4 Asked on January 4, 2021 by mrfacundo
2 Asked on January 4, 2021 by iliketocode123465
0 Asked on January 4, 2021 by adriano
0 Asked on January 4, 2021 by no-one
2 Asked on January 4, 2021 by ali-has
1 Asked on January 3, 2021 by 010110110101
0 Asked on January 3, 2021 by katie-schmidt
3 Asked on January 3, 2021 by snusifer
1 Asked on January 3, 2021 by ns68
Get help from others!