Re: optimization ideas for frequent, large(ish) updates

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