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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
