On Tue, Apr 28, 2009 at 7:30 PM, Greg Sabino Mullane <[email protected]> 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
