Re: synchronous commit vs. hint bits

On Mon, Nov 7, 2011 at 12:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Well, I'd argue that in most real world, high write intensity
databases there is constant pressure on pages to be flushed out to
make room for new ones being written to and the database size is much,
much larger than shared buffers -- pgbench is 100% update and pretty
novel in that respect. I guess I said 'bgwriter' when I really meant
'generally upon eviction, either by bgwriter or an evicting backend'.
But even given that, probably the lag is too long to be of useful
benefit to your problem.

merlin