On Thu, Jun 9, 2011 at 12:25 PM, Alvaro Herrera <alvhe...@commandprompt.com> wrote: > Excerpts from Tom Lane's message of mié jun 08 21:50:22 -0400 2011: >> Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
