From: Bill Moran <wmoran@collaborativefusion.com>
To: Joshua Fielek <jfielek@centriccrm.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Making the most of memory?
Date: Wed, 23 Jan 2008 09:59:08 -0500
Message-ID: <20080123095908.f993b56c.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <4796BE84.9030803@centriccrm.com>
References: <4796BE84.9030803@centriccrm.com>
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.
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: wmoran@collaborativefusion.com, jfielek@centriccrm.com
Subject: Re: Making the most of memory?
In-Reply-To: <20080123095908.f993b56c.wmoran@collaborativefusion.com>
* 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