Re: Potential Large Performance Gain in WAL synching - Mailing list pgsql-hackers

Curtis Faith wrote:
> > Yes, I can see some contention, but what does aio solve?
> > 
> 
> Well, theoretically, aio lets the file system handle the writes without
> requiring any locks being held by the processes issuing those reads. 
> The disk i/o scheduler can therefore issue the writes using spinlocks or
> something very fast since it controls the timing of each of the actual
> writes. In some systems this is handled by the kernal and can be very
> fast.

I am again confused.  When we do write(), we don't have to lock
anything, do we?  (Multiple processes can write() to the same file just
fine.)  We do block the current process, but we have nothing else to do
until we know it is written/fsync'ed.  Does aio more easily allow the
kernel to order those write?  Is that the issue?  Well, certainly the
kernel already order the writes.  Just because we write() doesn't mean
it goes to disk.  Only fsync() or the kernel do that.

> 
> I suspect that with large RAID controllers or intelligent disk systems
> like EMC this is even more important because they should be able to
> handle a much higher level of concurrent i/o.
> 
> Now whether or not the common file systems handle this well, I can't say,
> 
> Take a look at some comments on how Oracle uses asynchronous I/O
> 
> http://www.ixora.com.au/notes/redo_write_multiplexing.htm
> http://www.ixora.com.au/notes/asynchronous_io.htm
> http://www.ixora.com.au/notes/raw_asynchronous_io.htm

Yes, but Oracle is threaded, right, so, yes, they clearly could win with
it.  I read the second URL and it said we could issue separate writes
and have them be done in an optimal order.  However, we use the file
system, not raw devices, so don't we already have that in the kernel
with fsync()?

> It seems that OS support for this will likely increase and that this
> issue will become more and more important as uses contemplate SMP systems
> or if threading is added to certain PostgreSQL subsystems.

Probably.  Having seen the Informix 5/7 debacle, I don't want to fall
into the trap where we add stuff that just makes things faster on
SMP/threaded systems when it makes our code _slower_ on single CPU
systems, which is exaclty what Informix did in Informix 7, and we know
how that ended (lost customers, bought by IBM).  I don't think that's
going to happen to us, but I thought I would mention it.

> Assuming we can demonstrate no detrimental effects on system reliability
> and that the change is implemented in such a way that it can be turned
> on or off easily, will a 50% or better increase in speed for updates
> justify the sort or change I am proposing. 20%? 10%?

Yea, let's see what boost we get, and the size of the patch, and we can
review it.  It is certainly worth researching.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073