Re: FreeBSD UFS & fsync

On Thu, Mar 11, 2021 at 3:29 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> You should really be running pg_test_fsync for this kind of testing.
>

Sorry Bruce, but it is not clear to me: pg_test_fsync compares
different fsync implementations, but not the fsync on/off setting of a
cluster.

Now, pg_test_fsync reports the "non synced writes", which are
effectively 15x faster (that is near to what I was expecting turning
off fsync):

% 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 n/a
fdatasync 16269.365 ops/sec 61 usecs/op
fsync 8471.429 ops/sec 118 usecs/op
fsync_writethrough n/a
open_sync 5664.861 ops/sec 177 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync n/a
fdatasync 15196.244 ops/sec 66 usecs/op
fsync 7754.729 ops/sec 129 usecs/op
fsync_writethrough n/a
open_sync 2670.645 ops/sec 374 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 5486.140 ops/sec 182 usecs/op
2 * 8kB open_sync writes 2344.310 ops/sec 427 usecs/op
4 * 4kB open_sync writes 1323.548 ops/sec 756 usecs/op
8 * 2kB open_sync writes 659.449 ops/sec 1516 usecs/op
16 * 1kB open_sync writes 332.844 ops/sec 3004 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 7515.006 ops/sec 133 usecs/op
write, close, fsync 7107.698 ops/sec 141 usecs/op

Non-sync'ed 8kB writes:
write 278484.510 ops/sec 4 usecs/op

However, these are not results I'm getting via pgbench.

% sudo -u postgres postgres -C fsync -D /postgres/12/data
on
% sudo -u postgres postgres -C checkpoint_timeout -D /postgres/12/data
30
% pgbench -T 60 -c 4 -r -n -U luca pgbench
...
number of transactions actually processed: 7347
latency average = 32.947 ms
tps = 121.405308 (including connections establishing)
tps = 121.429075 (excluding connections establishing)

% sudo -u postgres postgres -C checkpoint_timeout -D /postgres/12/data
30
% sudo -u postgres postgres -C fsync -D /postgres/12/data
off
% pgbench -T 60 -c 4 -r -n -U luca pgbench
...
number of transactions actually processed: 8220
latency average = 29.212 ms
tps = 136.929481 (including connections establishing)
tps = 136.963971 (excluding connections establishing)

Of course, the above test is really quick (and covers at least one
checkpoint), but event longer tests provide similar results, that are
somehow in contrast with the pg_test_fsync result.
However, apparently the problem is not related to disck cache, since
pg_test_fsync reports correct times (as far as I understand).
Am I missing something?

Luca