Thread: proposal: setKeepAlive
Hi,
The attached makes tcp-keep-alive enable. When PostgreSQL server gets down or
network gets down, client application can't receive any response from the server.
PostgreSQL server has GUC parameters: tcp_keepalives_* and statement_timeout,
but JDBC doesn't have such features. setQueryTimeout() is not implemented so
far, so setKeepAlive() is needed for above situation at the very least.
Best regards,
--
Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp>
NTT Open Source Software Center
diff -cpr postgresql-jdbc-8.3-603.src-orig/org/postgresql/core/PGStream.java
postgresql-jdbc-8.3-603.src/org/postgresql/core/PGStream.java
*** postgresql-jdbc-8.3-603.src-orig/org/postgresql/core/PGStream.java 2008-02-08 16:42:59.000000000 +0900
--- postgresql-jdbc-8.3-603.src/org/postgresql/core/PGStream.java 2008-02-08 21:29:38.000000000 +0900
*************** public class PGStream
*** 107,112 ****
--- 107,115 ----
// really need to.
connection.setTcpNoDelay(true);
+ // Enable tcp keep alive timer
+ connection.setKeepAlive(true);
+
// Buffer sizes submitted by Sverre H Huseby <sverrehu@online.no>
pg_input = new VisibleBufferedInputStream(connection.getInputStream(), 8192);
pg_output = new BufferedOutputStream(connection.getOutputStream(), 8192);
"Toru SHIMOGAKI" <shimogaki.toru@oss.ntt.co.jp> writes: > Hi, > > The attached makes tcp-keep-alive enable. When PostgreSQL server gets down or > network gets down, client application can't receive any response from the server. This has to be an option, not on unconditionally. In fact they shouldn't be on automatically. Applications should have to explicitly request TCP keep-alives and set an application-specific appropriate interval. Otherwise you're breaking TCP in general for a behaviour which is only useful in particular circumstances. 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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On Fri, 8 Feb 2008, Gregory Stark wrote: > "Toru SHIMOGAKI" <shimogaki.toru@oss.ntt.co.jp> writes: > >> The attached makes tcp-keep-alive enable. When PostgreSQL server gets down or >> network gets down, client application can't receive any response from the server. > > This has to be an option, not on unconditionally. In fact they shouldn't > be on automatically. Applications should have to explicitly request TCP > keep-alives and set an application-specific appropriate interval. > Otherwise you're breaking TCP in general for a behaviour which is only > useful in particular circumstances. Java does not support a configurable keepalive interval, it's simply an on/off flag. Kris Jurka
"Kris Jurka" <books@ejurka.com> writes: > Java does not support a configurable keepalive interval, it's simply an on/off > flag. Uhm, well that kind of sucks, though to be honest I suspect most people who override the OS default set it to inappropriately short intervals anyways. But nonetheless the point stands that it must be an option and ought to be off by default. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
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. Also, usually DB connections are local and you don't really want to plaster over the fact that the other end has gone MIA. Between default TCP retransmission times and default TCP keepalive settings and the lack of a uniform setQueryTimeout() within the driver, it's actually really hard to work out promptly that the DB server is dead which is a royal pain if you are trying to do failover between servers. 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. > "Kris Jurka" <books@ejurka.com> writes: > >> > Java does not support a configurable keepalive interval, it's simply an on/off >> > flag. > > Uhm, well that kind of sucks, though to be honest I suspect most people who > override the OS default set it to inappropriately short intervals anyways. The lack of support in Java is why when I did the server-side work to support configurable TCP keepalive I didn't do the Java side too - the default OS timeouts are way too large for the case I cared about. Also, the Java side doesn't sit around waiting on read, so it won't actually notice the connection is dead until the next time it tries to use it anyway, at which point you might as well just poll the server with a dummy query every X seconds. -O
Oliver Jowett <oliver@opencloud.com> writes:
> 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.
There's certainly a use-case there, but we already provide TCP keepalive
functionality on the server side, which should be good enough to hold
open a NAT connection. It's not clear to me why we need keepalive
probes coming from the client side too.
regards, tom lane
"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!
Gregory Stark wrote: > 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. This does assume that the server is not down because it's caught fire.. >> 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). For the environment I'm running in, network congestion sufficient to drop multiple keepalive packets is equivalent to server failure anyway. Agreed that you want application-level tests anyway, it's just that if there are network problems I would prefer to hear about them sooner rather than later - it seems a little silly to have the information available at the TCP level, but ignore it. You have to tune app-level timeouts to take account of the normal response time of the app, which might be quite different to the normal response time of the network. -O
"Oliver Jowett" <oliver@opencloud.com> writes: > For the environment I'm running in, network congestion sufficient to drop > multiple keepalive packets is equivalent to server failure anyway. Well, it could just mean someone is doing an ftp transfer and you got unlucky with which packets got dropped. > Agreed that you want application-level tests anyway, it's just that if there > are network problems I would prefer to hear about them sooner rather than later > - it seems a little silly to have the information available at the TCP level, > but ignore it. Well you don't just "hear about" it. You get your connection reset. Effectively you guarantee that there are network problems even if there weren't really problems before. The worst offender on this front is SSH. Whenever my DSL modem reboots or my wireless modem gets interference my SSH connections often hang and I have to reconnect. I think this is actually due to the application-level keep-alives that SSH is doing, not TCP keep-alives, but it's the same principle. SSH turned an situation where no error was necessary into an error that required manual recovery. The various instant messenger systems are also frequent offenders but at least they have a reasonable excuse. They have to deal with millions of users on dynamic addresses which appear and disappear without warning. If they stuck around waiting for you to come back ten minutes later they would be waiting on thousands of such dead connections. That's what TCP keep-alives are for, but they're a trade-off between reliability and wasted resources. The instant messenger systems don't put much of a premium on reliability and they can just reconnect anyways. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
Gregory Stark wrote: > "Oliver Jowett" <oliver@opencloud.com> writes: > >> For the environment I'm running in, network congestion sufficient to drop >> multiple keepalive packets is equivalent to server failure anyway. > > Well, it could just mean someone is doing an ftp transfer and you got unlucky > with which packets got dropped. Not on the dedicated cluster interconnect LAN they're not. >> Agreed that you want application-level tests anyway, it's just that if there >> are network problems I would prefer to hear about them sooner rather than later >> - it seems a little silly to have the information available at the TCP level, >> but ignore it. > > Well you don't just "hear about" it. You get your connection reset. > Effectively you guarantee that there are network problems even if there > weren't really problems before. > > The worst offender on this front is SSH. Whenever my DSL modem reboots or my > wireless modem gets interference my SSH connections often hang and I have to > reconnect. I think this is actually due to the application-level keep-alives > that SSH is doing, not TCP keep-alives, but it's the same principle. SSH > turned an situation where no error was necessary into an error that required > manual recovery. You are talking about a WAN environment where you have a user at the helm and a high tolerance for unexpected and potentially large latency spikes. I agree that aggressive keepalives are not particularly useful in that case. However, this is not the only possible environment. In some environments you WANT any significant deviation from normal network operation to be noticed at the application level, so you can automatically fail over to your redundant server rather than try to soldier on with failing equipment.. -O
The attached is the second version of setKeepAlive patch, which enables that user can turn the configuration flag on/off. The default is off. 2008/2/10, Oliver Jowett <oliver@opencloud.com>: > I agree that aggressive keepalives are not particularly useful in that case. I agree the above too, and I don't think TCP keepalive is a perfect solution. As Gregory already said, to provide a detailed application-specific monitoring, some kind of application level test (maybe using setQueryTimeout()) is still needed. But nonetheless I think some of the cases are solved by this simple keepalive. For instance, resource collection without server rebooting on an application server side, which is occupied by lost connections: 1. There are master-slave database servers connecting to a shared disk, and application servers connect the master server first. 2. If the master database server gets down and fails over to the slave server by a high-availability clustering suite, then connections from the application servers to the master server are remained and not to be collected, because JDBC driver doesn't send any exceptions in this case, so application can't take measures to solve it. 3. If hundreds / thousands of connections are remained, a large quantity of resources used by the lost connections die in vain and should be removed without application server rebooting sooner or later. I do hope all the dead server detection is implemented. I'd like to avoid JDBC driver doesn't say anything in this case now. It seems to me implementing setQueryTimeout() is so easy as doing setKeepAlive(). I think this patch just provides the least-level assuarance. Best regards,
Attachment
On Mon, 11 Feb 2008, Toru SHIMOGAKI wrote: > The attached is the second version of setKeepAlive patch, which enables > that user can turn the configuration flag on/off. The default is off. Applied with some minor revisions. I renamed the URL parameter to tcpKeepAlive for naming consistency and I've added support for it as a DataSource option as well. Socket.setKeepAlive is only available in JDK1.3+ while we've previously retained support back to JDK1.2. I think everything prior to 1.4 is sufficiently dead that we don't need to worry about that anymore, so 8.4+ will no longer support JDK 1.2 or 1.3. Kris Jurka