Re: autovacuum next steps, take 2

On Feb 26, 2007, at 12:49 PM, Alvaro Herrera wrote:

> Jim C. Nasby wrote:
>
>> That's why I'm thinking it would be best to keep the maximum size of
>> stuff for the second worker small. It probably also makes sense to
>> tie
>> it to time and not size, since the key factor is that you want it
>> to hit
>> the high-update tables every X number of seconds.
>>
>> If we wanted to get fancy, we could factor in how far over the vacuum
>> threshold a table is, so even if the table is on the larger size, if
>> it's way over the threshold the second vacuum will hit it.
>
> Ok, I think we may be actually getting somewhere.
>
> I propose to have two different algorithms for choosing the tables to
> work on. The worker would behave differently, depending on whether
> there is one or more workers on the database already or not.
>
> The first algorithm is the plain threshold equation stuff we use
> today.
> If a worker connects and determines that no other worker is in the
> database, it uses the "plain worker" mode. A worker in this mode
> would
> examine pgstats, determine what tables to vacuum/analyze, sort them by
> size (smaller to larger), and goes about its work. This kind of
> worker
> can take a long time to vacuum the whole database -- we don't
> impose any
> time limit or table size limit to what it can do.
>
> 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.

Perhaps this has already been proposed, but maybe some combination of
the following inputs could be used to determine which table most
needs vacuuming:

- The proportion of tuples in a table that are dead (updated rows
since last vacuum/estimated row count). This would favor "hot" tables
naturally regardless of size.

- The time since the last vacuum, so that larger tables are
eventually vacuumed even if hot tables totally dominate

Of course tables that did not pass the minimum parameters specified
in postgresql.conf would not even get considered.

I'm being intentionally vague here on the exact algorithm, since you
all have though about this more than I have. One thing I like about
the above is that it is independent of table size, and doesn't
require anyone to determine which tables are hot manually.

-Casey