1

Looking for advice - I've read the other two threads about this

in my server.xml file I have two places where maxThreads are defined in two places:

  1. <Executor name="tomcatThreadPool" namePrefix="catalina-exec-" maxThreads="100" minSpareThreads="4"/>

AND

  1. <Connector port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol" maxThreads="100" SSLEnabled="true" scheme="https" secure="true" connectionTimeout="600000" keystoreFile="/usr/local/tomcat/conf/keystore.p12" keystorePass="mypassword" clientAuth="false" sslProtocol="TLS" />

The error we are frequently running into with our server is : "Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available [size:100;busy:100;idle:0;lastwait:30000]" before a fatal shutdown of the system (the machine resets and starts up again - on an AWS ECS cluster)

When I increase the maxThreads value to 300 in the second instance listed here, we get the same error message - so I'm not sure if the connection size has increased at all. The system behaviour is different (machine doesnt restart) but then users cannot connect - it eventually needs manual restart.

How can I achieve more connections to the system or keep connectivity as high as possible?

In other posts about this topic some suggest decreasing maxThreads as well (assuming they complete quickly) could give better performance.

UPDATE:

in my application-properties file i had the following settings:

spring.datasource.url=jdbc:postgresql://db####
spring.datasource.username=#####
spring.datasource.password=######
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.max-active=60
spring.datasource.tomcat.test-on-borrow=true

spring.jpa.show-sql=false
#spring.jpa.hibernate.ddl-auto=create-drop
#spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.jpa.hibernate.connection.provider_class=org.hibernate.c3p0.internal.C3P0ConnectionProvider
spring.jpa.properties.hibernate.c3p0.min_size=1
spring.jpa.properties.hibernate.c3p0.max_size=30
spring.jpa.properties.hibernate.c3p0.timeout=120
spring.jpa.properties.hibernate.c3p0.max_statements=20
RebRy
  • 11
  • 1
  • 6
  • Those messages are not sent by Tomcat's working thread pool, but by your database connection pool. Can you add the configuration of your DB connection pool? – Piotr P. Karwasz Jul 22 '21 at 22:15
  • Thanks for your response Piotr - in my database I've checked the (postgres) configuration file and the maxConnections is set to 500. Are the settings defined on the postgres server side? – RebRy Jul 22 '21 at 23:04
  • Your application probably uses a connection pool (Tomcat JDBC, DBCP2, HikariCP to name a few possibilities). Each one of them is configured in a different way. By [googling your error message](https://www.google.com/search?q=size%3A100%3Bbusy%3A100%3Bidle%3A0%3Blastwait%3A30000) one can guess that you are using Tomcat JDBC. Follow the [documentation](https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html) to increase `maxActive` (by default 100). – Piotr P. Karwasz Jul 22 '21 at 23:20
  • Yes - using Tomcat JDBC - as i'm new to this i couldn't figure out where else to look for these settings. This has helped enormously - thanks for posting the links! – RebRy Jul 22 '21 at 23:48
  • BTW: if you have 100 working threads and 100 database connections in the pool, your application might be leaking connections (i.e. not returning them to the pool). You might try using the `logAbandoned` and `removeAbandoned` properties of the pool to find them. – Piotr P. Karwasz Jul 23 '21 at 06:26
  • I have updated here showing the application-properties (which I think defines/defaults to the values I'm getting in the error). I havent had a chance to test in production yet. – RebRy Jul 26 '21 at 03:59

1 Answers1

1

Your application suffers from database connection pool exhaustion: since you have more working threads in Tomcat (100) than available connections in the connection pool (60), many threads need to wait for a connection to be available. You should have at least as many connections to the database as you have working threads. Try with:

spring.datasource.tomcat.max-active=200

Remark: Since your <Connector> does not have an executor attribute, the <Executor> you created is not used and can be deleted (unless another connector uses it).

Since there is no spring.jpa.hibernate.connection.provider_class property, the C3P0 connection pool you try to configure is never created: Hibernate will use the one configured through the spring.datasource.* properties. You can therefore remove the C3P0-related properties.

Piotr P. Karwasz
  • 5,292
  • 2
  • 9
  • 20
  • Unfortunately - with these changes the service still had the same error "Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000]." (I added maxConnections=300 to the – RebRy Jul 30 '21 at 00:56
  • Since the connection pool doesn't seem to change after a reconfiguration, maybe your application doesn't use Spring's connection pool, but another one. Check if you don't have a `` definition of type `javax.sql.DataSource` in your Tomcat `conf` folder (it can be in many different files). – Piotr P. Karwasz Jul 30 '21 at 05:08
  • I am looking back at this problem again (as we managed to live with it) and am revisiting being able to adjust our settings and missed a detail in your remark -- I have seen some commented out code in our server.xml : . would uncommenting these lines use the executor defined earlier in the server.xml file? Is there a way to echo/print/log these sorts of properties/configurations in the java/tomcat startup logging? – RebRy Oct 27 '21 at 05:54