FAQ
Ima::DBI and DBIx::Class::Storage::DBI both call ping() to check if
the database is alive before retuning a cached $dbh.

I'm curious if this is a smart approach for a very database-heavy
site, or if this might be a hold-over from the "morning after" problem
where site that were quiet all night would fail in the morning as
their connections had timed out.

Is there an advantage of calling checking $dbh->FETCH( 'Active' ) &&
$dbh->ping vs. just flagging that the dbh is dead on error and then
reconnecting next time a $dbh is needed?

Am I missing something obvious?





--
Bill Moseley
moseley@hank.org
Sent from my iMutt

Search Discussions

  • Ben Tilly at Apr 29, 2009 at 1:36 am

    On Tue, Apr 28, 2009 at 6:19 PM, Bill Moseley wrote:
    Ima::DBI and DBIx::Class::Storage::DBI both call ping() to check if
    the database is alive before retuning a cached $dbh.

    I'm curious if this is a smart approach for a very database-heavy
    site, or if this might be a hold-over from the "morning after" problem
    where site that were quiet all night would fail in the morning as
    their connections had timed out.
    Calling ping regularly is a strategy for making sure that you
    reconnect to your failover when your main database goes down.

    That said, I have personally seen a busy Oracle site fall over due to
    the overhead of pings, and been a hero for working out a strategy that
    got rid of 90% of the pinging activity.
    Is there an advantage of calling checking $dbh->FETCH( 'Active' ) &&
    $dbh->ping vs. just flagging that the dbh is dead on error and then
    reconnecting next time a $dbh is needed?
    If you can automatically catch any error in dealing with the database,
    and know to ping the next time you issue a dbh, that would be better.
    But when I faced that problem, that approach was technically harder
    because it would mean messing around with DBI internals that are
    written in C. It was much, much easier for me to just override the
    ping method in Perl.
    Am I missing something obvious?
    Not really.

    Cheers,
    Ben
  • Greg Sabino Mullane at Apr 29, 2009 at 2:31 am

    Ima::DBI and DBIx::Class::Storage::DBI both call ping() to check if
    the database is alive before retuning a cached $dbh.

    I'm curious if this is a smart approach for a very database-heavy
    site, or if this might be a hold-over from the "morning after" problem
    where site that were quiet all night would fail in the morning as
    their connections had timed out.
    It's a bad strategy no matter what the reasoning. Applications should
    simply do things as normal and handle exceptions in a sensible matter.
    That said, $dbh->ping() is about as low intensity as one can get and
    still get a reasonable snapshot on the health of the database. For
    DBD::Pg, we (in C/XS) check the current libpq status. If it's bad,
    we return it as bad right away. If libpq think it's good, we issue
    a simple SELECT query anyway, as we can't be sure libpq has the correct
    status. As long as you aren't calling ping() too often (e.g. before
    every query), it's generally fine to use.

    I realize that this is out of the scope of abstractions like DBIx::Class,
    but it's better to use $dbh->pg_ping(), as it returns a little more
    information than the boolean yes/no of $dbh->ping().

    - --
    Greg Sabino Mullane greg@turnstep.com
    End Point Corporation
    PGP Key: 0x14964AC8 200904282229
    http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
  • Ben Tilly at Apr 29, 2009 at 5:35 am

    On Tue, Apr 28, 2009 at 7:30 PM, Greg Sabino Mullane wrote:
    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: RIPEMD160

    Ima::DBI and DBIx::Class::Storage::DBI both call ping() to check if
    the database is alive before retuning a cached $dbh.

    I'm curious if this is a smart approach for a very database-heavy
    site, or if this might be a hold-over from the "morning after" problem
    where site that were quiet all night would fail in the morning as
    their connections had timed out.
    It's a bad strategy no matter what the reasoning. Applications should
    simply do things as normal and handle exceptions in a sensible matter.
    If by "sensible manner" you mean check whether the database state is
    the cause of the exception, I'd love to do it but the last time I had
    to look at it I would have had to alter a lot of error handling in a
    lot of places.

    If you mean "trap the error and display something reasonable" then
    your site will not successfully fail over in a timely manner to your
    backup database when your primary goes down. Unless, of course, you
    ping reasonably often. Where "reasonably often" is tied to how
    quickly you want your website to fail over if the database goes down.
    That said, $dbh->ping() is about as low intensity as one can get and
    still get a reasonable snapshot on the health of the database. For
    DBD::Pg, we (in C/XS) check the current libpq status. If it's bad,
    we return it as bad right away. If libpq think it's good, we issue
    a simple SELECT query anyway, as we can't be sure libpq has the correct
    status. As long as you aren't calling ping() too often (e.g. before
    every query), it's generally fine to use.
    I've seen preparing one select per dynamic web page on a busy site be
    too much for an Oracle database. It was an issue with lock contention
    on the stored query plan. I have no idea whether PostgreSQL has
    similar potential issues.
    I realize that this is out of the scope of abstractions like DBIx::Class,
    but it's better to use $dbh->pg_ping(), as it returns a little more
    information than the boolean yes/no of $dbh->ping().
    That depends what you're using it for. The only thing I've seen ping
    seriously used for is, "Is my cached connection still good, or do I
    need to reconnect?" In that case the binary yes/no of ping is exactly
    what you need. If you need more information, then ping is not a good
    fit.

    Cheers,
    Ben
  • Bill Moseley at Apr 29, 2009 at 2:33 pm

    On Wed, Apr 29, 2009 at 02:30:59AM -0000, Greg Sabino Mullane wrote:

    I'm curious if this is a smart approach for a very database-heavy
    site, or if this might be a hold-over from the "morning after" problem
    where site that were quiet all night would fail in the morning as
    their connections had timed out.
    It's a bad strategy no matter what the reasoning. Applications should
    simply do things as normal and handle exceptions in a sensible matter.
    I agree. And just because the ping to the database works (the simple
    select) doesn't mean the next request(s) won't fail.

    And by handling exceptions in a sensible matter means an eval wrapper
    high enough up in the application to catch every database exception
    and then determining which exception would indicate that a new $dbh
    should be created, right?

    Again, I wonder if the initial reason for the ping wasn't to deal with
    low traffic sites where the database connection might actually time
    out from inactivity. Otherwise, why bother pinging first when the
    actual request would fail?

    I suppose another reason might be if a ping fails you can attempt to
    reconnect and then continue on with the request. But, if an actual
    query or update throws an exception you can't really retry it. What
    I'm getting at is if you have AutoCommit on and you do an update that
    throws an exception can you be sure that the update didn't happen?

    (That's going to seem like a dumb question once I get some coffee.)

    BTW -- DBI's connect_cached() method does a ping. I suspect
    connect_cached() is used in plenty of busy applications.

    --
    Bill Moseley.
    moseley@hank.org
    Sent from my iMutt

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbd-pg @
categoriesperl
postedApr 29, '09 at 1:19a
activeApr 29, '09 at 2:33p
posts5
users3
websiteperl.org

People

Translate

site design / logo © 2022 Grokbase