Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 17:40:05 -0500
Perrin Harkins wrote:
>
> No, that's perfect. Since pnotes gets cleaned up at the end of every
> request, there's no danger of the handle sticking around. I assume you're
> calling DBI->connect() to get the handle in the HeaderParser phase, and
> using Apache::DBI?
>
> The dangerous thing is to put a DBI handle in a global and just keep using
> it on every request. If you put a DBI handle in a global (or a closure
> variable) make sure you clean it up at the end of every request.
>
I have a package that returns a DBI handle when called, using connect_cached,
without Apache::DBI (see package Marica::db_handle below)
For each request I do :
In the PerlHeaderParserHandler
my $dbh = Marica::db_handle::get_dbh_data( $database,
$r->pnotes('session')->{preferred_datestyle} ) ;
$r->pnotes( 'dbh' => $dbh ) ;
Then in the PerlResponseHandler
my $dbh = $r->pnotes('dbh') ;
my $data_set = $dbh->selectall_arrayref( $sql ) ;
It's very convenient, all my modules use this, and I only have one place to
edit the database connection if needed.
If I understand correctly, I could also use Apache::DBI and a persistent
connection for a similar result, modulo what you wrote in an earlier message
regarding the connection being more explicit (which I don't quite grasp, I'll
have to study on that)?
package Marica::db_handle ;
use strict ;
use warnings ;
sub get_dbh_data {
my $db_name = shift ;
#paramètre d'affichage des dates; les caches de connection en tiennent
compte
my $preferred_datestyle = shift || 'iso' ;
my $dbh = DBI->connect_cached( "DBI:Pg:dbname=$db_name", 'www-data', undef,
{
PrintError => 1,
RaiseError => 1,
AutoCommit => 1,
pg_bool_tf => 1,
private_preferred_datestyle => $preferred_datestyle } )
or die "Cannot connect to db: $DBI::errstr" ;
return $dbh ;
}
--
Salutations, Vincent Veyron
https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats
pour le service juridique
Re: Disconnect database connection after idle timeout
On Fri, Nov 14, 2014 at 7:58 AM, Vincent Veyron wrote: > If I understand correctly, I could also use Apache::DBI and a persistent > connection for a similar result, modulo what you wrote in an earlier > message regarding the connection being more explicit (which I don't quite > grasp, I'll have to study on that)? Right. Apache::DBI overrides DBI->connect() when loaded so that a normal connect() call gets a cached handle if available. What you're doing here looks fine. If you ever switch off AutoCommit in your code, I'd suggest adding a cleanup handler that checks if the handle is not currently in AutoCommit mode, and if not, issues a rollback and switches it to AutoCommit. That protects against an exception leaving your database handle in a bad state. (Apache::DBI tries to do that automatically, but it's not very smart about it. It only happens if you have AutoCommit off when you connect.) - Perrin
Re: Disconnect database connection after idle timeout
Hi Parrin, > the huge mod_perl-enabled server process (with all of its system resources) > will be tied up until the response is completely written to the client. While > it might take a few milliseconds for your script to complete the request, > there is a chance it will be still busy for some number of seconds or even > minutes if the request is from a slow connection client. Are you implying that the performance will be suffered when using mod_perl-enabled server processes as the front tier servers? - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 5:49 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 5:38 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: We have load balancer cache that can cache images and JavaScripts. This functions seems a bit duplicate. It's not about caching. Here's a quote from that link I sent earlier: "Another drawback of this approach is that when serving output to a client with a slow connection, the huge mod_perl-enabled server process (with all of its system resources) will be tied up until the response is completely written to the client. While it might take a few milliseconds for your script to complete the request, there is a chance it will be still busy for some number of seconds or even minutes if the request is from a slow connection client." You might think everyone has fast connections now so this won't matter, but it does. It's especially bad if you have keep-alive on for the apache server running mod_perl, since that means your large mod_perl processes sit around for some extra time doing nothing, while holding onto their database connections. Install a front-end proxy, turn off keep-alive on your mod_perl and reduce your max idle servers, and watch what happens. - Perrin
Re: Disconnect database connection after idle timeout
On Fri, 14 Nov 2014 08:30:18 -0500 Perrin Harkins wrote: > If you ever switch off AutoCommit in > your code, I'd suggest adding a cleanup handler that checks if the handle > is not currently in AutoCommit mode, and if not, issues a rollback and > switches it to AutoCommit. That protects against an exception leaving your > database handle in a bad state. (Apache::DBI tries to do that > automatically, but it's not very smart about it. It only happens if you > have AutoCommit off when you connect.) > Ha yes, I see some threads about this. Thanks for the heads up. -- Salutations, Vincent Veyron https://marica.fr/ Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique
Re: Disconnect database connection after idle timeout
On Fri, Nov 14, 2014 at 1:32 PM, Xinhuan Zheng wrote: > Are you implying that the performance will be suffered when using > mod_perl-enabled server processes as the front tier servers? Not performance, scalability. You can't handle as many requests per second if you use mod_perl without a front-end proxy. That's why people on this list with a large site use them, and why every similar system in Ruby, Python, and PHP also recommend separating your web front-end from your application server processes if you need to scale. - Perrin
Re: Disconnect database connection after idle timeout
What I don't understand is what you are worried about? Whether you are using Apache::DBI or David Wheelers DBIHandler (do I have that correct?) both will behave according to the configuration of the database and web server. Why not just let the servers decide whether to maintain or destroy a connection according to your configuration? Set your timeouts in both Oracle and Apache and let them deal with it On 11/13/2014 07:39 AM, Xinhuan Zheng wrote: < If TTL were implemented, the module would have to disconnect from db connection after a pre-defined timeout so the oracle server process could be shut down more gracefully. Would it be possible to implement that? Or is it too hard to implement? No one cares about shutting down Oracle gracefully. I have not used "shutdown immediate", other than by mistake in probably 10 years. I have never successfully used "shutdown normal" Idle db connections cost almost nothing, establishing a connection is expensive. Warning: I am better at Oracle than perl, and I am not very good with either :) YMMV Dave - xinhuan From: Paul Silevitch mailto:p...@silevitch.com>> Date: Wednesday, November 12, 2014 at 11:53 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>>, modperl mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout I don't fully understand your need here. I'm going to give my best. You could set an alarm in the cleanup handler that calls the disconnect after a specified amount of time. If a new request comes in, you could cancel the alarm in a postreadrequest handler (or something early in the cycle). To cover the race condition where the disconnect happens right before the cancel, you could check to make sure the database connection is active right after the cancel is called. HTH, Paul On Wed, Nov 12, 2014 at 9:36 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: Hello, I am having a database connection management question. Our apache+mod_perl application initiates a database connection request when it needs to then do data processing. Afterwards, if there is no more requests coming to this apache process, the database connection basically will be sitting there in idle state for quite a while until the OS TCP/IP idle timeout has reached. At that point the database would send a message into its alert log, telling that a connection timeout has occurred and the server process will be cleaned. I would like to figure out if mod_perl application can implement keep-alive timeout mechanism. The mod_perl would maintain the database connection after it finishes some processing until an idle timeout defined in the application has reached, for example, 5 minutes. Then the mod_perl would initiate a database disconnection request so the server process can be cleaned more gracefully. We are using Oracle 11g database. I knew in 11G oracle has implemented the connection pooling. I would think the oracle side connection pooling would be the server side maintaining the connection idle timeout. Would it be possible on the client side the mod_perl implement something like that? I just don’t know which side is more appropriate and how on the client side it can implement something like that. Thanks, - xinhuan -- Dave Morgan Senior Consultant, 100 Alberta Limited dave.mor...@100.com 403 399 2442
Re: Disconnect database connection after idle timeout
On 11/13/2014 09:42 AM, Perrin Harkins wrote: On Thu, Nov 13, 2014 at 11:29 AM, Dr James Smith mailto:j...@sanger.ac.uk>> wrote: 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 MySQL I don't know, Oracle, every connection should be persistent if you are making more than a single call The last time I worked with Oracle, connections were too slow to run without caching them. That was years ago though, and the situation may have improved. I hate to quote myself but . On 11/13/2014 08:00 AM, Dave Morgan wrote:> Idle db connections cost almost nothing, establishing a connection is expensive. Warning: I am better at Oracle than perl, and I am not very good with either :) Making a new connection to Oracle 11 or 12 is still painfully expensive. Dave -- Dave Morgan Senior Consultant, 100 Alberta Limited dave.mor...@100.com 403 399 2442
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 10:43:35 -0500 Perrin Harkins wrote: > 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. > Hi Perrin, Nobody suggested using connect_cached, where the documentations says : The cached database handle is replaced with a new connection if it has been disconnected or if the "ping" method fails Would that not solve OP's problem? -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
