Thread: 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
Attachment
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 >
Attachment
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. >
Attachment
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
Attachment
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
Attachment
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