Re: Performace Optimization for Dummies

In response to "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>:

> >> indexes. I don't know whether autovacuum will also analyze tables
> >> for you automagically, but it would be a good idea to analyze the table
> >
> > It does.
>
> So, I have checked my log and I see an autovacuum running once every minute
> on our various databases being hosted on the server - once every minute!
>
> From what I can see, autovacuum is hitting the db's in question about once
> every five minutes. Does this imply an ANALYZE is being done automatically
> that would meet the requirements we are talking about here? Is there any
> benefit ot explicitly performing an ANALYZE?
>
> (Or does this go hand-in-and with turning off autovacuum...?)

It's only checking to see if vacuum/analyze needs done every 5 minutes.
It may or may not do any actual work at that time, based on how much
the tables have changed. See:
http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM

This is a case, during your bulk loads, where autovacuum might actually
hurt you. How many records are you inserting/updating in 5 minutes?
You may be exceeding autovacuum's ability to keep things clean.

I can't say for sure, but I would suspect that you'd be better off not
using autovacuum until after the initial data loads are done. My
guess is that you'll get better performance if you disable autovac and
write manual vacuum/analyze into your load scripts. Exactly how often
to have your script do it is something that will require testing to
figure out, but probably starting with every 100 or so, then adjust
it up and down and see what works best.

Explicitly performing a vacuum or analyze can be very beneficial,
especially if you know what kind of changes your creating in the data.
(Now that I think of it, there's no reason to disable autovac, as it
will notice if you've just manually vacuumed a table and not do it
again.) If you know that you're radically changing the kind of data
in a table, manually running analyze is a good idea. If you know that
you're creating a lot of dead tuples, manually vacuuming is a good
idea. Especially during a big data load where these changes might be
taking place faster than autovac notices.

--
Bill Moran
Collaborative Fusion Inc.