Re: Autovacuum Improvements

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Christopher Browne <cbbrowne(at)acm(dot)org>
Cc: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Autovacuum Improvements
Date: 2007-01-08 17:47:44
Message-ID: 1168278464.22307.85.camel@coppola.muc.ecircle.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

On Sun, 2006-12-24 at 03:03, Christopher Browne wrote:
[snip]
> Seems to me that you could get ~80% of the way by having the simplest
> "2 queue" implementation, where tables with size < some threshold get
> thrown at the "little table" queue, and tables above that size go to
> the "big table" queue.

That would most definitely not cut it for me, I have more than 2
categories of tables:

- a few small but very often updated/inserted/deleted table: these must
be continuously vacuumed, your "little queue" is not good enough for
that, as even the round trip between the small tables could lead to
bloat on them;

- a few small and moderately updated, that could live with the "little
queue";

- a few big and frequently updated, but which only have a small
percentage of rows actively updated at any time: those could live with
the big queue;

- the rest which are rarely updated, I would put those in a separate
queue so they won't affect the rest, cause vacuuming them is really
mostly not critical;

The point is that I'm not sure there couldn't be even more reasons to
split the tables in even more queues based on the importance of
vacuuming them combined with update rate and their size. If I can set up
my own queues I can experiment with what works best for me... for the
base setup you could set up some default queues. I wonder though how
would you handle dynamics of tables, I mean when will a small table
which grows start to be considered a big table for the purpose of
putting it in one queue or the other ? I guess it would be done on
analyzing the table, which is also handled by autovacuum, so tables with
no vacuum queue settings could go to one of the 2 default queues you
mention.

> That should keep any small tables from getting "vacuum-starved."
>
> I'd think the next step would be to increase the number of queues,
> perhaps in a time-based fashion. There might be times when it's
> acceptable to vacuum 5 tables at once, so you burn thru little tables
> "like the blazes," and handle larger ones fairly promptly. And other
> times when you don't want to do *any* big tables, and limit a single
> queue to just the itty bitty ones.

This is all nice and it would be cool if you could set it up per vacuum
queue. I mean how much more effort would be to allow vacuum queues with
generic settings like time windows with max number of threads for each
window, and let the user explicitly assign tables to those queues,
instead of hard coding the queues and their settings and assign tables
to them based on size or any other heuristics ?

For the average application which needs simple settings, there could be
a default setup with the 2 queues you mention. If it would be possible
to set up some rules to assign tables to queues based on their
properties on analyze time, instead of explicitly assigning to one queue
or other, that would be nice too, and then you can completely cover the
default setup with those settings, and allow for more complex setups for
those who need it.

> This approach allows you to stay mostly heuristic-based, as opposed to
> having to describe policies in gratuitous detail.

I agree that for simple setups that would be OK, but like I said, if it
would be easy enough to code that heuristics, and provide some sane
setup as default, and then let the user optimize it, that would be a
cool solution.

Now it's true I don't really know how would you code 'assign all tables
which are smaller than x rows to vacuum queue "little-tables"' ... maybe
by providing a function to the queue which "matches" on the table ? And
you can change that function ? No idea, but it probably can be done...

> Having a mechanism that requires enormous DBA effort and where there
> is considerable risk of simple configuration errors that will be hard
> to notice may not be the best kind of "feature" :-).

I think most people will not want to touch the default settings unless
it will not work good enough for them. I definitely not like too much
that I had to set up some cron jobs beside autovacuum, as they are most
definitely not doing optimal job, but autovacuum was not doing that
either, and I'm afraid a 2-queue system would also not do it at least
for the queue-like tables I have, which must be vacuumed continuously,
but only if they need it... that's what I expect from autovacuum, to
vacuum all tables in the proper periodicity/time window for each of
them, but only if they need it... and I can imagine way more such
periodicity/time window settings than 2. Now if autovacuum could figure
out on itself all those settings, that would be even cooler, but if I
can set it up myself that would be good enough.

Actually I think all vacuum patterns could be automatically figured out
by looking at the statistics AND the dynamics of those statistics (i.e.
it changes in bursts, or steadily increasing over time, etc.), and
possibly also the read access statistics (there's no big reward in too
frequently vacuuming a table which is only inserted and deleted and
rarely read), and perhaps some hints from the user about speed
requirements for specific tables.

The problem with all this is that I doubt there is enough experience to
write such a heuristics to optimally cover all situations, and even if
there were, it could result in really complex code, so that's why I
think it is more reasonable to let people set up their vacuum queues...

Another point: it would be nice if autovacuum could also decide to do a
full vacuum, or even better a CLUSTER under certain circumstances for
tables which are badly bloated (you could argue that should never happen
if autovacuum is set up properly, but think about a queue-like table
heavily updated during a backup is running). Of course this can also
backfire if set up by default, so I guess this would have to be set up
explicitly... possibly with rules like table size, max bloat allowed,
time window, etc. One thing to avoid badly locking the application is to
acquire an exclusive lock with nowait and only do the full vacuum if the
lock succeeds (to avoid situations like: a backup is running, and it
will for the next 2 hours, we ask for an exclusive lock, will stay on
hold, but in the same time we lock all new read requests for the next 2
hours till the backup is done... while the operation we wanted to do is
guarantied to be finished in 10 seconds, as the table is heavily bloated
but still small).

Another thing autovacuum could figure out is not to do a vacuum at all
if there is a long running transaction running and disabling anyway the
work vacuum would do (although I'm not sure it does not do this one
already, does it ?).

Well, maybe not all what I rambled along makes sense, but I dumped my
brain now anyway... hope I didn't bore you too much :-)

Cheers,
Csaba.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2007-01-08 17:57:59 Slony in Windows installer?
Previous Message guillermo arias 2007-01-08 17:36:46 is there a tracking trace tool like the "SQL Analizer" in MS sqlserver.?

Browse pgsql-hackers by date

  From Date Subject
Next Message D'Arcy J.M. Cain 2007-01-08 17:49:08 Re: pgsql: Widen the money type to 64 bits.
Previous Message Bruce Momjian 2007-01-08 17:45:34 Re: 8.3 pending patch queue