Creating new database handles with mod

lschult2 has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I've run into an issue when stress testing mod_perl that the database connections are going away. I suspect that processes are sharing database connections, causing the issue.

But I've followed all instructions for Apache::DBI, and can't figure this out.

I'm making the connections in the child process and not in startup.pl. But when I examine the $dbh returned by each child from the DBI->connnect, the address is the same for every httpd process.

Firstly, if this is working properly and reconnecting for each process, should the address returned by DBI->connect be different for each child process? I've assumed so, but as far as I can tell the core C code in DBI (dbih_setup_handle) is managing this and is returning the same address. So maybe I'm not understanding what it means to reconnect in the child.

Am I reconnecting properly if the $dbh handles are the same?

Comment on Creating new database handles with mod_perl

Replies are listed 'Best First'.
Re: Creating new database handles with mod_perl by Anonymous Monk on May 20, 2012 at 00:36 UTC
First read and understand CGI to mod_perl Porting. mod_perl Coding guidelines, Lexical scoping like a fox, Variable Scoping in Perl: the basics Then read Apache::DBI and then view the source for Apache::DBI Then see Apache::Status and/or turn on http://search.cpan.org/perldoc/DBI#trace and examine the logs Also, try this `$ perl -le " for(1..3){my@f; print\@f }" ARRAY(0x99a934) ARRAY(0x99a934) ARRAY(0x99a934) $ perl -le " for(1..3){my@f; print\@f; push @global, \@f }" ARRAY(0x99a954) ARRAY(0x3f8cdc) ARRAY(0x3f8d4c)` [download] That ought to clarify for you what is going on :)	[reply] [d/l]
Re^2: Creating new database handles with mod_perl by Anonymous Monk on May 20, 2012 at 04:06 UTC
crossposted at http://stackoverflow.com/questions/10669752/creating-new-database-handles-with-mod-perl Since you're new, I'll quote davido on cross-posting Please make sure when you cross-post that you identify such, so that people here (or there) don't end up working on a problem that is solved. Identifying cross-posting will promote collaborative progress too. Getting a bunch of people doing research for you in parallel without knowing about each others' progress defeats the collaboration aspect of public forums.	[reply]
Re^3: Creating new database handles with mod_perl by Anonymous Monk on May 20, 2012 at 14:25 UTC
Great policy. I added the cross post from SO back to here as well	[reply]
Re^2: Creating new database handles with mod_perl by lschult2 (Novice) on May 20, 2012 at 14:34 UTC
I've read each of those cover to cover many times. I'm a long time Perl user: not quite a Monk but getting there. The one thing I am missing conceptually is how to verify that 2 child processes indeed have a different connection. Both $dbh have the same memory address, but does that mean a shared connection? Maybe, maybe not, since the addresses are virtual. I've been using Apache2::Status heavily, and documentation states that there is a section on handles, but it's not showing up for me and I haven't debugged why yet. If that would answer whether handles shared or not, maybe I should focus on debugging why the handles are not showing up in Apache2::Status.	[reply]
Re^3: Creating new database handles with mod_perl by Anonymous Monk on May 22, 2012 at 09:14 UTC
I've read each of those cover to cover many times. I'm a long time Perl user: not quite a Monk but getting there. :D Just covering my bases :) The one thing I am missing conceptually is how to verify that 2 child processes indeed have a different connection. Both $dbh have the same memory address, but does that mean a shared connection? Maybe, maybe not, since the addresses are virtual. My ideas for verification were checking the logs, or database server status. Are you sure you're not watching the same child? Are you checking the pid $$? $Apache::DBI::DEBUG=2 enables debugging output with pids :) Same memory address does not mean shared connection, each process gets a "distinct memory-space", but I would be surprised if both children would happen to have the same address. I suppose it might happen theoretically, but I doubt it :) maybe I should focus on debugging why the handles are not showing up in Apache2::Status. I don't see mention of handles in Apache2::Status docs, what handles are you talking about?	[reply]
Re: Creating new database handles with mod_perl by locked_user sundialsvc4 (Abbot) on May 20, 2012 at 22:46 UTC
Consider DBIx::Connector. Very handy. Instead of trying to guarantee that a database handle won’t drop off-line, cover for it.
Re^2: Creating new database handles with mod_perl by lschult2 (Novice) on May 21, 2012 at 14:30 UTC
It wasn't the dropping of connections that was the issue. It was confirming that the connections were done correctly and were different. From the feedback, I think now it is being done correctly even though $dbh handles have the same address. ab stress tests confirm this by passing after all the documented steps for Apache::DBI were followed.	[reply]