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 <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 <
> > 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
>
>
--
*Sampath Rajapakse* | Senior Software Engineer | WSO2 Inc.
+94717313761 | samp...@wso2.com
