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

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.

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.


---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to