WAL and commit_delay

I want to give some background on commit_delay, its initial purpose, and
possible options.

First, looking at the process that happens during a commit:

write() - copy WAL dirty page to kernel disk buffer
fsync() - force WAL kernel disk buffer to disk platter

fsync() take much longer than write().

What Vadim doesn't want is:

time backend 1 backend 2
---- --------- ---------
0 write()
1 fysnc() write()
2 fsync()

This would be better as:

time backend 1 backend 2
---- --------- ---------
0 write()
1 write()
2 fsync() fsync()

This was the purpose of the commit_delay. Having two fsync()'s is not a
problem because only one will see there are dirty buffers. The other
will probably either return right away, or wait for the other's fsync()
to complete.

With the delay, it looks like:

time backend 1 backend 2
---- --------- ---------
0 write()
1 sleep() write()
2 fsync() sleep()
3 fsync()

Which shows the second fsync() doing nothing, which is good, because
there are no dirty buffers at that time. However, a very possible
circumstance is:

time backend 1 backend 2 backend 3
---- --------- --------- ---------
0 write()
1 sleep() write()
2 fsync() sleep() write()
3 fsync() sleep()
4 fsync()

In this case, the fsync() by backend 2 does indeed do some work because
fsync's backend 3's write(). Frankly, I don't see how the sleep does
much except delay things because it doesn't have any smarts about when
the delay is useful, and when it is useless. Without that feedback, I
recommend removing the entire setting. For single backends, the sleep
is clearly a loser.

Another situation it can not deal with is:

time backend 1 backend 2
---- --------- ---------
0 write()
1 sleep()
2 fsync() write()
3 sleep()
4 fsync()

My solution can't deal with this either.

---------------------------------------------------------------------------

The quick fix is to remove the commit_delay code. A more elaborate
performance boost would be to have the each backend get feedback from
other backends, so they can block and wait for other about-to-fsync
backends before fsync(). This allows the write() to bunch up before
the fsync().

Here is the single backend case, which experiences no delays:

time backend 1 backend 2
---- --------- ---------
0 get_shlock()
1 write()
2 rel_shlock()
3 get_exlock()
4 rel_exlock()
5 fsync()

Here is the two-backend case, which shows both write()'s completing
before the fsync()'s:

time backend 1 backend 2
---- --------- ---------
0 get_shlock()
1 write()
2 rel_shlock() get_shlock()
3 get_exlock() write()
4 rel_shlock()
5 rel_exlock()
6 fsync() get_exlock()
7 rel_exlock()
8 fsync()

Contrast that with the first 2 backend case presented above:

time backend 1 backend 2
---- --------- ---------
0 write()
1 fysnc() write()
2 fsync()

Now, it is my understanding that instead of just shared locking around
the write()'s, we could block the entire commit code, so the backend can
signal to other about-to-fsync backends to wait.

I believe our existing lock code can be used for the locking/unlocking.
We can just lock a random, unused table log pg_log or something.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026