On Tue, Apr 28, 2009 at 7:30 PM, Greg Sabino Mullane wrote:
-----BEGIN PGP SIGNED MESSAGE-----
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