Re: tuning autovacuum

On Thu, Jun 9, 2011 at 12:25 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Tom Lane's message of mié jun 08 21:50:22 -0400 2011:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> > I think it'd be really useful to expose some more data in this area
>> > though.  One random idea is - remember the time at which a table was
>> > first observed to need vacuuming. Clear the timestamp when it gets
>> > vacuumed.  Then you can do:
>>
>> As far as I recall that logic, there is no delay between when we know
>> that a table needs vacuumed and when we do it.  I don't see the point of
>> introducing any such delay, either.
>
> Autovacuum checks each table twice.  When it first connects to a
> database it grabs a complete list of relations needing vacuum.  Then it
> starts vacuuming, and before processing each relation, it rechecks.
>
> So there *is* a delay, which corresponds to how long it took to process
> the tables that preceded it in the list.

There's that, plus as of 9.1 autovacuum will skip tables that it can't
immediately take ShareUpdateExclusiveLock on (except in case of
impending wraparound). So in the worst case we might have tables left
over from a previous run of the autovacuum worker that never got
processed. And, of course, in any version, it's also possible for
autovacuum to get booted off the table by a conflicting lock request
that arrives midway through; the system might get shut down in the
middle of the run; autovacuum might error out in medias res; etc.

> Robert's suggestion would seem
> to make sense.  I'm not sure how to implement it: do we want some more
> (highly volatile) data points in pgstat?  Do we need some other
> mechanism?  This seems like a use case for pg_class_nt (see
> http://archives.postgresql.org/pgsql-patches/2006-06/msg00114.php)

I haven't looked at the implementation, but I like the concept. It's
tempting to just shove everything in pgstat, but we already have
scalability limits there.

> In any case, given the "rebalancing" feature of vacuum_cost_delay (which
> increases the delay the more workers there are), the only "solution" to
> the problem of falling behind is reducing the delay parameter.  If you
> just add more workers, they start working more slowly.

Unless, of course, you already have the delay set to zero, in which
case starting more workers might be all you can do. The case where
this really matters is with big tables, I think. If you have #(big
tables) > #(autovacuum workers), it seems like you could end up in a
situation where the big tables pin down all the workers and no small
tables can get vacuumed for a long time. Perhaps we can tune the
algorithm to compensate for that in some way, but I think we need more
raw data to work with first.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company