From: Matthew T. O'Connor <matthew@zeut.net>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Default autovacuum settings too conservative
Date: Wed, 01 Feb 2006 16:37:07 -0500
Message-ID: <43E12A03.6070906@zeut.net> (raw)
In-Reply-To: <20060201211633.GM95850@pervasive.com>
References: <20060201211633.GM95850@pervasive.com>
Jim C. Nasby wrote:
> Small tables are most likely to have either very few updates (ie: a
> 'lookup table') or very frequent updates (ie: a table implementing a
> queue). In the former, even with vacuum_threshold = 0 vacuum will be a
> very rare occurance. In the later case, a high threshold is likely to
> cause a large amount of un-nececcasry bloat.
Well a threshold of 0 won't work because then a 0 tuple table will get
vacuumed every time. Or at least autovacuum needs to special case this.
> Also, vacuum_scale_factor of 0.4 seems unreasonably large. It means
> tables will be 40% dead space, which seems excessively wasteful.
> Something between 0.1 and 0.2 seems much better.
Depends on the app and the usage patterns as to what too much slack
space is.
> Has anyone looked at how effective these two settings are?
As far I as I know, we are still looking for real world feedback. 8.1
is the first release to have the integrated autovacuum. The thresholds
in 8.1 are a good bit less conservative than the thresholds in the
contrib version. The contrib thresholds were universally considered WAY
to conservative, but that was somewhat necessary since you couldn't set
them on a per table basis as you can in 8.1. If we continue to hear
from people that the current 8.1 default thresholds are still to
conservative we can look into lowering them.
I think the default settings should be designed to minimize the impact
autovacuum has on the system while preventing the system from ever
getting wildly bloated (also protect xid wraparound, but that doesn't
have anything to do with the thresholds).
Matt
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: matthew@zeut.net, jnasby@pervasive.com, pgsql-admin@postgresql.org
Subject: Re: [PERFORM] Default autovacuum settings too conservative
In-Reply-To: <43E12A03.6070906@zeut.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox