Re: COMMIT NOWAIT Performance Option

Zeugswetter Andreas ADI SD wrote:
>>> Maybe a suitable replacement for full-page would be to sync the
> first
>>> WAL record for a page change before writing the buffer
>> We *always* sync WAL records for page changes before writing
>> the buffer for the page.
>
> Um, is that so ? And how is that done ? (e.g. bgwriter would need to
> wait for or even initiate a WAL record sync)
> Seems this is not clear to others eighter.

Yes. The bgwriter or backend that flushes the buffer indeed initiates a
WAL sync if the WAL record corresponding the LSN of the buffer hasn't
been flushed yet.

> Why else would people think we need a full data scan after crash without
> full-pages ?

They're confused. ;-)

> If this is true, all pages with possibly pending IO during crash will be
> touched and could thus be checked during startup recovery.

That's right. Torn page detection wouldn't require any extra I/O, just CPU.

> I can see that we need to sync wal before write for certain changes, but
> I don't see why we would currently need it for e.g. a heap insert, that
> gets a free slot.

The corresponding index insert might hit the disk but the heap insert
might not. Now if someone after the crash uses the same heap slot, you'd
end up with the index tuple of the aborted transaction pointing to wrong
heap tuple. There might be other error scenarios, that's just the first
example I could think of.

In practice, it's extremely rare for a writer to need to do the WAL
flush. When a buffer is chosen as a victim, it's been unused for some
time, and the WAL has already been flushed. It does happen when you're
vacuuming, see Simon's recent thread, but we should fix that anyway. It
also happens on checkpoints, but it's not a big performance issue.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com