pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedinfinite 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