Date: Tue, 15 Aug 2006 10:05:30 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Richard Huxton <dev@archonet.com>
Cc: Carl Youngblood <carl@youngbloods.org>,
	pgsql-performance@postgresql.org
Subject: Re: Beginner optimization questions, esp. regarding Tsearch2
Message-ID: <20060815150529.GO27928@pervasive.com>
References: <e5ed7b690608092100v43590a8epdbdec7afa847c722@mail.gmail.com>
	<44DAFB2B.4070307@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <44DAFB2B.4070307@archonet.com>
User-Agent: Mutt/1.5.11

On Thu, Aug 10, 2006 at 10:23:55AM +0100, Richard Huxton wrote:
> Carl Youngblood wrote:
> >- I noticed that there are six different postmaster daemons running.
> >Only one of them is taking up a lot of RAM (1076m virtual and 584m
> >resident).  The second one is using 181m resident while the others are
> >less than 20m each.  Is it normal to have multiple postmaster
> >processes?
> 
> You should have one master backend process and one per connection. PG is 
> a classic multi-process designed server.
> 
> > Even the biggest process doesn't seem to be using near as
> >much RAM as I have on this machine.  Is that bad?  What percentage of
> >my physical memory should I expect postgres to use for itself?  How
> >can I encourage it to cache more query results in memory?
> 
> OK - one of the key things with PostgreSQL is that it relies on the O.S. 
> to cache its disk files. So, allocating too much memory to PG can be 
> counterproductive.
> 
> From your figures, you're allocating about 64MB to work_mem, which is 
> per sort. So, a complex query could use several times that amount. If 
> you don't have many concurrent queries that might be what you want.
> 
> Also, you've allocated 1GB to your shared_buffers which is more than I'd 
> use as a starting point.
 
See the recent thread about how old rules of thumb for shared_buffers
are now completely bunk. With 4G of memory, setting shared_buffers to 2G
could easily be reasonable. The OP really needs to test several
different values with their actual workload and see what works best.

> You've only mentioned one main table with 100,000 rows, so presumably 
> you're going to cache the entire DB in RAM. So, you'll want to increase 
> effective_cache_size and reduce random_page_cost.
> 
> -- 
>   Richard Huxton
>   Archonet Ltd
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461