Thread: Killing long-running queries
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
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
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
Tom Lane wrote > You should be using SIGINT, not SIGTERM. > > regards, tom lane > Thank you very much for clarifying this point! It works :)
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/
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