Re: postmaster(s) have high load average

Oops! cjw@ucar.edu (Chris Webster) was seen spray-painting on a wall:
> Martijn van Oosterhout wrote:
>
>>Have you run VACUUM and/or VACUUM FULL and/or ANALYZE recently?
>>
> a) yes.  I have it run analyze every 30 minutes or 1600 record
> additions.  Records are never updated or deleted so I assume I don't
> need vacuum.

You only really need to run analyze when the statistical
characteristics of the data changes; as the database grows, that is
fairly likely to stabilize somewhat so that you can ANALYZE less
frequently over time...

Have you verified that nothing has gotten touched?  Run a VACUUM
VERBOSE and see what it does...  Note that if you ever get cases where
records are added but rolled back due to some later part of a
transaction failing, that too will lead to dead tuples...

> b) It does it even at start up when there are fewer than 100 records
> in the database.
>
> c)  Would this even matter for clients that only connect but NEVER
> make any requests from the database?

Run VACUUM VERBOSE on it; you'll no doubt see that some internal
tables such as pg_activity, pg_statistic, and such have a lot of dead
tuples.  Establishing a connection leads to _some_ DB activity, and
probably a dead tuple or two; every time you ANALYZE, you create a
bunch of dead tuples since old statistics are "killed off."
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/sap.html
"Rules of Optimization:
     Rule 1: Don't do it.
     Rule 2 (for experts only): Don't do it yet."
-- M.A. Jackson