Re: Scaling further up

On Mon, 2004-03-08 at 11:40, William Yu wrote:
> Anjan Dave wrote:
> > Great response, Thanks.
> >
> > Regarding 12GB memory and 13G db, and almost no I/O, one thing I don't
> > understand is that even though the OS caches most of the memory and PG
> > can use it if it needs it, why would the system swap (not much, only
> > during peak times)? The SHMMAX is set to 512MB, shared_buffers is 150MB,
> > effective cache size is 2GB, sort mem is 2MB, rest is default values. It
> > also happens that a large query (reporting type) can hold up the other
> > queries, and the load averages shoot up during peak times.
>
> In regards to your system going to swap, the only item I see is sort_mem
> at 2MB. How many simultaneous transactions do you get? If you get
> hundreds or thousands like your first message stated, every select sort
> would take up 2MB of memory regardless of whether it needed it or not.
> That could cause your swap activity during peak traffic.
>
> The only other item to bump up is the effective cache size -- I'd set it
> to 12GB.
>

Was surprised that no one corrected this bit of erroneous info (or at
least I didn't see it) so thought I would for completeness. a basic
explanation is that sort_mem controls how much memory a given query is
allowed to use before spilling to disk, but it will not grab that much
memory if it doesn't need it.

See the docs for a more detailed explanation:
http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-RESOURCE

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL