From: Robert Treat <xzilla@users.sourceforge.net>
To: William Yu <wyu@talisys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Scaling further up
Date: 12 Mar 2004 18:01:57 -0500
Message-ID: <1079132517.27322.22.camel@camel> (raw)
In-Reply-To: <c2i7o0$2urj$1@news.hub.org>
References: <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098045@vt-pe2550-001.vantage.vantage.com>
<c2i7o0$2urj$1@news.hub.org>
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
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: xzilla@users.sourceforge.net, wyu@talisys.com
Subject: Re: Scaling further up
In-Reply-To: <1079132517.27322.22.camel@camel>
* 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