0

I run a JEE application on Payara 4.1 which uses PostgreSQL 9.5.8. The connection pool is configured in following way.

<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">

From what monitors say, the applications needs 1-3 DB connections to postgres when running. Steady pool size is set to 5, max pool size is 30.

I see, that about 4 times a day the application opens all connections to the database hitting the max pool size limit. Some requests to the server fail at this point with exception: java.sql.SQLException: Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.

After some seconds all issues are gone, and the server runs fine till the next hiccup.

I have requested some TCP dumps to be performed to look closely into what happens exactly. I see that:

  1. After 30 connections (sockets) have been opened, most of the connections are rarely used.
  2. After some time (1h or so) the server tries to access some of such pooled connections to realize, that the socket is closed (DB responds immediately with a TCP RST).
  3. As the pooled connections count decreases hitting steady pool size, the connection pool opens 25 connections (sockets) which takes some time (about 0,5 up to 1 second per connection – don’t know why this long, as the TCP handshakes are immediate). At this point some server transactions are failing.
  4. The loop repeats.

This issue is driving me mad. I was wondering, whether I am missing some crucial pool configuration to revalidate the connections more often but could not find anything that would help.

EDIT: What does not help, as we have tested it already:

  • Making the pool size bigger (same issues)
  • Removing idleTimeoutInSeconds="0". We had issues with the connection pool every 10 minutes we did that.
Marek Puchalski
  • 3,286
  • 2
  • 26
  • 35
  • Oh, and raising the pool size (even by factor 10) does not help. – Marek Puchalski Jun 05 '20 at 11:30
  • 1
    https://docs.payara.fish/docs/5.201/documentation/payara-server/advanced-jdbc/advanced-connection-pool-properties.html It is for a newer Payara version. But there is `fish.payara.validate-atmost-once-period-in-seconds`. Did you try that? Maybe also works for Payara 4.1. – Christoph John Jun 05 '20 at 11:52
  • We have validateAtmostOncePeriodInSeconds="30". It does not sound as something that could help. I'd rather have something that would validate at-least once every x seconds. – Marek Puchalski Jun 05 '20 at 12:53
  • 1
    Are you sure that `validationTableName="version()"` is the correct syntax? I would have expected only `version` but just guessing. – Christoph John Jun 05 '20 at 14:21
  • 1
    We also use Postgres with Payara and for us the following parameters work: `` (from domain.xml) – Christoph John Jun 05 '20 at 14:21

0 Answers0