Re: WALWriteLock contention

On Fri, May 15, 2015 at 9:15 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> I implemented this 2-3 years ago, just dropping the WALWriteLock immediately
> before the fsync and then picking it up again immediately after, and was
> surprised that I saw absolutely no improvement.  Of course it surely depends
> on the IO stack, but from what I saw it seemed that once a fsync landed in
> the kernel, any future ones on that file were blocked rather than
> consolidated.

Interesting.

> Alas I can't find the patch anymore, I can make more of an
> effort to dig it up if anyone cares.  Although it would probably be easier
> to reimplement it than it would be to find it and rebase it.
>
> I vaguely recall thinking that the post-fsync bookkeeping could be moved to
> a spin lock, with a fair bit of work, so that the WALWriteLock would not
> need to be picked up again, but the whole avenue didn't seem promising
> enough for me to worry about that part in detail.
>
> My goal there was to further improve group commit.  When running pgbench
> -j10 -c10, it was common to see fsyncs that alternated between flushing 1
> transaction, and 9 transactions. Because the first one to the gate would go
> through it and slam it on all the others, and it would take one fsync cycle
> for it reopen.

Hmm, yeah.  I remember somewhat (Peter Geoghegan, I think) mentioning
behavior like that before, but I had not made the connection to this
issue at that time.  This blog post is pretty depressing:

http://oldblog.antirez.com/post/fsync-different-thread-useless.html

It suggests that an fsync in progress blocks out not only other
fsyncs, but other writes to the same file, which for our purposes is
just awful.  More Googling around reveals that this is apparently
well-known to Linux kernel developers and that they don't seem excited
about fixing it.  :-(

<crazy-idea>I wonder if we could write WAL to two different files in
alternation, so that we could be writing to one file which fsync-ing
the other.</crazy-idea>

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company