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

[Kevin Neufeld]

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
>