Rules of Thumb for Autovaccum

On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote:

What rules of thumb exist for:
* How often a table needs to be vacuumed?
* How often a table needs to be analyzed?
* How to tune Autovacuum?

I have a large DB server, and I'm concerned that it's not being
autovaccumed and autoanalyzed frequently enough. But I have no idea
what proper values should be?

A related question: If I INSERT a large number of records per day,
similar in nature to the existing records, does that require new
vacuum? new analyze? Or do I only need those for DELETEs or changes to
the nature of records?

Finally: What type of performance impact can I expect from vacuum and
analyze, in general?

Unless you are very high-volume, the auto-vacuum default settings are
fine. The default do allow up to 20% of unused space in tables, but
making that lower is expensive to performance.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Thanks. What about auto-analyze? When will they be analyzed by default?
And what actions generally require new analyze?

Hi,

On 16 February 2012 01:14, Robert James <srobertjames@gmail.com> wrote:

What rules of thumb exist for:
* How often a table needs to be vacuumed?
* How often a table needs to be analyzed?
* How to tune Autovacuum?

I prefer to use autovacuum daemon and sets thresholds on per table
basis i.e. sets reasonable defaults and then add few exceptions.
I keep *_threshold as is and change *_scale_factor and turn off cost
based vacuum/analyse (see other note about this). My lowest
scale_factor is 0.002 ie. 0.2% of table has to change (which
corresponds to ~8mil rows) to trigger analyse/vacuum.

autovacuum/analyse can produce significant I/O so you have two options:
- tune cost based settings in order to limit I/O used by this porocess
- turn off autovacuum daemon and schedule manual acuum/analyse in quiet period

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)