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 enough?


Well let me flip it around. Would you think a default fillfactor of 10% would be helpful or overkill? I think it would nearly always be overkill and waste
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 tuples per page. That means a fillfactor or vacuum at 5% both translate into trying to 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 easily have 50-100 tuple per page which would require only 1-2% of dead space overhead.

<idle speculation>

Perhaps the two parameters should be tied together and we should make the autovacuum parameter: max(1%, min(10%, fillfactor(table))) and make the
default fill factor 5%.

Hm. We have the width of the table in the stats don't we? We could actually
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 percentage of the whole table, just compare the number of updates/deletes with the number
of pages in the table.

</speculation>

How about the analyze scale factor, should we keep the current 10%? I have less of a problem with reducing it further since analyze is cheaper
than vacuum.

My "try to maintain one tuple's worth of space" model doesn't answer this question at all. It depends entirely on whether the ddl is changing the data
distribution.

Perhaps this should be 1/max(stats_target) for the table. So the default would 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 the rows stands an even chance of doubling or halving the size of your bucket. Except there's no math behind that intuition at all and I rather doubt it makes much
sense.

Actually I feel like there should be a factor of 2 or more in there as well. If you modify 1/10th of the rows and you have 10 buckets then we should be
analyzing *before* the distribution has a chance to be modified beyond
recognition.

Perhaps I shouldn't have closed the <speculation> tag so early :) The problem if we try to calculate reasonable defaults like this is it makes it unclear
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

Reply via email to