[postgis-users] Query that locks up system memory/disk usage

[Kevin Neufeld]

Ah yes, I've come across the same thing several times.  For example, if 
you use the pgstattuple module that comes with postgres and perform a 
dead tuple query on at a table, it will scan the entire table, even 
after issuing a ctrl^c or kill -2 ... but it will eventually terminate.

However, using top and killing queries using signal 2 has saved me many 
times on a development box with several developers.  I can quickly kill 
that runaway query from a dropped JDBC connection without bringing down 
the whole database.  Usually the query stops right way and the rollback 
is not very intensive, but it depends on what you are doing.

-- Kevin

Mike Leahy wrote:
> Kevin,
> 
> Perhaps I can cause the same situation (maybe tomorrow when the server 
> isn't occupied) and give this a try.  But I think I have already ruled 
> this out as an option.  When I would hit ctrl+c in the psql terminal, 
> I'd see a message that the query was being cancelled...then it would 
> just sit there and continue grinding away - unless there's a possibility 
> that maybe the ctrl+c command didn't actually reach the postgres 
> process, my guess is that maybe the attempt to rollback gracefully is 
> just as intensive.
> 
> Mike
> 
> 
> Kevin Neufeld wrote:
>> Paul, while "kill -9" will undoubtedly stop the current running query, 
>> it will also crash the entire database cluster since the shared memory 
>> will become corrupt.
>> I recommend using a "kill -2" instead which is the same thing as 
>> issuing a ctrl^c while in the terminal program.  It may take longer 
>> since it has to rollback the transaction, but it will do so gracefully.
>>
>> -- Kevin
>>
>> Paul Ramsey wrote:
>>> Break yourself of the subquery habit:
>>>
>>> select a.* from a join b on (st_dwithin(a.the_geom,b.the_geom,50000))
>>> where b.gid = 10;
>>>
>>>
>>> On your process:
>>>
>>> ps ax | grep postgres
>>>
>>> Find the process id that is using all the CPU and just kill -9 it. The
>>> glory of running a proper ACID database like PgSQL is that if you
>>> don't like what it's doing, you can rip the power cord out of the
>>> wall, and it'll still start up clean. (Do not try this with MySQL.)
>>>
>>> P.
>>>
>>> buffer((select b.the_geom
>>>  
>>>> where gid = 10),50000));
>>>>     
>>>
>>> On Mon, Jul 14, 2008 at 4:33 PM, Mike Leahy <mgleahy at 
>>> alumni.uwaterloo.ca> wrote:
>>>  
>>>> Hello list,
>>>>
>>>> I've run into some situations where running certain queries end up 
>>>> locking
>>>> up all of my system's ram memory, with constant disk access.  I 
>>>> can't cancel
>>>> the query by hitting ctrl+c in the psql terminal, by restarting the 
>>>> service,
>>>> or even killing the postmaster.  I'm running on a fairly high end 
>>>> system, so
>>>> it's not an issue with CPU power or available ram.  Here's an 
>>>> example of
>>>> what I did today that caused this:
>>>>
>>>> Table A is a table I imported from a tile index shapefile.
>>>>
>>>> Table B has several fairly large irregular polygons of different study
>>>> areas.
>>>>
>>>> To get all of the polygons in Table A within a certain distance 
>>>> (50km) of
>>>> one of the polygons in Table B, without giving it much thought I did 
>>>> the
>>>> following:
>>>>
>>>> select * from a where st_intersects(a.the_geom,buffer((select 
>>>> b.the_geom
>>>> where gid = 10),50000));
>>>>
>>>> I realize how wrong that is, as calculates the buffer for every tile it
>>>> compares to...I should have done something like:
>>>>
>>>> select * from a where st_intersects(a.the_geom,(select b.the_geom 
>>>> where gid
>>>> = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 
>>>> 10))<=50000;
>>>>
>>>> The problem is...I'm still waiting for the first query to either 
>>>> finish, or
>>>> cancel, or something.  In the meantime, postmaster is still using 
>>>> 99% of my
>>>> memory, and the disk is still thrashing away (though CPU usage 
>>>> pretty much
>>>> at 0).  What's the best strategy to kill the previous query without 
>>>> having
>>>> to shut down the entire server?
>>>>
>>>> Keep in mind that is just an example of how this can happen for me - 
>>>> I've
>>>> had it happen in other more complex situations where it was less 
>>>> obvious
>>>> what I was doing wrong in the logic of the query.  I'm just 
>>>> wondering how I
>>>> can recover from these sorts of mistakes without potentially 
>>>> damaging the
>>>> database.
>>>>
>>>> Regards,
>>>> Mike
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>     
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>   
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users