Re: autovacuum next steps

Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>> On first blush, I'm not sure I like this as it doesn't directly attack
>> the table starvation problem, and I think it could be a net loss of speed.
>>
>> VACUUM is I/O bound, as such, just sending multiple vacuum commands at a
>> DB isn't going to make things faster, you are now going to have multiple
>> processes reading from multiple tables at the same time. I think in
>> general this is a bad thing (unless we someday account for I/O made
>> available from multiple tablespaces).
>
> Yeah, I understand that. However, I think that can be remedied by using
> a reasonable autovacuum_vacuum_cost_delay setting, so that each worker
> uses less than the total I/O available. The main point of the proposal
> is to allow multiple workers on a DB while also allowing multiple
> databases to be processed in parallel.

So you are telling people to choose an autovacuum_delay so high that
they need to run multiple autovacuums at once to keep up? I'm probably
being to dramatic, but it seems inconsistent.

>> I think we can extend the current autovacuum stats to add one more
>> column that specifies "is hot" or something to that effect. Then when
>> the AV launcher sends a worker to a DB, it will first look for tables
>> marked as hot and work on them. While working on hot tables, the
>> launcher need not send any additional workers to this database, if the
>> launcher notices that a worker is working on regular tables, it can send
>> another worker which will look for hot tables to working, if the worker
>> doesn't find any hot tables that need work, then it exits leaving the
>> original working to continue plodding along.
>
> How would you define what's a "hot" table?

I wasn't clear, I would have the Admin specified it, and we can store it
as an additional column in the pg_autovacuum_settings table. Or perhaps
if the table is below some size threshold and autovacuum seems that it
needs to be vacuumed every time it checks it 10 times in a row or
something like that.