Using Postgres pg_test_fsync tool for testing low latency writes
17 comments
·May 28, 2025singron
Note that this workload is a worst case for iops and you will get higher iops in nearly any optimized workload. E.g. postgres needs to sync the WAL in order to commit (which does look like this test), but there are a ton of other writes that happen in parallel on the heap and index pages in addition to any reading you do. IME the consumer drives that benchmark at 500K iops and get only 500 iops on this test might get 10K or 20K iops on a more typical mixed workload.
tanelpoder
Throughput with enough I/O concurrency, yes. That's actually why I wrote this blog entry, just to bring attention to this - having nice IOPS numbers do not translate to nice individual I/O latency numbers. If an individual WAL write takes ~1.5 ms (instead of tens of microseconds), this means that your app transactions also take 1.5+ ms and not sub-millisecond. Not everyone cares about this (and often don't even need to care about this), but worth being aware of.
I tend to set up a small, but completely separate block device (usually on enterprise SAN storage or cloud block store) just for WAL/redo logs to have a different device with its own queue for that. So that when that big database checkpoint or fsync happens against datafiles, the thousands of concurrently submitted IO requests won't get in the way of WAL writes that still need to complete fast. I've done something similar in the past with separate filesystem journal devices too (for niche use cases...)
Edit: Another use case for this is that ZFS users can put the ZIL on low-latency devices, while keeping the main storage on lower cost devices.
natmaka
> I tend to set up a small, but completely separate block device (usually on enterprise SAN storage or cloud block store) just for WAL/redo logs
I'm not sure about this, as this separate device may handle more of the total (aggregated) work by being a member of an unique pool (RAID made of all available non-spare devices) used by the PostgreSQL server.
It seems to me that in most cases the most efficient setup, even when trying hard to reduce the maximal latency (and therefore to sacrifice some throughput), is an unique pool AND an adequate I/O scheduling enforcing a "max latency" parameter.
If, during peaks of activity, your WAL-dedicated device isn't permanently at 100% usage while the data pool is, then dedicating it may (overall) bump up the max latency and reduce throughput.
Tweaking some parameters (bgwriter, full_page_writes, wal_compression, wal_writer_delay, max_wal_senders, wal_level, wal_buffers, wal_init_zero...) with respect to the usage profile (max tolerated latency, OLTP, OLAP, proportion of SELECTs and INSERTs/UPDATEs, I/O subsystem characteristics and performance, kernel parameters...) is key.
tanelpoder
When doing 1M+ IOPS, you probably do not want to use OS IO schedulers due to the OS (timer & spinlock) overhead [1] and let the hardware take care of any scheduling in their device queues. But you're right about flattening the IO burst spikes via DB configuration, so that you'd have constant slow checkpointing going on, instead of a huge spike every 15 minutes...
All this depends on what kind of storage backend you're on, local consumer SSDs with just one NVMe namespace each, or local SSDs with multiple namespaces (with their own queues) or a full-blown enterprise storage backend where you have no idea what's really going in the backend :-)
[1]: https://tanelpoder.com/posts/11m-iops-with-10-ssds-on-amd-th...
Edit: Note that I wasn't proposing using an entire physical disk device (or multiple) for the low latency files, but just a part of it. Local enterprise-grade SSDs support multiple namespaces (with their own internal queues) so you can carve out just 1% of that for separate I/O processing. And with enterprise SAN arrays (or cloud elastic block store offerings) this works too, you don't know how many physical disks are involved in the backend anyway, but at your host OS level, you get a separate IO queue that is not gonna be full of thousands of checkpoint writes.
CodesInChaos
Could you run this on network block storage like EBS? I assume those have pretty high latency as well, even with high IOPS volumes?
tanelpoder
I'll see if I have a chance to run such a test on AWS in coming days (and would need to keep running it for much longer than just 5 seconds shown in the blog).
If you care about WAL write/commit latency, you could provision a small-ish EBS io2 Block Express device (with provisioned IOPS) just for your WAL files and the rest of your data can still reside on cheaper EBS storage. And you might not even need to hugely overprovision your WAL device IOPS (as databases can batch commit writes for multiple transactions).
But the main point is that once your WAL files are on a completely separate blockdevice from all the other datafile I/O, they won't suffer from various read & write IO bursts that can happen during regular database activity. On Oracle databases, I put controlfiles to these separate devices too, as they are on the critical path during redo log switches...
CodesInChaos
How do you backup those split volumes? I like EBS volume snapshots, since they're atomic, incremental, fast to restore and make it easy to spin up a clone. But obviously that approach won't work for split volumes.
tanelpoder
Yep indeed, that's a tradeoff, if your DB fits into a single volume. I'm not that deeply familiar with databases other than Oracle (that has its own ways to work work around this), so for ease of use, everything on a single volume keeps things simpler.
One thing that I try to achieve anyway, is to spread & "smoothen" the database checkpoint & fsync activity over time via database checkpointing parameters, so you won't have huge "IO storms" every 15 minutes, but just steady writing of dirty buffers going on all the time. So, even if all your files are stored on the same blockdevice, you'll less likely see a case where your WAL writes wait behind 50000 checkpoint write requests issued just before.
dboreham
Aha! Useful article because I somehow never knew about pg_test_fsync until now. I wrote and maintained a similar tool long ago before open source times, when I worked on database storage engines. Several times since I've wished I still had that tool. Now I do. Excellent.
CoolCold
fio, used like
fio --rw=write --ioengine=sync --fdatasync=1 --directory=test-data --size=22m --bs=2300 --name=mytest
blocksocial130
[dead]
The numbers in the post highlight an issue I have with Samsung consumer SSDs - they've slowed down FUA writes to an absurd degree.
open_datasync (i.e. O_DSYNC) ends up as FUA writes, fdatasync() as a plain write followed by a cache flush.On just about anything else a single FUA write is either the same speed as a write + fdatasync, or considerably faster.
This is pretty annoying, as using O_DSYNC is a lot more suitable for concurrent WAL writes, but because Samsung SSDs are widespread, changing the default would regress performance substantially for a good number of users.