[freetds] [PATCH] Configurable TCP KeepAlives

James K. Lowden jklowden at freetds.org
Mon Aug 11 18:52:49 EDT 2008

Hi Andrew, 

I remain opposed, but not opposed to discussing it.  

Andrew Victor wrote:
> But in the real-world TDS probably runs mostly on TCP, and FreeTDS
> only runs on TCP.

The point isn't that TDS runs on TCP.  The point is that TCP has its own
tools and API.  FreeTDS need not provide an interface for controlling
something it merely uses.  And TCP parameters are widely misunderstood and
easily misinterpreted.  

> It's not necessarily always a network issue - the remote host could be
> rebooted or have a power-failure, similar for a stateful router /
> firewall.
> The keepalive mechanism has to do with detecting these unexpected
> situations sooner.

Er, sooner than what?  

Servers are mostly reliable, and network problems are mostly transitory. 
By insisting on tight timings, the connection is made more fragile.  The
developers of TCP/IP incorporated that knowledge in the protocol, and it
takes a lot of specialized knowledge -- some location-specific -- to
improve on the defaults.  

It's not true that keepalives are needed to know when a server was
rebooted or otherwise went away (on a properly configured network). A
reboot or power failure can be detected in numerous ways depending on how
it happens.  

1.  If a query is being sent, the ACK packet will not be received,
eventually resulting in a write(2) failure.  That timeout is based on RTT
and will typically be under a minute.  

2.  If a query was accepted but not answered, or only partly answered, the
"query timeout" suffices, or read(2) will fail. 

3.  If a TDS link is exactly idle, the server is awaiting data from the
client.  The client will discover the server is down when next he tries to
write to it.  And he'll find that out quickly if the server was rebooted,
because it will reject the packet outright.  

I appreciate that everyone would like to distinguish between slow and
broken when it comes to handling query timeouts.  OTOH there is *no*
reliable way to know!  It's perfectly possible for the server to stop
responding to queries (or even just one query) while still responding to
ping(1) or keepalive packets.  The important question for the application
is not whether the server was rebooted; it's whether the query was
answered.  And the only way to know that is to wait until it's answered,
or give up.  

> http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx
>   "As an example, under default configuration, SQL Server can detect a
> 'orphaned' connection in about 35 seconds."

Yes, but that's no advantage to FreeTDS.  If Microsoft were frugal with
connection resources, slow-dying "orphaned" resources wouldn't be a

> Maybe we can then rather add a [tcp] section to the freetds.conf
> The patch only modifies the KeepAlive settings if you add the options
> to the configuration file.  Don't need them, don't add them.

Understood.  My case is A) it's not needed and B) it can do more harm than
good.  (B follows from A.)  Plus, it needs documentation and support
which, if you accept my assertions, will mostly consist of saying, "don't
do that".  

So, here's my threshold.  Someone has to describe to me a situation on a
correctly configured network -- no borked firewall -- wherein a keepalive
timer will give the application better information than can be had in its
absence.  The description should account for any applicable TCP and TDS
timeouts, and explicitly describe the state of the TCP connection.  Also
explain why the keepalive applies only to TDS connections (not the whole
network), and to *all* TDS connections, not to just some applications or
some servers[1].  Actual quantities should indicate how many seconds would
elapse with and without the keepalive.  

Make your best case.  I think the value of a keepalive parameter in
FreeTDS is negligible or negative.  If I'm going to add it, I'll have to
understand why it's useful well enough to explain it in the UG.  If it
rises above "negligible", I'll at least have a start on the documentation.



[1] unless you believe it should be a per-server configuration property.  

More information about the FreeTDS mailing list