Re: is autovacuum recommended?

On Fri, Jul 10, 2009 at 2:47 PM, Willy-Bas Loos<willybas(at)gmail(dot)com> wrote:
> Hi,
>
> Thanks for your answers!
> I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could
> probably configure them much better.

Note that support for 8.1 on windows is gone, as it is no longer
considered supportable due to design / engineering issues. Upgrading
them all to 8.3 or 8.4 is probably a good idea since they are better
at autovacuum and such than 8.2 and before.

> We've recently moved to brand new dedicated database servers with pg8.3 on
> debian in 2 projects and it has been much easier to configure these
> correctly. There I don't encounter the probems that i described.

Smart move. The amount of effort needed to learn debian or any other
linux distro is usually less than the amount of ongoing effort to keep
a production pg server happy on windows. Also, 64 bit pgsql on 64 bit
unix/linux is capable of better scaling and handling more memory.

> The thing is that the whole concept of autovacuum is not feeling right.
> Per design, the vacuum is likely to kick off when i am doing something big.

That assumes that autovacuum always runs in some mode that must
interfere with db operation. If you set the
autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit,
autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold parameters
properly, as well as the free space map settings large enough to hold
all your dead tuples, then autovacuum should not cause a lot of
issues, unless your machine is already IO bound. And if it's already
IO bound and dragging butt, then the problem isn't autovacuum, but a
machine without enough IO bandwidth to do its job well.

> And when i am doing something big, a vacuum is the last thing i'd wish for.

I don't even notice when it kicks in on my servers.

> I'd wish for a vacuum when the database is doing nothing at all, but the
> autovacuum will NEVER kick off in such a moment.

Again, if the delay and such are set right, then autovac will use so
little IO as to be unnoticeable.

> That's why i feel better scheduling the vacuum at times at which i know
> things will be generally quiet.

For some very large tables on slow IO machines, it makes sense to
remove them from the purview of autovac, I agree. Take a look at the
pg_autovacuum table. it's pretty easy to see how it works.

> To be honest, i am a bit surprised that all 3 reactions recommend using
> autovacuum, even if it means i have to buy a new server for this purpouse.

Well, autovacuum is more primitive in the versions you're running, and
more likely to get in the way. 8.3, and to a greater extent 8.4,
remove a lot of these issues.

> I was thinking that autovacuum was just a mechanism to ensure that postgres
> works well out of the box, but that it would be recommended to schedule your
> own vacuum tailored to your specific needs.
> I agree though, that it is a tough tailoring job and that the autovacuum
> must be doing a better job than i am. It just fires at the wrong time.

And that's kind of the point, that it's better to have a db that runs
a little slow than one that explodes in sheets of flame.

> Just a thought (to think positively..): wouldn't it be possible to let the
> autovacuum wait until the load goes down, or until the end of the
> transaction that triggered the autovacuum?

You could set up a cron job that updated the pg_autovacuum table at
certain times to accomplish this. I'm not sure how easy it would be
to program autovac to do the same thing. You could certainly set the
cost delay higher than normal (like 20 or 40 ms) for some tables so
that autovac didn't get in the way, but then you run the risk of it
never keeping up, and on 8.1 with only one thread to autovac, that
could be bad. Definitely consider upgrading pg versions on your
windows machines.