pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marinos J. Yannikos <mjy@geizhals.at>
To: Jeff Trout <jeff@jefftrout.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimization ideas for frequent, large(ish) updates
Date: Mon, 16 Feb 2004 03:53:15 +0100
Message-ID: <4030309B.7090807@geizhals.at> (raw)
In-Reply-To: <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com>
References: <402C213A.4030105@geizhals.at>
	<200402122228.41680.josh@agliodbs.com>
	<402ED348.7000800@geizhals.at>
	<460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com>

Jeff Trout wrote:

> Remember that it is going to allocate 800MB per sort.  It is not "you 
> can allocate up to 800MB, so if you need 1 meg, use one meg".  Some 
> queries may end up having a few sort steps.

I didn't know that it always allocates the full amount of memory 
specificed in the configuration (e.g. the annotated configuration guide 
says: "Note that for a complex query, several sorts might be running in 
parallel, and each one _will be allowed to use_ as much memory as this 
value specifies before it starts to put data into temporary files."). 
The individual postgres processes don't look like they're using the full 
amount either (but that could be because the memory isn't written to).

> In terms of sort mem it is best to set a system default to a nice good 
> value for most queries.  and then in your reporting queries or other 
> ones set sort_mem for that session (set sort_mem = 800000) then only 
> that session will use the looney sort_mem

Queries from the web front-end use up to ~130MB sort memory (according 
to pgsql_tmp), so I set this to 150MB - thanks.

> It would be interesting to know if your machine is swapping.

It's not being monitored closely (other than with the occasional "top"), 
  but it's highly unlikely:

Mem:  12441864k total, 10860648k used,  1581216k free,    84552k buffers
Swap:  4008176k total,     2828k used,  4005348k free,  9762628k cached

(that's a typical situation - the "2828k used" are probably some rarely 
used processes that have lower priority than the cache ...)

Regards,
  Marinos




view thread (10+ messages)  latest in thread

Message-ID: <4030309B.7090807@geizhals.at>
Permalink:  ../4030309B.7090807@geizhals.at/
Also on:    postgresql.org/message-id/4030309B.7090807@geizhals.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mjy@geizhals.at, jeff@jefftrout.com
  Subject: Re: optimization ideas for frequent, large(ish) updates
  In-Reply-To: <4030309B.7090807@geizhals.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox