Rob Nagler wrote:
> It seems a simple "vacuum" (not full or analyze) slows down the
> database dramatically. I am running vacuum every 15 minutes, but it
> takes about 5 minutes to run even after a fresh import. Even with
> vacuuming every 15 minutes, I'm not sure vacuuming is working
> properly.
>
> There are a lot of updates. The slowest relation is the primary key
> index, which is composed of a sequence. I've appended a csv with the
> parsed output from vacuum. The page counts are growing way too fast
> imo. I believe this is caused by the updates, and index pages not
> getting re-used. The index values aren't changing, but other values
> in the table are.
You should try 7.4 beta and pg_autovacuum which is a contrib module in CVS tip.
It works with 7.3 as well.
Major reason for 7.4 is, it fixes index growth in vacuum. So if your database is
fit, it will stay that way with proper vacuuming.
>
> Any suggestions how to make vacuuming more effective and reducing the
> time it takes to vacuum? I'd settle for less frequent vacuuming or
> perhaps index rebuilding. The database can be re-imported in about an
> hour.
Make sure that you have FSM properly tuned. Bump it from defaults to suit your
needs. I hope you have gone thr. this page for general purpose setting.
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>
> Rob
> ----------------------------------------------------------------
> Spacing every 15 minutes
> Pages,Tuples,Deleted
> 7974,1029258,1536
> 7979,1025951,4336
> 7979,1026129,52
> 7979,1025618,686
Assuming those were incremental figures, largest you have is ~8000 tuples per 15
minutes and 26 pages. I think with proper FSM/shared buffers/effective cache and
a pg_autovacuum with 1 min. polling interval, you could end up in lot better shape.
Let us know if it works.
Shridhar