Re: [Catalyst] Database connections leaking
On Mon, 24 Dec 2007 18:22:13 + Matt S Trout [EMAIL PROTECTED] wrote: You might want to see about ripping out the C::M::DBI connection management and using a DBIx::Class::Storage::DBI object instead - the Model::DBI code originates from a copy of the DBIx::Class code but I'm unaware of whether bugfixes and refactorings have been propagated to it. That's basically what I ended up doing. I didn't like having two files that each had a db connection setup; since all of our tables are DBIx::Class, I got rid of the C::M::DBI and did some more searching on my problem (wanting to do straight SQL from legacy code). Found exactly what I needed in an excerpt from Jonathan Rockway's new book (my copy has been on order for almost a week now and can't wait to get it!) For those of you interested in this solution, see: http://www.packtpub.com/article/catalyst-web-framework-building-your-own-model and the section titled 'Extending a DBIx::Class Model'. It took a few days to modify all the legacy code and get it running, but it's nice having it a bit 'cleaner'. Unfortunately, still have db connection leaking so the sysadmin is checking the Apache/mod_perl side (this is beyond me at this point). I would like to thank everyone for their help and suggestions -- I would have stumbled a lot longer without your help. :-) -- Xelia Wizard Systems Inc Kevin Nathan 1220 S. Park Lane Ste. 3 Tempe, AZ 85281 (480) 516-0710 [EMAIL PROTECTED] [EMAIL PROTECTED] NOTICE: This e-mail may contain confidential and privileged material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete and destroy all copies. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Database connections leaking
On Sat, Dec 22, 2007 at 12:21:00AM -0700, Kevin Nathan wrote: On Sat, 22 Dec 2007 15:34:14 +1030 Jon Schutz [EMAIL PROTECTED] wrote: It seems to me you would know about it, one way or another, if you had 3000 apache processes! It would seem so, wouldn't it? :-) From what I remember, though, we never saw a real increase in port 443 connections. I would speculate that the logic in Catalyst::Model::DBI that checks whether it currently has an active connection, is not working for remote Postgres connections, so it keeps creating new ones. That makes sense. I even looked at C::M::DBI, quite a bit, but it just didn't make it through all my hair-pulling . . . You might want to see about ripping out the C::M::DBI connection management and using a DBIx::Class::Storage::DBI object instead - the Model::DBI code originates from a copy of the DBIx::Class code but I'm unaware of whether bugfixes and refactorings have been propagated to it. -- Matt S Trout Need help with your Catalyst or DBIx::Class project? Technical Directorhttp://www.shadowcat.co.uk/catalyst/ Shadowcat Systems Ltd. Want a managed development or deployment platform? http://chainsawblues.vox.com/http://www.shadowcat.co.uk/servers/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/ Dev site: http://dev.catalyst.perl.org/
[Catalyst] Database connections leaking
Just for background, I have inherited a
Catalyst/TT/Postgresql/Apache2/mod_perl web application. The
programmer that was responsible for the design and initial coding left
our company and is unavailable for consults. I am very new to
Catalyst, fair level of experience in TT, Perl and postgresql. Very
little experience in Apache and none in mod_perl. I'm an old C and ASM
programmer, stumbling my way through the web-centric world -- and
actually liking it, until this problem! :-)
The program seems to be working fine while on the Catalyst test
server, and when on an Apache/mod_perl server when the postgres
database is on the same physical server. The database connections
don't start leaking until we attempt to use a separate (physical)
database server. We verified this using iptraf and seeing none of the
connections being re-used *or* closed.
The following two modules are present in the system:
---
package MyApp::Model::DBI;
use warnings;
use strict;
use base 'Catalyst::Model::DBI';
__PACKAGE__-config(
dsn = 'dbi:Pg:dbname=xyz',
user = 'xyz',
password = 'xyz',
options = {AutoCommit = 1, RaiseError=1}
);
---
---
package MyApp::Model::Default;
use warnings;
use strict;
use base qw(
Catalyst::Model::DBIC::Schema
);
__PACKAGE__-config(
schema_class = 'MyApp::Schema',
connect_info = [
'dbi:Pg:dbname=xyz',
'xyz',
'xyz',
],
);
---
My gut feeling is this may be part of the problem, but the Model::DBI
is needed for direct SQL queries (many weeks of work needed to convert
them to the Catalyst method) and the current authentication system,
while the Model::Default is needed for all the Model::* packages using
the Catalyst system. (A script utilizing DBIx::Class::Schema::Loader
keeps all of our Schema files matched to the database.)
The 'dsn' entry in the above files works fine for the web server and
database server on one machine. When we change it to:
'dbi:Pg:dbname=xyz;host=10.10.0.34'
to access a different server for the database, that's when the
connection leaking goes nuts -- the program still works fine until it
dies from lack of connections. I've been searching the web and not
finding many answers.
I've gone through about a year's worth of msgs on this mailing list
and didn't see anything that helped. If someone could point me to some
documents or threads or other source of info (or maybe a more
appropriate mailing list if I picked wrong!) that could shed light on
this for me, I'd be grateful. And my one, remaining, brain cell would
last me a while longer . . .
--
Kevin Nathan
[EMAIL PROTECTED]
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Database connections leaking
On Fri, 2007-12-21 at 18:34 -0700, [EMAIL PROTECTED] wrote: The program seems to be working fine while on the Catalyst test server, and when on an Apache/mod_perl server when the postgres database is on the same physical server. The database connections don't start leaking until we attempt to use a separate (physical) database server. We verified this using iptraf and seeing none of the connections being re-used *or* closed. How many database connections are you actually seeing? You should expect up to one per Apache/mod_perl process. Are you also seeing growth in the number of Apache processes? -- Jon SchutzMy tech notes http://notes.jschutz.net Chief Technology Officerhttp://www.youramigo.com YourAmigo ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Database connections leaking
On Sat, 22 Dec 2007 13:32:00 +1030 Jon Schutz [EMAIL PROTECTED] wrote: On Fri, 2007-12-21 at 18:34 -0700, [EMAIL PROTECTED] wrote: The program seems to be working fine while on the Catalyst test server, and when on an Apache/mod_perl server when the postgres database is on the same physical server. The database connections don't start leaking until we attempt to use a separate (physical) database server. We verified this using iptraf and seeing none of the connections being re-used *or* closed. How many database connections are you actually seeing? You should expect up to one per Apache/mod_perl process. Are you also seeing growth in the number of Apache processes? I can't check the server right now (need to be at work for that), but I didn't think to watch for Apache/mod_perl processes. What we were looking for was port 5432 connections (postgres). When it works, we get two or three connections per login and they stay steady. When it's not working, they continuously increase until config limit is reached (we've gone as high as 3000 connections). It will be a few days now, before I can check it again unless I get some time to drive to the colocation, but I will check for the Apache processes. If they are going up, too, where should I look? Any hints (or websites) that may help? Thanks for the quick response! -- Kevin Nathan (Arizona, USA) [EMAIL PROTECTED] Open standards. Open source. Open minds. The command line is the front line. Linux 2.6.16.27-0.9-default 9:29pm up 7:40, 18 users, load average: 0.27, 0.25, 0.35 ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Database connections leaking
On Fri, 2007-12-21 at 21:32 -0700, Kevin Nathan wrote: I can't check the server right now (need to be at work for that), but I didn't think to watch for Apache/mod_perl processes. What we were looking for was port 5432 connections (postgres). When it works, we get two or three connections per login and they stay steady. When it's not working, they continuously increase until config limit is reached (we've gone as high as 3000 connections). It will be a few days now, before I can check it again unless I get some time to drive to the colocation, but I will check for the Apache processes. If they are going up, too, where should I look? Any hints (or websites) that may help? Thanks for the quick response! It seems to me you would know about it, one way or another, if you had 3000 apache processes! I would speculate that the logic in Catalyst::Model::DBI that checks whether it currently has an active connection, is not working for remote Postgres connections, so it keeps creating new ones. I suggest (a) making sure you have the latest version of DBI and DBD::pg and postgres libraries, and if that doesn't solve it, (b) add debug into Catalyst::Model::DBI stay_connected sub to find out which bit of the logic is causing a reconnect. As I'm not much of a postgres or mod_perl user, I bow out here and let the experts take over... -- Jon SchutzMy tech notes http://notes.jschutz.net Chief Technology Officerhttp://www.youramigo.com YourAmigo ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Database connections leaking
On Sat, 22 Dec 2007 15:34:14 +1030 Jon Schutz [EMAIL PROTECTED] wrote: It seems to me you would know about it, one way or another, if you had 3000 apache processes! It would seem so, wouldn't it? :-) From what I remember, though, we never saw a real increase in port 443 connections. I would speculate that the logic in Catalyst::Model::DBI that checks whether it currently has an active connection, is not working for remote Postgres connections, so it keeps creating new ones. That makes sense. I even looked at C::M::DBI, quite a bit, but it just didn't make it through all my hair-pulling . . . I suggest (a) making sure you have the latest version of DBI and DBD::pg and postgres libraries, I will do that tomorrow, although I'm pretty sure they're fairly current -- the servers were just built a few weeks ago (Gentoo, from scratch). and if that doesn't solve it, (b) add debug into Catalyst::Model::DBI stay_connected sub to find out which bit of the logic is causing a reconnect. See? Now *that's* what I should have realized! ;-) Even after looking at that code, it didn't occur to me to add the debug code; of course, I have debug code all over everything else. Looks like I will be driving in to work tomorrow . . . As I'm not much of a postgres or mod_perl user, I bow out here and let the experts take over... Well, I would like to thank you profusely. You've at least helped clear the cobwebs away from my overworked brain cell and given me a bit of hope that I'm not entirely out of my depth -- close, but not completely, yet! :-) Thanks, again. -- Kevin Nathan (Arizona, USA) [EMAIL PROTECTED] Open standards. Open source. Open minds. The command line is the front line. Linux 2.6.16.27-0.9-default 12:08am up 10:19, 18 users, load average: 0.33, 1.06, 1.65 ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[EMAIL PROTECTED]/ Dev site: http://dev.catalyst.perl.org/
