Re: Disconnect database connection after idle timeout

On 13/11/2014 15:43, Perrin Harkins wrote:
> On Thu, Nov 13, 2014 at 10:29 AM, Xinhuan Zheng 
> <[email protected] <mailto:[email protected]>> wrote:
>
>     We don’t have any front end proxy.
>
>
> I think I see the problem... ;)
>
> If you use a front-end proxy so that your mod_perl servers are only 
> handling mod_perl requests, and tune your configuration so that idle 
> mod_perl servers don't sit around for long, that should avoid 
> timeouts. Apache::DBI should also re-connect with no problems if a 
> request comes in after a connection has timed out. If that isn't 
> happening, make sure you are using Apache::DBI properly.
>
> Seriously, using a front-end proxy usually reduces the number of 
> databases connections about 10 times. It's the easiest fix here by far.
>
> - Perrin
 From experience - and having chatted with our DBAs at work, with modern 
Oracle and with MySQL keeping persistent connections around is no real 
gain and usually lots of risks... We have a number of issues with 
inter-machine connection - as we have a number of firewalls in place...

If the connection failure is not due to either end shutting down but the 
network failing in between Apache::DBI will (cannot) do the right thing 
- the ping check just hangs as it tries to the broken connection (and as 
it isn't connected it waits forever to respond)

You are far better using non-persistent connections - but where we do 
use persistent connections (cache/session database) we set a timeout in 
the adaptor level which won't use a connection if it has been idle for 
more than a given period of time, but instead drops the connections and 
re-builds it....

Most of the DB servers now cache queries etc on the server so persistent 
connections are not required to take advantage of these.

Additionally if you have a load balanced situation where you have a 
number of backends - each talking back to a large number of databases 
(at least in MySQL it is very easy to reach DB connection limits)....

    7 backends x 50 processes x 300 databases ~ 100,000 connections if 
each child process has a database connection to each database...

perhaps this is a bit extreme (the project involved is a very complex 
genomic interface) but persistent connections used to be it's achilles 
heel... where we worry about database connection issues we often use 
"DBIx::Connector" as a better way to handle disconnects....



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com



-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.