You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Pierre Smolarek <pi...@serverspy.net> on 2005/11/22 18:15:05 UTC
Apache::DBI + DBD::Multiplex
Hello,
Is anyone aware of any issues with the way Apache:::DBI works with
DBD::Multiplex under Apache2 + modperl 2.
I'm experiencing an issue where Apache::DBI seems to remember a
connection but never reuses it. So it constantly creates new DB
connections but never lets existing ones disconnect until the DB server
timesout the connection.
--
Best Regards,
Pierre Smolarek
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Carl Johnstone wrote:
> The subroutine will only be defined once at file load time so the reference
> to it won't change.
>
> Thinking about it, depending where you define the @dsns variable I suggested
> won't necessarily work that much better. You need to make sure it's declared
> in a namespace where it won't get redefined - effectively you want to ensure
> that you only ever define that array once, then the reference to it won't
> ever change.
>
> Or *if* you do need to change the DSN list without restarting mod_perl you
> need to do something that's a bit more of a work-around.
>
> Carl
>
>
Ok, i see what you're saying. I declaired a global with the array and
then refernced it as you suggested.
6202 Apache::DBI new connect to
'database=ssAccess;host=localhostserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0x8682158)mx_error_proc=CODE(0x85fa3e0)mx_exit_mode=first_successmx_master_id=dbserver1'
6202 Apache::DBI new connect to
'database=ssAccess;host=localhostserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0x8682158)mx_error_proc=CODE(0x85fa3e0)mx_exit_mode=first_successmx_master_id=dbserver1'
6202 Apache::DBI new connect to
'database=ssAccess;host=localhostserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0x8682158)mx_error_proc=CODE(0x85fa3e0)mx_exit_mode=first_successmx_master_id=dbserver1'
6202 Apache::DBI new connect to
'database=ssAccess;host=localhostserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0x8682158)mx_error_proc=CODE(0x85fa3e0)mx_exit_mode=first_successmx_master_id=dbserver1'
6202 Apache::DBI new connect to
'database=ssAccess;host=localhostserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0x8682158)mx_error_proc=CODE(0x85fa3e0)mx_exit_mode=first_successmx_master_id=dbserver1'
Looks like that fixed the array address but Apache::DBI still seems to
be struggling holding onto the connection.
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by Carl Johnstone <mo...@fadetoblack.demon.co.uk>.
> Thanks for the suggestion. It appears that DBD::Multiplex is mashing up
> the ref address each time. Interesting its not mashing up the sub ref
> address for mx_error_proc. I'll dig deaper into Multiplex and see why
> its changing it.
The subroutine will only be defined once at file load time so the reference
to it won't change.
Thinking about it, depending where you define the @dsns variable I suggested
won't necessarily work that much better. You need to make sure it's declared
in a namespace where it won't get redefined - effectively you want to ensure
that you only ever define that array once, then the reference to it won't
ever change.
Or *if* you do need to change the DSN list without restarting mod_perl you
need to do something that's a bit more of a work-around.
Carl
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Carl Johnstone wrote:
> Have you copied the example code from DBD::Multiplex?
>
> %attr = (
> 'mx_dsns' => [$dsn1, $dsn2, $dsn3, $dsn4],
> 'mx_master_id' => 'dbaaa1',
> 'mx_connect_mode' => 'ignore_errors',
> 'mx_exit_mode' => 'first_success',
> 'mx_error_proc' => \&MyErrorProcedure,
> );
> $dbh = DBI->connect("dbi:Multiplex:", 'username', 'password', \%attr);
> If so you're creating a NEW anonymous array everytime you build %attr. Try:
> my @dsns = ($dsn1, $dsn2, $dsn3, $dsn4); %attr = ( 'mx_dsns' =>
> \@dsns, 'mx_master_id' => 'dbaaa1', 'mx_connect_mode' =>
> 'ignore_errors', 'mx_exit_mode' => 'first_success',
> 'mx_error_proc' => \&MyErrorProcedure, ); $dbh =
> DBI->connect("dbi:Multiplex:", 'username', 'password', \%attr); Or even
> better find someway of defining this stuff once, then re-using it. I'd
> suggest a custom DB settings module then when loaded initialises the
> required variables, and can return a DB handle to any other code when you
> need it.Carl
>
>
Hello Carl,
Thanks for the suggestion. It appears that DBD::Multiplex is mashing up
the ref address each time. Interesting its not mashing up the sub ref
address for mx_error_proc. I'll dig deaper into Multiplex and see why
its changing it.
Regards,
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by Carl Johnstone <mo...@fadetoblack.demon.co.uk>.
> Philippe M. Chiasson wrote:
> > 2 possible avenues to investigate:
> > - Change your code to not use an array-ref as mx_dsns argument but some
string (comma delimited, etc)
> > - Look at changing Apache::DBI to not use the ref as part of the dsn
cache key, but use the contents of the array
> > something along the lines of :
> > $Key = Dumper(\@_);
> >
>
> The arrayref is a requirment of multiplex. I'll do some hacking in both
> modules and see what works. I'm not certain if the first possible change
> would work as suggested as Apache::DBI would end up caching possibly
> just one, if any of the multiple potential DB connections. The target
> will be to cache a persistant connection for all DB servers that
> multiplex is setup to query against.
>
> I'm not completly up to scratch when or how Apache::DBI does its magic,
> either within the connect call of my DBI handle or within Multiplex's
> (as multiplex does a similar thing to Apache::DBI in redirecting the
> connect request).
Have you copied the example code from DBD::Multiplex?
%attr = (
'mx_dsns' => [$dsn1, $dsn2, $dsn3, $dsn4],
'mx_master_id' => 'dbaaa1',
'mx_connect_mode' => 'ignore_errors',
'mx_exit_mode' => 'first_success',
'mx_error_proc' => \&MyErrorProcedure,
);
$dbh = DBI->connect("dbi:Multiplex:", 'username', 'password', \%attr);
If so you're creating a NEW anonymous array everytime you build %attr. Try:
my @dsns = ($dsn1, $dsn2, $dsn3, $dsn4); %attr = ( 'mx_dsns' =>
\@dsns, 'mx_master_id' => 'dbaaa1', 'mx_connect_mode' =>
'ignore_errors', 'mx_exit_mode' => 'first_success',
'mx_error_proc' => \&MyErrorProcedure, ); $dbh =
DBI->connect("dbi:Multiplex:", 'username', 'password', \%attr); Or even
better find someway of defining this stuff once, then re-using it. I'd
suggest a custom DB settings module then when loaded initialises the
required variables, and can return a DB handle to any other code when you
need it.Carl
Re: Apache::DBI + DBD::Multiplex
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, 2005-11-23 at 13:15 -0700, Pierre Smolarek wrote:
> I'm not completly up to scratch when or how Apache::DBI does its magic,
> either within the connect call of my DBI handle or within Multiplex's
> (as multiplex does a similar thing to Apache::DBI in redirecting the
> connect request).
Maybe it does something to add persistence and makes Apache::DBI
redundant. Anyway, Apache::DBI is a very short piece of code and easy
to read. The only surprise is that DBI itself has a little bit of
special behavior when Apache::DBI is loaded. Just look at the DBI
source to see it.
- Perrin
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Philippe M. Chiasson wrote:
> 2 possible avenues to investigate:
> - Change your code to not use an array-ref as mx_dsns argument but some string (comma delimited, etc)
> - Look at changing Apache::DBI to not use the ref as part of the dsn cache key, but use the contents of the array
> something along the lines of :
> $Key = Dumper(\@_);
>
The arrayref is a requirment of multiplex. I'll do some hacking in both
modules and see what works. I'm not certain if the first possible change
would work as suggested as Apache::DBI would end up caching possibly
just one, if any of the multiple potential DB connections. The target
will be to cache a persistant connection for all DB servers that
multiplex is setup to query against.
I'm not completly up to scratch when or how Apache::DBI does its magic,
either within the connect call of my DBI handle or within Multiplex's
(as multiplex does a similar thing to Apache::DBI in redirecting the
connect request).
I'll report back any findings.
Many thanks,
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by "Philippe M. Chiasson" <go...@ectoplasm.org>.
Philip M. Gollucci wrote:
> Philippe M. Chiasson wrote:
>
>> Philip M. Gollucci wrote:
>>
>>>> 2 possible avenues to investigate:
>>>> - Look at changing Apache::DBI to not use the ref as part of the dsn
>>>> cache key, but use the contents of the array
>>>> something along the lines of :
>>>> $Key = Dumper(\@_);
>>>
>>> If you do patch Apache::DBI, please post the diff, maybe we could add an
>>> httpd.conf PerlSetVar to enable/disable your new behavior ?
>> Possible patch to DBI to correct this behavior (untested).
>>
>> --- DBI.pm.orig 2005-11-23 13:54:15.000000000 -0800
>> +++ DBI.pm 2005-11-23 14:00:07.000000000 -0800
>> @@ -89,19 +89,11 @@
>
> What version is that against ?
Oh, whatever is on search.cpan.org/dist/Apache-DBI/
--------------------------------------------------------------------------------
Philippe M. Chiasson m/gozer\@(apache|cpan|ectoplasm)\.org/ GPG KeyID : 88C3A5A5
http://gozer.ectoplasm.org/ F9BF E0C2 480E 7680 1AE5 3631 CB32 A107 88C3A5A5
Re: Apache::DBI + DBD::Multiplex
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Philippe M. Chiasson wrote:
> Philip M. Gollucci wrote:
>
>>>2 possible avenues to investigate:
>>> - Look at changing Apache::DBI to not use the ref as part of the dsn
>>>cache key, but use the contents of the array
>>> something along the lines of :
>>> $Key = Dumper(\@_);
>>
>>
>>If you do patch Apache::DBI, please post the diff, maybe we could add an
>>httpd.conf PerlSetVar to enable/disable your new behavior ?
>
>
> Possible patch to DBI to correct this behavior (untested).
>
> --- DBI.pm.orig 2005-11-23 13:54:15.000000000 -0800
> +++ DBI.pm 2005-11-23 14:00:07.000000000 -0800
> @@ -89,19 +89,11 @@
What version is that against ?
Alright, I give up, how to I get ask to give me Karma for this module I so I can index on CPAN and move to svn.perl.org ?
--
--------------------------------------------------------------------------
"Love is not the one you can picture yourself marrying,
but the one you can't picture the rest of your life without."
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Philip M. Gollucci (pgollucci@p6m7g8.com) 301.254.5198
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
Re: Apache::DBI + DBD::Multiplex
Posted by "Philippe M. Chiasson" <go...@ectoplasm.org>.
Perrin Harkins wrote:
> Philippe M. Chiasson wrote:
>
>> + my $Idx;
>> + {
>> + local $Data::Dumper::Indent = 0;
>> + local $Data::Dumper::Terse = 1;
>> + $Idx = Dumper(\@args);
>
> This may also be significantly slower than what it's replacing, and
> since this is just calculating a cache key, people expect it to be fast.
> If this does solve the problem, it should be optional, unless
> benchmarks show it to be a negligible hit.
Absolutely, I was just making a point clear ;-)
If you look at the existing code, there is a partial attempt at this already,
with a loop unrolling HASH refs in the arguments, it's just not dealing with
ARRAY refs correctly.
> Maybe more to the point, I'm not sure it's a good idea to shoot in the
> dark like this without knowing how DBD::Multiplex uses this data or
> what's in it.
Well, I am not sure what the DBI api says, but I would assume that what Apache::DBI
needs to be able to do is to identify 2 connect() attempts for the same dsn.
And that, IMO, is when the arguments to connect() are identical in value, not reference,
but I could be wrong.
--------------------------------------------------------------------------------
Philippe M. Chiasson m/gozer\@(apache|cpan|ectoplasm)\.org/ GPG KeyID : 88C3A5A5
http://gozer.ectoplasm.org/ F9BF E0C2 480E 7680 1AE5 3631 CB32 A107 88C3A5A5
Re: Apache::DBI + DBD::Multiplex
Posted by Perrin Harkins <pe...@elem.com>.
Philippe M. Chiasson wrote:
> + my $Idx;
> + {
> + local $Data::Dumper::Indent = 0;
> + local $Data::Dumper::Terse = 1;
> + $Idx = Dumper(\@args);
This may also be significantly slower than what it's replacing, and
since this is just calculating a cache key, people expect it to be fast.
If this does solve the problem, it should be optional, unless
benchmarks show it to be a negligible hit.
Maybe more to the point, I'm not sure it's a good idea to shoot in the
dark like this without knowing how DBD::Multiplex uses this data or
what's in it.
- Perrin
Re: Apache::DBI + DBD::Multiplex
Posted by "Philippe M. Chiasson" <go...@ectoplasm.org>.
Philip M. Gollucci wrote:
>> 2 possible avenues to investigate:
>> - Look at changing Apache::DBI to not use the ref as part of the dsn
>> cache key, but use the contents of the array
>> something along the lines of :
>> $Key = Dumper(\@_);
>
>
> If you do patch Apache::DBI, please post the diff, maybe we could add an
> httpd.conf PerlSetVar to enable/disable your new behavior ?
Possible patch to DBI to correct this behavior (untested).
--- DBI.pm.orig 2005-11-23 13:54:15.000000000 -0800
+++ DBI.pm 2005-11-23 14:00:07.000000000 -0800
@@ -89,19 +89,11 @@
my $dsn = "dbi:$drh->{Name}:$args[0]";
my $prefix = "$$ Apache::DBI ";
- my $Idx = join $;, $args[0], $args[1], $args[2]; # key of %Connected and %Rollback.
-
- # the hash-reference differs between calls even in the same
- # process, so de-reference the hash-reference
- if (3 == $#args and ref $args[3] eq "HASH") {
- # should we default to '__undef__' or something for undef values?
- map { $Idx .= "$;$_=" .
- (defined $args[3]->{$_}
- ? $args[3]->{$_}
- : '')
- } sort keys %{$args[3]};
- } elsif (3 == $#args) {
- pop @args;
+ my $Idx;
+ {
+ local $Data::Dumper::Indent = 0;
+ local $Data::Dumper::Terse = 1;
+ $Idx = Dumper(\@args);
}
# don't cache connections created during server initialization; they
@@ -292,7 +284,8 @@
my($r, $q) = @_;
my(@s) = qw(<TABLE><TR><TD>Datasource</TD><TD>Username</TD></TR>);
for (keys %Connected) {
- push @s, '<TR><TD>', join('</TD><TD>', (split($;, $_))[0,1]), "</TD></TR>\n";
+ my @data = @{ eval $_ }
+ push @s, '<TR><TD>', join('</TD><TD>', @data[0,1], "</TD></TR>\n";
}
push @s, '</TABLE>';
return \@s;
@@ -306,7 +299,8 @@
my($r, $q) = @_;
my(@s) = qw(<TABLE><TR><TD>Datasource</TD><TD>Username</TD></TR>);
for (keys %Connected) {
- push @s, '<TR><TD>', join('</TD><TD>', (split($;, $_))[0,1]), "</TD></TR>\n";
+ my @data = @{ eval $_ }
+ push @s, '<TR><TD>', join('</TD><TD>', @data[0,1], "</TD></TR>\n";
}
push @s, '</TABLE>';
return \@s;
--------------------------------------------------------------------------------
Philippe M. Chiasson m/gozer\@(apache|cpan|ectoplasm)\.org/ GPG KeyID : 88C3A5A5
http://gozer.ectoplasm.org/ F9BF E0C2 480E 7680 1AE5 3631 CB32 A107 88C3A5A5
Re: Apache::DBI + DBD::Multiplex
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
> 2 possible avenues to investigate:
> - Look at changing Apache::DBI to not use the ref as part of the dsn cache key, but use the contents of the array
> something along the lines of :
> $Key = Dumper(\@_);
If you do patch Apache::DBI, please post the diff, maybe we could add an httpd.conf PerlSetVar to enable/disable your
new behavior ?
--
--------------------------------------------------------------------------
"Love is not the one you can picture yourself marrying,
but the one you can't picture the rest of your life without."
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Philip M. Gollucci (pgollucci@p6m7g8.com) 301.254.5198
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
Re: Apache::DBI + DBD::Multiplex
Posted by "Philippe M. Chiasson" <go...@ectoplasm.org>.
Pierre Smolarek wrote:
> Perrin Harkins wrote:
>
>> There is a debug setting described in the Apache::DBI documentation
>> which will tell you this.
>>
>> - Perrin
>>
>
> I've attached about 2 seconds of debug output. From what i can gather,
> its constantly creating new connections on what appears to be the same
> database per PID.
>
> for example:
> 91571 Apache::DBI new connect to
> 'database=ssAccess;host=server3.serverspy.netserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0xa56e918)mx_error_proc=CODE(0xa3c0d60)mx_exit_mode=first_successmx_master_id=dbserver1'
>
>
> Then later on:
>
> 91571 Apache::DBI new connect to
> 'database=ssAccess;host=server3.serverspy.netserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0xa573128)mx_error_proc=CODE(0xa3c0d60)mx_exit_mode=first_successmx_master_id=dbserver1'
>
>
> The only difference i see is mx_dsns=ARRAY(0xa56e918). So my layman
> guess is that the arrayref of db servers set within the DBD::Multiplex
> dsn is at a different address each time and therefore the cache sees a
> unique dsn? (Please correct me :) i'm far form an expert on these things).
Yup, that's exactly what is hapenning. Apache::DBI needs to know wether to
dsn's are identical, and this ever changing mx_dsns=ARRAY(0xa56e918) argument
is causing it to believe it's being asked about a new dsn each time.
2 possible avenues to investigate:
- Change your code to not use an array-ref as mx_dsns argument but some string (comma delimited, etc)
- Look at changing Apache::DBI to not use the ref as part of the dsn cache key, but use the contents of the array
something along the lines of :
$Key = Dumper(\@_);
--------------------------------------------------------------------------------
Philippe M. Chiasson m/gozer\@(apache|cpan|ectoplasm)\.org/ GPG KeyID : 88C3A5A5
http://gozer.ectoplasm.org/ F9BF E0C2 480E 7680 1AE5 3631 CB32 A107 88C3A5A5
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Perrin Harkins wrote:
> Sounds likely, but I have no idea how DBD::Multiplex uses that or if it
> can be safely ignored when connecting. Have you tried contacting the
> author of DBD::Multiplex for help? No one here seems to be using it.
>
> - Perrin
>
>
Not yet. Are there any other recomendations on a method to query against
replicated DB servers? I assumed Multiplex was the normal practice.
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, 2005-11-23 at 10:32 -0700, Pierre Smolarek wrote:
> The only difference i see is mx_dsns=ARRAY(0xa56e918). So my layman
> guess is that the arrayref of db servers set within the DBD::Multiplex
> dsn is at a different address each time and therefore the cache sees a
> unique dsn?
Sounds likely, but I have no idea how DBD::Multiplex uses that or if it
can be safely ignored when connecting. Have you tried contacting the
author of DBD::Multiplex for help? No one here seems to be using it.
- Perrin
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Perrin Harkins wrote:
> There is a debug setting described in the Apache::DBI documentation
> which will tell you this.
>
> - Perrin
>
I've attached about 2 seconds of debug output. From what i can gather,
its constantly creating new connections on what appears to be the same
database per PID.
for example:
91571 Apache::DBI new connect to
'database=ssAccess;host=server3.serverspy.netserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0xa56e918)mx_error_proc=CODE(0xa3c0d60)mx_exit_mode=first_successmx_master_id=dbserver1'
Then later on:
91571 Apache::DBI new connect to
'database=ssAccess;host=server3.serverspy.netserverspyserverspyAutoCommit=1PrintError=0Username=serverspymx_connect_mode=ignore_errorsmx_dsns=ARRAY(0xa573128)mx_error_proc=CODE(0xa3c0d60)mx_exit_mode=first_successmx_master_id=dbserver1'
The only difference i see is mx_dsns=ARRAY(0xa56e918). So my layman
guess is that the arrayref of db servers set within the DBD::Multiplex
dsn is at a different address each time and therefore the cache sees a
unique dsn? (Please correct me :) i'm far form an expert on these things).
Any direction on this would be greatly appreciated.
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by Perrin Harkins <pe...@elem.com>.
Pierre Smolarek wrote:
> Can you recommend anything obvious to help debug how Apache::DBI is
> caching, checking and reusing its DB connections ?
There is a debug setting described in the Apache::DBI documentation
which will tell you this.
- Perrin
Re: Apache::DBI + DBD::Multiplex
Posted by Pierre Smolarek <pi...@serverspy.net>.
Philip M. Gollucci wrote:
> I don't use DBD::Multiplex, so I can't say. I've never seen anyone
> report this to the list either.
>
Thanks Phillip,
Can you recommend anything obvious to help debug how Apache::DBI is
caching, checking and reusing its DB connections ?
Pierre
Re: Apache::DBI + DBD::Multiplex
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Pierre Smolarek wrote:
> Hello,
>
> Is anyone aware of any issues with the way Apache:::DBI works with
> DBD::Multiplex under Apache2 + modperl 2.
>
> I'm experiencing an issue where Apache::DBI seems to remember a
> connection but never reuses it. So it constantly creates new DB
> connections but never lets existing ones disconnect until the DB server
> timesout the connection.
>
I don't use DBD::Multiplex, so I can't say. I've never seen anyone report this to the list either.
--
--------------------------------------------------------------------------
"Love is not the one you can picture yourself marrying,
but the one you can't picture the rest of your life without."
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Philip M. Gollucci (pgollucci@p6m7g8.com) 301.254.5198
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com