Alternatives to Apache::DBI?

[Kurt Hansen-2]

Hello,

I'm wondering what techniques folks are using to get persistent database
connections other than Apache::DBI.

Apache::DBI has worked great, but I've treated it as a black box and so
don't fully understand what it is doing in the box. That was fine, but
the fact that the current version (1.07) on CPAN is broken and has been
for a year suggests to me that people are using other methods. These
methods might be better than what I've been doing.

I've read about connect caching and pooling, but I'm not sure if this is
what I want to do instead or where best to read up about it.

Thanks!

Kurt Hansen

[Igor Chudov-3]

I run 1.07 on my webserver (algebra.com) and Apache::DBI works great for me.

i

On Fri, Oct 2, 2009 at 11:30 AM, Kurt Hansen <khansen@...> wrote:

Hello,

I'm wondering what techniques folks are using to get persistent database connections other than Apache::DBI.

Apache::DBI has worked great, but I've treated it as a black box and so don't fully understand what it is doing in the box. That was fine, but the fact that the current version (1.07) on CPAN is broken and has been for a year suggests to me that people are using other methods. These methods might be better than what I've been doing.

I've read about connect caching and pooling, but I'm not sure if this is what I want to do instead or where best to read up about it.

Thanks!

Kurt Hansen

[Perrin Harkins-3]

Apache::DBI is fine.  You can also use DBI->connect_cached, as long as
you are careful to avoid making any database connections during server
startup and install your own cleanup handler to issue a rollback on
all open database handles at the end of every web request.

The Apache::DBI code is very short and simple, so if you're wondering
what it does, just read it.

- Perrin

On Fri, Oct 2, 2009 at 12:30 PM, Kurt Hansen <khansen@...> wrote:

> Hello,
>
> I'm wondering what techniques folks are using to get persistent database
> connections other than Apache::DBI.
>
> Apache::DBI has worked great, but I've treated it as a black box and so
> don't fully understand what it is doing in the box. That was fine, but the
> fact that the current version (1.07) on CPAN is broken and has been for a
> year suggests to me that people are using other methods. These methods might
> be better than what I've been doing.
>
> I've read about connect caching and pooling, but I'm not sure if this is
> what I want to do instead or where best to read up about it.
>
> Thanks!
>
> Kurt Hansen
>

[David E. Wheeler]

On Oct 2, 2009, at 9:30 AM, Kurt Hansen wrote:

> I'm wondering what techniques folks are using to get persistent  
> database connections other than Apache::DBI.

I plan to release a new module, DBIx::Connection, on Monday. It's  
based on the connection caching stuff in DBIx::Class, and also has  
some nice transaction management stuff, so you might want to [check it  
out](http://github.com/theory/dbix-connection/).

Right now, following DBIx::Class's precedent, it disables Apache::DBI  
when it connects to the database. That may or may not be something I  
want to revisit, but since DBIx::Connection does its own caching, I  
rather suspect it's best not to also cache with Apache::DBI.

If you use it, you might also want "install your own cleanup handler  
to issue a rollback on all open database handles at the end of every  
web request," as Perrin says. Bricolage does that with its use of  
connect_cached(), and that works great. It's safe to create a  
connection on startup with DBIx::Connection though, as it is careful  
not to cache across fork or thread boundaries.

Critiques welcome, BTW. I'll likely blog about it next week, too.

Best,

David

[Perrin Harkins-3]

On Fri, Oct 2, 2009 at 10:40 PM, David E. Wheeler <david@...> wrote:
> It's safe to create a connection on
> startup with DBIx::Connection though, as it is careful not to cache across
> fork or thread boundaries.

It may be necessary to set InactiveDestroy on any handles you open
during startup, even if you avoid ever using them again.  They will
eventually time out and may cause trouble when the database tries to
clean them up.

- Perrin

[David E. Wheeler]

On Oct 3, 2009, at 5:25 AM, Perrin Harkins wrote:

>> It's safe to create a connection on
>> startup with DBIx::Connection though, as it is careful not to cache  
>> across
>> fork or thread boundaries.
>
> It may be necessary to set InactiveDestroy on any handles you open
> during startup, even if you avoid ever using them again.  They will
> eventually time out and may cause trouble when the database tries to
> clean them up.

I realized, reading this, that I should check for the Apache startup  
and not cache things if it's during startup. That's useful under  
mod_perl, and won't hurt anything elsewhere. I'll get that committed  
this weekend.

But yes, when DBIx::Connection detects that it's in a new process and  
expires the cached handle, it first sets InactiveDestroy to true. So  
we should be good there.

Best,

David

[Perrin Harkins-3]

On Sat, Oct 3, 2009 at 4:56 PM, David E. Wheeler <david@...> wrote:
> I realized, reading this, that I should check for the Apache startup and not
> cache things if it's during startup. That's useful under mod_perl, and won't
> hurt anything elsewhere. I'll get that committed this weekend.

That should work.  Or you could have it close all connections at the
end of startup.

- Perrin

[David E. Wheeler]

On Oct 3, 2009, at 2:00 PM, Perrin Harkins wrote:

>> I realized, reading this, that I should check for the Apache  
>> startup and not
>> cache things if it's during startup. That's useful under mod_perl,  
>> and won't
>> hurt anything elsewhere. I'll get that committed this weekend.
>
> That should work.  Or you could have it close all connections at the
> end of startup.

In PerlPostConfigHandler? Don't see anything like that in mod_perl1.  
My mod_perl foo is getting rusty, I gotta admit.

David

[Bill Moseley]

On Fri, Oct 2, 2009 at 7:40 PM, David E. Wheeler <david@...> wrote:

On Oct 2, 2009, at 9:30 AM, Kurt Hansen wrote:

I'm wondering what techniques folks are using to get persistent database connections other than Apache::DBI.

I plan to release a new module, DBIx::Connection, on Monday. It's based on the connection caching stuff in DBIx::Class, and also has some nice transaction management stuff, so you might want to [check it out](http://github.com/theory/dbix-connection/).

Hi David,

This looks nice.  Thanks.  I don't use Apache::DBI, but this will be good for general connection and transaction support.  I simply use connect_cached now with { privite_pid = $$ } but that doesn't allow me to set InactiveDestroy (although I'm not using $dbh in Apache parent or forking children so has not been an issue).

One question regarding do():

It's only if the code reference dies that C<do()> will check the connection.
If the handle is not connected to the database (because the database was
restarted, for example), I<then> C<do()> will create a new database handle and
execute the code reference again.

I'm not quite sure how to handle the pings.  I also don't like the idea of a ping every time a $dbh is needed.   But, I wonder about re-issuing the query.

If a do() throws an exception and then the connection is tested and fails the ping are you sure that the query didn't complete?  My concern, of course, is running the query successfully twice.

At one time I considered inspecting the error message and trying to determine if it came from the database so that I knew the query failed, and then reconnect and rerun the query, otherwise just die.

Obviously, you can't reissue the query if in a transaction -- and I see you check for this.

Checking the ping before returning $dbh isn't fool proof either.  The connection could always die between the ping and then when $dbh is used.

Is the history of the ping to catch connections that have been inactive for a long period and perhaps timed out?  I've thought about only issuing the ping if the $dbh hasn't been fetched in some amount of time (say a few minutes) to effectively disable the pings when the site is busy (which should be most of the time).

If you use it, you might also want "install your own cleanup handler to issue a rollback on all open database handles at the end of every web request," as Perrin says. Bricolage does that with its use of connect_cached(), and that works great. It's safe to create a connection on startup with DBIx::Connection though, as it is careful not to cache across fork or thread boundaries.

But, if you use txn_do() for all transactions then is there any need for the cleanup handler rollback?

I also see in your disconnect method that you manually issue a rollback, call disconnect and then undefine $dbh.  Doesn't DBI do that automatically when $dbh is DESTROYed?
 

--
Bill Moseley
moseley@...

[Fred Moyer]

On Sun, Oct 4, 2009 at 10:17 AM, Bill Moseley <moseley@...> wrote:
>
> On Fri, Oct 2, 2009 at 7:40 PM, David E. Wheeler <david@...>
> Is the history of the ping to catch connections that have been inactive for
> a long period and perhaps timed out?  I've thought about only issuing the
> ping if the $dbh hasn't been fetched in some amount of time (say a few
> minutes) to effectively disable the pings when the site is busy (which
> should be most of the time).

I've thought about this also, but I don't have any data to indicate
what that period should be.

I use the ping setting with every request.  If the database isn't
available, I want to know immediately, and I'm willing to foot the
overhead of an additional very simple query per each application query
I run.

I would submit as an additional consideration, that the times when the
site is busy are periods where you would want active pings.  The
database connections are likely to fail when the site is busy than
when it is idle, I would postulate.

[David E. Wheeler]

On Oct 4, 2009, at 10:17 AM, Bill Moseley wrote:

> This looks nice.  Thanks.  I don't use Apache::DBI, but this will be  
> good
> for general connection and transaction support.  I simply use  
> connect_cached
> now with { privite_pid = $$ } but that doesn't allow me to set
> InactiveDestroy (although I'm not using $dbh in Apache parent or  
> forking
> children so has not been an issue).

Yeah, in that case, unless you want the transaction management or the  
saving of the overhead of ping(), as long as you're not doing anything  
in the parent process, I think what you've got is just fine. Very  
simple.

> I'm not quite sure how to handle the pings.  I also don't like the  
> idea of a
> ping every time a $dbh is needed.   But, I wonder about re-issuing the
> query.

Yes, you only want to do this if re-executing the code reference has  
no side-effects.

> If a do() throws an exception and then the connection is tested and  
> fails
> the ping are you sure that the query didn't complete?  My concern, of
> course, is running the query successfully twice.

I stole this code from DBIx::Class, which as you likely know, is  
pretty widely used. This was their approach, and I think it works well  
for them. But even better is to use txn_do() instead, as it should  
avoid the possibility of executing the query twice.

> At one time I considered inspecting the error message and trying to
> determine if it came from the database so that I knew the query  
> failed, and
> then reconnect and rerun the query, otherwise just die.
>
> Obviously, you can't reissue the query if in a transaction -- and I  
> see you
> check for this.

Yep.

> Checking the ping before returning $dbh isn't fool proof either.  The
> connection could always die between the ping and then when $dbh is  
> used.

Yet this is how the vast majority of database caching approaches work,  
including Apache::DBI and connect_cached(). I don't think that's worth  
worrying about, frankly.

> Is the history of the ping to catch connections that have been  
> inactive for
> a long period and perhaps timed out?  I've thought about only  
> issuing the
> ping if the $dbh hasn't been fetched in some amount of time (say a few
> minutes) to effectively disable the pings when the site is busy (which
> should be most of the time).

I think that would be more complicated; you'd have to do more record-
keeping. And then what do you do when you don't check it but a  
reconnect would allow you to continue?

> But, if you use txn_do() for all transactions then is there any need  
> for the
> cleanup handler rollback?

No.

> I also see in your disconnect method that you manually issue a  
> rollback,
> call disconnect and then undefine $dbh.  Doesn't DBI do that  
> automatically
> when $dbh is DESTROYed?

Probably, unless InactiveDestroy is set. That's more code I borrowed  
from DBIx::Class.

Best,

David

[Jonathan Vanasco-3]

Has anyone here played with Plack yet ? ( http://plackperl.org/ )

It's about a week old or so publicly, but I'm sure a few of you folks here were privvy to a preview...

// Jonathan Vanasco

e. jonathan@...
w. http://findmeon.com/user/jvanasco
blog. http://destructuring.net

|   -   -   -   -   -   -   -   -   -   -
|   Founder/CEO - FindMeOn, Inc.
|      FindMeOn.com - The cure for Multiple Web Personality Disorder
|   -   -   -   -   -   -   -   -   -   -
|   CTO - ArtWeLove, LLC
|      ArtWeLove.com - Explore Art On Your Own Terms
|   -   -   -   -   -   -   -   -   -   -
|      RoadSound.com - Tools for Bands, Stuff for Fans
|   -   -   -   -   -   -   -   -   -   -

[Issac Goldstand]

Whaddaya know...

Ironically, this might have saved Plone at my workplace had I known that
this was on the way.  We were looking at writing custom WSGI components
in Python and shuddering (well, I was shuddering)

Jonathan Vanasco wrote:

>
> Has anyone here played with Plack yet ? ( http://plackperl.org/ )
>
> It's about a week old or so publicly, but I'm sure a few of you folks
> here were privvy to a preview...
>
>
>
> // Jonathan Vanasco
>
> e. jonathan@... <mailto:jonathan@...>
> w. http://findmeon.com/user/jvanasco
> blog. http://destructuring.net <http://destructuring.net/>
>
> |   -   -   -   -   -   -   -   -   -   -
> |   Founder/CEO - FindMeOn, Inc.
> |      FindMeOn.com - The cure for Multiple Web Personality Disorder
> |   -   -   -   -   -   -   -   -   -   -
> |   CTO - ArtWeLove, LLC
> |      ArtWeLove.com - Explore Art On Your Own Terms
> |   -   -   -   -   -   -   -   -   -   -
> |      RoadSound.com - Tools for Bands, Stuff for Fans
> |   -   -   -   -   -   -   -   -   -   -
>

[Jonathan Vanasco-3]

On Oct 15, 2009, at 10:01 AM, Issac Goldstand wrote:

> Whaddaya know...
>
> Ironically, this might have saved Plone at my workplace had I known  
> that
> this was on the way.  We were looking at writing custom WSGI  
> components
> in Python and shuddering (well, I was shuddering)

I'm 80% Python now, so that would be exiciting to me ;)  I can't stand  
plone though, I do everything in Pylons... which is actually a lot  
like ModPerl

Porting WSGI to Perl is really awesome though. And MP is already  
supported out of the box !

[Adam Prime]

Jonathan Vanasco wrote:
>
> Has anyone here played with Plack yet ? ( http://plackperl.org/ )
>
> It's about a week old or so publicly, but I'm sure a few of you folks
> here were privvy to a preview...
>

I haven't played with it, but i have read a bunch of Miyagawa's blog
posts about it.  I do know that Jeff Horwitz is planning to support WSGI
in mod_parrot / mod_perl 6, but i don't know exactly what that means ;)

Adam

[Jonathan Vanasco-3]

On Oct 15, 2009, at 1:36 PM, Adam Prime wrote:

> I haven't played with it, but i have read a bunch of Miyagawa's blog  
> posts about it.  I do know that Jeff Horwitz is planning to support  
> WSGI in mod_parrot / mod_perl 6, but i don't know exactly what that  
> means ;)

Yeah I heard about that too.  Unfortunately, I seriously doubt I'll  
ever use Perl6.

The PSGI spec is really neat.  It's just a perl version of WSGI.  I'm  
hoping to play around with it on some spare time next month, and see  
if it can get around some of the weird stuff I've had to do with  
libapreq in the past.

[Foo JH]

I've been - in my spare time - trying to figure this PSGI thing out. I'm
a Windows guy you see, and I realised there's no PPM for Plack.

Is Strawberry the only alternative?

Jonathan Vanasco wrote:

>
> On Oct 15, 2009, at 1:36 PM, Adam Prime wrote:
>
>> I haven't played with it, but i have read a bunch of Miyagawa's blog
>> posts about it.  I do know that Jeff Horwitz is planning to support
>> WSGI in mod_parrot / mod_perl 6, but i don't know exactly what that
>> means ;)
>
> Yeah I heard about that too.  Unfortunately, I seriously doubt I'll
> ever use Perl6.
>
> The PSGI spec is really neat.  It's just a perl version of WSGI.  I'm
> hoping to play around with it on some spare time next month, and see
> if it can get around some of the weird stuff I've had to do with
> libapreq in the past.