On Thu, 30 Oct 2003, Joshua D. Drake wrote:
> If I understood correctly, Josh was complaining about VACUUM sucking too
>
> >much of his disk bandwidth. autovacuum wouldn't help that --- in fact
> >would likely make it worse, since a cron-driven vacuum script can at
> >least be scheduled for low-load times of day. autovacuum is likely to
> >kick in at the least convenient times.
> >
> >
> >
> Exactly!
Wait a minute, I thought the problem was that vacuums were happening too
far apart, therefore taking too long, and may have been full, no?
If the autovacuum daemon causes a lazy vacuum to run on only the busiest
(i.e. most updated) tables, then it is likely to only take a few minutes
to run, instead of hours, plus you can try to keep things steady state.
I.e. no more than x% or so dead tuples in a table at any given time, and
they all get reused by fsm / lazy vacuum.
So, have you TESTED the autovacuum daemon with your load, and set it to
run every 5 minutes? Keep in mind, it won't actually vacuum every table
every 5 minutes, it'll just check the stats to see which ones have updated
a fair bit and vacuum those, and they're lazy vacuums. I've found it to
be a win. If you haven't tested it and dismissed it out of hand, then you
should really give it a try to see if it can be configured to provide good
performance and behavior.