Keep alive JDBC Connection Pool

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Keep alive JDBC Connection Pool

Trinath
Hi,
  Is there any way I can keep JDBC Connection Pools alive for the lifecycle of the server? There is a testQuery configuration but this gets used only when handing over a connection from pool to the requestor. I need the pool to be maintained throughout so that I always get a valid connection. I am using JDK 1.6 with BTM 1.3.3
  Also are there any JMX APIs to monitor the connection pools (active, current, pending etc)?
  Many Thanks.
Regards,
Trinath
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban
Administrator
Hi,

There already is some basic JMX support for monitoring the pools in BTM 1.3.3 but it's quite basic. An improved version has been implemented and will be delivered with next version: http://jira.codehaus.org/browse/BTM-32

I have no idea what you mean with keeping the connections alive.

Likewise, 'always getting a valid connection' sounds vague. If you configure a testQuery the pool guarantees it will always return a valid connection. What's wrong with this mechanism?
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Hi Ludovic,
  Thanks for your quick response. Could you please let me know how do we make use of the basic JMX support which is already existing. Because I looked at all the JMX APIs through jManage and I could not find anything which would be useful. I am able to peek into a particular connection object but that is not something I am looking forward to. My query is more towards BTM-32. We should be able to use it once its out.

   Coming to the main issue: this is what I am trying to do when I got an exception.
   Init and max connections in Pool - 100
   After some idle time (say if I leave the server overnight come in the morning and fire some tests), when I request for a connection, bitronix invokes testQuery() and finds the connection is invalid, waits for a sec (default time) and then tries to pick up another connection in pool which meets with the same fate and so on. So after looping through 30 times, we hit the limit of AcquisitionTimeout of 30sec and we get an exception. So this is the problem. Hope I made it clear.

   I am expecting some functionality like what is present in WLS like test connections regularly, say every 5 min, and also test before giving the connection to the caller, so that the possibility of getting broken connections would be lot lesser (if not eliminated).

   I can increase the AcquistionTimeout and reduce the AcquisitionInterval to 0 with a reduced ConnectionPool size to make things work, however need to have connection pool settings at a relatively high level so that we cater to peak volumes.

  Any suggestions would be much appreciated!
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban
Administrator
If you already peeked at the JMX beans and did not find what you need the only thing you can do is to wait for the next release. I would also suggest you to try out a trunk build to make sure the BTM-32 changes meet your requirements.

Regarding your connection pool 'problem' I understand your arguments but I think you're looking at it from the wrong perspective.

And are you certain you want your DBMS to kill connections after a certain timeout on production?
If yes, the pool can be configured to shrink to 0 when connections are idle for too long. Why would you keep connections open in the pool if you want the DB server to actually close them after some time?

Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Ok. Problem is not DB killing the connections, problem is we are reaching database through proxies/firewalls which is dropping the connections. DB is happy to keep connections on, untill we issue a close command. We would ideally like the keepalive interval to be less than the time proxies drop the connection. Hope I have made myself clear.
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban
Administrator
It's not the DB which is killing the connections but the problem is basically the same: idle connections are dropped so why not configuring the min pool size to 0 to have them expire before your proxy/firewall kills them?

I could implement some connection keepalive mechanism but that won't happen anytime soon so you'd still have to find a workaround in the meantime.
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Ludovic Orban wrote
It's not the DB which is killing the connections but the problem is basically the same: idle connections are dropped so why not configuring the min pool size to 0 to have them expire before your proxy/firewall kills them?

I could implement some connection keepalive mechanism but that won't happen anytime soon so you'd still have to find a workaround in the meantime.
I think then this negates the concept of connection pooling and will lead to increased response times. I will be looking forward to your keepalive implementation whenever it comes.
Just want to add that I have worked around this problem by setting acquistioninterval to 0sec and acquisitiontimeout to 300sec.
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Sir,
   Do we have the keepAlive functionality available yet (after two years)? This is a persistent problem and all along we have been working around it.

  The problem we have is, once we hit the limit, there is no way we can get a valid connection. So it would really help if a functionality is provided where once Bitronix reaches the upper limit, it will reclaim all invalid connections and re-create valid connection(s) to hand over to the requestor(s).

Regards,
Trinath

Trinath wrote
Ludovic Orban wrote
It's not the DB which is killing the connections but the problem is basically the same: idle connections are dropped so why not configuring the min pool size to 0 to have them expire before your proxy/firewall kills them?

I could implement some connection keepalive mechanism but that won't happen anytime soon so you'd still have to find a workaround in the meantime.
I think then this negates the concept of connection pooling and will lead to increased response times. I will be looking forward to your keepalive implementation whenever it comes.
Just want to add that I have worked around this problem by setting acquistioninterval to 0sec and acquisitiontimeout to 300sec.
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Brett Wooldridge-2
Trinath,

I have a question.  I'm a developer who works on BTM, and I've added a
feature to an experimental branch of bitronix.  I was wondering if this feature
might help in your case.

In my case, my company uses Bitronix in combination with Apache Derby.
Derby has a limitation that all temporary tables are in-memory, and the
resources are not freed until the connection the temporary tables was
created on is closed.  This obviously causes a problem with connection 
pools.  Connections that are never closed, but are used for long periods of
time, eventually consume ever increasing amounts of memory.

In order to work around this issue, I have been experimenting with a new
bitronix setting to configure a "maximum lifetime" on a database connection.
Connections in the idle state, but which exceed the maximum lifetime are
closed (and the pool is refilled up to the minimum level).  Note this is different
from an "idle timeout".  In our system, there is constant activity, which keeps
connections in the pool from ever reaching the standard idle timeout threshold.

But with this new setting, if you configured a maximum lifetime for a connection of
30 minutes, it would be closed if it had lived more than 30 minutes, even if it was
only idle for 30 seconds.

If this setting would be useful to you, and it sounds like it might, I can look at
merging the change over into the trunk.  I've been intending do to it, because it is
essential for anyone using Derby and temporary tables, but there hasn't been an
urgency to do so.

Brett

On Thu, Apr 5, 2012 at 7:35 PM, Trinath <[hidden email]> wrote:

Sir,
  Do we have the keepAlive functionality available yet (after two years)?
This is a persistent problem and all along we have been working around it.

 The problem we have is, once we hit the limit, there is no way we can get
a valid connection. So it would really help if a functionality is provided
where once Bitronix reaches the upper limit, it will reclaim all invalid
connections and re-create valid connection(s) to hand over to the
requestor(s).

Regards,
Trinath


Trinath wrote:
>
>
> Ludovic Orban wrote:
>>
>> It's not the DB which is killing the connections but the problem is
>> basically the same: idle connections are dropped so why not configuring
>> the min pool size to 0 to have them expire before your proxy/firewall
>> kills them?
>>
>> I could implement some connection keepalive mechanism but that won't
>> happen anytime soon so you'd still have to find a workaround in the
>> meantime.
>>
> I think then this negates the concept of connection pooling and will lead
> to increased response times. I will be looking forward to your keepalive
> implementation whenever it comes.
> Just want to add that I have worked around this problem by setting
> acquistioninterval to 0sec and acquisitiontimeout to 300sec.
>

--
View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33568802.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Hi Brett,
  I might be able to work around my problem with this setting however my requirement is a bit different from yours.
   My requirement is: Keeping alive the connections in pool for ever (or until the servers are bounced) and if they happen to break for any reason like DB crash, network/firewall dropping the connections, they need to be resusicated to the min pool level.
   In this way, I am always guarenteed a connection and a valid one if I turn on 'testQuery' (until ofcourse I run out of pool i.e. all connections in pool are in use).
   Hope I am clear.
 
Regards,
Trinath

Brett Wooldridge-2 wrote
Trinath,

I have a question.  I'm a developer who works on BTM, and I've added a
feature to an experimental branch of bitronix.  I was wondering if this
feature
might help in your case.

In my case, my company uses Bitronix in combination with Apache Derby.
Derby has a limitation that all temporary tables are in-memory, and the
resources are not freed until the connection the temporary tables was
created on is closed.  This obviously causes a problem with connection
pools.  Connections that are never closed, but are used for long periods of
time, eventually consume ever increasing amounts of memory.

In order to work around this issue, I have been experimenting with a new
bitronix setting to configure a "maximum lifetime" on a database connection.
Connections in the idle state, but which exceed the maximum lifetime are
closed (and the pool is refilled up to the minimum level).  Note this is
different
from an "idle timeout".  In our system, there is constant activity, which
keeps
connections in the pool from ever reaching the standard idle timeout
threshold.

But with this new setting, if you configured a maximum lifetime for a
connection of
30 minutes, it would be closed if it had lived more than 30 minutes, even
if it was
only idle for 30 seconds.

If this setting would be useful to you, and it sounds like it might, I can
look at
merging the change over into the trunk.  I've been intending do to it,
because it is
essential for anyone using Derby and temporary tables, but there hasn't
been an
urgency to do so.

Brett

On Thu, Apr 5, 2012 at 7:35 PM, Trinath <trinathm@techmahindra.com> wrote:

>
> Sir,
>   Do we have the keepAlive functionality available yet (after two years)?
> This is a persistent problem and all along we have been working around it.
>
>  The problem we have is, once we hit the limit, there is no way we can get
> a valid connection. So it would really help if a functionality is provided
> where once Bitronix reaches the upper limit, it will reclaim all invalid
> connections and re-create valid connection(s) to hand over to the
> requestor(s).
>
> Regards,
> Trinath
>
>
> Trinath wrote:
> >
> >
> > Ludovic Orban wrote:
> >>
> >> It's not the DB which is killing the connections but the problem is
> >> basically the same: idle connections are dropped so why not configuring
> >> the min pool size to 0 to have them expire before your proxy/firewall
> >> kills them?
> >>
> >> I could implement some connection keepalive mechanism but that won't
> >> happen anytime soon so you'd still have to find a workaround in the
> >> meantime.
> >>
> > I think then this negates the concept of connection pooling and will lead
> > to increased response times. I will be looking forward to your keepalive
> > implementation whenever it comes.
> > Just want to add that I have worked around this problem by setting
> > acquistioninterval to 0sec and acquisitiontimeout to 300sec.
> >
>
> --
> View this message in context:
> http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33568802.html
> Sent from the Bitronix Transaction Manager mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Brett Wooldridge-2
Hi Trinath,

Yes, your requirement was clear.  But I read it more strictly in terms
of behavior rather than implementation.  From my reading, your
requirement is/was: I am always guarenteed a connection and a valid
one.  Whether connections are kept alive forever or for a maximum
lifetime seems like an implementation detail.  With the maximum
lifetime setting, in a normally functioning system, you should always
get valid connections.

Even if connections in the idle pool were checked periodically as you
suggest (say every 30 seconds) for 'aliveness', there will always be a
window in which dead connections will be in the idle pool.
Additionally, such a live check would add significant overhead to the
pool (not to mention your database).  The pool maintenance thread is
designed to run in 10's of milliseconds.  A live check could run 10's
os milliseconds per-connection.  In a pool with several hundred
connections, the overhead would be quite high.

Regards,
Brett

Sent from my iPad

On Apr 9, 2012, at 23:12, Trinath <[hidden email]> wrote:

>
> Hi Brett,
>  I might be able to work around my problem with this setting however my
> requirement is a bit different from yours.
>   My requirement is: Keeping alive the connections in pool for ever (or
> until the servers are bounced) and if they happen to break for any reason
> like DB crash, network/firewall dropping the connections, they need to be
> resusicated to the min pool level.
>   In this way, I am always guarenteed a connection and a valid one if I
> turn on 'testQuery' (until ofcourse I run out of pool i.e. all connections
> in pool are in use).
>   Hope I am clear.
>
> Regards,
> Trinath
>
>
> Brett Wooldridge-2 wrote:
>>
>> Trinath,
>>
>> I have a question.  I'm a developer who works on BTM, and I've added a
>> feature to an experimental branch of bitronix.  I was wondering if this
>> feature
>> might help in your case.
>>
>> In my case, my company uses Bitronix in combination with Apache Derby.
>> Derby has a limitation that all temporary tables are in-memory, and the
>> resources are not freed until the connection the temporary tables was
>> created on is closed.  This obviously causes a problem with connection
>> pools.  Connections that are never closed, but are used for long periods
>> of
>> time, eventually consume ever increasing amounts of memory.
>>
>> In order to work around this issue, I have been experimenting with a new
>> bitronix setting to configure a "maximum lifetime" on a database
>> connection.
>> Connections in the idle state, but which exceed the maximum lifetime are
>> closed (and the pool is refilled up to the minimum level).  Note this is
>> different
>> from an "idle timeout".  In our system, there is constant activity, which
>> keeps
>> connections in the pool from ever reaching the standard idle timeout
>> threshold.
>>
>> But with this new setting, if you configured a maximum lifetime for a
>> connection of
>> 30 minutes, it would be closed if it had lived more than 30 minutes, even
>> if it was
>> only idle for 30 seconds.
>>
>> If this setting would be useful to you, and it sounds like it might, I can
>> look at
>> merging the change over into the trunk.  I've been intending do to it,
>> because it is
>> essential for anyone using Derby and temporary tables, but there hasn't
>> been an
>> urgency to do so.
>>
>> Brett
>>
>> On Thu, Apr 5, 2012 at 7:35 PM, Trinath <[hidden email]> wrote:
>>
>>>
>>> Sir,
>>>  Do we have the keepAlive functionality available yet (after two years)?
>>> This is a persistent problem and all along we have been working around
>>> it.
>>>
>>> The problem we have is, once we hit the limit, there is no way we can
>>> get
>>> a valid connection. So it would really help if a functionality is
>>> provided
>>> where once Bitronix reaches the upper limit, it will reclaim all invalid
>>> connections and re-create valid connection(s) to hand over to the
>>> requestor(s).
>>>
>>> Regards,
>>> Trinath
>>>
>>>
>>> Trinath wrote:
>>>>
>>>>
>>>> Ludovic Orban wrote:
>>>>>
>>>>> It's not the DB which is killing the connections but the problem is
>>>>> basically the same: idle connections are dropped so why not
>>> configuring
>>>>> the min pool size to 0 to have them expire before your proxy/firewall
>>>>> kills them?
>>>>>
>>>>> I could implement some connection keepalive mechanism but that won't
>>>>> happen anytime soon so you'd still have to find a workaround in the
>>>>> meantime.
>>>>>
>>>> I think then this negates the concept of connection pooling and will
>>> lead
>>>> to increased response times. I will be looking forward to your
>>> keepalive
>>>> implementation whenever it comes.
>>>> Just want to add that I have worked around this problem by setting
>>>> acquistioninterval to 0sec and acquisitiontimeout to 300sec.
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33568802.html
>>> Sent from the Bitronix Transaction Manager mailing list archive at
>>> Nabble.com.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe from this list, please visit:
>>>
>>>   http://xircles.codehaus.org/manage_email
>>>
>>>
>>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33655668.html
> Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Hi Brett,
   Ok, how much would be the overhead of setting maxlifetime vis-a-vis a feature like keep alive. I understand for maxlifetime you will close all connections and open min number of connections unlike keepalive where connection will be created only when broken.

   My situation is my connection will break every 5min as my firewall keeps dropping my connections which are idle for more than 5min. So Maxlifetime will be more useful if most of my connections are broken rather than being open. If they are open I would be needlessly closing them and recreating.

   If I have to set MaxLifeTime, I will have a setting of around 10min on one of my connection pools which is seldom used and leave it to default behaviour (i.e. dont set this parameter) on the connection pool which is heavily used.

Regards,
Trinath

Brett Wooldridge-2 wrote
Hi Trinath,

Yes, your requirement was clear.  But I read it more strictly in terms
of behavior rather than implementation.  From my reading, your
requirement is/was: I am always guarenteed a connection and a valid
one.  Whether connections are kept alive forever or for a maximum
lifetime seems like an implementation detail.  With the maximum
lifetime setting, in a normally functioning system, you should always
get valid connections.

Even if connections in the idle pool were checked periodically as you
suggest (say every 30 seconds) for 'aliveness', there will always be a
window in which dead connections will be in the idle pool.
Additionally, such a live check would add significant overhead to the
pool (not to mention your database).  The pool maintenance thread is
designed to run in 10's of milliseconds.  A live check could run 10's
os milliseconds per-connection.  In a pool with several hundred
connections, the overhead would be quite high.

Regards,
Brett

Sent from my iPad

On Apr 9, 2012, at 23:12, Trinath <trinathm@techmahindra.com> wrote:

>
> Hi Brett,
>  I might be able to work around my problem with this setting however my
> requirement is a bit different from yours.
>   My requirement is: Keeping alive the connections in pool for ever (or
> until the servers are bounced) and if they happen to break for any reason
> like DB crash, network/firewall dropping the connections, they need to be
> resusicated to the min pool level.
>   In this way, I am always guarenteed a connection and a valid one if I
> turn on 'testQuery' (until ofcourse I run out of pool i.e. all connections
> in pool are in use).
>   Hope I am clear.
>
> Regards,
> Trinath
>
>
> Brett Wooldridge-2 wrote:
>>
>> Trinath,
>>
>> I have a question.  I'm a developer who works on BTM, and I've added a
>> feature to an experimental branch of bitronix.  I was wondering if this
>> feature
>> might help in your case.
>>
>> In my case, my company uses Bitronix in combination with Apache Derby.
>> Derby has a limitation that all temporary tables are in-memory, and the
>> resources are not freed until the connection the temporary tables was
>> created on is closed.  This obviously causes a problem with connection
>> pools.  Connections that are never closed, but are used for long periods
>> of
>> time, eventually consume ever increasing amounts of memory.
>>
>> In order to work around this issue, I have been experimenting with a new
>> bitronix setting to configure a "maximum lifetime" on a database
>> connection.
>> Connections in the idle state, but which exceed the maximum lifetime are
>> closed (and the pool is refilled up to the minimum level).  Note this is
>> different
>> from an "idle timeout".  In our system, there is constant activity, which
>> keeps
>> connections in the pool from ever reaching the standard idle timeout
>> threshold.
>>
>> But with this new setting, if you configured a maximum lifetime for a
>> connection of
>> 30 minutes, it would be closed if it had lived more than 30 minutes, even
>> if it was
>> only idle for 30 seconds.
>>
>> If this setting would be useful to you, and it sounds like it might, I can
>> look at
>> merging the change over into the trunk.  I've been intending do to it,
>> because it is
>> essential for anyone using Derby and temporary tables, but there hasn't
>> been an
>> urgency to do so.
>>
>> Brett
>>
>> On Thu, Apr 5, 2012 at 7:35 PM, Trinath <trinathm@techmahindra.com> wrote:
>>
>>>
>>> Sir,
>>>  Do we have the keepAlive functionality available yet (after two years)?
>>> This is a persistent problem and all along we have been working around
>>> it.
>>>
>>> The problem we have is, once we hit the limit, there is no way we can
>>> get
>>> a valid connection. So it would really help if a functionality is
>>> provided
>>> where once Bitronix reaches the upper limit, it will reclaim all invalid
>>> connections and re-create valid connection(s) to hand over to the
>>> requestor(s).
>>>
>>> Regards,
>>> Trinath
>>>
>>>
>>> Trinath wrote:
>>>>
>>>>
>>>> Ludovic Orban wrote:
>>>>>
>>>>> It's not the DB which is killing the connections but the problem is
>>>>> basically the same: idle connections are dropped so why not
>>> configuring
>>>>> the min pool size to 0 to have them expire before your proxy/firewall
>>>>> kills them?
>>>>>
>>>>> I could implement some connection keepalive mechanism but that won't
>>>>> happen anytime soon so you'd still have to find a workaround in the
>>>>> meantime.
>>>>>
>>>> I think then this negates the concept of connection pooling and will
>>> lead
>>>> to increased response times. I will be looking forward to your
>>> keepalive
>>>> implementation whenever it comes.
>>>> Just want to add that I have worked around this problem by setting
>>>> acquistioninterval to 0sec and acquisitiontimeout to 300sec.
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33568802.html
>>> Sent from the Bitronix Transaction Manager mailing list archive at
>>> Nabble.com.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe from this list, please visit:
>>>
>>>   http://xircles.codehaus.org/manage_email
>>>
>>>
>>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33655668.html
> Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban-2
What I really don't get is why the current functionality isn't good enough to cover your needs.

Configuring a test query on the pool should make sure it never hands a non-working connection to the calling code. Do you know why that's not working for you?


On Mon, Apr 9, 2012 at 4:56 PM, Trinath <[hidden email]> wrote:

Hi Brett,
  Ok, how much would be the overhead of setting maxlifetime vis-a-vis a
feature like keep alive. I understand for maxlifetime you will close all
connections and open min number of connections unlike keepalive where
connection will be created only when broken.

  My situation is my connection will break every 5min as my firewall keeps
dropping my connections which are idle for more than 5min. So Maxlifetime
will be more useful if most of my connections are broken rather than being
open. If they are open I would be needlessly closing them and recreating.

  If I have to set MaxLifeTime, I will have a setting of around 10min on
one of my connection pools which is seldom used and leave it to default
behaviour (i.e. dont set this parameter) on the connection pool which is
heavily used.

Regards,
Trinath

Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Hi Ludovic,
   Yes it does, but once we hit the max connections limit in search of a valid connection, there is no way for the requestor to get the valid connection except bouncing the server.

   So the connections keep building up in the pool upto the max limit and if there is a lull for couple of hours then the connections break because of firewall dropping the connections (I see connection reset exceptions when testQuery is fired/executed) as a result there is no way for me to get a valid connection.

Regards,
Trinath

Ludovic Orban-2 wrote
What I really don't get is why the current functionality isn't good enough
to cover your needs.

Configuring a test query on the pool should make sure it never hands a
non-working connection to the calling code. Do you know why that's not
working for you?


On Mon, Apr 9, 2012 at 4:56 PM, Trinath <trinathm@techmahindra.com> wrote:

>
> Hi Brett,
>   Ok, how much would be the overhead of setting maxlifetime vis-a-vis a
> feature like keep alive. I understand for maxlifetime you will close all
> connections and open min number of connections unlike keepalive where
> connection will be created only when broken.
>
>   My situation is my connection will break every 5min as my firewall keeps
> dropping my connections which are idle for more than 5min. So Maxlifetime
> will be more useful if most of my connections are broken rather than being
> open. If they are open I would be needlessly closing them and recreating.
>
>   If I have to set MaxLifeTime, I will have a setting of around 10min on
> one of my connection pools which is seldom used and leave it to default
> behaviour (i.e. dont set this parameter) on the connection pool which is
> heavily used.
>
> Regards,
> Trinath
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban-2
What you're saying sounds bizarre to me. You may have uncovered a bug.

The connection pool should never grow if it still contains idle connections. Idle connections are always tested with the testQuery before being handed over to the callee. If an error occurs during the test, the connection should be closed, removed from the pool and another one from the pool should be picked and tested. If your pool is full of stale connections, a single getConnection() call should clean them all and create a fresh new one.

Since that's not the behavior you're experiencing, can I ask you to write the simplest client you can that reproduces the problem in your environment? I'll then try it out here, making sure there's a firewall in between the pool and the DB that drops connections.

--
 Ludovic

On Mon, Apr 9, 2012 at 5:11 PM, Trinath <[hidden email]> wrote:

Hi Ludovic,
  Yes it does, but once we hit the max connections limit in search of a
valid connection, there is no way for the requestor to get the valid
connection except bouncing the server.

  So the connections keep building up in the pool upto the max limit and if
there is a lull for couple of hours then the connections break because of
firewall dropping the connections (I see connection reset exceptions when
testQuery is fired/executed) as a result there is no way for me to get a
valid connection.

Regards,
Trinath


Ludovic Orban-2 wrote:
>
> What I really don't get is why the current functionality isn't good enough
> to cover your needs.
>
> Configuring a test query on the pool should make sure it never hands a
> non-working connection to the calling code. Do you know why that's not
> working for you?
>
>
> On Mon, Apr 9, 2012 at 4:56 PM, Trinath <[hidden email]> wrote:
>
>>
>> Hi Brett,
>>   Ok, how much would be the overhead of setting maxlifetime vis-a-vis a
>> feature like keep alive. I understand for maxlifetime you will close all
>> connections and open min number of connections unlike keepalive where
>> connection will be created only when broken.
>>
>>   My situation is my connection will break every 5min as my firewall
>> keeps
>> dropping my connections which are idle for more than 5min. So Maxlifetime
>> will be more useful if most of my connections are broken rather than
>> being
>> open. If they are open I would be needlessly closing them and recreating.
>>
>>   If I have to set MaxLifeTime, I will have a setting of around 10min on
>> one of my connection pools which is seldom used and leave it to default
>> behaviour (i.e. dont set this parameter) on the connection pool which is
>> heavily used.
>>
>> Regards,
>> Trinath
>>
>>
>
>

--
View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33655945.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Trinath
Thanks Ludovic, this is exactly the problem. I will try to replicate it with a simple test client and get back to you. I will get back to you soon.
Regards,
Trinath
Ludovic Orban-2 wrote
What you're saying sounds bizarre to me. You may have uncovered a bug.

The connection pool should never grow if it still contains idle
connections. Idle connections are always tested with the testQuery before
being handed over to the callee. If an error occurs during the test, the
connection should be closed, removed from the pool and another one from the
pool should be picked and tested. If your pool is full of stale
connections, a single getConnection() call should clean them all and create
a fresh new one.

Since that's not the behavior you're experiencing, can I ask you to write
the simplest client you can that reproduces the problem in your
environment? I'll then try it out here, making sure there's a firewall in
between the pool and the DB that drops connections.

--
 Ludovic

On Mon, Apr 9, 2012 at 5:11 PM, Trinath <trinathm@techmahindra.com> wrote:

>
> Hi Ludovic,
>   Yes it does, but once we hit the max connections limit in search of a
> valid connection, there is no way for the requestor to get the valid
> connection except bouncing the server.
>
>   So the connections keep building up in the pool upto the max limit and if
> there is a lull for couple of hours then the connections break because of
> firewall dropping the connections (I see connection reset exceptions when
> testQuery is fired/executed) as a result there is no way for me to get a
> valid connection.
>
> Regards,
> Trinath
>
>
> Ludovic Orban-2 wrote:
> >
> > What I really don't get is why the current functionality isn't good
> enough
> > to cover your needs.
> >
> > Configuring a test query on the pool should make sure it never hands a
> > non-working connection to the calling code. Do you know why that's not
> > working for you?
> >
> >
> > On Mon, Apr 9, 2012 at 4:56 PM, Trinath <trinathm@techmahindra.com>
> wrote:
> >
> >>
> >> Hi Brett,
> >>   Ok, how much would be the overhead of setting maxlifetime vis-a-vis a
> >> feature like keep alive. I understand for maxlifetime you will close all
> >> connections and open min number of connections unlike keepalive where
> >> connection will be created only when broken.
> >>
> >>   My situation is my connection will break every 5min as my firewall
> >> keeps
> >> dropping my connections which are idle for more than 5min. So
> Maxlifetime
> >> will be more useful if most of my connections are broken rather than
> >> being
> >> open. If they are open I would be needlessly closing them and
> recreating.
> >>
> >>   If I have to set MaxLifeTime, I will have a setting of around 10min on
> >> one of my connection pools which is seldom used and leave it to default
> >> behaviour (i.e. dont set this parameter) on the connection pool which is
> >> heavily used.
> >>
> >> Regards,
> >> Trinath
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33655945.html
> Sent from the Bitronix Transaction Manager mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

sathwikbp

Hi,
    I am seeing that when a firewall drops the idle connection bitronix is
not able to provide a valid connection.
    I am attaching a sample eclipse project BTMTest.zip.

    Environment: Ubuntu 11.04, Mysql 5.1, BTM 2.1.2, mysql-connector 5.1.18    
   
     The sample program creates 10 min pool connections acquires them and
releases them, then the current execution thread is made to sleep for 110
seconds. While the thread is sleeping, using the Mysql Administrator kill
all the 10 connections. When the thread wakes up a request is made to
acquire a connection and then error occurs.

     I see that there are about 8 tries made to get a valid connection from
the pool and then BTM throws an error as shown below. I am not sure why it
only tries for 8 times, in my opinion it should try till AcquisitionTimeout
(30 sec).

What is strange is that when I increase the thread sleep time to 120
seconds, i get a valid connection.

Here is the error printed on the eclipse console. For btm log please refer
the log director with in the project.

Now trying to get a connection from Pool-----------------
java.sql.SQLException: unable to get a connection from pool of a
PoolingDataSource containing an XAPool of resource jdbc/BPMSDB with 2
connection(s) (2 still available)
        at
bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:252)
        at test.BTMTest.acquireConnections(BTMTest.java:57)
        at test.BTMTest.main(BTMTest.java:99)
Caused by: bitronix.tm.internal.BitronixRuntimeException: cannot get valid
connection from an XAPool of resource jdbc/BPMSDB with 2 connection(s) (2
still available) after trying for 30s
        at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:162)
        at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:97)
        at
bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:248)
        ... 2 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet successfully received from the server was 117,142
milliseconds ago.  The last packet sent successfully to the server was 0
milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
        at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
        at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
        at
bitronix.tm.resource.jdbc.JdbcPooledConnection.testConnection(JdbcPooledConnection.java:200)
        at
bitronix.tm.resource.jdbc.JdbcPooledConnection.getConnectionHandle(JdbcPooledConnection.java:286)
        at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:136)
        ... 4 more
Caused by: java.io.EOFException: Can not read response from server. Expected
to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
        ... 14 more

Is this a bug?

regards,
sathwik


Trinath wrote:

>
> Thanks Ludovic, this is exactly the problem. I will try to replicate it
> with a simple test client and get back to you. I will get back to you
> soon.
> Regards,
> Trinath
>
> Ludovic Orban-2 wrote:
>>
>> What you're saying sounds bizarre to me. You may have uncovered a bug.
>>
>> The connection pool should never grow if it still contains idle
>> connections. Idle connections are always tested with the testQuery before
>> being handed over to the callee. If an error occurs during the test, the
>> connection should be closed, removed from the pool and another one from
>> the
>> pool should be picked and tested. If your pool is full of stale
>> connections, a single getConnection() call should clean them all and
>> create
>> a fresh new one.
>>
>> Since that's not the behavior you're experiencing, can I ask you to write
>> the simplest client you can that reproduces the problem in your
>> environment? I'll then try it out here, making sure there's a firewall in
>> between the pool and the DB that drops connections.
>>
>> --
>>  Ludovic
>>
>> On Mon, Apr 9, 2012 at 5:11 PM, Trinath  wrote:
>>
>>>
>>> Hi Ludovic,
>>>   Yes it does, but once we hit the max connections limit in search of a
>>> valid connection, there is no way for the requestor to get the valid
>>> connection except bouncing the server.
>>>
>>>   So the connections keep building up in the pool upto the max limit and
>>> if
>>> there is a lull for couple of hours then the connections break because
>>> of
>>> firewall dropping the connections (I see connection reset exceptions
>>> when
>>> testQuery is fired/executed) as a result there is no way for me to get a
>>> valid connection.
>>>
>>> Regards,
>>> Trinath
>>>
>>>
>>> Ludovic Orban-2 wrote:
>>> >
>>> > What I really don't get is why the current functionality isn't good
>>> enough
>>> > to cover your needs.
>>> >
>>> > Configuring a test query on the pool should make sure it never hands a
>>> > non-working connection to the calling code. Do you know why that's not
>>> > working for you?
>>> >
>>> >
>>> > On Mon, Apr 9, 2012 at 4:56 PM, Trinath
>>> wrote:
>>> >
>>> >>
>>> >> Hi Brett,
>>> >>   Ok, how much would be the overhead of setting maxlifetime vis-a-vis
>>> a
>>> >> feature like keep alive. I understand for maxlifetime you will close
>>> all
>>> >> connections and open min number of connections unlike keepalive where
>>> >> connection will be created only when broken.
>>> >>
>>> >>   My situation is my connection will break every 5min as my firewall
>>> >> keeps
>>> >> dropping my connections which are idle for more than 5min. So
>>> Maxlifetime
>>> >> will be more useful if most of my connections are broken rather than
>>> >> being
>>> >> open. If they are open I would be needlessly closing them and
>>> recreating.
>>> >>
>>> >>   If I have to set MaxLifeTime, I will have a setting of around 10min
>>> on
>>> >> one of my connection pools which is seldom used and leave it to
>>> default
>>> >> behaviour (i.e. dont set this parameter) on the connection pool which
>>> is
>>> >> heavily used.
>>> >>
>>> >> Regards,
>>> >> Trinath
>>> >>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33655945.html
>>> Sent from the Bitronix Transaction Manager mailing list archive at
>>> Nabble.com.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe from this list, please visit:
>>>
>>>    http://xircles.codehaus.org/manage_email
>>>
>>>
>>>
>>
>>
>
>
http://old.nabble.com/file/p33763608/BTMTest.zip BTMTest.zip
--
View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33763608.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban
Administrator
Thanks for opening BTM-119 (https://jira.codehaus.org/browse/BTM-119) and attaching the example code, it really helped me reproduce the problem in little time.

There are a few minor bugs in the 2.1.2 release in the time allowed to close invalid connections from a connection pool. I've fixed them for the 2.1.3 release.

If you have question about what is wrong, what I fixed and what you can do to mitigate the problem, please read the comments I added in the JIRA. If you still have questions afterwards, feel free to ask them here.

Thanks for the report,
Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

hareendra
I had the similar issue explained above. I reran the test "BTMTest.zip" with BTM 2.1.3 version, but I see the same error. Looks like this error is not this fixed in 2.1.3. can you check?
 Error I see is:

Max Idle Connection time in seconds: 200
 AcquireIncrement: 1
 AcquisitionInterval: 1
 AcquisitionTimeout time in seconds: 30
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Acquiring connections from Pool ----------------
 Connection 1:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1434234 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 2:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@f7f540 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 3:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@c8f6f8 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 4:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1ef9157 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 5:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@3ecfff on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Current Pool statistics----------------
 free connections:0
 in use connections:5
 total connections:5
 Closing acquired connections from Pool ----------------
 Closing remaining connections:5
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1434234 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@f7f540 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@c8f6f8 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1ef9157 on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@3ecfff on a JDBC LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connections closed---------------
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Thread Sleeping for 110 seconds, during this time kill all the database connections on the database server side-----------------
 Thread Woke up---------------------------------
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Now trying to get a connection from Pool-----------------
 SQLException Exception occurred:unable to get a connection from pool of a PoolingDataSource containing an XAPool of resource jdbc/BPMSDB with 3 connection(s) (3 still available)
 Closing remaining connections:0
 Connections closed---------------
 java.sql.SQLException: unable to get a connection from pool of a PoolingDataSource containing an XAPool of resource jdbc/BPMSDB with 3 connection(s) (3 still available)
 at bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:262)
 at test.BTMTest.acquireConnections(BTMTest.java:58)
 at test.BTMTest.main(BTMTest.java:100)
 Caused by: bitronix.tm.internal.BitronixRuntimeException: cannot get valid connection from an XAPool of resource jdbc/BPMSDB with 3 connection(s) (3 still available) after trying for 30s
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:160)
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:91)
 at bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:258)
 ... 2 more
 Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 151,225 milliseconds ago. The last packet sent successfully to the server was 18,901 milliseconds ago.
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
 at java.lang.reflect.Constructor.newInstance(Unknown Source)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
 at bitronix.tm.resource.jdbc.JdbcPooledConnection.testConnection(JdbcPooledConnection.java:215)
 at bitronix.tm.resource.jdbc.JdbcPooledConnection.getConnectionHandle(JdbcPooledConnection.java:299)
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:130)
 ... 4 more
 Caused by: java.net.SocketTimeoutException: Read timed out
 at java.net.SocketInputStream.socketRead0(Native Method)
 at java.net.SocketInputStream.read(Unknown Source)
 at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
 at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
 at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2549)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
 ... 14 more

Thanks
Hareendra
Reply | Threaded
Open this post in threaded view
|

Re: Keep alive JDBC Connection Pool

Ludovic Orban-2
What happens when you set acquisitionInterval to 0 on your datasource?

On Thu, May 17, 2012 at 1:55 PM, hareendra <[hidden email]> wrote:

I had the similar issue explained above. I reran the test "BTMTest.zip" with
BTM 2.1.3 version, but I see the same error. Looks like this error is not
this fixed in 2.1.3. can you check?
 Error I see is:

Max Idle Connection time in seconds: 200
 AcquireIncrement: 1
 AcquisitionInterval: 1
 AcquisitionTimeout time in seconds: 30
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Acquiring connections from Pool ----------------
 Connection 1:a JdbcConnectionHandle of a JdbcPooledConnection from
datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a
JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1434234 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 2:a JdbcConnectionHandle of a JdbcPooledConnection from
datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a
JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@f7f540 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 3:a JdbcConnectionHandle of a JdbcPooledConnection from
datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a
JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@c8f6f8 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 4:a JdbcConnectionHandle of a JdbcPooledConnection from
datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a
JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1ef9157 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection 5:a JdbcConnectionHandle of a JdbcPooledConnection from
datasource jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a
JDBC LrcXAConnection on com.mysql.jdbc.JDBC4Connection@3ecfff on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Current Pool statistics----------------
 free connections:0
 in use connections:5
 total connections:5
 Closing acquired connections from Pool ----------------
 Closing remaining connections:5
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource
jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC
LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1434234 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource
jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC
LrcXAConnection on com.mysql.jdbc.JDBC4Connection@f7f540 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource
jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC
LrcXAConnection on com.mysql.jdbc.JDBC4Connection@c8f6f8 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource
jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC
LrcXAConnection on com.mysql.jdbc.JDBC4Connection@1ef9157 on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connection:a JdbcConnectionHandle of a JdbcPooledConnection from datasource
jdbc/BPMSDB in state ACCESSIBLE with usage count 1 wrapping a JDBC
LrcXAConnection on com.mysql.jdbc.JDBC4Connection@3ecfff on a JDBC
LrcConnectionHandle on a JDBC LrcXAResource in state NO_TX
 Connections closed---------------
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Thread Sleeping for 110 seconds, during this time kill all the database
connections on the database server side-----------------
 Thread Woke up---------------------------------
 Current Pool statistics----------------
 free connections:5
 in use connections:0
 total connections:5
 Now trying to get a connection from Pool-----------------
 SQLException Exception occurred:unable to get a connection from pool of a
PoolingDataSource containing an XAPool of resource jdbc/BPMSDB with 3
connection(s) (3 still available)
 Closing remaining connections:0
 Connections closed---------------
 java.sql.SQLException: unable to get a connection from pool of a
PoolingDataSource containing an XAPool of resource jdbc/BPMSDB with 3
connection(s) (3 still available)
 at
bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:262)
 at test.BTMTest.acquireConnections(BTMTest.java:58)
 at test.BTMTest.main(BTMTest.java:100)
 Caused by: bitronix.tm.internal.BitronixRuntimeException: cannot get valid
connection from an XAPool of resource jdbc/BPMSDB with 3 connection(s) (3
still available) after trying for 30s
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:160)
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:91)
 at
bitronix.tm.resource.jdbc.PoolingDataSource.getConnection(PoolingDataSource.java:258)
 ... 2 more
 Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet successfully received from the server was 151,225
milliseconds ago. The last packet sent successfully to the server was 18,901
milliseconds ago.
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
Source)
 at java.lang.reflect.Constructor.newInstance(Unknown Source)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 at
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
 at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
 at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
 at
bitronix.tm.resource.jdbc.JdbcPooledConnection.testConnection(JdbcPooledConnection.java:215)
 at
bitronix.tm.resource.jdbc.JdbcPooledConnection.getConnectionHandle(JdbcPooledConnection.java:299)
 at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:130)
 ... 4 more
 Caused by: java.net.SocketTimeoutException: Read timed out
 at java.net.SocketInputStream.socketRead0(Native Method)
 at java.net.SocketInputStream.read(Unknown Source)
 at
com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
 at
com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
 at
com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2549)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
 ... 14 more

Thanks
Hareendra

--
View this message in context: http://old.nabble.com/Keep-alive-JDBC-Connection-Pool-tp27420030p33863712.html
Sent from the Bitronix Transaction Manager mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



12