From: Tony Wasson <ajwasson@gmail.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Killing long-running queries
Date: Tue, 2 May 2006 16:43:52 -0700
Message-ID: <6d8daee30605021643k4415462oec2ea8487fc521b7@mail.gmail.com> (raw)
In-Reply-To: <4457E918.10007@drivefaster.net>
References: <4457E918.10007@drivefaster.net>
On 5/2/06, Dan Harris <fbsd@drivefaster.net> wrote:
> My database is used primarily in an OLAP-type environment. Sometimes my
> users get a little carried away and find some way to slip past the
> sanity filters in the applications and end up bogging down the server
> with queries that run for hours and hours. And, of course, what users
> tend to do is to keep queuing up more queries when they don't see the
> first one return instantly :)
>
> So, I have been searching for a way to kill an individual query. I read
> in the mailing list archives that you could 'kill' the pid. I've tried
> this a few times and more than once, it has caused the postmaster to
> die(!), terminating every query that was in process, even unrelated to
> that query.
>
> Is there some way I can just kill a query and not risk breaking
> everything else when I do it?
>
> Thanks
>
Hi Dan,
You can kill a specific pid under 8.1 using SELECT
pg_cancel_backend(pid). You can kill a query from the command line by
doing $ kill -TERM pid or $kill -SIGINT pid.
There are several tips from this thread that may be useful about
killing long running SQL:
http://archives.postgresql.org/pgsql-general/2006-02/msg00298.php
In short, the recommendations are:
1) Use statement_timeouts if at all possible. You can do this
database wide in postgresql.conf. You can also set this on a per user
or per SQL statement basis.
2) Make step #1 does not kill autovacuum, or necessary automated
jobs. You can do this with "ALTER USER SET statement_timeout = 0".
I'm using a web page to show SELECT * FROM pg_stat_activity output
from several servers. This makes it easy to see the pids of any
long-running SQL.
http://archives.postgresql.org/pgsql-general/2006-02/msg00427.php
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ajwasson@gmail.com, fbsd@drivefaster.net
Subject: Re: Killing long-running queries
In-Reply-To: <6d8daee30605021643k4415462oec2ea8487fc521b7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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