mod_perl2 with mod_dbd and Apache2

Quick question for the list. I've been scouring Google looking for
information on Connection Pooling to MySQL 5.0 in mod_perl2 on Apache2
using mod_dbd or similar, and while I can find docs for configuring
mod_dbd in Apache2, and am quite familiar with mod_perl development, I'm
unable to find specific examples online for how to code mod_perl to access
mod_dbd connection pools via APR... Am I completely on the wrong track? Is
there another more simple way to implement MySQL DBI connection pooling in
mod_perl?
I'm running a website that is starting to get enough traffic that
opening a fresh DB connection via Perl's DBI libs on each request is
starting to cost me serious time in serving mod_perl pages through
Apache2, and I'd like to implement connection pooling to improve
performance. Any suggestions or URLs that could help me get on the right
track would be muchly appreciated!

Thanks,

- Tim Partee

Re: mod_perl2 with mod_dbd and Apache2

On Wed, Aug 27, 2008 at 5:15 AM, Timothy Partee wrote:
> Am I completely on the wrong track?

In my opinion, yes. You'd have to write your own DBI, or a DBD driver
that talks to mod_dbd. It would not be easy.

> Is there
> another more simple way to implement MySQL DBI connection pooling in
> mod_perl?

There are a few. You can use DBI::Gofer. A couple of large sites
already do this. You can read Tim Bunce's presentation on it for
details of how it reduces your connections.

You can also use SQL Relay: http://sqlrelay.sourceforge.net/

> I'm running a website that is starting to get enough traffic that opening
> a fresh DB connection via Perl's DBI libs on each request is starting to
> cost me serious time in serving mod_perl pages through Apache2, and I'd like
> to implement connection pooling to improve performance.

You're already running a reverse proxy in front of mod_perl, right?
If not, do that first. It usually cuts the number of persistent
connections dramatically.

- Perrin

Re: mod_perl2 with mod_dbd and Apache2

Perrin Harkins wrote:
> In my opinion, yes. You'd have to write your own DBI, or a DBD driver
> that talks to mod_dbd. It would not be easy.
Yeah were definitely missing the that APR::* XS glue for this, but its
something I'm incredibly interested in. I just lack the time atm....

I think some other things would have to be solved first too.

For instance we really only wrap apr v0.9 and they're up to 1.3.3.
This should be loads of 'fun' to fix.


[snipped Perrin's comments which are valid]



--
------------------------------------------------------------ ------------
Philip M. Gollucci (philip@ridecharge.com)
o:703.549.2050x206
Senior System Admin - Riderway, Inc.
http://riderway.com / http://ridecharge.com
1024D/DB9B8C1C B90B FBC3 A3A1 C71A 8E70 3F8C 75B8 8FFB DB9B 8C1C

Work like you don't need the money,
love like you'll never get hurt,
and dance like nobody's watching.

Re: mod_perl2 with mod_dbd and Apache2

Timothy Partee wrote:
>
>
> Quick question for the list. I've been scouring Google looking for
> information on Connection Pooling to MySQL 5.0 in mod_perl2 on Apache2
> using mod_dbd or similar, and while I can find docs for configuring
> mod_dbd in Apache2, and am quite familiar with mod_perl development, I'm
> unable to find specific examples online for how to code mod_perl to access
> mod_dbd connection pools via APR... Am I completely on the wrong track? Is
> there another more simple way to implement MySQL DBI connection pooling in
> mod_perl?
>
>

I've found that using ODBC's connection pooling helps a lot, and is easy to
implement (simply switch to DBD::ODBC, and enable connection pooling in your
odbcinst.ini file (Pooling = Yes)


--
View this message in context: http://www.nabble.com/mod_perl2-with-mod_dbd-and-Apache2-tp1 9177352p21914216.html
Sent from the mod_perl - General mailing list archive at Nabble.com.

Re: mod_perl2 with mod_dbd and Apache2

I realize that this response is months late, but this thread is only
now coming to my attention due to another recent response. Still, I
think you may have been trying to solve the wrong problem. Perhaps I'm
missing a detail or two here, but you are running mod_perl. Perl
processes live on beyond the time to service one request. Why don't
you set up a PerlChildInitHandler that establishes your database
connection and holds on to it?

$dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
or die $DBI::errstr;

As long as you are not running so many severs / children that you
saturate you DB connection limit, you should be fine.

Ivan

On Wed, Aug 27, 2008 at 1:15 AM, Timothy Partee wrote:
>
> Quick question for the list. I've been scouring Google looking for
> information on Connection Pooling to MySQL 5.0 in mod_perl2 on Apache2 using
> mod_dbd or similar, and while I can find docs for configuring mod_dbd in
> Apache2, and am quite familiar with mod_perl development, I'm unable to find
> specific examples online for how to code mod_perl to access mod_dbd
> connection pools via APR... Am I completely on the wrong track? Is there
> another more simple way to implement MySQL DBI connection pooling in
> mod_perl?
> I'm running a website that is starting to get enough traffic that opening
> a fresh DB connection via Perl's DBI libs on each request is starting to
> cost me serious time in serving mod_perl pages through Apache2, and I'd like
> to implement connection pooling to improve performance. Any suggestions or
> URLs that could help me get on the right track would be muchly appreciated!
>
> Thanks,
>
> - Tim Partee
>
>

Re: mod_perl2 with mod_dbd and Apache2

You'd probably want to use Apache::DBI in that case.

I got the impression that you were maybe running under a
threaded model with many children serving static content but
sometimes opening DBI connections.

Connection pooling is a cool idea but I don't know who's
made it work. For example, `man DBI` says:

"Why would you want to do this? You don't, forget I even
mentioned it. Unless, that is, you're implementing something
advanced like a multi-threaded connection pool. See
DBI::Pool."

But DBI::Pool doesn't appear to be on CPAN.

Probably what you want to do is to factor the parts of your
application that require the database onto a separate
server with a different URL, that way you can manage them.

Mark

On Wed, 11 Feb 2009, Ivan Heffner wrote:

> I realize that this response is months late, but this thread is only
> now coming to my attention due to another recent response. Still, I
> think you may have been trying to solve the wrong problem. Perhaps I'm
> missing a detail or two here, but you are running mod_perl. Perl
> processes live on beyond the time to service one request. Why don't
> you set up a PerlChildInitHandler that establishes your database
> connection and holds on to it?
>
> $dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
> or die $DBI::errstr;
>
> As long as you are not running so many severs / children that you
> saturate you DB connection limit, you should be fine.
>
> Ivan
>
> On Wed, Aug 27, 2008 at 1:15 AM, Timothy Partee wrote:
> >
> > Quick question for the list. I've been scouring Google looking for
> > information on Connection Pooling to MySQL 5.0 in mod_perl2 on Apache2 using
> > mod_dbd or similar, and while I can find docs for configuring mod_dbd in
> > Apache2, and am quite familiar with mod_perl development, I'm unable to find
> > specific examples online for how to code mod_perl to access mod_dbd
> > connection pools via APR... Am I completely on the wrong track? Is there
> > another more simple way to implement MySQL DBI connection pooling in
> > mod_perl?
> > I'm running a website that is starting to get enough traffic that opening
> > a fresh DB connection via Perl's DBI libs on each request is starting to
> > cost me serious time in serving mod_perl pages through Apache2, and I'd like
> > to implement connection pooling to improve performance. Any suggestions or
> > URLs that could help me get on the right track would be muchly appreciated!
> >
> > Thanks,
> >
> > - Tim Partee
> >
> >
>

Re: mod_perl2 with mod_dbd and Apache2

On Fri, Feb 13, 2009 at 1:22 PM, Mark Hedges wrote:
> Connection pooling is a cool idea but I don't know who's
> made it work.

I gave a couple of options earlier in this thread.

- Perrin