pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
infinite blocking statements in 8.2.3
9+ messages / 3 participants
[nested] [flat]

* infinite blocking statements in 8.2.3
@ 2007-05-02 08:33  Thomas Markus <t.markus@proventis.net>
  0 siblings, 5 replies; 9+ messages in thread

From: Thomas Markus @ 2007-05-02 08:33 UTC (permalink / raw)
  To: pgsql-admin

I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)

sometimes i have SELECTs that never ends. Normally I drop connections by 
killing the connection process (kill <PID>). But these hanging 
connections (which blocks other statements infinitly) cant be killed. 
the only way is a pg_ctl -m immediate stop or a brutal kill -9
what can i do to
- limit statement runtime (set hard timeout)
- remove these blocking connections without killing other connections

Thomas

Attachments:

t.markus.vcf (text/x-vcard, 249B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr;dom:;;Zimmerstr. 79-80;Berlin;Berlin;10117 email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: infinite blocking statements in 8.2.3
@ 2007-05-02 12:59  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Thomas Markus <t.markus@proventis.net>
  4 siblings, 0 replies; 9+ messages in thread

From: Alvaro Herrera @ 2007-05-02 12:59 UTC (permalink / raw)
  To: Thomas Markus <t.markus@proventis.net>; +Cc: pgsql-admin

Thomas Markus wrote:
> I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)
> 
> sometimes i have SELECTs that never ends. Normally I drop connections by 
> killing the connection process (kill <PID>). But these hanging 
> connections (which blocks other statements infinitly) cant be killed. 

What are they doing?  We've added interrupt checks in most of the loops,
so queries can normally be killed quickly, but maybe we've missed just
the one you're running.


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



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

* Re: infinite blocking statements in 8.2.3
@ 2007-05-02 15:39  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Thomas Markus <t.markus@proventis.net>
  4 siblings, 0 replies; 9+ messages in thread

From: Scott Marlowe @ 2007-05-02 15:39 UTC (permalink / raw)
  To: Thomas Markus <t.markus@proventis.net>; +Cc: pgsql-admin

On Wed, 2007-05-02 at 03:33, Thomas Markus wrote:
> I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)
> 
> sometimes i have SELECTs that never ends. Normally I drop connections by 
> killing the connection process (kill <PID>).

You shouldn't do that.  You should issue a cancel query to the backend
running the query.  If you do kill <pgbackendPID> then you would kill
that one backend, but that's not necessarily transaction safe.  What
you're doing is killing the connecting program, and then the connection
eventually will timeout when tcp_keepalive runs out and the OS detects
the hung connection.  You could reduce tcp_keepalive if killing the
connecting process is the only way you have to do this.

>  But these hanging 
> connections (which blocks other statements infinitly) cant be killed. 
> the only way is a pg_ctl -m immediate stop or a brutal kill -9

That's pretty close to a hammer to the brain response.  

> what can i do to

You can use 

select * from pg_stat_activity

to see what the queries are, and 

select pg_cancel_backend(procpid)

from the stat_activity table to kill individual backends.

> - limit statement runtime (set hard timeout)

Useful for lots reasons.  note that you can change this per user and per
database as well as per cluster in the postgresql.conf file.

alter user bubba set statement_timeout=300;
alter database loveshack set statement_timeout=600;

> - remove these blocking connections without killing other connections

pg_cancel_backend()



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

* Re: infinite blocking statements in 8.2.3
@ 2007-05-03 05:33  Thomas Markus <t.markus@proventis.net>
  parent: Thomas Markus <t.markus@proventis.net>
  4 siblings, 1 reply; 9+ messages in thread

From: Thomas Markus @ 2007-05-03 05:33 UTC (permalink / raw)
  To: pgsql-admin

thanks for your awnsers

i cant repeat the problem after increasing autovacuum_naptime to 5min. a 
vacuumdb -f -a runs over night (and finished :) ) and now that blocking 
statement finished after some seconds. it seems autovacuum = on is not 
enough.



Thomas Markus schrieb:
> I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)
>
> sometimes i have SELECTs that never ends. Normally I drop connections 
> by killing the connection process (kill <PID>). But these hanging 
> connections (which blocks other statements infinitly) cant be killed. 
> the only way is a pg_ctl -m immediate stop or a brutal kill -9
> what can i do to
> - limit statement runtime (set hard timeout)
> - remove these blocking connections without killing other connections
>
> Thomas
>

Attachments:

t.markus.vcf (text/x-vcard, 253B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: infinite blocking statements in 8.2.3
@ 2007-05-03 13:11  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Thomas Markus <t.markus@proventis.net>
  0 siblings, 1 reply; 9+ messages in thread

From: Alvaro Herrera @ 2007-05-03 13:11 UTC (permalink / raw)
  To: Thomas Markus <t.markus@proventis.net>; +Cc: pgsql-admin

Thomas Markus wrote:
> thanks for your awnsers
> 
> i cant repeat the problem after increasing autovacuum_naptime to 5min. a 
> vacuumdb -f -a runs over night (and finished :) ) and now that blocking 
> statement finished after some seconds. it seems autovacuum = on is not 
> enough.

Enough for what?  You still haven't said what the problem was.  I am
unsure if I should be concerned.  It now sounds like ordinary slowness
caused by table bloat.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



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

* Re: infinite blocking statements in 8.2.3
@ 2007-05-03 13:32  Thomas Markus <t.markus@proventis.net>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Thomas Markus @ 2007-05-03 13:32 UTC (permalink / raw)
  To: pgsql-admin

the problem was not the long running statement. that statement has 
blocked other connections and i was unable to drop that connection. at 
the end all connections where blocked (output in ps: UPDATE waiting ..). 
the only solution was to restart the server. After my understanding I 
can terminate a connection (produces rollback) always however this 
special connection could not not be terminated. Normally I could do 
this. I will observe whether the behavior again arise.

Alvaro Herrera schrieb:
> Thomas Markus wrote:
>   
>> thanks for your awnsers
>>
>> i cant repeat the problem after increasing autovacuum_naptime to 5min. a 
>> vacuumdb -f -a runs over night (and finished :) ) and now that blocking 
>> statement finished after some seconds. it seems autovacuum = on is not 
>> enough.
>>     
>
> Enough for what?  You still haven't said what the problem was.  I am
> unsure if I should be concerned.  It now sounds like ordinary slowness
> caused by table bloat.
>   

Attachments:

t.markus.vcf (text/x-vcard, 253B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: infinite blocking statements in 8.2.3
@ 2007-05-09 11:50  Thomas Markus <t.markus@proventis.net>
  parent: Thomas Markus <t.markus@proventis.net>
  4 siblings, 1 reply; 9+ messages in thread

From: Thomas Markus @ 2007-05-09 11:50 UTC (permalink / raw)
  To: pgsql-admin

my problem is back :(

ps shows a SELECT
i tried to cancel that blocking statement with |pg_cancel_backend|(pid). 
after that ps show 'idle in transaction'. a simple kill <pid> doesn't 
help. i did a pg_ctl -m immediate stop.
Postgres is away however the dead process is further present. I can stop 
these also only with kill -9.

Does someone have an idea where the problem is and as I this to avoid can?

thx
Thomas



Thomas Markus schrieb:
> I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)
>
> sometimes i have SELECTs that never ends. Normally I drop connections 
> by killing the connection process (kill <PID>). But these hanging 
> connections (which blocks other statements infinitly) cant be killed. 
> the only way is a pg_ctl -m immediate stop or a brutal kill -9

Attachments:

t.markus.vcf (text/x-vcard, 253B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: infinite blocking statements in 8.2.3
@ 2007-05-10 05:08  Thomas Markus <t.markus@proventis.net>
  parent: Thomas Markus <t.markus@proventis.net>
  4 siblings, 0 replies; 9+ messages in thread

From: Thomas Markus @ 2007-05-10 05:08 UTC (permalink / raw)
  To: pgsql-admin

my problem is back :(

ps shows a SELECT
i tried to cancel that blocking statement with |pg_cancel_backend|(pid).
after that ps show 'idle in transaction'. a simple kill <pid> doesn't
help. i did a pg_ctl -m immediate stop.
Postgres is away however the dead process is further present. I can stop
these also only with kill -9.

Does someone have an idea where the problem is and as I this to avoid can?

thx
Thomas



Thomas Markus schrieb:
> I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686)
>
> sometimes i have SELECTs that never ends. Normally I drop connections 
> by killing the connection process (kill <PID>). But these hanging 
> connections (which blocks other statements infinitly) cant be killed. 
> the only way is a pg_ctl -m immediate stop or a brutal kill -9

Attachments:

t.markus.vcf (text/x-vcard, 253B) download begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:t.markus@proventis.net tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: infinite blocking statements in 8.2.3
@ 2007-05-10 15:11  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Thomas Markus <t.markus@proventis.net>
  0 siblings, 0 replies; 9+ messages in thread

From: Alvaro Herrera @ 2007-05-10 15:11 UTC (permalink / raw)
  To: Thomas Markus <t.markus@proventis.net>; +Cc: pgsql-admin

Thomas Markus wrote:
> my problem is back :(
> 
> ps shows a SELECT
> i tried to cancel that blocking statement with |pg_cancel_backend|(pid). 
> after that ps show 'idle in transaction'. a simple kill <pid> doesn't 
> help. i did a pg_ctl -m immediate stop.
> Postgres is away however the dead process is further present. I can stop 
> these also only with kill -9.
> 
> Does someone have an idea where the problem is and as I this to avoid can?

Maybe now you can find the time to answer the questions.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



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


end of thread, other threads:[~2007-05-10 15:11 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-02 08:33 infinite blocking statements in 8.2.3 Thomas Markus <t.markus@proventis.net>
2007-05-02 12:59 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-02 15:39 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-03 05:33 ` Thomas Markus <t.markus@proventis.net>
2007-05-03 13:11   ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-03 13:32     ` Thomas Markus <t.markus@proventis.net>
2007-05-09 11:50 ` Thomas Markus <t.markus@proventis.net>
2007-05-10 15:11   ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-10 05:08 ` Thomas Markus <t.markus@proventis.net>

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