Re: Scaling further up

We upgraded from 8GB to 12GB RAM a month or so ago, but even in the
past, I've never seen the system exhaust on it's system cache (~6GB, in
'top'), while it's swapping.

Some one had mentioned why not have the entire DB in memory? How do I
configure that, for knowledge?

Max connections is set to 500, and we haven't bumped it yet. (I've seen
over 200 active queries, but the traffic is seasonal, so the high
connection value)

Thanks,
Anjan

-----Original Message-----
From: Robert Treat [mailto:xzilla@users.sourceforge.net]
Sent: Friday, March 12, 2004 6:02 PM
To: William Yu
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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#RUNTI
ME-CONFIG-RESOURCE

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings