Re: [cgiapp] CAP::DBH + FastCGI: persistent DB connections, please

[Cees Hek <suppressed>]

On 10/31/05, Mark A. Fuller <suppressed> wrote:
> I never liked the approach of pinging the DBH *before every* access. Since the

By every access, do you mean every query, or every page request?  I
don't think it is that unreasonable to do a ping when you first ask
for a cached database handle.  If you are talking about pinging before
every SQL query, then that is definately overkill...

> occasional error is the exception, why not write a small common "retry" routine
> that would only be called if the access fails? If it fails, drop the presumably
> prepared statement handle, prepare again. If it fails, drop the DBH and all
> prepared statement handles, reconnect, prepare the statement handle.

That seems like lots more code for something that you already
acknowledge rarely happens.  Seems like more room for error to me...

> I guess it has to do with how obsessed a person might be with performance.
> But, once you write the error handler it works for any application.

Apache::DBI makes it quite easy to customize the ping settings so that
you are not wasting more time than necesary.  This is from the docs:

------------------------------
 Apache::DBI->setPingTimeOut($data_source, $timeout)

This configures the usage of the ping method, to validate a connection.
Setting the timeout to 0 will always validate the database connection
using the ping method (default). Setting the timeout < 0 will de-activate
the validation of the database handle. This can be used for drivers, which
do not implement the ping-method. Setting the timeout > 0 will ping the
database only if the last access was more than timeout seconds before.
------------------------------

So even if your code calls ->connect_cached multiple times, you could
still set the ping time to 1 or 2  seconds or so, and you will only do
one ping per request (depending on how long your requests last).  But
in most cases, you only call ->connect_cached once per request anyway,
so it shouldn't be an issue.

Since the original question surrounded FastCGI, this strategy could
also be employed in a dbh caching system for FastCGI.

Of course none of this stops the need for proper error handling. 
After all, the database connection could die right after the ping
(network failure, the DB could be restarted, or crashed by some other
connection).  It is unlikely, but possible.

Cheers,

Cees

---------------------------------------------------------------------
Web Archive:  http://www.mail-archive.com/suppressed/
              http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
To unsubscribe, e-mail: suppressed
For additional commands, e-mail: suppressed