This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: Apache::DBI strategy/philosophy
From: John M Vinopal <jvinopal@abattoir.com>
Date: Fri, 16 Jun 2000 23:54:11 -0700
Part of the problem I've had is transitioning a cgi application onto
mod_perl and keeping performance up on both platforms until a switch
can be made. So I've done some comparisons of the various dbh schemes.
Comments and corrections very welcome. I'm wary of connect_cached()
under mod_perl but perhaps for no good reason.
DBI Connection Tests
Since connecting can be expensive, you generally just
connect at the start of your program and disconnect at the
end.
DBI.pm 1.14 docs
For programmer ease you probally want to connect inside a module, and for
mod_perl speed you want to preload that module. But you can't do both AND
connect once at the start. Mod_perl insists that dbh connections must not
be made in the parent and then used in the child: that means that you cannot
initialize your dbh in BEGIN or outside of a subroutine. The trickier
problem involves a popular hack '$dbh ||= connect()', in CGI this will
work effectively, however in mod_perl the variable $dbh is persistent
between invocations. Should your database vanish, the connection within
$dbh will be invalid, but the variable $dbh will still be a TRUE value
and all of your queries will fail until you restart apache (ie: ||= doesn't
call ping()).
This benchmark compares some different techiques of DBI connection and
discusses their merits. Each set of DBI connections is done inside a
module. Each module calls a number of subroutines, all of which
perform database operations. There are 14 connect and execute calls
over two different database handles.
Run from the command line
Benchmark: timing 20 iterations of 0 my, 1 local, 2 or, 3 global, 4 cache, 5 cache my...
0 my: 55 wallclock secs ( 5.47 usr 3.44 sys + 22.36 cusr 21.31 csys = 52.58 CPU) @ 2.24/s (n=20)
1 local: 52 wallclock secs ( 5.54 usr 3.48 sys + 20.86 cusr 19.99 csys = 49.87 CPU) @ 2.22/s (n=20)
2 or: 12 wallclock secs ( 2.13 usr 0.74 sys + 2.58 cusr 3.08 csys = 8.53 CPU) @ 6.97/s (n=20)
3 global: 12 wallclock secs ( 2.11 usr 0.84 sys + 2.60 cusr 3.04 csys = 8.59 CPU) @ 6.78/s (n=20)
4 cache: 13 wallclock secs ( 2.80 usr 0.91 sys + 2.73 cusr 2.94 csys = 9.38 CPU) @ 5.39/s (n=20)
5 cache my: 13 wallclock secs ( 2.82 usr 0.89 sys + 2.87 cusr 2.77 csys = 9.35 CPU) @ 5.39/s (n=20)
Run under CGI
Benchmark: timing 20 iterations of 0 my, 1 local, 2 or, 3 global, 4 cache, 5 cache my...
0 my: 54 wallclock secs ( 5.31 usr 2.90 sys + 22.34 cusr 21.20 csys = 51.75 CPU) @ 2.44/s (n=20)
1 local: 51 wallclock secs ( 5.49 usr 2.88 sys + 20.78 cusr 19.96 csys = 49.11 CPU) @ 2.39/s (n=20)
2 or: 12 wallclock secs ( 2.35 usr 0.63 sys + 2.74 cusr 2.82 csys = 8.54 CPU) @ 6.71/s (n=20)
3 global: 12 wallclock secs ( 2.28 usr 0.68 sys + 2.59 cusr 2.86 csys = 8.41 CPU) @ 6.76/s (n=20)
4 cache: 13 wallclock secs ( 2.88 usr 0.76 sys + 2.82 cusr 2.86 csys = 9.32 CPU) @ 5.49/s (n=20)
5 cache my: 13 wallclock secs ( 2.99 usr 0.67 sys + 2.84 cusr 2.77 csys = 9.27 CPU) @ 5.46/s (n=20)
Run under Apache::PerlRun and NO Apache::DBI
Benchmark: timing 20 iterations of 0 my, 1 local, 2 or, 3 global, 4 cache, 5 cache my...
0 my: 55 wallclock secs ( 5.43 usr 3.23 sys + 21.99 cusr 22.04 csys = 52.69 CPU) @ 2.31/s (n=20)
1 local: 53 wallclock secs ( 5.49 usr 3.35 sys + 21.34 cusr 19.83 csys = 50.01 CPU) @ 2.26/s (n=20)
2 or: 12 wallclock secs ( 2.12 usr 0.67 sys + 2.85 cusr 2.82 csys = 8.46 CPU) @ 7.17/s (n=20)
3 global: 12 wallclock secs ( 2.12 usr 0.74 sys + 2.69 cusr 2.88 csys = 8.43 CPU) @ 6.99/s (n=20)
4 cache: 13 wallclock secs ( 2.89 usr 0.80 sys + 2.67 cusr 2.93 csys = 9.29 CPU) @ 5.42/s (n=20)
5 cache my: 12 wallclock secs ( 3.08 usr 0.72 sys + 2.61 cusr 2.86 csys = 9.27 CPU) @ 5.26/s (n=20)
Run under Apache::PerlRun and Apache::DBI
Benchmark: timing 20 iterations of 0 my, 1 local, 2 or, 3 global, 4 cache, 5 cache my...
0 my: 12 wallclock secs ( 2.29 usr 0.58 sys + 2.64 cusr 2.82 csys = 8.33 CPU) @ 6.97/s (n=20)
1 local: 12 wallclock secs ( 2.43 usr 0.61 sys + 2.61 cusr 3.02 csys = 8.67 CPU) @ 6.58/s (n=20)
2 or: 11 wallclock secs ( 2.14 usr 0.69 sys + 2.98 cusr 2.95 csys = 8.76 CPU) @ 7.07/s (n=20)
3 global: 12 wallclock secs ( 2.16 usr 0.66 sys + 2.61 cusr 2.86 csys = 8.29 CPU) @ 7.09/s (n=20)
4 cache: 12 wallclock secs ( 2.72 usr 0.74 sys + 2.80 cusr 2.81 csys = 9.07 CPU) @ 5.78/s (n=20)
5 cache my: 13 wallclock secs ( 2.97 usr 0.76 sys + 2.79 cusr 2.88 csys = 9.40 CPU) @ 5.36/s (n=20)
EXPLANATION OF THE TESTS
my: my dbh are created within the subroutines.
CGI: slow due to reconnections.
MOD_PERL: safe for preloading.
local: Global dbh are initialized within the subroutines.
CGI: slow due to reconnections.
MOD_PERL: safe for preloading.
or: Global dbh are initialized if false using ||=
CGI: fast due to single variable connections.
MOD_PERL: dbh connection done in child, but invalid handles retain
true value and do not reconnect on error.
global: Two dbh initialized globally.
CGI: fast due to single variable connections.
MOD_PERL: connect in parent is disallowed.
cache: Global dbh initialized using connect_cached().
CGI: fast due to caching.
MOD_PERL: fast.
cache my: my dbh initialized using connect_cached().
CGI: fast due to caching.
MOD_PERL: fast.
I suggest the use of a centralized connect routine:
sub my_connect {
my $db = shift;
my $d;
if ($db eq 'db1') {
$d = DBI->connect('dbi:Oracle:','uid','pass');
} else {
die "Invalid db '$db'";
}
die DBI->errstr unless ($d);
return($d);
}
Where you use connect() for mod_perl and connect_cached() for CGI and use
only my variables in your code. disconnect() is ignored under Apache::DBI,
and disconnect() removes the handle from connect_cached(), so omit
disconnect().
===
Subject: Re: Apache::DBI strategy/philosophy
From: Perrin Harkins <perrin@primenet.com>
Date: Sat, 17 Jun 2000 12:03:55 -0700
John M Vinopal wrote:
> For programmer ease you probally want to connect inside a module, and for
> mod_perl speed you want to preload that module. But you can't do both AND
> connect once at the start. Mod_perl insists that dbh connections must not
> be made in the parent and then used in the child: that means that you cannot
> initialize your dbh in BEGIN or outside of a subroutine.
You can preload the module without actually making the connection. When
using Apache::DBI, you do want to call DBI->connect once per request so
that your database handle will be ping'ed. You can use
Apache::DBI->connect_on_init() to make sure that there's a cached
connection waiting for you when you issue your first DBI->connect().
===