Using Postgres pg_test_fsync tool for testing low latency writes

Summary

Here’s a useful tool for quickly testing whether a disk (or a cloud block store volume) is a good candidate for your database WAL/redo logs and any other files that require low latency writes. The pg_test_fsync tool is bundled with standard Postgres packages, so no extra installation is needed. You don’t actually have to use Postgres as your database, this tool’s output is universally valuable for any workload requiring fast writes. First, I’ll show the disks attached to my test server, using my new lsds tool. You can scroll right to see the full output: $ lsds -a FUA,HWSEC DEVNAME MAJ:MIN SIZE TYPE SCHED ROT MODEL QDEPTH NR_RQ WCACHE FUA HWSEC nvme0n1 259:1 186.3 GiB NVMeDisk none 0 Micron_7400_MTFDKBA960TDZ - 1023 write through 0 4096 nvme0n2 259:6 200.0 GiB NVMeDisk none 0 Micron_7400_MTFDKBA960TDZ - 1023 write through 0 4096 nvme1n1 259:0 1863.0 GiB NVMeDisk none 0 Samsung SSD 990 PRO 2TB - 1023 write back 1 512 nvme2n1 259:2 260.8 GiB NVMeDisk none 0 INTEL SSDPED1D280GA - 1023 write through 0 512 sda 8:0 3726.0 GiB Disk mq-deadline 1 P9233 30 60 write back 0 4096 sdb 8:16 3726.0 GiB Disk mq-deadline 1 P9233 30 60 write back 0 4096 sdc 8:32 3726.0 GiB Disk mq-deadline 1 P9233 30 60 write back 0 4096 sdd 8:48 3726.0 GiB Disk mq-deadline 1 P9233 30 60 write back 0 4096 I’ll first run a test on my consumer-grade Samsung 990 Pro locally attached NVMe SSD (/dev/nvme1n1): $ mount | grep /data /dev/nvme1n1 on /data type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota) $ cd /data $ /usr/lib/postgresql/16/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 249.578 ops/sec 4007 usecs/op fdatasync 608.573 ops/sec 1643 usecs/op fsync 177.431 ops/sec 5636 usecs/op fsync_writethrough n/a open_sync 185.095 ops/sec 5403 usecs/op Compare file sync methods using two 8kB writes: (...