pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedKilling long-running queries
6+ messages / 5 participants
[nested] [flat]
* Killing long-running queries
@ 2006-05-02 23:19 Dan Harris <fbsd@drivefaster.net>
2006-05-02 23:30 ` Re: Killing long-running queries Tom Lane <tgl@sss.pgh.pa.us>
2006-05-02 23:43 ` Re: Killing long-running queries Tony Wasson <ajwasson@gmail.com>
2006-05-03 00:01 ` Re: Killing long-running queries Devrim GUNDUZ <devrim@commandprompt.com>
0 siblings, 3 replies; 6+ messages in thread
From: Dan Harris @ 2006-05-02 23:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Killing long-running queries
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
@ 2006-05-02 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-02 23:53 ` Re: Killing long-running queries Dan Harris <fbsd@drivefaster.net>
2 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-05-02 23:30 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan Harris <fbsd@drivefaster.net> writes:
> 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.
You should be using SIGINT, not SIGTERM.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Killing long-running queries
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
2006-05-02 23:30 ` Re: Killing long-running queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-02 23:53 ` Dan Harris <fbsd@drivefaster.net>
0 siblings, 0 replies; 6+ messages in thread
From: Dan Harris @ 2006-05-02 23:53 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote
> You should be using SIGINT, not SIGTERM.
>
> regards, tom lane
>
Thank you very much for clarifying this point! It works :)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Killing long-running queries
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
@ 2006-05-02 23:43 ` Tony Wasson <ajwasson@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tony Wasson @ 2006-05-02 23:43 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Killing long-running queries
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
@ 2006-05-03 00:01 ` Devrim GUNDUZ <devrim@commandprompt.com>
2006-05-03 02:09 ` Re: Killing long-running queries Will Reese <wreese@rackspace.com>
2 siblings, 1 reply; 6+ messages in thread
From: Devrim GUNDUZ @ 2006-05-03 00:01 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Hi,
On Tue, 2006-05-02 at 17:19 -0600, Dan Harris wrote:
> Is there some way I can just kill a query and not risk breaking
> everything else when I do it?
Use pg_stat_activity view to find the pid of the process (pidproc
column) and send the signal to that process. I think you are now killing
postmaster, which is wrong.
Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Killing long-running queries
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
2006-05-03 00:01 ` Re: Killing long-running queries Devrim GUNDUZ <devrim@commandprompt.com>
@ 2006-05-03 02:09 ` Will Reese <wreese@rackspace.com>
0 siblings, 0 replies; 6+ messages in thread
From: Will Reese @ 2006-05-03 02:09 UTC (permalink / raw)
To: pgsql-performance
There is also the statement_timeout setting in postgresql.conf, but
you have to be careful with this setting. I'm not sure about
postgres 8.0 or 8.1, but in 7.4.5 this setting will terminate the
COPY statements used by pg_dumpall for backups. So I actually use
the pg_stat_activity table to kill long running queries or idle in
transactions that are hanging around (very bad for vacuum). For
example, you can do something like this to kill off idle in
transactions that are truly idle for more than 1 hour...
psql -U postgres -A -t -c "select procpid from pg_stat_activity where
current_query ilike '%idle in transaction%' and query_start < now() -
interval '1 hour'" template1 | xargs kill
Just throw that in your crontab to run every few minutes, redirect
standard error to /dev/null, and quit worrying about vacuum not
reclaiming space because some developer's code fails to commit or
rollback a transaction. Just be careful you aren't killing off
processes that are actually doing work. :)
-- Will Reese http://blog.rezra.com
On May 2, 2006, at 7:01 PM, Devrim GUNDUZ wrote:
> Hi,
>
> On Tue, 2006-05-02 at 17:19 -0600, Dan Harris wrote:
>> Is there some way I can just kill a query and not risk breaking
>> everything else when I do it?
>
> Use pg_stat_activity view to find the pid of the process (pidproc
> column) and send the signal to that process. I think you are now
> killing
> postmaster, which is wrong.
>
> Regards,
> --
> The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-05-03 02:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-02 23:19 Killing long-running queries Dan Harris <fbsd@drivefaster.net>
2006-05-02 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-02 23:53 ` Dan Harris <fbsd@drivefaster.net>
2006-05-02 23:43 ` Tony Wasson <ajwasson@gmail.com>
2006-05-03 00:01 ` Devrim GUNDUZ <devrim@commandprompt.com>
2006-05-03 02:09 ` Will Reese <wreese@rackspace.com>
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