Re: Killing "stuck" queries and preventing queries from getting "stuck"

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: Tim Uckun <timuckun(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Killing "stuck" queries and preventing queries from getting "stuck"
Date: 2010-09-28 17:57:34
Message-ID: 778964D5-3005-4FEC-82BB-15AC70019D9B@solfertje.student.utwente.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 28 Sep 2010, at 1:41, Tim Uckun wrote:

> Sometimes some queries get "stuck" in that they run for hours and
> hours. They never stop running. Killing the deamon does not stop the
> query from running.

You really should try to find out why they get "stuck". Killing stuck clients isn't going to solve your problem (aside from the fact that you probably shouldn't be using kill -9 on them, that's like using a jackhammer on a jammed door).

Some things to look into: Are those queries waiting on a lock by another daemon maybe? Are some of them keeping transactions open for a long time without committing them (or rolling them back)?

I recall you were having another problem (with deleting records). This all smells like you either are waiting for locks on records or that the statistics used for query planning aren't reflecting the actual situation.

Have a look in pg_locks and check the query plans of some of your more problematic queries (see: explain analyse) to see what's going on there. Posting the results of those here would allow more eyes to look into your issues, in case it isn't obvious to you. There's some good documentation on these subjects too.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4ca22c9f678304378921584!

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric McKeeth 2010-09-28 18:18:12 Re: Exclusion constraint issue
Previous Message Dave Page 2010-09-28 17:50:22 Re: PostgreSQL 9 Mac OS X one-click install - PL/perl broken