Tomcat connections pool

[dbothwell]

Hello,

I have two load balanced Tomcat application servers connecting to a single PostgreSQL database server. I am using the latest PostgreSQL JDBC 42.6.0 driver. My question is in regards to setting the “maxActive” attribute in my Tomcat server xml file. Below is a scenario to help describe the issue:

Let's say I have a PostgreSQL server with the “max_connections” GUC variable set 220, putting aside 20 connections for superuser_reserved, replication and direct connections. I can make 200 connections available for my Tomcat servers.

For years my assumption has been, with 200 connections available at the database, I would need to set both of my Tomcat server's data source “maxActive” attributes to 100 each, to prevent exceeding “max_connections”.

Recently one of my Tomcat servers threw an exception and using the above example would have looked like this:

“org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio2-8080-exec-260] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0”

Fortunately at the time I was monitoring the number of active backends in the PostgreSQL database, which peaked at only 100 connections, even though both Tomcat servers were running and active. Which leads me to believe the Tomcat server is looking, not to itself, but to the database to determine the number connections available to the pool.

So my question is: based on the above example, should I be setting the “maxActive” attribute on the Tomcat servers to 100 or 200 to maximize my database resources?

Thanks
Dave

[davecramer]

Well you have a bit of a conundrum here, eh? You have 2 maximums that don't know about each other. 1 is the local tomcat pool and the other is postgres. Looks to me like you maxed out the one tomcat pool.

[dbothwell]

I agree, I just had another exception about an hour ago and this time the PostgreSQL active backend count was much lower then Tomcat maxActive value. I'm thinking it was just a coincidence they were equal the last time. I'm looking into creating a servlet to monitoring the Tomcat connection pool.

Thanks for getting back to me so quickly.
Dave