Key Article Takeaways
- Four ZFS storage metrics help diagnose database performance: sync write latency, ARC demand hit ratio, I/O amplification, and queue depth.
- Sync write latency sets a floor on database commit latency because a transaction cannot complete until its write-ahead or redo log reaches stable storage.
- ARC demand hit ratio shows how often reads are served from RAM, while I/O amplification can reveal a mismatch between database page size and ZFS recordsize.
- Queue depth shows where I/O requests are waiting and helps determine whether storage media, pool layout, or something above the filesystem is limiting performance.
A modern monitoring stack will happily graph fifty storage metrics for you, but it can’t tell you which ones are the important ones. For a database running on ZFS, there are four of them that decide nearly everything, and the rest are commentary and flavour. This article is the reference card: the four metrics to look at, why each one maps directly to something your database feels, how to read them, and what to do when they're bad. Previously, we walked the full diagnosis: storage versus RAM versus CPU. This is the companion you keep close at hand, focused on the two databases we see most in production, PostgreSQL and MySQL/MariaDB.
Metric 1: Sync Write Latency
Every committed transaction ends the same way: an fsync on the write-ahead log. PostgreSQL's WAL, InnoDB's redo log: it makes no difference. The database cannot acknowledge the commit until ZFS reports the record is on stable storage and will survive an unexpected reboot. That makes your pool's sync write latency a hard floor under commit latency. No query tuning, optimization, or black magic can get your query latency below that floor.
$ zpool iostat -yw tank 5 # latency histograms; watch the sync write column
$ zpool iostat -yv tank 5 # per-vdev; watch the log device line under load
When dealing with latency metrics, reach for a histogram, not just an average. Queries have a strict order, so a single one being slow can have an outsized impact on all future queries. Commits ride the p99, so a clean mean with a tail in the tens or hundreds of milliseconds is exactly the pattern that users will report as "the database stalls sometimes". If 75% of your queries are fine, the average is going to look good and hide the real problem. Cross-check from the database side as well: PostgreSQL's pg_stat_wal tracks WAL sync time directly, and InnoDB reports log waits in SHOW ENGINE INNODB STATUS.
The troubleshooting checklist for this is pretty short. If there is no dedicated log device, sync writes are competing with everything else on the main disks, and a single power-loss-protected NVMe SLOG is the easy fix. If you already have a special vdev, updating to OpenZFS 2.4 allows you to leverage your existing flash as an embedded special log. If there is a SLOG and the latency tail persists, verify the device actually has power-loss protection. Consumer NVMe are well known for lying about flushes, which is worse than no SLOG at all, since it results in lost data. What you must never do is set sync=disabled to make the number improve. It improves because ZFS starts acknowledging writes it hasn't persisted, lying to the database engine, and your last few seconds of committed transactions become a bet on system stability and the power grid.
Metric 2: ARC Demand Hit Ratio
On the read side, there is one number that matters more than the rest:
FreeBSD:
$ sudo pkg install zfs-stats
$ zfs-mon -a 5 # watch the ARC demand % columns under real load
Linux:
$ arcstat 5 # watch the demand hit columns under real load
A database reading at a 99% demand hit ratio is effectively running from RAM. Whereas at 70%, a third of reads pay the full latency of disk access. There are two important practitioner notes when reading these stats. First, read the trend under load, not the average since boot. Totals and long-term averages smooth over the Monday-morning working-set shift that is actually hurting you. Second, remember there are two caches in this stack, the ARC and the database's own buffer pool, and they contain much of the same data. Size them as a pair, and see the per-database sections below, because the right split differs between PostgreSQL and InnoDB, and is highly workload-dependent.
If the ratio is low and the ARC is already at its target size, that is a RAM conversation, not a tuning one. If the ratio is low and the ARC is small, find what's capping it before buying anything. And resist bolting on an L2ARC device before RAM is maxed: headers pointing data to the L2ARC consume the very ARC you're short on, and on a RAM-starved system the "cache upgrade" makes the problem worse.
Metric 3: I/O Amplification
Databases do small, fixed-size I/O: PostgreSQL defaults to 8K pages, InnoDB to 16K pages. ZFS reads and writes whole records. When the dataset's recordsize is the 128K default, a 16K page update can turn into a 128K read, followed by an in-memory modify, and then a full 128K write, and your storage does sixteen times the work your database asked for.
$ zpool iostat -yr tank 5 # request-size histograms
$ zfs get recordsize,compressratio tank/db
The request-size histogram makes amplification visible: your database issues 8K and 16K operations, and if the pool is moving mostly 128K, the mismatch is right there in those columns. The fix is adjusting the per-dataset recordsize, set to the values in the database sections below, with one important caveat: recordsize applies to newly created files only, so after changing it, existing data must be rewritten to benefit. The compressratio rides along for free: lz4 typically pays for itself on database data, and a ratio near 1.0x on supposedly compressible data is its own clue that something upstream is already compressing or encrypting, or that the small recordsize is killing compression efficiency.
Metric 4: Queue Depth
When latency is bad, this metric tells you whether the disks are slow or just overwhelmed:
$ zpool iostat -yq tank 5 # queue depths: where requests are waiting
Deep queues with busy disks mean the pool is genuinely out of IOPS: faster media or more vdevs are the only answer. Deep queues while individual disks sit half-idle means the layout is the constraint, and for databases that is almost always RAIDZ doing what RAIDZ does: a RAIDZ vdev delivers roughly one disk's worth of random I/O regardless of width. Databases want mirrors. If the queues are shallow and latency is still bad, stop looking at storage; the problem is above the filesystem.
PostgreSQL on ZFS
The OpenZFS project's own tuning guidance for PostgreSQL is refreshingly specific, and it matches what we recommend our customers deploy:
$ zfs create -o recordsize=32K -o compression=lz4 tank/pgdata
$ zfs create -o recordsize=32K -o compression=lz4 tank/pgwal
Then in postgresql.conf, set full_page_writes = off. PostgreSQL writes full pages after checkpoints to defend against torn pages on power loss; ZFS never commits a partial write, so the defence is redundant and turning it off removes a large source of WAL volume. That single setting is the biggest ZFS-specific PostgreSQL win, and it is only safe because of copy-on-write.
Leave primarycache=all for PostgreSQL. Its shared_buffers is typically a modest slice of RAM, and it leans on the OS cache by design, which on ZFS means the ARC. Modest shared_buffers, generous ARC, and watch Metric 2. An optional experiment you might try is setting logbias=throughput on the data dataset, which avoids writing data through the ZIL, before then writing it to the main disk. If you don’t have a dedicated LOG device, this setting can trade a small amount of additional latency for halving the amount of writes to the device. The impact is very workload-specific, so measure it against your real-world applications before putting it anywhere near production.
MySQL and MariaDB on ZFS
InnoDB wants the opposite arrangement: it does its own caching well, and its page size is fixed at 16K.
$ zfs create -o recordsize=16K -o primarycache=metadata tank/mysql-data
$ zfs create -o recordsize=128K tank/mysql-logs
Then in my.cnf: innodb_doublewrite=0, because the doublewrite buffer exists to defend against torn pages, and ZFS makes torn pages impossible, so InnoDB is paying double writes for protection it already has. Disable AIO with innodb_use_native_aio=0 and innodb_use_atomic_writes=0, both required on ZFS. Give InnoDB a large buffer pool, since primarycache=metadata deliberately hands it the caching job, and monitor Metric 2 knowing that the database data isn’t in the ARC, only the metadata.
The Habit
Build the habit of watching these four metrics, one command for each: zpool iostat -w, arcstat, zpool iostat -r, zpool iostat -q. Run them for one quiet hour and keep the output, then run them again during your heaviest known load, a checkpoint, a batch import, the Monday open, and keep that too. Those two baselines turn your next incident from an exercise in archaeology to a simple comparison. And when the numbers say the layout or the media is genuinely the limit, you'll be spending money on the metric that proves it, which is the whole point of watching them.
Monitoring the right ZFS metrics is only part of maintaining database performance. Klara’s Quarterly ZFS Health Check reviews your pool configuration, recordsize, compression, ARC sizing, and other settings against your actual workloads. Our engineers provide a written report with recommendations ranked by risk and track changes quarter over quarter to identify potential issues before they affect production.
