Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Sampath,
On 8/12/21 07:02, Sampath Rajapakshe wrote:
Hi Chris,
Thanks for the detailed explanation, yes, we tried with abandoned true logs
and found an issue with our code base as well.
It seems we had a case where a single thread creates a new connection and
before closing that connection creates a new connection and closes that new
connection and then afterwards closes the initial connection. So in a
scenario where a huge traffic goes through the same logic path pool gets
exhausted due to all threads waiting to create another connection before
closing their initial connection.
After fixing that issue, now the system runs without pool exhaustion.
So thank you very much for your explanations.
Yes, this is item #1 in my (ancient!) blog post's "general tips" section:
1. In development, set your connection pool to a fixed size of 1
connection. [...]
If you had set you pool size to "exactly 1" in development, you would
have caught this problem long ago.
-chris
On Wed, Aug 11, 2021 at 7:25 PM Christopher Schultz <
ch...@christopherschultz.net> wrote:
Sampath,
On 8/9/21 01:45, Sampath Rajapakshe wrote:
In our case, we know the reason for the pool exhausted behaviour,
there are slow queries and also due to high TPS where pool is not
enough. So we are expected to get pool exhaustion with current
configurations.
Ok.
What we wanted to verify was the behaviour after pool exhaustion. Do
the current executing connections continue their executions during
pool exhausted duration?
I would not expect the connection pool to actively kill connections
unless explicitly configured to do so. Usually, connections that are
"orphaned" will stay that way, "just in case". If you aren't seeing
exceptions being thrown due to "connection is closed" or some such
thing, you are probably okay as far as the long-running queries are
concerned.
Do you know in advance which queries will take a "long time"? Perhaps
you'd like to use a different connection pool for those long-running
queries -- one where the timeout is significantly higher.
As per our observations, they do not, and connections are stuck
without executing any queries until maxWait. is that the expected
behaviour after pool exhaustion?
Let's be clear what we mean when we say "connection". The only
"connection" here that is relevant is the "connection to the database."
It sounds like you mean "incoming HTTP connection" whose thread will
stall if a DB connection is not immediately available from the pool.
That may be true, but the "(HTTP) connection" isn't waiting for a DB
connection; the request-processing thread is waiting for a DB connection.
Do you mean "behavior of connections checked-out and used long-term" or
do you mean "behavior of the pool when all connections are checked-out
and we need a NEW one?"
I assume the second question is what you are asking.
When all the connections are being used, the pool usually stalls,
meaning that your code will just sit there a wait (possibly forever) for
a connection. To fix that, you'd have to adjust the configuration of the
pool (e.g. add more possible connections, increase maxWait to avoid
errors). You can also usually configure the pool to allow connections
which are checked-out and not returned after a certain period of time
("abandoned" to use the Commons-Pool terminology) to be allowed to
"leak" and replenish the pool.
You didn't say which pool you were using, so I will assume you are using
the default DB connection pool based upon Apache commons-dbcp2. Here is
the documentation for that pool; you can use these configuration
settings on your element in your web application's
META-INF/context.xml file:
https://commons.apache.org/proper/commons-dbcp/configuration.html
I recommend looking at the "abandoned"-related configuration options.
-chris
On Sat, Aug 7, 2021 at 3:43 AM Christopher Schultz <
ch...@christopherschultz.net> wrote:
Sampath,
On 8/6/21 08:37, Sampath Rajapakshe wrote:
Hi All,
In my local setup before pool exhaustion exception is thrown, all the
connections seem to be in freezed and when checking processList in
mysql,
those connections are in sleep state and doesn't execute any queries.
After
waiting for maxWait period the pool exhausted exception gets thrown and
seems to reset the connections and then the queries are getting
processed
as normally.
>
So, my question is, with pool exhausted scenarios, doesn't existing
connections execute their queries during that time(maxWait) and try to
resolve the exhausted behaviour by releasing those connections to idle
queue automatically? When checking the JMX matrix during this pool
exhausted time all the connections are in the active queue.
https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/
If not, what i am experiencing is as expected behaviour where the
system
is
stuck after pool exhaustion for the best case of maxWait?
Most of the time I've seen this kind of behavior
Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Hi Chris,
Thanks for the detailed explanation, yes, we tried with abandoned true logs
and found an issue with our code base as well.
It seems we had a case where a single thread creates a new connection and
before closing that connection creates a new connection and closes that new
connection and then afterwards closes the initial connection. So in a
scenario where a huge traffic goes through the same logic path pool gets
exhausted due to all threads waiting to create another connection before
closing their initial connection.
After fixing that issue, now the system runs without pool exhaustion.
So thank you very much for your explanations.
Thanks and Regards,
Sampath
On Wed, Aug 11, 2021 at 7:25 PM Christopher Schultz <
ch...@christopherschultz.net> wrote:
> Sampath,
>
> On 8/9/21 01:45, Sampath Rajapakshe wrote:
> > In our case, we know the reason for the pool exhausted behaviour,
> > there are slow queries and also due to high TPS where pool is not
> > enough. So we are expected to get pool exhaustion with current
> > configurations.
> Ok.
>
> > What we wanted to verify was the behaviour after pool exhaustion. Do
> > the current executing connections continue their executions during
> > pool exhausted duration?
> I would not expect the connection pool to actively kill connections
> unless explicitly configured to do so. Usually, connections that are
> "orphaned" will stay that way, "just in case". If you aren't seeing
> exceptions being thrown due to "connection is closed" or some such
> thing, you are probably okay as far as the long-running queries are
> concerned.
>
> Do you know in advance which queries will take a "long time"? Perhaps
> you'd like to use a different connection pool for those long-running
> queries -- one where the timeout is significantly higher.
>
> > As per our observations, they do not, and connections are stuck
> > without executing any queries until maxWait. is that the expected
> > behaviour after pool exhaustion?
> Let's be clear what we mean when we say "connection". The only
> "connection" here that is relevant is the "connection to the database."
> It sounds like you mean "incoming HTTP connection" whose thread will
> stall if a DB connection is not immediately available from the pool.
> That may be true, but the "(HTTP) connection" isn't waiting for a DB
> connection; the request-processing thread is waiting for a DB connection.
>
> Do you mean "behavior of connections checked-out and used long-term" or
> do you mean "behavior of the pool when all connections are checked-out
> and we need a NEW one?"
>
> I assume the second question is what you are asking.
>
> When all the connections are being used, the pool usually stalls,
> meaning that your code will just sit there a wait (possibly forever) for
> a connection. To fix that, you'd have to adjust the configuration of the
> pool (e.g. add more possible connections, increase maxWait to avoid
> errors). You can also usually configure the pool to allow connections
> which are checked-out and not returned after a certain period of time
> ("abandoned" to use the Commons-Pool terminology) to be allowed to
> "leak" and replenish the pool.
>
> You didn't say which pool you were using, so I will assume you are using
> the default DB connection pool based upon Apache commons-dbcp2. Here is
> the documentation for that pool; you can use these configuration
> settings on your element in your web application's
> META-INF/context.xml file:
>
> https://commons.apache.org/proper/commons-dbcp/configuration.html
>
> I recommend looking at the "abandoned"-related configuration options.
>
> -chris
>
> > On Sat, Aug 7, 2021 at 3:43 AM Christopher Schultz <
> > ch...@christopherschultz.net> wrote:
> >
> >> Sampath,
> >>
> >> On 8/6/21 08:37, Sampath Rajapakshe wrote:
> >>> Hi All,
> >>>
> >>> In my local setup before pool exhaustion exception is thrown, all the
> >>> connections seem to be in freezed and when checking processList in
> mysql,
> >>> those connections are in sleep state and doesn't execute any queries.
> >> After
> >>> waiting for maxWait period the pool exhausted exception gets thrown and
> >>> seems to reset the connections and then the queries are getting
> processed
> >>> as normally.
> >> >
> >>> So, my question is, with pool exhausted scenarios, doesn't existing
> >>> connections execute their queries during that time(maxWait) and try to
> >>> resolve the exhausted behaviour by releasing those connections to idle
> >>> queue automatically? When checking the JMX matrix during this pool
> >>> exhausted time all the connections are in the active queue.
> >>
> >>
> >>
> https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/
> >>
> >>> If not, what i am experiencing is as expected behaviour where the
> system
> >> is
> >>> stuck after pool exhaustion for the best case of maxWait?
> >>
> >> Most of the time I've seen this kind of behavior it's due to sloppy
> >> resource-management.
>
Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Sampath,
On 8/9/21 01:45, Sampath Rajapakshe wrote:
In our case, we know the reason for the pool exhausted behaviour,
there are slow queries and also due to high TPS where pool is not
enough. So we are expected to get pool exhaustion with current
configurations.
Ok.
What we wanted to verify was the behaviour after pool exhaustion. Do
the current executing connections continue their executions during
pool exhausted duration?
I would not expect the connection pool to actively kill connections
unless explicitly configured to do so. Usually, connections that are
"orphaned" will stay that way, "just in case". If you aren't seeing
exceptions being thrown due to "connection is closed" or some such
thing, you are probably okay as far as the long-running queries are
concerned.
Do you know in advance which queries will take a "long time"? Perhaps
you'd like to use a different connection pool for those long-running
queries -- one where the timeout is significantly higher.
As per our observations, they do not, and connections are stuck
without executing any queries until maxWait. is that the expected
behaviour after pool exhaustion?
Let's be clear what we mean when we say "connection". The only
"connection" here that is relevant is the "connection to the database."
It sounds like you mean "incoming HTTP connection" whose thread will
stall if a DB connection is not immediately available from the pool.
That may be true, but the "(HTTP) connection" isn't waiting for a DB
connection; the request-processing thread is waiting for a DB connection.
Do you mean "behavior of connections checked-out and used long-term" or
do you mean "behavior of the pool when all connections are checked-out
and we need a NEW one?"
I assume the second question is what you are asking.
When all the connections are being used, the pool usually stalls,
meaning that your code will just sit there a wait (possibly forever) for
a connection. To fix that, you'd have to adjust the configuration of the
pool (e.g. add more possible connections, increase maxWait to avoid
errors). You can also usually configure the pool to allow connections
which are checked-out and not returned after a certain period of time
("abandoned" to use the Commons-Pool terminology) to be allowed to
"leak" and replenish the pool.
You didn't say which pool you were using, so I will assume you are using
the default DB connection pool based upon Apache commons-dbcp2. Here is
the documentation for that pool; you can use these configuration
settings on your element in your web application's
META-INF/context.xml file:
https://commons.apache.org/proper/commons-dbcp/configuration.html
I recommend looking at the "abandoned"-related configuration options.
-chris
On Sat, Aug 7, 2021 at 3:43 AM Christopher Schultz <
ch...@christopherschultz.net> wrote:
Sampath,
On 8/6/21 08:37, Sampath Rajapakshe wrote:
Hi All,
In my local setup before pool exhaustion exception is thrown, all the
connections seem to be in freezed and when checking processList in mysql,
those connections are in sleep state and doesn't execute any queries.
After
waiting for maxWait period the pool exhausted exception gets thrown and
seems to reset the connections and then the queries are getting processed
as normally.
>
So, my question is, with pool exhausted scenarios, doesn't existing
connections execute their queries during that time(maxWait) and try to
resolve the exhausted behaviour by releasing those connections to idle
queue automatically? When checking the JMX matrix during this pool
exhausted time all the connections are in the active queue.
https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/
If not, what i am experiencing is as expected behaviour where the system
is
stuck after pool exhaustion for the best case of maxWait?
Most of the time I've seen this kind of behavior it's due to sloppy
resource-management.
-chris
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Hi Chris, Thanks for the clarification. I've gone through the blog, In our case, we know the reason for the pool exhausted behaviour, there are slow queries and also due to high TPS where pool is not enough. So we are expected to get pool exhaustion with current configurations. What we wanted to verify was the behaviour after pool exhaustion. Do the current executing connections continue their executions during pool exhausted duration? As per our observations, they do not, and connections are stuck without executing any queries until maxWait. is that the expected behaviour after pool exhaustion? Thanks and Regards, Sampath On Sat, Aug 7, 2021 at 3:43 AM Christopher Schultz < ch...@christopherschultz.net> wrote: > Sampath, > > On 8/6/21 08:37, Sampath Rajapakshe wrote: > > Hi All, > > > > In my local setup before pool exhaustion exception is thrown, all the > > connections seem to be in freezed and when checking processList in mysql, > > those connections are in sleep state and doesn't execute any queries. > After > > waiting for maxWait period the pool exhausted exception gets thrown and > > seems to reset the connections and then the queries are getting processed > > as normally. > > > > So, my question is, with pool exhausted scenarios, doesn't existing > > connections execute their queries during that time(maxWait) and try to > > resolve the exhausted behaviour by releasing those connections to idle > > queue automatically? When checking the JMX matrix during this pool > > exhausted time all the connections are in the active queue. > > > https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/ > > > If not, what i am experiencing is as expected behaviour where the system > is > > stuck after pool exhaustion for the best case of maxWait? > > Most of the time I've seen this kind of behavior it's due to sloppy > resource-management. > > -chris > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > > -- *Sampath Rajapakse* | Senior Software Engineer | WSO2 Inc. +94717313761 | samp...@wso2.com
Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Sampath, On 8/6/21 08:37, Sampath Rajapakshe wrote: Hi All, In my local setup before pool exhaustion exception is thrown, all the connections seem to be in freezed and when checking processList in mysql, those connections are in sleep state and doesn't execute any queries. After waiting for maxWait period the pool exhausted exception gets thrown and seems to reset the connections and then the queries are getting processed as normally. > So, my question is, with pool exhausted scenarios, doesn't existing connections execute their queries during that time(maxWait) and try to resolve the exhausted behaviour by releasing those connections to idle queue automatically? When checking the JMX matrix during this pool exhausted time all the connections are in the active queue. https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/ If not, what i am experiencing is as expected behaviour where the system is stuck after pool exhaustion for the best case of maxWait? Most of the time I've seen this kind of behavior it's due to sloppy resource-management. -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
