mod_perl postgres idle connections

[James Taylor <jt...@hatesville.com>]

Hi everyone, I'm fairly new to mod_perl but have been working with perl 
for a few years now.
I have a site which connects to postgres, no idea whether it uses 
persistent connections or not, as the custom modules built prior to me 
looking at it goes pretty deep.
Connections to postgres are made through DBI, whereupon the database 
connection is then thrown into a session variable $self->{dbh}.  The 
problem I'm having is that when you attempt to go to the site, it 
appears at least 4 connections are made to the database, with 2 idle 
connections being made for every session.  These idle connections 
remain even after the user leaves the site, and continue to build up 
until the max number of connections has been reached, whereupon 
postgres must be restarted.   I've tried throwing in implicit 
$dbh->disconnect() statements wherever they might be useful, although I 
was under the impression perl killed these whether you disconnect or 
not.  So, I'm assuming mod_perl is a completely different beast here.  
I've never used persistent connections, but from everything I've read 
so far it seems that this site isn't using them, as the number of pgsql 
processes doesn't scale with the number of httpd processes; they just 
continually go up until we die.

Does anyone have any idea why mod_perl is letting the idle processes 
stack up, and if so, how might I get rid of them??? Thanks for your 
help


-- 
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html

Re: mod_perl postgres idle connections

[Chris Ochs <ch...@paymentonline.net>]

What you want to be using is Apache::DBI.  Sounds like someone made a bad
attempt at making their own cached connections in the code you have.
Apache::DBI handles everything transparently for you.   But you are going to
have to dig in and get rid of all the caching stuff that is currently in
your code base.  After that, just put use Apache::DBI instead of use DBI.

First read through the Apache::DBI manpage, it explains how it all works
much better than I can.

Chris


----- Original Message ----- 
From: "James Taylor" <jt...@hatesville.com>
To: <mo...@perl.apache.org>
Sent: Tuesday, February 24, 2004 3:12 PM
Subject: mod_perl postgres idle connections


> Hi everyone, I'm fairly new to mod_perl but have been working with perl
> for a few years now.
> I have a site which connects to postgres, no idea whether it uses
> persistent connections or not, as the custom modules built prior to me
> looking at it goes pretty deep.
> Connections to postgres are made through DBI, whereupon the database
> connection is then thrown into a session variable $self->{dbh}.  The
> problem I'm having is that when you attempt to go to the site, it
> appears at least 4 connections are made to the database, with 2 idle
> connections being made for every session.  These idle connections
> remain even after the user leaves the site, and continue to build up
> until the max number of connections has been reached, whereupon
> postgres must be restarted.   I've tried throwing in implicit
> $dbh->disconnect() statements wherever they might be useful, although I
> was under the impression perl killed these whether you disconnect or
> not.  So, I'm assuming mod_perl is a completely different beast here.
> I've never used persistent connections, but from everything I've read
> so far it seems that this site isn't using them, as the number of pgsql
> processes doesn't scale with the number of httpd processes; they just
> continually go up until we die.
>
> Does anyone have any idea why mod_perl is letting the idle processes
> stack up, and if so, how might I get rid of them??? Thanks for your
> help
>
>
> -- 
> Report problems: http://perl.apache.org/bugs/
> Mail list info: http://perl.apache.org/maillist/modperl.html
> List etiquette: http://perl.apache.org/maillist/email-etiquette.html
>
>


-- 
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html