Re: Setting vacuum_freeze_min_age really low

Hi Josh,

On 2013-05-11 16:28:32 -0700, Josh Berkus wrote:
> > 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.

Well. For one you haven't proven that the changed setting actually
improves performance. So the comparison isn't really valid. We will
still need full table vacuums to be able to change relfrozenxids. Also,
he small percentages are the cases where the shit really hit the
fan. Making sure you have at least some chance of a) diagnosing the
issue b) recovering data is a pretty good thing.

> > 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.

I think you're missing the fact that we don't neccessarily dirty pages,
just because vacuum visits them. In a mostly insert workload its not
uncommon that vacuum doesn't change anything. In many scenarios the
first time vacuum visits a page it cannot yet me marked "all-visible"
yet so we will visit again soon after anyway. And after that there will
be regular full table vacuums.

> > 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*.

There's multiple points here:
a) we don't necessarily write/dirty anything if vacuum doesn't find
   anything to do
b) freezing tuples requires a xlog_heap_freeze wal record to be
   emitted. If we don't freeze, we don't need to emit it.

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

Sure, *if* we writeout the page, it doesn't matter at all whether we
changed one byte or all of them. Unless it also requires extra xlog
records to be emitted.

> 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.

INSERT only produces workloads like 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.

Well, that's easier said than done ;)

I wonder if we couldn't do the actual freezeing - not the dead tuple
deletion - without a cleanup but just with an exclusive lock?

I think I have said that before, but anyway: I think as long as we need
to regularly walk the whole relation for correctness there isn't much
hope to get this into an acceptable state. If we would track the oldest
xid in a page in a 'freeze map' we could make much of this more
efficient and way more scalable to bigger data volumes.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services