Autovacuum Improvements (was: Second attempt, roll your own autovacuum)

From: Matthew O'Connor <matthew(at)zeut(dot)net>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>, Richard Huxton <dev(at)archonet(dot)com>, Christopher Browne <cbbrowne(at)acm(dot)org>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Autovacuum Improvements (was: Second attempt, roll your own autovacuum)
Date: 2006-12-19 16:56:17
Message-ID: 458819B1.7080903@zeut.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Alvaro Herrera wrote:
> Csaba Nagy wrote:
>>> Alternatively, perhaps a threshold so that a table is only considered
>>> for vacuum if:
>>> (table-size * overall-activity-in-last-hour) < threshold
>>> Ideally you'd define your units appropriately so that you could just
>>> define threshold in postgresql.conf as 30% (of peak activity in last 100
>>> hours say).
>> No, this is definitely not enough. The problem scenario is when
>> autovacuum starts vacuuming a huge table and that keeps it busy 10 hours
>> and in the meantime the small but frequently updated tables get awfully
>> bloated...
>>
>> The only solution to that is to have multiple vacuums running in
>> parallel, and it would be really nice if those multiple vacuums would be
>> coordinated by autovacuum too...
>
> Yes, I agree, having multiple "autovacuum workers" would be useful.

Bruce, I think there are a couple of items here that might be worth
adding to the TODO list.

1) Allow multiple "autovacuum workers": Currently Autovacuum is only
capable of ordering one vacuum command at a time, for most work loads
this is sufficient but falls down when a hot (very actively updated
table) goes unvacuumed for a long period of time because a large table
is currently being worked on.

2) Once we can have multiple autovacuum workers: Create the concept of
hot tables that require more attention and should never be ignored for
more that X minutes, perhaps have one "autovacuum worker" per hot table?
(What do people think of this?)

3) Create "Maintenance Windows" for autovacuum: Currently autovacuum
makes all of it's decisions based on a single per-table threshold value,
maintenance windows would allow the setting of a per-window, per-table
threshold. This makes it possible to, for example, forbid (or strongly
discourage) autovacuum from doing maintenance work during normal
business hours either for the entire system or for specific tables.

None of those three items are on the todo list, however I think there is
general consensus that they (at least 1 & 3) are good ideas.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2006-12-19 17:01:44 Re: Autovacuum Improvements (was: Second attempt,
Previous Message Tony Caduto 2006-12-19 16:53:21 Re: Creating an Independant Application

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2006-12-19 16:59:32 ERROR: tuple concurrently updated
Previous Message Martijn van Oosterhout 2006-12-19 16:25:04 Re: column ordering, was Re: [PATCHES] Enums patch v2