Message-ID: <44DAFB2B.4070307@archonet.com>
Date: Thu, 10 Aug 2006 10:23:55 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060719)
MIME-Version: 1.0
To: Carl Youngblood <carl@youngbloods.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Beginner optimization questions, esp. regarding Tsearch2
References: <e5ed7b690608092100v43590a8epdbdec7afa847c722@mail.gmail.com>
In-Reply-To: <e5ed7b690608092100v43590a8epdbdec7afa847c722@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.

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