Thread: Backend timeout
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 )? Thanks in advance, -- Dragan Ciric
On Sep 16, 2008, at 6:21 AM, Dragan Ciric 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 )? Are you absolutely positive that the client is gone? Postgres should detect when client connections disappear and roll back any open transactions. If you're *sure* the client connection is gone and the transactions are stuck in idle in transaction then they may just sit there like that until you kill them off manually. I'd make sure that there really isn't a client process holding on to that connection by: 1. Get client_addr and client_port from pg_stat_activity 2. Go to client_addr and run (without the brackets): lsof -i tcp:<client_port> If there's still a client for that connection you should turn up a process there. If that's the case then you should be tracking down why your client connection are holding on to open transactions. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability (415) 963-4410 x 260 Location: US/Pacific IRC: mage2k
On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan.ciric@a-asoft.com> 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. 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.
Scott Marlowe wrote: > On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan.ciric@a-asoft.com> 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. >