Re: Vaccuum best practice: cronjob or autovaccuum?

On Thu, Aug 28, 2008 at 07:53:17PM +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.
>
> But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
> processes contradict each other, or add unnecessary load to the
> system?

They do the latter. Especially vacuuming every hour, you're foiling
the autovacuum and just introducing completely unnecessary load.

> The reason we introduced the cronjob we felt was that the autovaccuum
> was not really doing its job.

What gave you this feeling?

What's the "churn rate" on these tables (i.e. how fast do dead rows
get created?)

My suspicion is that, using the 8.2 autovacuum, your "huge" table
needs to be vacuumed manually, and the others just left to
autovacuum. But that's just a guess at the moment.

A
--
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/