Re: Performance improvement of WAL writing?

On Wed, Aug 28, 2019 at 2:43 AM Moon, Insung <tsukiwamoon(dot)pgsql(at)gmail(dot)com>
wrote:

> So what about modifying the XLogWrite function only to write the size
> that should record?
> Can this idea benefit from WAL writing performance?
> If it's OK to improve, I want to do modification.
> How do you think of it?
>

I have performed tests in this direction several years ago.

The way it works now guarantees that the data of recycled WAL segments is
never read from disk, as long as the WAL block size is a multiple of the
filesystem's page/fragment size. The OS sees that the write() is on a
fragment boundary and full fragment(s) in size. If the write() would be
smaller in size, the OS would be forced to combine the new data with the
rest of the fragment's old data on disk. To do so the system now has to
wait until the old fragment is actually in the OS buffer. Which means that
once you have enough WAL segments to cycle through so that the checkpoint
reason is never XLOG and the blocks of the WAL segment that is cycled in
have been evicted since it was last used, this causes real reads. On
spinning media, which are still an excellent choice for WAL, this turns
into a total disaster because it adds a rotational delay for every single
WAL block that is only partially overwritten.

I believe that this could only work if we stopped recycling WAL segments
and instead delete old segments, then create new files as needed. This
however adds the overhead of constant metadata change to WAL writing and I
have no idea what performance or reliability impact that may have. There
were reasons we chose to implement WAL segment recycling many years ago.
These reasons may no longer be valid on modern filesystems, but it
definitely is not a performance question alone.

Regards, Jan

--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info