Re: Proposed LogWriter Scheme, WAS: Potential Large PerformanceGain in WAL synching

Bruce Momjian wrote:
> So every backend is to going to wait around until its fsync gets done by
> the backend process?  How is that a win?  This is just another version
> of our GUC parameters:
>     
>     #commit_delay = 0               # range 0-100000, in microseconds
>     #commit_siblings = 5            # range 1-1000
> 
> which attempt to delay fsync if other backends are nearing commit.  
> Pushing things out to another process isn't a win;  figuring out if
> someone else is coming for commit is.  

It's not the same at all. My proposal make two extremely important changes
from a performance perspective.

1) WALWriteLocks are never held by processes for lengthy transations. Only
for long enough to copy the log entry into the buffer. This means real
work can be done by other processes while a transaction is waiting for
it's commit to finish. I'm sure that blocking on XLogInsert because another
transaction is performing an fsync is extremely common with frequent update
scenarios.

2) The log is written using optimal write sizes which is much better than
a user-defined guess of the microseconds to delay the fsync. We should be
able to get the bottleneck to be the maximum write throughput of the disk
with the modifications to Tom Lane's scheme I proposed.

> Remember, write() is fast, fsync is slow.

Okay, it's clear I missed the point about Unix write earlier :-)

However, it's not just saving fsyncs that we need to worry about. It's the
unnecessary blocking of other processes that are simply trying to
append some log records in the course of whatever updating, inserting they
are doing. They may be a long way from commit.

fsync being slow is the whole reason for not wanting to have exclusive
locks held for the duration of an fsync.

On an SMP machine this change alone would probably speed things up by
an order of magnitude (assuming there aren't any other similar locks
causing the same problem).

- Curtis