Re: [JDBC] proposal: setKeepAlive

"Oliver Jowett" <oliver@opencloud.com> writes:

> Gregory Stark wrote:
>
>> TCP is supposed to work even if the network disappears and then reappears
>> later, gracefully handling intermittent network failures. The case where you
>> want to close connections which doesn't respond quickly is when you have hosts
>> (usually clients, which makes it strange to want this in a database client
>> driver) which are frequently permanently unplugged or renumbered such as
>> dialup users. That's why it's important for ftp servers or web servers.
>
> People seem to like to put stateful firewalls with low connection activity
> timeouts between their DB server and client. We get regular traffic on the list
> from people who see connections get reset on their first use after a long idle
> period. Presumably TCP keepalive would help in some of these cases.

With such broken routers keepalives won't actually help unless you set much more aggressive times than the defaults which are required to be at least 2 hours. And of course the more aggressive you make them the more likely you are to get spurious errors from an intermittent failure.

> Also, usually DB connections are local and you don't really want to plaster
> over the fact that the other end has gone MIA.

The local case is precisely where you wouldn't want to use keep-alives. When the server does come back up you'll get RSET packets and the connections will die. It's only in the remote case where you might never speak to the host again or he may have been renumbered or permanently disconnected and there might be no host with the old ip address at all that you might want to use keep-alives.

> Keepalive helps a little but it's certainly not perfect. I ended up doing
> all the dead server detection at a higher level in the end.

I think you're doing exactly what you have to do. You certainly don't want to predicate failover on simple network reachability. You want to base it on the application actually functioning. Network reachability is both too sensitive (keep-alive packets could be dropped due to congestion) and not sensitive enough (if the application crashes the host may still be reachable).

-- 
  Gregory Stark
  EnterpriseDB          
http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly