Re: limiting query time and/or RAM

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
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
14491 ? Ss 0:01 0 3562 636545 7284 0.0 postgres:
foobar pgdb001 192.168.2.66(55705) SELECT

14889 ? Rs 29:36 12 3562 6316937 4876228 29.6 postgres:
foobar pgdb001 192.168.2.62(48275) SELECT

14940 ? Ss 0:00 0 3562 636845 7912 0.0 postgres:
foobar pgdb001 192.168.2.62(43561) SELECT
14959 ? Rs 29:34 16 3562 6315141 4885224 29.7 postgres:
foobar pgdb001 192.168.2.62(48314) SELECT
14985 ? Ss 0:01 0 3562 636545 7288 0.0 postgres:
foobar pgdb001 192.168.2.66(55946) SELECT

--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In Defense of Food"