Re: Need to know more about pg_test_fsync utility

On Fri, Dec 10, 2021 at 3:20 PM PGSQL DBA <pgsqldba(dot)1987(at)gmail(dot)com> wrote:
> 1) How to interpret the output of pg_test_fsync?

The main interesting area is probably the top section that compares
the different wal_sync_method settings. For example, it's useful to
verify the claim that fdatasync() is faster than fsync() (because it
only flushes data, not meta-data like file modified time). It may
also be useful for measuring the effects of different caching settings
on your OS and storage. Unfortunately open_datasync is a bit
misleading; we don't actually use O_DIRECT with open_datasync anymore,
unless you set wal_level=minimal, which almost nobody ever does.

> 2) What is the meaning of ops/sec & usecs/op?

Number of times it managed to flush data to disk per second
sequentially, and the same information expressed as microseconds per
flush.

> 3) How does this utility work internally?

It just does a loop over some system calls, or to be more precise,

https://github.com/postgres/postgres/blob/master/src/bin/pg_test_fsync/pg_test_fsync.c

> 4) What is the IO pattern of this utility? serial/sequence IO or Multiple thread with Parallel IO?

Sequential, no threads.

> 5) Can we change the testing like FIO with multiple threads and parallel IO?

Nope. This is a simple tool. Fio is much more general and useful.

> 6) How a commit happened in the background while executing this utility?

Nothing happens in the background, it uses synchronous system calls
from one thread.

> 7) How can we use this tool to measure the I/O issue?

It's a type of micro-benchmark that gives you an idea of a sort of
baseline you can expect from a single PostgreSQL session committing to
the WAL.

> 8) In which area or section in the output do we need to focus while troubleshooting I/O issues?

If PostgreSQL couldn't commit small sequential transactions about that
fast I'd be interested in finding out why, and if fdatasync is
performing faster than published/device IOPS suggest should be
possible then I'd investigate whether data is being cached
unexpectedly, perhaps indicating that committed transactions be lost
in a system crash event.

> 9) What is the meaning of “Non-sync’ed 8kB writes?

Calling the pwrite() system call, which writes into your operating
system's page cache but (usually) doesn't wait for any I/O. Should be
somewhere north of 1 million/sec.