You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Gareth Coltman <ga...@majorband.co.uk> on 2001/10/31 17:39:17 UTC
Database expiry
We are having problems with database connections disconnecting unexpectedly after 1 hour, whether or not that have been used during
this period. After exploring our network and database configurations, it seems the only culprit can be Turbine (we are using 2.1). I
have tried to track down the code in Turbine which causes the connections to expire but can't. I have found the isExpired method in
the ConnectionPool class :
return ((System.currentTimeMillis() -
connection.getTimestamp()) > expiryTime);
- this is interesting because it expires whether or not it has been active during that period. However I can't find any thread that
monitors the connections and expires them. Obviously missing something. Ideally what one would want is for connections to remain
cached up to expiry_time after they were last used. Anyway, please help!
Gareth
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Database expiry
Posted by Daniel Rall <dl...@finemaltcoding.com>.
In the future, please don't reply to other's post w/ a different topic.
"Gareth Coltman" <ga...@majorband.co.uk> writes:
> We are having problems with database connections disconnecting unexpectedly after 1 hour, whether or not that have been used during
> this period. After exploring our network and database configurations, it seems the only culprit can be Turbine (we are using 2.1). I
> have tried to track down the code in Turbine which causes the connections to expire but can't. I have found the isExpired method in
> the ConnectionPool class :
>
> return ((System.currentTimeMillis() -
> connection.getTimestamp()) > expiryTime);
>
> - this is interesting because it expires whether or not it has been active during that period. However I can't find any thread that
> monitors the connections and expires them. Obviously missing something. Ideally what one would want is for connections to remain
> cached up to expiry_time after they were last used. Anyway, please help!
What database are you using? Are you using Torque to handle the
database connections? Are you ever using a DBConnection object
directly, and if so are you remembering to return the connection to
the pool using a try/finally block?
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Database expiry
Posted by John McNally <jm...@collab.net>.
Gareth Coltman wrote:
>
> Hmm. I agree with you, but this approach also causes problems. On a site with heavy load this can cause problems. If all the
> connections disconnect at once (i.e. after an hour) then this can cause a bottleneck as the connections reconnect. This is what
> happens on our site:
>
> Initially only about 2-3 connections are created.
> After an hour they are disconnected
> A delay is caused by having to reconnect (averaging > 0.5 < 1 second)
> In a peak period our site generates a lot of database hits (perhaps 10 a second). This means that dropping the connection may result
> in a build up of 10 or more requests waiting to be served.
> What I have noticed from tracing the connections in Oracle is that immediately after dropping the connections after the hour has
> elapsed, Turbine creates more connections that it dropped: i.e. if 3 connections were in use, dropping them will result in 10
> connections immediately being created to cope with the backlog.
>
> This spiking is bad for performance.
>
> I can see two solutions, implement a more standard timeout (i.e. inactivity based), or have connections expire more gradually,
> rather than all at once. The second option would probably be a very messy solution.
>
> You said database connections go stale, but were vague about why/how. Any more info on this?
Sorry, I do not know the reason for this statement. I suspect the
dynamics of the internet, but that is just a guess, I do not know the
low level details of making a connection and keeping it open. Possibly
it is not much of a problem with a db/webserver on the same box. I am
just going on something that was told to me, which is not a very valid
reason, but the logic has generally worked well in practice, so I am a
bit reluctant to change it. Instituting a policy of only closing
connections when activity falls off would probably work just as well in
practice, if the expiration time is set low enough that connections get
regularly expired.
You say the second option given above would be messy, but it would seem
reasonably simple to add a Math.random call to the check for an expired
connection, so that it varies between 1/2 to the maximum expiration
time.
>
> Do you know whether it would be possible for me to alter Turbine to drop sessions only if they have been inactive?
It would probably not be that difficult.
>
> Gareth
>
> > -----Original Message-----
> > From: jmcnally [mailto:jmcnally]On Behalf Of John McNally
> > Sent: Wednesday, October 31, 2001 19:50
> > To: Turbine Users List
> > Subject: Re: Database expiry
> >
> >
> > It is a good idea to expire a connection after several hours
> > or possibly
> > a day or two, regardless of whether the connection is being
> > used during
> > that time. I do not know the reason for this, but connections tend to
> > get stale. If you set the expiration to only occur after the
> > connection
> > has not been used for the given amount of time, it likely will never
> > expire until it goes bad.
> >
> > john mcnally
> >
> > Gareth Coltman wrote:
> > >
> > > We are having problems with database connections
> > disconnecting unexpectedly after 1 hour, whether or not that
> > have been used during
> > > this period. After exploring our network and database
> > configurations, it seems the only culprit can be Turbine (we
> > are using 2.1). I
> > > have tried to track down the code in Turbine which causes
> > the connections to expire but can't. I have found the
> > isExpired method in
> > > the ConnectionPool class :
> > >
> > > return ((System.currentTimeMillis() -
> > > connection.getTimestamp()) > expiryTime);
> > >
> > > - this is interesting because it expires whether or not it
> > has been active during that period. However I can't find any
> > thread that
> > > monitors the connections and expires them. Obviously
> > missing something. Ideally what one would want is for
> > connections to remain
> > > cached up to expiry_time after they were last used. Anyway,
> > please help!
> > >
> > > Gareth
> > >
> > > --
> > > To unsubscribe, e-mail:
> > <ma...@jakarta.apache.org>
> > > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> > --
> > To unsubscribe, e-mail:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> >
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Connection Pool Performance (was Re: Database expiry)
Posted by Daniel Rall <dl...@finemaltcoding.com>.
Eric Dobbs <er...@dobbse.net> writes:
> Based on your experience with the connection pool under load, can you
> offer some rough rules of thumb for the number of connections relative
> to the load?
Number of connections to use depends on your application and hardware.
To get optimum performance assure that your application is never
having to wait for a connection (add instrumentation to
getInternalPooledConnection() to determine this--a patch to automate
this would be nice), but without exceeding the capabilities of your
RDBMS and its hardware (don't set a max number of connections higher
than what your database setup can handle).
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Connection Pool Performance (was Re: Database expiry)
Posted by Eric Dobbs <er...@dobbse.net>.
On Thursday, November 1, 2001, at 09:52 PM, Daniel Rall wrote:
> I'm quite familiar with using Turbine's connection pool under heavy
> load (take a look the CollabNet entries on the poweredby.html page up
> on the web site). Supplying only 3 connections is definitely not the
> way to go--your application will be starved for connections and run
> slow.
Dan,
Based on your experience with the connection pool under load, can you
offer some rough rules of thumb for the number of connections relative
to the load?
-Eric
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Database expiry
Posted by Daniel Rall <dl...@finemaltcoding.com>.
"Gareth Coltman" <ga...@majorband.co.uk> writes:
>>
>> > I can see two solutions, implement a more standard timeout
>> > (i.e. inactivity based), or have connections expire more gradually,
>> > rather than all at once. The second option would probably be a very
>> > messy solution.
>>
>> They do not expire "all at once," since they are created lazily.
>
> You didn't read my post. The site we are using is under incredibly
> heavy load. If a webserver is restarted at any time other than the
> middle of the night - ALL the connections get created at once.
I did read your post.
<repeat>If you increase the max number of connections to 40 or 80, all
of your connections will likely not be created at once.</repeat>
I'm quite familiar with using Turbine's connection pool under heavy
load (take a look the CollabNet entries on the poweredby.html page up
on the web site). Supplying only 3 connections is definitely not the
way to go--your application will be starved for connections and run
slow.
> Have modified the connection pool so if an expiry time of -1 is
> supplied they connections never expire. Want me to submit a patch?
This or what John proposed sounds reasonable. Actually, having 0 mean
never expire and -1 mean expire randomly, would be a pretty cool
feature to add to the pool.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Database expiry
Posted by Gareth Coltman <ga...@majorband.co.uk>.
>
> > I can see two solutions, implement a more standard timeout
> > (i.e. inactivity based), or have connections expire more gradually,
> > rather than all at once. The second option would probably be a very
> > messy solution.
>
> They do not expire "all at once," since they are created lazily.
You didn't read my post. The site we are using is under incredibly heavy load. If a webserver is restarted at any time other than
the middle of the night - ALL the connections get created at once.
Have modified the connection pool so if an expiry time of -1 is supplied they connections never expire. Want me to submit a patch?
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Database expiry
Posted by Daniel Rall <dl...@finemaltcoding.com>.
"Gareth Coltman" <ga...@majorband.co.uk> writes:
> Hmm. I agree with you, but this approach also causes problems. On a site with heavy load this can cause problems. If all the
> connections disconnect at once (i.e. after an hour) then this can cause a bottleneck as the connections reconnect. This is what
> happens on our site:
>
> Initially only about 2-3 connections are created. After an hour
> they are disconnected A delay is caused by having to reconnect
> (averaging > 0.5 < 1 second) In a peak period our site generates a
> lot of database hits (perhaps 10 a second). This means that dropping
> the connection may result in a build up of 10 or more requests
> waiting to be served. What I have noticed from tracing the
> connections in Oracle is that immediately after dropping the
> connections after the hour has elapsed, Turbine creates more
> connections that it dropped: i.e. if 3 connections were in use,
> dropping them will result in 10 connections immediately being
> created to cope with the backlog.
>
> This spiking is bad for performance.
Connections are created lazily (i.e. only on an as-needed basis).
They are not all created at once. Increase the max number of
connections for your pool.
> I can see two solutions, implement a more standard timeout
> (i.e. inactivity based), or have connections expire more gradually,
> rather than all at once. The second option would probably be a very
> messy solution.
They do not expire "all at once," since they are created lazily.
> Do you know whether it would be possible for me to alter Turbine to
> drop sessions only if they have been inactive?
Of course. See the source of ConnectionPool.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Database expiry
Posted by Gareth Coltman <ga...@majorband.co.uk>.
Hmm. I agree with you, but this approach also causes problems. On a site with heavy load this can cause problems. If all the
connections disconnect at once (i.e. after an hour) then this can cause a bottleneck as the connections reconnect. This is what
happens on our site:
Initially only about 2-3 connections are created.
After an hour they are disconnected
A delay is caused by having to reconnect (averaging > 0.5 < 1 second)
In a peak period our site generates a lot of database hits (perhaps 10 a second). This means that dropping the connection may result
in a build up of 10 or more requests waiting to be served.
What I have noticed from tracing the connections in Oracle is that immediately after dropping the connections after the hour has
elapsed, Turbine creates more connections that it dropped: i.e. if 3 connections were in use, dropping them will result in 10
connections immediately being created to cope with the backlog.
This spiking is bad for performance.
I can see two solutions, implement a more standard timeout (i.e. inactivity based), or have connections expire more gradually,
rather than all at once. The second option would probably be a very messy solution.
You said database connections go stale, but were vague about why/how. Any more info on this?
Do you know whether it would be possible for me to alter Turbine to drop sessions only if they have been inactive?
Gareth
> -----Original Message-----
> From: jmcnally [mailto:jmcnally]On Behalf Of John McNally
> Sent: Wednesday, October 31, 2001 19:50
> To: Turbine Users List
> Subject: Re: Database expiry
>
>
> It is a good idea to expire a connection after several hours
> or possibly
> a day or two, regardless of whether the connection is being
> used during
> that time. I do not know the reason for this, but connections tend to
> get stale. If you set the expiration to only occur after the
> connection
> has not been used for the given amount of time, it likely will never
> expire until it goes bad.
>
> john mcnally
>
> Gareth Coltman wrote:
> >
> > We are having problems with database connections
> disconnecting unexpectedly after 1 hour, whether or not that
> have been used during
> > this period. After exploring our network and database
> configurations, it seems the only culprit can be Turbine (we
> are using 2.1). I
> > have tried to track down the code in Turbine which causes
> the connections to expire but can't. I have found the
> isExpired method in
> > the ConnectionPool class :
> >
> > return ((System.currentTimeMillis() -
> > connection.getTimestamp()) > expiryTime);
> >
> > - this is interesting because it expires whether or not it
> has been active during that period. However I can't find any
> thread that
> > monitors the connections and expires them. Obviously
> missing something. Ideally what one would want is for
> connections to remain
> > cached up to expiry_time after they were last used. Anyway,
> please help!
> >
> > Gareth
> >
> > --
> > To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Database expiry
Posted by John McNally <jm...@collab.net>.
It is a good idea to expire a connection after several hours or possibly
a day or two, regardless of whether the connection is being used during
that time. I do not know the reason for this, but connections tend to
get stale. If you set the expiration to only occur after the connection
has not been used for the given amount of time, it likely will never
expire until it goes bad.
john mcnally
Gareth Coltman wrote:
>
> We are having problems with database connections disconnecting unexpectedly after 1 hour, whether or not that have been used during
> this period. After exploring our network and database configurations, it seems the only culprit can be Turbine (we are using 2.1). I
> have tried to track down the code in Turbine which causes the connections to expire but can't. I have found the isExpired method in
> the ConnectionPool class :
>
> return ((System.currentTimeMillis() -
> connection.getTimestamp()) > expiryTime);
>
> - this is interesting because it expires whether or not it has been active during that period. However I can't find any thread that
> monitors the connections and expires them. Obviously missing something. Ideally what one would want is for connections to remain
> cached up to expiry_time after they were last used. Anyway, please help!
>
> Gareth
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>