Re: Unexpected PostgreSQL performance degradation

"Cody Phanekham" <Cody.Phanekham@salmat.com.au> writes:
> Over a period of 3 or so months the performance of PostgreSQL on the
> PROD server seems to have degraded.

You say you were doing daily vacuuming, but had you checked that this
was adequate?  The behavior you describe sounds a lot like table or
index bloat.  Tables bloat if you don't vacuum often enough to keep the
amount of free space within what the free space map (FSM) can hold.
(You can cure this by increasing the FSM size, or by vacuuming more
often, or a combination.)  Indexes bloat if the range of indexed keys
moves significantly --- in PG 7.3 there is not a lot you can do about
this except to REINDEX periodically.  (The index bloat problem is
thought to be largely solved in 7.4, however.)

> Funny thing was, i was hounding the server admin saying there is
> something wrong with server (because there was only 5MB worth of free
> memory out of 1GB)

On any Unix machine, near-zero free memory is the expected and desirable
condition.  That's because the kernel automatically uses any memory
that's not currently needed for processes to hold cached disk pages.
What you need to worry about is not free memory per se, but the fraction
of real RAM that's being used for disk buffers --- when that gets too
small, then you worry.

> Out of curiosity, I checked the memory usage after the DB restoration
> and the free memory was back up to 500MB!

You didn't improve matters, you just temporarily invalidated all the
kernel's cached copies of database file pages.  This will mean extra
physical I/O until the kernel repopulates its cache, after which you'll
be back to near-zero "free" memory.  That's not a bad thing.

            regards, tom lane