Scott Marlowe wrote:
On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan.ciric@xxxxxxxxxxx> wrote:
Hi!
We need some help.
Sometimes we have broken connections with backend ( postgresql server ).
When this occurs, we have "idle in transaction" indication on server side. Can you
answer us, how long will server stay in this state and what happens with this
broken connection ( client started transaction, but can't send commit or rollback )?
If the client socket on the other end has simply disappeared, then the
connection will be harvested approximately net.ipv4.tcp_keepalive_time
+ net.ipv4.tcp_keepalive_probes * net.ipv4.tcp_keepalive_intvl seconds
later. On default setups, this is something like 7200 + 90 * 9 for a
total of 8010 seconds. i.e. just over an hour.
Not to be picky but 60 sec * 60 min = 3600 sec = 1 hour so
the above timeout would be just over 2 hours.
On later model postgresql's you can change these settings for just the
pgsql server to something more sane, like
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30
which get it down to 6.5 minutes or so before stale connections are harvested.
The advantage to using tcp_keepalive is it won't kill living but idle
connections.
