Re: synchronous commit vs. hint bits

On Mon, Nov 7, 2011 at 10:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> 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.
>
> It seems to me that you've jumped to proposing solutions before you know
> where the problem actually is --- or at least, if you do know where the
> problem is, you didn't explain it.  Is the cost in repeating clog
> lookups, or in testing to determine whether it's safe to set the bit
> yet, or is it contention associated with one or the other of those?

Good question. One possibly informative fact is that, on unlogged
tables, the same change doesn't seem to make any difference. Here are
the benchmark results with unlogged tables, configuration otherwise
identical to the OP:

[unpatched]
tps = 10624.851704 (including connections establishing)
tps = 10507.024822 (including connections establishing)
tps = 10714.411389 (including connections establishing)
[test whacked out]
tps = 10779.704540 (including connections establishing)
tps = 10523.863100 (including connections establishing)
tps = 10654.102699 (including connections establishing)

The difference might be noise, or it may be a very small real effect,
but it's clearly tiny compared to the change for permanent tables (but
note that this was not true prior to commit
53f1ca59b5875f1d3e95ee709ecaddcbdfdbd175). This seems to me to be
fairly compelling evidence that the problem is in the clog lookups
themselves, rather than the test that determines whether or not it's
safe to set the bit. However, I don't know whether the problem is the
cost of the test itself or some kind of associated contention. I
don't see much difference in CPU utilization between the patched and
unpatched code, but that's not really accurate enough to be certain.

I just reran both tests with LWLOCK_STATS defined. Again, five minute test run:

lwlock 11: shacq 87323748 exacq 3708555 blk 1932719 [unpatched]
lwlock 11: shacq 11682513 exacq 4769472 blk 677534 [patched]

11 = CLogControlLock, so you can see that the unpatched code is
acquiring CLogControlLock in shared mode more 7x as often and blocks
on the lock about 3x as often, despite processing fewer transactions.
The patched code has more exclusive-acquires, but that's at least
partly just because it's processing more transactions. Unfortunately,
I don't have oprofile access on this box and can't see exactly where
the time is being spent. However, I am not sure how much it matters.
With that much of an increase in CLOG traffic, something's gotta give.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company