jdbc connection pool size 2

[clemensx]

Hi,

we use TomEE 7.0.4 productively in a Cloud Foundry environment. JDBC
connections are only used via JPA (eclipselink). Usually after one day or so
uptime we see these errors:

org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8080-exec-10]
Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none
available[size:2; busy:2; idle:0; lastwait:30000].",

We did not specify pool size anywhere and I believe standard pool size is
more like 100 than 2. What could cause this extremely small pool size? Is
this somehow negotiated with the JDBC driver and/or DBMS? We use postgresql
9.4.1212 jdbc driver for a postgresql server on version 9.6.

Any ideas greatly appreciated. Many thanks!



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[Romain Manni-Bucau]

Hello,

are you able to use jmx to dump the datasource mbeans to see the actual
config? (ensure to remove your credentials before sharing this ;)).

Romain Manni-Bucau
@rmannibucau <https://twitter.com/rmannibucau> | Blog
<https://rmannibucau.metawerx.net/> | Old Blog
<http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> |
LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book
<https://www.packtpub.com/application-development/java-ee-8-high-performance>

Post by clemensx
Hi,
we use TomEE 7.0.4 productively in a Cloud Foundry environment. JDBC
connections are only used via JPA (eclipselink). Usually after one day or so
org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8080-exec-10]
Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none
available[size:2; busy:2; idle:0; lastwait:30000].",
We did not specify pool size anywhere and I believe standard pool size is
more like 100 than 2. What could cause this extremely small pool size? Is
this somehow negotiated with the JDBC driver and/or DBMS? We use postgresql
9.4.1212 jdbc driver for a postgresql server on version 9.6.
Any ideas greatly appreciated. Many thanks!
--
http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[clemensx]

<Loading Image...>
<Loading Image...>

I guess MaxActive 2 is the problem?





--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[Jonathan Gallimore]

Yes, although it looks like you're also waiting 30 seconds to get a
connection, which suggests maybe those 2 connections aren't coming back to
the pool as well. Are you able to get a thread dump of the process?

Jon

Post by clemensx
<http://tomee-openejb.979440.n4.nabble.com/file/t376152/mbeans1.jpg>
<http://tomee-openejb.979440.n4.nabble.com/file/t376152/mbeans2.jpg>
I guess MaxActive 2 is the problem?
--
http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[clemensx]

We have very long background processes working intensively with the DB. So I
would guess that at least one connection is almost always in use. Two just
is too few connections. Even if a much larger pool would maybe not really
solve the problem, it would at least make it much less severe.

If we last around a day currently with two connections, my guess would be to
last many days with many more connections...



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[Jonathan Gallimore]

I tend to run with small connection pools and only increase them if its
really necessary. If you do have something leaking a connection somewhere,
its worth solving that rather than increasing the pool size. That said, I'd
say a maxactive of 2 is very small, and maybe 10 would be a good starting
point. If you're seeing most of your connections in use all the time at
that point, get some thread dumps and maybe some timings on your queries to
make sure nothing is getting stuck.

Jon

Post by clemensx
We have very long background processes working intensively with the DB. So I
would guess that at least one connection is almost always in use. Two just
is too few connections. Even if a much larger pool would maybe not really
solve the problem, it would at least make it much less severe.
If we last around a day currently with two connections, my guess would be to
last many days with many more connections...
--
http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[Romain Manni-Bucau]

I'd just add to Jon's answer that setting a maxWait to 0 will help you to
identify these issues, otherwise it can hides some undersizing problem
during the tuning phase.

Romain Manni-Bucau
@rmannibucau <https://twitter.com/rmannibucau> | Blog
<https://rmannibucau.metawerx.net/> | Old Blog
<http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> |
LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book
<https://www.packtpub.com/application-development/java-ee-8-high-performance>


Le ven. 28 sept. 2018 à 16:24, Jonathan Gallimore <

Post by Jonathan Gallimore
I tend to run with small connection pools and only increase them if its
really necessary. If you do have something leaking a connection somewhere,
its worth solving that rather than increasing the pool size. That said, I'd
say a maxactive of 2 is very small, and maybe 10 would be a good starting
point. If you're seeing most of your connections in use all the time at
that point, get some thread dumps and maybe some timings on your queries to
make sure nothing is getting stuck.
Jon

Post by clemensx
We have very long background processes working intensively with the DB.

So

Post by clemensx
I
would guess that at least one connection is almost always in use. Two

just

Post by clemensx
is too few connections. Even if a much larger pool would maybe not really
solve the problem, it would at least make it much less severe.
If we last around a day currently with two connections, my guess would be to
last many days with many more connections...
--
http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[clemensx]

Thank for all the good suggestions. But my main question remains: Why is the
max connection count set to 2? This cannot be the default, right?

Also, changing (default) parameters is very complicated in a CloudFoundry
environment - I didn't get this working for other problems, e.g. with
default thread pool size. Nevertheless, I first need to understand why TomEE
uses 2 connections, then think about where to change that.



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html

[Romain Manni-Bucau]

2 is not our default, it should be inherited from the pool defaults

In cloudfoundry or any environment it is quite easy to change the default
cause you can always use resources.xml to force the value if you want (it
supports placeholders on the env variable if needed to get back the
url/user/password)

Romain Manni-Bucau
@rmannibucau <https://twitter.com/rmannibucau> | Blog
<https://rmannibucau.metawerx.net/> | Old Blog
<http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> |
LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book
<https://www.packtpub.com/application-development/java-ee-8-high-performance>

Post by clemensx
Thank for all the good suggestions. But my main question remains: Why is the
max connection count set to 2? This cannot be the default, right?
Also, changing (default) parameters is very complicated in a CloudFoundry
environment - I didn't get this working for other problems, e.g. with
default thread pool size. Nevertheless, I first need to understand why TomEE
uses 2 connections, then think about where to change that.
--
http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html