Re: synchronous commit vs. hint bits

On Mon, Nov 7, 2011 at 8:31 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I've long considered synchronous_commit=off to be one of our best
> performance features.  Certainly, it's not applicable in every
> situation, but there are many applications where losing a second or so
> worth of transactions is an acceptable price to pay for not needing to
> wait for the disk to spin around for every commit.  However, recent
> experimentation has convinced me that it's got a serious downside:
> SetHintBits() can't set HEAP_XMIN_COMMITTED or HEAP_XMAX_COMMITTED
> hints until the commit record has been durably flushed to disk.  It
> turns out that can cause a major performance regression on systems
> with many CPU cores.  I fixed this for temporary and unlogged tables
> in commit 53f1ca59b5875f1d3e95ee709ecaddcbdfdbd175, but the same issue
> exists (without any clear fix) for permanent tables.

What's the source of the regression? Is it coming from losing the hint
bit and being forced out to clog? How likely is it really going to
happen in non synthetic real world cases?

Thinking about the hint bit cache I was playing with a while back, I
guess you could have put the hint bit in the cache but refrained from
marking it in the page in the TransactionIdIsValid(xid)=false case --
in the first implementation I had only put the bit in the cache when
it was valid -- since TransactionIdIsValid(xid) is not necessarily
cheap though, maybe it's worth reserving an extra bit for the
transaction being valid in the cache if you went down that road.

Another way to attack this problem is to re-check and set the hint bit
if you can do it in the bgwriter -- there's a good chance you will
catch it in oltp environments like pgbench although it not clear if
the cost to the general case would be too high.

merlin