Re: Making the most of memory? - Mailing list pgsql-performance

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.

From there, I recommend that you graph those #s and any others that you
find interesting.  We use MRTG, but there are certainly other options.
Add that to stats collecting that you should be doing on machine data,
such as overall IO and CPU usage, and you start to get a pretty clear
view of what your machine is doing.

Note that you have to flip some stats collecting switches on in your
postgresql.conf file, and overall this can put some additional load on
your machine.  My opinion is that you're _FAR_ better off sizing your
hardware up a bit so that you can gather this data on a continual basis
than if you don't know what's going on.

Another thing to do is turn on statement timing.  This will create huge
log files and increase your IO traffic considerably, but the data involved
is priceless.  Run it through pgFouine periodically (possibly on a schedule
via a cron job) to isolate problematic queries and address them
individually.

Note that it can be tempting to configure Postgres to "only log queries
that take longer than 500ms" in an attempt to "only catch the slow and
problematic queries without creating unmanageable amounts of IO"  The
danger in this is that you may have some relatively fast queries that
are used so often that they constitute a serious performance problem.
Optimizing a query from 25ms to 22ms doesn't seem like it's worth the
effort, but if it's run 1x10^25 times a day it is.  If the IO load of
logging all queries presents too much of a slowdown, I recommend selecting
data collection periods and do it for perhaps an hour, then turn it
back off.  Maybe once a week or so.

Hope this helps.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023