Обсуждение: infinite blocking statements in 8.2.3

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

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

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()

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
>

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.

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.
>

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

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

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