Re: synchronous commit vs. hint bits

On Mon, Nov 7, 2011 at 1:08 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Thinking about this more, the backend local cache approach is probably
> going to be useless in terms of addressing this problem -- mostly due
> to the fact that the cache is, well, local.  Even if backend A takes
> the time to mark the bit in its own cache, backends B-Z haven't yet
> and presumably by the time they do the page has been rolled out
> anyways so you get no benefit.  The cache helps when a backend sees
> the same transaction spread out over a number of tuples/pages --
> that's simply not the case in OLTP.

Ah, right. Good point.

> Doing the work in the bgwriter might do the trick assuming the
> bgwriter consistently loses the race against both transaction
> resolution and the wal, and the extra clog lookup (when you win the
> race) penalty doesn't sting too muh...

But I can't see how this can work. The background writer is only
designed to do one thing: ensuring a supply of clean buffers for
backends that need to allocate new ones. I'm not sure the background
writer is going to do anything at all on this test, since the data set
fits inside shared_buffers and therefore there's no buffer eviction
happening. But even if it does, it's certainly not going to scan all
1 million shared buffers anywhere near quick enough to matter; it's
going to be limited to at most 100 buffers every 200 ms, which means
that even if it ran at top speed for the entire test, it would only
get through about 15% of the buffer pool even *once* before the test
ended. That's not even slightly close to what would be needed to move
the needle here; you would need to visit any given buffer within a few
hundred milliseconds of the relevant transaction commit.

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