Hi,

postgresql is about to use sync_file_range(SYNC_FILE_RANGE_WRITE) to control writeback more explicitly. It'd be cool if more OSs than just linux could benefit.

Some background:
Postgres regularly 'checkpoints' it's in-memory data to disk, to be able to remove older journalling/write ahead log data. In a database with a write heavy workload that can imply a lot of writes. At the end of the checkpoint postgres then fsync()s all the files.  This unfortunately often causes latency spikes because a) the fsyncs at the end might have to write back a lot of data, unnecessarily stalling other IO b) before the fsync a lot of dirty data might accumulate kernel-side, which then also can trigger latency spikes.  Often this also leads to irregular IO with periods of no IO.

What postgres is going to do on linux is to issue sync_file_range(SYNC_FILE_RANGE_WRITE) every few (32 seems to work well) blocks during the checkpoint. That makes it rather likely that there's little dirty data remaining when the fsync()s at the end are executed, making them fast. It also prevents large amounts of dirty buffers from accumulating.

We've considered some alternative approaches to this for other operating systems. For one there's posix_fadvise(POSIX_FADV_DONTNEED), but that does more than just writeout dirty data. I've also tried mmap();msync(MS_ASYNC);munmap(); - but at least on linux that doesn't do anything. Using MS_SYNC flushes to disk on linux, but it's synchronous, which isn't what we want here.


I find the sync_file_range() API to be rather useful - so I think it'd make sense to implement it. But baring that, could you possibly clarify somewhere public whether msync(MS_ASYNC) does what we'd need it to do on freebsd? I.e. initiate writeback, without blocking?


Regards,

Andres Freund