Date: Wed, 23 Jan 2008 09:59:08 -0500
From: Bill Moran <wmoran@collaborativefusion.com>
To: Joshua Fielek <jfielek@centriccrm.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Making the most of memory?
Message-Id: <20080123095908.f993b56c.wmoran@collaborativefusion.com>
In-Reply-To: <4796BE84.9030803@centriccrm.com>
References: <4796BE84.9030803@centriccrm.com>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to Joshua Fielek <jfielek@centriccrm.com>:
> 
> Hey folks --
> 
> For starters, I am fairly new to database tuning and I'm still learning 
> the ropes. I understand the concepts but I'm still learning the real 
> world impact of some of the configuration options for postgres.
> 
> We have an application that has been having some issues with performance 
> within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a 
> nice little performance increase just off the improved query 
> optimization, but we are still having other performance issues.
> 
> The database itself is not that large -- a db_dump of the sql file as 
> text is only about 110MB. I haven't checked the exact size of the actual 
> data base, but the entire data directory is smaller than the available 
> memory at about 385MB including logs and config files. This is a single 
> database with a relatively small number of client connections (50 or so) 
> making a fair number of smaller queries. This is not a massive data 
> effort by any means at this time, but it will be growing.
> 
> We have available currently ~4GB (8GB total) for Postgres. We will be 
> moving to a server that will have about 24GB (32GB total) available for 
> the database, with the current server becoming a hot backup, probably 
> with slony or something similar to keep the databases in sync.
> 
> I've been monitoring the memory usage of postgres on the current system 
> and it seems like none of the threads ever allocate more than about 
> 400MB total and about 80-90MB shared memory. It seems to me that since 
> we have a very large chunk of memory relative to the database size we 
> should be loading the entire database into memory. How can we be sure 
> we're getting the most out of the memory we're allocating to postgres? 
> What can we do to improve the memory usage, looking for performance 
> first and foremost, on both the larger and smaller systems?

Every system is a little different.  I recommend you do some profiling.

First off, Install the pg_buffercache add-on.  This gives you an easy
view to see how much of your shared_buffers are being used, with a
query like:
select count(*) from pg_buffercache where reldatabase is not null;

There is also a lot of interesting information in the pg_stat_database
table, i.e.:
select sum(blks_hit) from pg_stat_database;
Which gives you the # of reads that were satisfied from shared_buffers,
or
select sum(blks_read) from pg_stat_database;
which gives you the # of reads that had to go to disk.

There are lots of other stats you can graph, but those are some that I
find particularly telling as to how things are being used.