Обсуждение: Rules of Thumb for Autovaccum
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?
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? On 2/15/12, Bruce Momjian <bruce@momjian.us> wrote: > 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. + >
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)