Mathew,
For frequently updated tables I set vac_scale_factor to 0.01.
I also changed cost_delay from 150 to 10
Now it seems to work much better. Autovacuum is executed much quicker and still doesn't influence regular operations very much (and that was my concern when it was set to 0).
I guess that cost_delay = 150 was much too high and was probably the main reason of my problems. One of the frequently updated tables was much bigger then others : about 400.000 records and vacuuming that table was really taking a lot of time, so from time to time there was bigger delay between vacuuming of other tables.
If problems will continue probably I will turn off autovacum for this bigger table and will vacuum it from cron. The rest of tables I will leave for autovacuum (updated much more frequently but much smaller in size: around 40.000 records)
Does it seem reasonable ?
Tomasz Rakowski
----- Original Message ----
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.