[September 2026] OpenZFS Wiki - Workload Tuning Guidelines Changed? How Up to Date is This?

[SinisterPisces]

A google search happened to land me in the Workload Tuning section of the OpenZFS wiki, which I generally avoid because I don’t know how up-to-date it is, and it’s generally geared towards we-do-this-for-money micro-optimizations for people who really know what they’re doing in production. It can be confusing and it’s not always clear whether certain recommendations are assuming you’re using ZFS in a way that volbocksize or recordsize is the controlling attribute.

Anyway, I don’t remember the recommendations for databases being this complex: Workload Tuning — OpenZFS documentation

MySQL

InnoDB

Make separate datasets for InnoDB’s data files and log files. Set recordsize=16K on InnoDB’s data files to avoid expensive partial record writes and leave recordsize=128K on the log files. Set primarycache=metadata on both to prefer InnoDB’s caching 4. Set logbias=throughput on the data to stop ZIL from writing twice.

Set innodb_doublewrite=0 in my.cnf to prevent innodb from writing twice. The double writes are a data integrity feature meant to protect against corruption from partially-written records, but those are not possible on ZFS. It should be noted that Percona’s blog had advocated using an ext4 configuration where double writes were turned off for a performance gain, but later recanted it because it caused data corruption. Following a well timed power failure, an in place filesystem such as ext4 can have half of a 8KB record be old while the other half would be new. This would be the corruption that caused Percona to recant its advice. However, ZFS’ copy on write design would cause it to return the old correct data following a power failure (no matter what the timing is). That prevents the corruption that the double write feature is intended to prevent from ever happening. The double write feature is therefore unnecessary on ZFS and can be safely turned off for better performance.

On Linux, the driver’s AIO implementation is a compatibility shim that just barely passes the POSIX standard. InnoDB performance suffers when using its default AIO codepath. Set innodb_use_native_aio=0 and innodb_use_atomic_writes=0 in my.cnf to disable AIO. Both of these settings must be disabled to disable AIO.

There’s a lot more there for InnoDB than I remember, and I’ve also been advised by more than one professional ZFS-based DB admin to use recordsize=1M for the log files.

While I’m interested in how up-to-date those recommendations (and the recommendations for SQL Lite and other specific workloads) are, it’s the Postgres rec that caught my attention.

PostgreSQL

Make separate datasets for PostgreSQL’s data and WAL. Set compression=lz4 and recordsize=32K (64K also work well, as does the 128K default) on both. Previous advice before compression=lz4 was to to set recordsize=8K to match PostgreSQL’s 8K page size to avoid write amplification, but compression often causes even bigger performance improvements 1, and compression is only effective when given larger recordesize.

Configure full_page_writes = off for PostgreSQL, as ZFS will never commit a partial write. But do not do this if you set recordsize < 8K for some reason, or if there is any chance that somebody may copy this database config to a non-ZFS system, as PostgreSQL can end up with corrupt data on power loss if the filesystem does not provide at least Postgres-page-sized atomic writes 2.

For a database with large updates, experiment with logbias=throughput on PostgreSQL’s data to avoid writing twice, but be aware that with this setting smaller updates can cause severe fragmentation.

I’ve been to the Workload Tuning page multiple times (and ran away each time because it’s clearly written for people who know much more about micro-optimizing ZFS than I do and it confused the crap out of me–I’m much more confident with the advice and mentoring I’ve gotten here ).

But I don’t ever remember seeing that bolded part.

LZ4, compression ON has been the default in every install of ZFS I’ve used (Proxmox and TrueNAS) since at least 2020, and I’ve always seen guides say, with no ambiguity, to store Postgres database data with recordsize=8k, full stop. That’s also the advice I’ve seen repeated here. I don’t ever recall seeing LZ4 mentioned as a reason to deviate from that.

So, a couple of questions.

1. In 2026, How trustworthy/worthwhile is the advice on the Workload Tuning page in general? I still feel like it’s very overcomplicated. And the advice there for BitTorrent, for example, doesn’t match the advice here at all: ZFS Performance Tuning for BitTorrent
2. Is the Postgres advice in particular legit? (Or at least, is it worth paying attention to at the consumer/hobbyist/homelab ZFS level?)

[mercenary_sysadmin]

I typically advise recordsize=16K for postgres, and always advise compression lz4 for database workload unless there’s something REALLY odd going on with how the DB is being used (eg storing multi-MiB blobs as records).

Doubling the page size is frequently a good match for many DB workloads even in the absence of compression; it depends on the actual DB workload but in a lot of cases the DB is very rarely reading or writing single pages at a time; many queries and updates read or write clusters of a couple or handful of pages at a time.

32K is often good for innodb for this same reason.

The big difference between directly advising to double the page size on innodb whereas innodb usually gets initial advice to match instead of doubling is because the page size is SO crazy small on postgres that there isn’t enough room to get decent compression.

With recordsize=8K and ashift=12 (4KiB sectors) a block can only be compressed by a single sector (50%), and that can only happen if the compression algorithm can GET 50% compression or better on that block logically. There also just plain isn’t as much data to try to find compressible patterns in, in the first place.

With recordsize=16K, a block may be compressed to 75%, 50%, or 25%. So you have a lot more chances for compression, you can compress more tightly, AND you have more data in each block to try to find compressible patterns in in the first place.

Expand that again to 32K, and now you can potentially compress that block to seven different levels.

So what you’re doing here is looking for the sweet spot between compressibility–which trades a bit of CPU work for reducing storage workload, which is usually a good answer because the CPU is a lot faster than most storage–and avoiding read amplification.

This all boils down to “for databases, you generally want to either match the page size or double it. Which of the two is better depends on the specific application workload the DB engine is doing.” And since postgres’ page size is so punishingly small to start with, we’re biased a lot further toward doubling the page size than matching it.

The more complex advice in there about multiple datasets and messing with logbias and so forth is generally accurate, but really isn’t necessary for the vast majority of applications in my experience. Essentially, I wouldn’t advise screwing around with those tunables unless and until you discover that you really need to do so. At which point to begin carefully experimenting, and those recommendations are where you begin your careful experiments and testing.