Re: autovacuum next steps, take 2

Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:

> >The second mode is the "hot table worker" mode, enabled when the worker
> >detects that there's already a worker in the database. In this mode,
> >the worker is limited to those tables that can be vacuumed in less than
> >autovacuum_naptime, so large tables are not considered. Because of
> >this, it'll generally not compete with the first mode above -- the
> >tables in plain worker were sorted by size, so the small tables were
> >among the first vacuumed by the plain worker. The estimated time to
> >vacuum may be calculated according to autovacuum_vacuum_delay settings,
> >assuming that all pages constitute cache misses.
>
> How can you determine what tables can be vacuumed within
> autovacuum_naptime?

My assumption is that
pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_delay = time to vacuum

This is of course not the reality, because the delay is not how long
it takes to fetch the pages. But it lets us have a value with which we
can do something. With the default values, vacuum_cost_delay=10,
vacuum_cost_page_miss=10, autovacuum_naptime=60s, we'll consider tables
of under 600 pages, 4800 kB (should we include indexes here in the
relpages count? My guess is no).

A table over 600 pages does not sound like a good candidate for hot, so
this seems more or less reasonable to me. On the other hand, maybe we
shouldn't tie this to the vacuum cost delay stuff.

> So at:
> t=0*autovacuume_naptime: worker1 gets started on DBX
> t=1*autovacuume_naptime: worker2 gets started on DBX
> worker2 determines all tables that need to be vacuumed,
> worker2 excludes tables that are too big from it's to-do list,
> worker2 gets started working,
> worker2 exits when it either:
> a) Finishes it's entire to-do-list.
> b) Catches up to worker1
>
> I think the questions are 1) What is the exact math you are planning on
> using to determine which tables are too big? 2) Do we want worker2 to
> exit when it catches worker1 or does the fact that we have excluded
> tables that re "too big" mean that we don't have to worry about this?

Right, I think the fact that we excluded big tables means that this
won't be a problem most of the time, but we'll need some sort of
protection anyway. I think this is easy to achieve -- store the table
each worker is currently processing in shared memory, and have all
workers check all other workers. If a plain worker finds that another
worker is processing the table already, it skips that table and
continues with the next one. A hot table worker instead exits right
away (caught up).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support