Re: Second attempt, roll your own autovacuum

From: Matthew O'Connor <matthew(at)zeut(dot)net>
To: Glen Parker <glenebob(at)nwlink(dot)com>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Second attempt, roll your own autovacuum
Date: 2006-12-19 20:07:46
Message-ID: 45884692.9080408@zeut.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Glen Parker wrote:
> Tom Lane wrote:
>> What is it that you find inadequate
>> about regular autovacuum? It is configurable through the pg_autovacuum
>> catalog --- which I'd be the first to agree is a sucky user interface,
>> but we're not going to set the user interface in concrete until we are
>> pretty confident it's feature-complete. So: what do you see missing?
>
> Traditional vacuum does every table in the DB, which is absolutely The
> Wrong Thing for us. Vacuum can be fired against individual tables, but
> then how do I know which tables need it? Autovacuum is smart about
> which tables it hits, but exceedingly stupid about *when* it hits them.
>
> What I want is a way to do all needed vacuuming, in as short a time span
> as possible, when I decide it should be done. For us, that's between ~2
> AM and ~3 AM each morning. If a vacuum runs past 3 AM, so be it, but
> it's better to hit it hard and try to be done by 3 AM than it is to
> lolly gag around about it unil 5 AM.
>
> The obvious answer for me is to vacuum all the tables that autovacuum
> would hit, but only on demand. Something like "VACUUM CONDITIONAL WHERE
> autovacuum_says_so()" :-)

I believe the correct answer to this problems is to write a cron script
that enables autovacuum at 2AM and disables it at 3AM. I think there is
some talk of this in the archives somewhere.

If you need to hit specific tables more often than that, then you can
have another cron script that vacuums a table ever hour or something or
something along those lines.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Angva 2006-12-19 20:20:51 Re: out of memory woes
Previous Message Tony Caduto 2006-12-19 20:06:57 Re: Creating an Independant Application

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2006-12-19 20:15:16 Re: Companies Contributing to Open Source
Previous Message Glen Parker 2006-12-19 19:49:55 Re: Second attempt, roll your own autovacuum