pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Blocking connection and timeout problem
8+ messages / 5 participants
[nested] [flat]

* Blocking connection and timeout problem
@ 2005-08-03 11:23 KÖPFERL Robert <robert.koepferl@sonorys.at>
  2005-08-03 14:38 ` Re: Blocking connection and timeout problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: KÖPFERL Robert @ 2005-08-03 11:23 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

Hi,

I managed to reproduce a locking problem, which is about blocking
transactions and dead connections. How can this be avoided?

What I did was:
1. Start a transaction on Client A
2. Execute a fcn doing an UPDATE on a certain record on client A
3. Execute a fcn doing an UPDATE on the same record on client B
/ as expected SQL on client B stalls untl client A's transaction ends/
4. disconnect client A's network cable
5. kill the psql on client A
/ C.A's transaction still runs since two hours, C.B's SQL lasts for equal
that time/
6. wait on client B's statement to finish...
7. kill -TERM client A's postmaster
/ client B's statement succeeds


Why is that? Why isn't client A's transaction detected as dead ? Yes,
because the socked wasn't closed due to a network error. But this can happen
all the time. What can I do?



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
  2005-08-03 11:23 Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-08-03 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2005-08-03 14:38 UTC (permalink / raw)
  To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-admin

=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> Why is that? Why isn't client A's transaction detected as dead ?

It will be eventually, when the TCP connection times out.  The standard
timeout is generally an hour or two :-(

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
@ 2005-08-03 14:48 KÖPFERL Robert <robert.koepferl@sonorys.at>
  2005-08-03 15:08 ` Re: Blocking connection and timeout problem Guido Barosio <gbarosio@gmail.com>
  2005-08-03 17:33 ` Re: Blocking connection and timeout problem Dario Brignardello <dbrignar@uolsinectis.com>
  2005-08-03 18:50 ` Re: Blocking connection and timeout problem Dario Brignardello <dbrignar@sinectis.com>
  0 siblings, 3 replies; 8+ messages in thread

From: KÖPFERL Robert @ 2005-08-03 14:48 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin



|-----Original Message-----
|From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
|Sent: Mittwoch, 03. August 2005 16:38
|To: KÖPFERL Robert
|Cc: pgsql-admin@postgresql.org
|Subject: Re: [ADMIN] Blocking connection and timeout problem 
|
|
|=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
|> Why is that? Why isn't client A's transaction detected as dead ?
|
|It will be eventually, when the TCP connection times out.  The standard
|timeout is generally an hour or two :-(

Seems not like so. I waited for two hours and it still existed.

But non-the-less. Can this timeout be configured to be less?

|
|			regards, tom lane
|



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
  2005-08-03 14:48 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-08-03 15:08 ` Guido Barosio <gbarosio@gmail.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Guido Barosio @ 2005-08-03 15:08 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

Used to have this problem also with pgadmin connections, that didn't close
some sessions remain there, for a period, and you must kill them to continue 

without a restart of the service. 

scenery:
pgsql >= 7.4.3 , not tested on >=8
pgadmin <= 1.0.2


On other layer of this issue, this should be also related with some in deep 
tcp/ip settings.
ie, I know that in order to keep an <idle> ssh session open for a longer 
time, you shall touch some values somewhere *not* in the main ssh_config 
file. (a global parameter for the OS) And this will keep your session open 
for a longer time. 

If this is particular for every OS, that could explain a longer time on your 
case, and 1 or 2 hours in Tom's case.

Anyway, I would like to hear a little bit about this, cause it's a little 
bit difficult to find such cases, 
we always end on the problem lacking, without a previous warn. (and that is 
bad, for a DBA at least).

Regards,
Guido



On 8/3/05, KÖPFERL Robert <robert.koepferl@sonorys.at> wrote:
> 
> 
> 
> |-----Original Message-----
> |From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> |Sent: Mittwoch, 03. August 2005 16:38
> |To: KÖPFERL Robert
> |Cc: pgsql-admin@postgresql.org
> |Subject: Re: [ADMIN] Blocking connection and timeout problem
> |
> |
> |=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> |> Why is that? Why isn't client A's transaction detected as dead ?
> |
> |It will be eventually, when the TCP connection times out. The standard
> |timeout is generally an hour or two :-(
> 
> Seems not like so. I waited for two hours and it still existed.
> 
> But non-the-less. Can this timeout be configured to be less?
> 
> |
> | regards, tom lane
> |
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 



-- 
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
  2005-08-03 14:48 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-08-03 17:33 ` Dario Brignardello <dbrignar@uolsinectis.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Dario Brignardello @ 2005-08-03 17:33 UTC (permalink / raw)
  To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-admin

Assuming you are on a linux box (Not sure if it's stated in your mail,
sorry if I miss that) you could set

/proc/sys/net/ipv4/tcp_keepalive_time

to a lower value. That should do the trick :-)


Hope it helps.

Greetings
Dario.



On Wed, 2005-08-03 at 16:48 +0200, KÖPFERL Robert wrote:
> 
> 
> 
> |-----Original Message-----
> |From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> |Sent: Mittwoch, 03. August 2005 16:38
> |To: KÖPFERL Robert
> |Cc: pgsql-admin@postgresql.org
> |Subject: Re: [ADMIN] Blocking connection and timeout problem 
> |
> |
> |=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> |> Why is that? Why isn't client A's transaction detected as dead ?
> |
> |It will be eventually, when the TCP connection times out.  The standard
> |timeout is generally an hour or two :-(
> 
> Seems not like so. I waited for two hours and it still existed.
> 
> But non-the-less. Can this timeout be configured to be less?
> 
> |
> |			regards, tom lane
> |
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 
> 
-- 
Atte:
Dario Brignardello
Planificacion y Desarrollo
Tecnologia
UOL Argentina S.A

Florida 537 Piso 6, Buenos Aires, Argentina
+54-11-4321-9110 ext 2533
PGP public key: http://webs.uolsinectis.com.ar/dbrignar/pgp.html

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
  2005-08-03 14:48 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-08-03 18:50 ` Dario Brignardello <dbrignar@sinectis.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Dario Brignardello @ 2005-08-03 18:50 UTC (permalink / raw)
  To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-admin

Assuming you are on a linux box (Not sure if it's stated in your mail,
sorry if I missed that) you could set

/proc/sys/net/ipv4/tcp_keepalive_time

to a lower value. That should do the trick. 

Hope this helps
Greetings

Dario


On Wed, 2005-08-03 at 16:48 +0200, KÖPFERL Robert wrote:
> 
> 
> 
> |-----Original Message-----
> |From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> |Sent: Mittwoch, 03. August 2005 16:38
> |To: KÖPFERL Robert
> |Cc: pgsql-admin@postgresql.org
> |Subject: Re: [ADMIN] Blocking connection and timeout problem 
> |
> |
> |=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> |> Why is that? Why isn't client A's transaction detected as dead ?
> |
> |It will be eventually, when the TCP connection times out.  The standard
> |timeout is generally an hour or two :-(
> 
> Seems not like so. I waited for two hours and it still existed.
> 
> But non-the-less. Can this timeout be configured to be less?
> 
> |
> |			regards, tom lane
> |
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
@ 2005-08-05 07:43 KÖPFERL Robert <robert.koepferl@sonorys.at>
  2005-08-05 13:28 ` Re: Blocking connection and timeout problem Dario Brignardello <dbrignar@sinectis.com>
  0 siblings, 1 reply; 8+ messages in thread

From: KÖPFERL Robert @ 2005-08-05 07:43 UTC (permalink / raw)
  To: 'dbrignar@uolsinectis.com' <dbrignar@uolsinectis.com>; +Cc: pgsql-admin

Erm, no, it is a Solaris box. 
And as I could experience, there are rather long timeouts in solaris' tcp
implementation.

Thanks anyway.

What about Postgres. Wouldn't it be better to have postgres sending keep
alive or heart beat packages during that time?

|-----Original Message-----
|From: Dario Brignardello [mailto:dbrignar@uolsinectis.com]
|Sent: Mittwoch, 03. August 2005 19:33
|To: KÖPFERL Robert
|Cc: pgsql-admin@postgresql.org
|Subject: Re: [ADMIN] Blocking connection and timeout problem
|
|
|Assuming you are on a linux box (Not sure if it's stated in your mail,
|sorry if I miss that) you could set
|
|/proc/sys/net/ipv4/tcp_keepalive_time
|
|to a lower value. That should do the trick :-)
|
|
|Hope it helps.
|
|Greetings
|Dario.
|
|
|
|On Wed, 2005-08-03 at 16:48 +0200, KÖPFERL Robert wrote:
|> 
|> 
|> 
|> |-----Original Message-----
|> |From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
|> |Sent: Mittwoch, 03. August 2005 16:38
|> |To: KÖPFERL Robert
|> |Cc: pgsql-admin@postgresql.org
|> |Subject: Re: [ADMIN] Blocking connection and timeout problem 
|> |
|> |
|> |=?iso-8859-1?Q?K=D6PFERL_Robert?= 
|<robert.koepferl@sonorys.at> writes:
|> |> Why is that? Why isn't client A's transaction detected as dead ?
|> |
|> |It will be eventually, when the TCP connection times out.  
|The standard
|> |timeout is generally an hour or two :-(
|> 
|> Seems not like so. I waited for two hours and it still existed.
|> 
|> But non-the-less. Can this timeout be configured to be less?
|> 
|> |
|> |			regards, tom lane
|> |
|> 
|> ---------------------------(end of 
|broadcast)---------------------------
|> TIP 6: explain analyze is your friend
|> 
|> 
|-- 
|Atte:
|Dario Brignardello
|Planificacion y Desarrollo
|Tecnologia
|UOL Argentina S.A
|
|Florida 537 Piso 6, Buenos Aires, Argentina
|+54-11-4321-9110 ext 2533
|PGP public key: http://webs.uolsinectis.com.ar/dbrignar/pgp.html
|



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Blocking connection and timeout problem
  2005-08-05 07:43 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-08-05 13:28 ` Dario Brignardello <dbrignar@sinectis.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Dario Brignardello @ 2005-08-05 13:28 UTC (permalink / raw)
  To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-admin

Taking the risk of running offtopic: That value is defined in Solaris as
well. Use:

	ndd -get /dev/tcp tcp_keepalive_interval

to retrieve it, and

	/usr/sbin/ndd -set /dev/tcp tcp_keepalive_interval 3600000

to set it to one hour. Check
http://sunsolve.sun.com/search/document.do?assetkey=1-30-2876-1
for more information. 

Regarding the possibility of heartbeats in postgres ... have no idea how
wise would it be ... there's people more qualified here that could
answer that ;-D

Hope this helps, anyway.
Regards.
Dario





On Fri, 2005-08-05 at 09:43 +0200, KÖPFERL Robert wrote:
> 
> Erm, no, it is a Solaris box. 
> And as I could experience, there are rather long timeouts in solaris' tcp
> implementation.
> 
> Thanks anyway.
> 
> What about Postgres. Wouldn't it be better to have postgres sending keep
> alive or heart beat packages during that time?
> 
> |-----Original Message-----
> |From: Dario Brignardello [mailto:dbrignar@uolsinectis.com]
> |Sent: Mittwoch, 03. August 2005 19:33
> |To: KÖPFERL Robert
> |Cc: pgsql-admin@postgresql.org
> |Subject: Re: [ADMIN] Blocking connection and timeout problem
> |
> |
> |Assuming you are on a linux box (Not sure if it's stated in your mail,
> |sorry if I miss that) you could set
> |
> |/proc/sys/net/ipv4/tcp_keepalive_time
> |
> |to a lower value. That should do the trick :-)
> |
> |
> |Hope it helps.
> |
> |Greetings
> |Dario.
> |
> |
> |
> |On Wed, 2005-08-03 at 16:48 +0200, KÖPFERL Robert wrote:
> |> 
> |> 
> |> 
> |> |-----Original Message-----
> |> |From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> |> |Sent: Mittwoch, 03. August 2005 16:38
> |> |To: KÖPFERL Robert
> |> |Cc: pgsql-admin@postgresql.org
> |> |Subject: Re: [ADMIN] Blocking connection and timeout problem 
> |> |
> |> |
> |> |=?iso-8859-1?Q?K=D6PFERL_Robert?= 
> |<robert.koepferl@sonorys.at> writes:
> |> |> Why is that? Why isn't client A's transaction detected as dead ?
> |> |
> |> |It will be eventually, when the TCP connection times out.  
> |The standard
> |> |timeout is generally an hour or two :-(
> |> 
> |> Seems not like so. I waited for two hours and it still existed.
> |> 
> |> But non-the-less. Can this timeout be configured to be less?
> |> 
> |> |
> |> |			regards, tom lane
> |> |
> |> 
> |> ---------------------------(end of 
> |broadcast)---------------------------
> |> TIP 6: explain analyze is your friend
> |> 
> |> 
> |-- 
> |Atte:
> |Dario Brignardello
> |Planificacion y Desarrollo
> |Tecnologia
> |UOL Argentina S.A
> |
> |Florida 537 Piso 6, Buenos Aires, Argentina
> |+54-11-4321-9110 ext 2533
> |PGP public key: http://webs.uolsinectis.com.ar/dbrignar/pgp.html
> |
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-08-05 13:28 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-03 11:23 Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-08-03 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-03 14:48 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-08-03 15:08 ` Guido Barosio <gbarosio@gmail.com>
2005-08-03 17:33 ` Dario Brignardello <dbrignar@uolsinectis.com>
2005-08-03 18:50 ` Dario Brignardello <dbrignar@sinectis.com>
2005-08-05 07:43 Re: Blocking connection and timeout problem KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-08-05 13:28 ` Dario Brignardello <dbrignar@sinectis.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox