tl;dr: We've found that under many conditions, PostgreSQL's re-use of old WAL
files appears to significantly degrade query latency on ZFS. The reason is
complicated and I have details below. Has it been considered to make this
behavior tunable, to cause PostgreSQL to always create new WAL files instead of
re-using old ones?
Context: we're running a large fleet of PostgreSQL shards. Each shard consists
of a primary, a synchronous standby, and an asynchronous standby using chained
replication. For this problem, we can consider only the primary and
synchronous standby.
PostgreSQL: 9.6.3
OS: illumos (SmartOS, mixed versions, but all from 2017 or later)
FS: ZFS over mirrored HDDs (not SSDs), using a record size of 8K to match
PostgreSQL's record size. We have an SSD log device for completing synchronous
writes very quickly.
WAL files are 16MB each, and we're keeping a very large number of segments.
(There's likely a lot of improvement for WAL tuning here.)
Since we're using an 8K record size, when PostgreSQL writes small (or
non-aligned) records to the WAL files, ZFS has to read the old contents in
order to write the new 8K record (read-modify-write). If that's in cache,
that's not a big deal. But when PostgreSQL decides to reuse an old WAL file
whose contents have been evicted from the cache (because they haven't been used
in hours), this turns what should be a workload bottlenecked by synchronous write
performance (that's well-optimized with our SSD log device) into a random read
workload (that's much more expensive for any system).
What's significantly worse is that we saw this on synchronous standbys. When
that happened, the WAL receiver was blocked on a random read from disk, and
since it's single-threaded, all write queries on the primary stop until the
random read finishes. This is particularly bad for us when the sync is doing
other I/O (e.g., for an autovacuum or a database backup) that causes disk reads
to take hundreds of milliseconds.
Reusing old WAL files seems like an optimization intended for filesystems that
allocate disk blocks up front. With copy-on-write, it doesn't seem to make
much sense. If instead of using an old WAL file, PostgreSQL instead just
created a new one, there would be no random reads required to complete these
operations, and we believe we'd completely eliminate our latency outliers.
Thanks,
Dave
