3 ms·

sync_file_range(2) allows you to specify ranges to sync, instead of the whole file, but it's not that useful anyway. In most DBs, you have separate data and lo

sync_file_range(2) allows you to specify ranges to sync, instead of the whole file, but it's not that useful anyway. In most DBs, you have separate data and log. The log is always only appended to, and the nature of disks is such that grouping concurrent transactions together into a single commit is a big win regardless of how syncing works. So syncing all outstanding I/O to the log file is generally what you want to do anyway. While the data store is always being written to, its writes do not need to be synced to disk until a log checkpoint is made (and the old log subsequently discarded). And since that is an intermittent asynchronous process, there is not much to gain from finer-grained syncing here either. See e.g. http://www.postgresql.org/message-id/4A51CB76.5020407@enterprisedb.com http://www.postgresql.org/message-id/4A51CB76.5020407@enterp...

anarazel 11y ago
Actually, we made postgres use sync_file_range() for checkpointing if available in 9.6. Not for durability - there's a few to many caveats in the manpage - but to control how much work a later fsync() has to do. In many workloads checkpointing can generate a lot of writes, and the OS's writeback caching of those can generate a lot of dirty buffers in the kernel's page cache. If the kernel decides to flush those (on it's own or due to an fsync), latency for every other FS operation can skyrocket. We've seen stalls in the 10s of minutes. So we now regularly sync_file_range(SYNC_FILE_RANGE_WRITE), unless the feature is disabled of course, to control how much dirty data the kernel has. See http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b1d6b29ca599c5700d4bc4f4ce4c5880369bf http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;... and http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9cd00c457e6a1ebb984167ac556a9961812a683c http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...
- colanderman 11y ago
  Thanks for the info, as a heavy user of Postgres it's great to know that option will be available!

anarazel 11y ago

Actually, we made postgres use sync_file_range() for checkpointing if available in 9.6. Not for durability - there's a few to many caveats in the manpage - but to control how much work a later fsync() has to do. In many workloads checkpointing can generate a lot of writes, and the OS's writeback caching of those can generate a lot of dirty buffers in the kernel's page cache. If the kernel decides to flush those (on it's own or due to an fsync), latency for every other FS operation can skyrocket. We've seen stalls in the 10s of minutes. So we now regularly sync_file_range(SYNC_FILE_RANGE_WRITE), unless the feature is disabled of course, to control how much dirty data the kernel has. See http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b1d6b29ca599c5700d4bc4f4ce4c5880369bf http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;... and http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9cd00c457e6a1ebb984167ac556a9961812a683c http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...

colanderman 11y ago
Thanks for the info, as a heavy user of Postgres it's great to know that option will be available!