RE: PgSQL 12 on WinSrv ~3x faster than on Linux

> The first thing that comes to mind for me is fsync working correctly (i.e.
> actually waiting for the disk write) in Linux but not in Windows.
> On a weird VM stack like you've got, it's not hard for that sort of thing to go
> wrong. Needless to say, if that's the issue then the apparent performance
> win is coming at the cost of crash safety.
>
> pg_test_fsync might help detect such a problem.
>
> regards, tom lane
>

fsync performance on win is much better (results are below). Also network performance for VMs on same HV for win-win is 40% better than for win-linux (5,97Gbps vs 3,6Gbps).
Regarding weird VM stack - we're running both win and linux VMs and Hyper-V works reasonable well except... this issue )

Win:
C:\Program Files\PostgreSQL\12.6-6.1C\bin>pg_test_fsync.exe
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 7423,645 ops/sec 135 usecs/op
fdatasync n/a
fsync 1910,611 ops/sec 523 usecs/op
fsync_writethrough 1987,900 ops/sec 503 usecs/op
open_sync n/a

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 3827,254 ops/sec 261 usecs/op
fdatasync n/a
fsync 1920,720 ops/sec 521 usecs/op
fsync_writethrough 1863,852 ops/sec 537 usecs/op
open_sync n/a

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write n/a
2 * 8kB open_sync writes n/a
4 * 4kB open_sync writes n/a
8 * 2kB open_sync writes n/a
16 * 1kB open_sync writes n/a

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 144,065 ops/sec 6941 usecs/op
write, close, fsync 148,751 ops/sec 6723 usecs/op

Non-sync'ed 8kB writes:
write 165,484 ops/sec 6043 usecs/op

Linux:
[root(at)pgsql12 ~]# /usr/pgsql-12/bin/pg_test_fsync

5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 2947.296 ops/sec 339 usecs/op
fdatasync 2824.271 ops/sec 354 usecs/op
fsync 1885.924 ops/sec 530 usecs/op
fsync_writethrough n/a
open_sync 1816.312 ops/sec 551 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 1458.849 ops/sec 685 usecs/op
fdatasync 2712.756 ops/sec 369 usecs/op
fsync 1769.353 ops/sec 565 usecs/op
fsync_writethrough n/a
open_sync 902.626 ops/sec 1108 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 1798.811 ops/sec 556 usecs/op
2 * 8kB open_sync writes 887.727 ops/sec 1126 usecs/op
4 * 4kB open_sync writes 494.843 ops/sec 2021 usecs/op
8 * 2kB open_sync writes 233.659 ops/sec 4280 usecs/op
16 * 1kB open_sync writes 117.417 ops/sec 8517 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 1673.781 ops/sec 597 usecs/op
write, close, fsync 1727.787 ops/sec 579 usecs/op

Non-sync'ed 8kB writes:
write 200638.271 ops/sec 5 usecs/op

--
Taras