Re: limiting query time and/or RAM

On Thu, Sep 17, 2009 at 1:56 PM, Alan McKay <alan(dot)mckay(at)gmail(dot)com> wrote:
> On Thu, Sep 17, 2009 at 3:35 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> True, but with a work_mem of 2M, I can't imagine having enough sorting
>> going on to need 4G of ram.  (2000 sorts? That's a lot)  I'm betting
>> the OP was looking at top and misunderstanding what the numbers mean,
>> which is pretty common really.
>
> Our databases are pretty big, and our queries pretty complex.
>
> Here is a snippet from last night's fun, leaving in a few "normal"
> rows, and the 3 errant ones which were an order of magnitude bigger
>
> The ps man page does not seem to say what the "DRS" field is.  One of

It means Data Resident Size. If it's like RES in top, it means the
Total memory being access. You'd need to subtract however much of
your shared_buffers it's touching to know how much it's really using.
Which is why top is so handy, it shows both RES and SHR next to each
other.

> our DB guys read it as such.  May well be misreading, but the fact is
> we had a few queries running that were an order of magnitude bigger
> than others, and once we isloated this this morning we were able to
> reproduce the problem in our test environment, and hang it.   Just
> prior to this happening, Munin shows committed memory spikes from
> about 1.5G to 18G which equals RAM + SWAP
>
> ps -U postgres -v
>
>  PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
>  1064 ?        Ss     0:01      0  3562 636289 7232  0.0 postgres:
> foobar pgdb001 192.168.3.151(46867) idle
>
> 14235 ?        Ss    29:41      0  3562 6316881 4852556 29.5 postgres:
> foobar pgdb001 192.168.2.66(60421) SELECT

I'm gonna make a SWAG that you've got 4 to 4.5G shared buffers, and if
you subract that from DRS you'll find it's using a few hundred to
several hundred megs. still a lot, but not in the 4G range you're
expecting. What does top say about this?