Re: Setting vacuum_freeze_min_age really low - Mailing list pgsql-performance

Robert, Andres,

> That, and Tom's concern about forensics, which I understand to be the
> larger sticking point.

I don't buy the idea that we should cause regular recurring performance
issues for all of our users in order to aid diagnosing the kind of
issues which happen 1% of the time to 2% of our users.

> So, if the table's age is less than vacuum_freeze_table_age, we'll
> only scan pages not already marked all-visible.  Regardless of vfma,
> we probably won't freeze much.

Right, but the pages which were dirtied *anyway* will get frozen.

> On the other hand, if the table's age is at least
> vacuum_freeze_table_age, we'll scan the whole table and freeze a lotta
> stuff all at once.  Again, whether vfma is high or low won't matter
> much: it's definitely less than vacuum_freeze_table_age.

Right.

> Basically, I would guess that both the costs and the benefits of
> changing this are pretty small.  It would be nice to hear from someone
> who has tried it, though.

Well, I have, but I don't exactly have empirical testing results from
it.  That's really the sticking point here: can we measurably
demonstrate that lowering vfma makes autovacuum freeze happen less
often, and do less work when it does?  Realistically, I think that's
waiting on me having time to do some lengthy performance testing.

> It will also often enough lead to a page being frozen repeatedly which
> causes unneccessary IO and WAL traffic. If a page contains pages from
> several transactions its not unlikely that some tuples are older and
> some are newer than vfma. That scenario isn't unlikely because of two
> scenarios:

Nobody has yet explained to me where this extra WAL and IO traffic would
come from.  vfma only takes effect if the page is being vacuumed
*anyway*.  And if the page is being vacuumed anyway, the page is being
rewritten anyway, and it doesn't matter how many changes we make on that
page, except as far as CPU time is concerned.  As far as IO is
concerned, an 8K page is an 8K page.  No?

The only time I can imagine this resulting in extra IO is if vacuum is
regularly visiting pages which don't have any other work to do, but do
have tuples which could be frozen if vfma was lowered.  I would tend to
think that this would be a tiny minority of pages, but testing may be
the only way to answer that.

> When a page contains freezable items, as determined by freeze_min_age,
> and we are doing a full table scan we won't skip buffers that we can't
> lock for cleanup. Instead we will wait and then lock them for
> cleanup. So I think this would be rather noticeably impact the speed of
> vacuum (since it waits more often) and concurrency (since we lock more
> buffers than before, even if they are actively used).

Well, that behavior sounds like something we should maybe fix,
regardless of whether we're lowering the default vfma or not.

--Josh Berkus






--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com