On Mon, Nov 7, 2011 at 8:31 AM, Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
