Re: Ineffective autovacuum

On Tue, Sep 27, 2011 at 7:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Royce Ausburn <royce(dot)ml(at)inomial(dot)com> writes:
>> Since sending this first email I've up'd the autovacuum log level and I've noticed that the same tables seem to be auto vacuum'd over and over again… Some of the tables are a bit surprising in that they're updated semi-regularly, but not enough (I'd think) to warrant an autovacuum every few minutes… Is this unusual?
>
> Well, that proves autovacuum isn't getting blocked anyway.  At this
> point I suspect that Marti has fingered the correct issue: you likely
> need to increase the FSM settings.  You should try running a manual
> VACUUM VERBOSE and see if it suggests that more FSM space is needed
> (there'll be some FSM stats at the end of the verbose printout).

That's the probably the best first step, a good second one might be to
increase the aggressiveness of autovac by lowering the delay, and
increasing the cost limit.

OP: You need to watch it a little closer during the day. first do as
suggested and increase the max_fsm_pages. High settings on it don't
cost a lot as they're only 6 bytes per page. So 1M max_fsm_pages
costs 6M of shared RAM. After that run vacuum verbose every hour or
two to keep an eye on the trend of how many pages it says are needed.
If that number doesn't stabilize, but just keeps growing then you're
not vacuuming aggressively enough. Up autovacuum_vacuum_cost_limit by
a couple of factors, and lower autovacuum_vacuum_cost_delay to 5ms or
less. Make sure you don't swamp your IO subsystem. On big machines
with lots of spindles it's hard to swamp the IO. On smaller
workstation class machines it's pretty easy.