On Jul 24, 2007, at 1:02 AM, Gregory Stark wrote:
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
We didn't, but while I agree with the idea, I think 5% is too low. I
don't want autovacuum to get excessively aggressive. Is 10% not
Well let me flip it around. Would you think a default fillfactor of
be helpful or overkill? I think it would nearly always be overkill
heap space and therefore cache hit rate and i/o bandwidth.
I get my 5% intuition from the TPCC stock table which has about 20
page. That means a fillfactor or vacuum at 5% both translate into
maintain a margin of one tuple's worth of space per page. Enough
for an update
to happen without migrating to a new page.
That's actually a fairly wide table though. A narrower table could
50-100 tuple per page which would require only 1-2% of dead space
Perhaps the two parameters should be tied together and we should
autovacuum parameter: max(1%, min(10%, fillfactor(table))) and make
default fill factor 5%.
Hm. We have the width of the table in the stats don't we? We could
calculate the "1 tuple's worth of space" percentage automatically on a
per-table basis. Or for that matter instead of calculating it as a
of the whole table, just compare the number of updates/deletes with
of pages in the table.
How about the analyze scale factor, should we keep the current
have less of a problem with reducing it further since analyze is
My "try to maintain one tuple's worth of space" model doesn't
question at all. It depends entirely on whether the ddl is changing
Perhaps this should be 1/max(stats_target) for the table. So the
be 10% but if you raise the stats_target for a column to 100 it
would go down
to 1% or so.
The idea being that if you have ten buckets then updating 1/10th of
stands an even chance of doubling or halving the size of your
there's no math behind that intuition at all and I rather doubt it
Actually I feel like there should be a factor of 2 or more in there
If you modify 1/10th of the rows and you have 10 buckets then we
analyzing *before* the distribution has a chance to be modified beyond
Perhaps I shouldn't have closed the <speculation> tag so early :)
if we try to calculate reasonable defaults like this is it makes it
how to expose any knob for the user to adjust it if they need to.
In reality, I think trying to get much below 10% on any large-ish
production systems just isn't going to work well. It's starting to
approach the point where you need to be vacuuming continuously, which
is going to put us right back into starvation territory.
Put another way, there's only so low you can get table bloat with
vacuum as it currently stands. If you want to do better, you need
things like HOT and DSM.
Regarding page splits, it might make sense to drop the fillfactor a
bit. I'm thinking that in most cases, the difference between 85% and
90% won't be noticed. For cases where it will matter (ie: insert-
only), you'd want to set fillfactor to 100% anyway.
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly