[prev in list] [next in list] [prev in thread] [next in thread] 

List:       tomcat-user
Subject:    Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
From:       Christopher Schultz <chris () christopherschultz ! net>
Date:       2021-08-13 17:12:51
Message-ID: 50f45ec6-811c-f3a6-170d-fb1c80b3abf4 () christopherschultz ! net
[Download RAW message or body]

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 <
> chris@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 <Resource> 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 <
>>> chris@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-unsubscribe@tomcat.apache.org
>>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>>
>>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]