Re: WAL Re-Writes

On 01/27/2016 08:30 AM, Amit Kapila wrote:
> operation. Now why OS couldn't find the corresponding block in
> memory is that, while closing the WAL file, we use
> POSIX_FADV_DONTNEED if wal_level is less than 'archive' which
> lead to this problem. So with this experiment, the conclusion is that
> though we can avoid re-write of WAL data by doing exact writes, but
> it could lead to significant reduction in TPS.

POSIX_FADV_DONTNEED isn't the only way how those blocks would vanish
from OS buffers. If I am not mistaken we recycle WAL segments in a round
robin fashion. In a properly configured system, where the reason for a
checkpoint is usually "time" rather than "xlog", a recycled WAL file
written to had been closed and not touched for about a complete
checkpoint_timeout or longer. You must have a really big amount of spare
RAM in the machine to still find those blocks in memory. Basically we
are talking about the active portion of your database, shared buffers,
the sum of all process local memory and the complete pg_xlog directory
content fitting into RAM.

Regards, Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info