Key Article Takeaways
- Start with the database's own view before touching ZFS tools. Slow commits point to the sync write path; slow reads point to cache misses.
- Diagnose storage with latency histograms and per-device iostat, not averages. Check recordsize first: it's free to fix and the most common finding in performance audits.
- Measure RAM with the ARC's demand hit ratio via arcstat. A 99% hit ratio means the database isn't RAM-starved, no matter what the vendor quote implies.
- Spend in order of leverage per dollar: free tuning, then RAM, SLOG, layout changes, and finally more disks. Buying more hardware before fixing configuration can leave an NVMe pool slower than a tuned SATA pool.
When a database running on ZFS slows down, there are three prime suspects, and the cost to fix them varies wildly. More disks cost tens or hundreds of dollars per terabyte and require a maintenance window. More RAM costs tens of dollars per gigabyte and a reboot. More CPU usually means a new server. Guessing wrong doesn't just waste the money you spent; it leaves the actual bottleneck in place, which is how teams end up buying all three over eighteen months and still dealing with latency issues. Hardware prices being what they are, the best choice you'll ever make is the measurement that tells you which one you actually need to buy.
So, let's diagnose the problem properly. Start from the latency the database experiences, then walk down the stack until you find the queue and who is to blame for it.
First, Establish What the Database Sees
Before touching ZFS tooling, get the database's own read on the problem. PostgreSQL's pg_stat_io and slow-query logs, or MySQL's performance schema: if the database says commits are slow, you're hunting for problems in the sync write path. If reads are slow, you're hunting for cache misses and evictions. That single distinction cuts the investigation search space in half, because ZFS treats those two paths entirely differently.
Suspect One: Storage
Start with the latency histograms, not the averages. Where are the outliers?
$ zpool iostat -yw tank 5 # latency distribution per vdev
$ zpool iostat -yq tank 5 # queue depths: where requests wait
$ zpool iostat -yv tank 5 # per-device: find the one slow disk
Averages are lies. A pool doing most reads in 200 microseconds with a tail at 4 seconds shows a tolerable mean and an intolerably slow database. A histogram exposes the tail that is hidden by averages. Inspecting the queues can tell you where things are getting stuck, and the per-device view shows whether it's one dying disk that is dragging the entire pool down (one device's numbers far worse than its siblings) or a structural problem (every device equally busy).
There are two main structural patterns that dominate database performance. First is sync write pressure: each database transaction has to wait on commits to the ZIL to ensure the data is safely on disk before continuing. If there is no dedicated log device, they wait on the main pool disks, which might have high latency and are often very busy. If you have no SLOG and ZIL commits are what are slowing you down, a single power-loss-protected NVMe device is the highest-leverage solution that you can get, and it’ll only cost you a few hundred dollars. Second is IOPS starvation: a RAIDZ vdev delivers roughly one disk worth of random I/O no matter how wide it is. If the queue depths are high while individual disks are not saturated, the layout is the bottleneck, and no single purchase fixes that. You are looking at switching to mirrored vdevs, or adding more vdevs.
But before spending anything, check the things that are free to fix first: recordsize. A 16K-page database on a 128K dataset multiplies its own I/O by up to 8x. We covered this in Misconfigurations Part 1, but it remains the most common finding in our performance audits, and it costs nothing but a rewrite to fix it.
Suspect Two: RAM
The ARC is the reason ZFS databases feel fast, using your available RAM as the fastest storage media and leveraging ZFS compression to maximize the benefits provided. You can measure the effectiveness of your ARC with a single number: demand data hit percentage:
$ arcstat 5
time read ddread ddh% dmread dmh% pread size c
12:00:01 8.2K 7.9K 99 310 62 0 61G 62G
The key metrics to look at are ddh% (Demand Data Hit Percentage) and dmh% (Demand Metadata Hit Percentage). These tell you how often, when a user or application asked ZFS for some data, ZFS already had that data in the ARC and was able to deliver it immediately without having to read it from disk.
A database with a 99% demand hit ratio is not RAM-starved, no matter what the vendor quote says. A ddh% of 70% means you are doing a third of reads from disk, and every point of hit ratio you buy back is latency the storage no longer has to serve, and your workload no longer has to suffer.
The economics here are the friendliest in the stack: RAM is the cheapest latency you can buy, right up until the working set fits entirely in memory, after which additional RAM buys nothing at all. arc_summary's ARC size versus target, plus the hit ratio trend as load varies, tells you which side of that line you're on. Two big cautions to keep in mind: don't cap the ARC to "leave room" for a database so it can cache the same data a second time, or worse, do its reads from disk; instead, tune the two caches as a pair. If the database compresses well, there is a lot to be gained from letting ZFS handle the caching and have the database get out of the way. If the database has a large buffer pool and uses it well, consider setting primarycache=metadata for its datasets so the same pages aren't cached twice. Don't reach for L2ARC before RAM is maxed out. Cache-device headers consume the very ARC you're short on; Part 2 of Misconfigurations has the details.
Suspect Three: CPU
ZFS spends CPU on real work: compression, checksums, and encryption. Usually that trade-off is beneficial (lz4 often makes workloads faster, since fewer bytes touch disk). But it has to be checked, because a saturated CPU shows up as often unpredictable I/O latency and sends you shopping in the wrong aisle:
$ top -SH # FreeBSD: look for z_wr_iss / z_rd_int threads
$ zfs get compression,compressratio tank/db
The giveaway that something is wrong is high system time concentrated in ZFS worker threads while the disks are underworked. The usual causes are: zstd at an overly ambitious level (zstd-9 costs a lot more than the default zstd-3 for only a few percentage points more compression ratio), and encryption without AES-NI acceleration in play. The fixes are mostly free: dial the compression type and level to suit your workload and available CPU headroom. Then confirm the crypto path is being accelerated correctly for your hardware, and the CPU you already paid for goes back to doing useful work.
Going Deeper: Three Tools for Those Stubborn Cases
When the first pass doesn't solve it, ZFS can reveal more of its secrets if you ask it precisely.
Request-size histograms can expose amplification: zpool iostat -yr tank 5 breaks I/O down by size, so a database issuing 16K reads that the pool serves as 128K I/Os stops being a theory and becomes a column of numbers. The raw ARC counters live in sysctl kstat.zfs.misc.arcstats (FreeBSD) or /proc/spl/kstat/zfs/arcstats (Linux), and the interesting ones under pressure are the eviction and throttle counters: memory_throttle_count ticking up means the ARC is being squeezed by the rest of the system, which no amount of pool tuning fixes. And on FreeBSD, DTrace closes the loop between a slow query and a physical disk: a one-liner on the io provider aggregating latency by file and answers "is it my datafile or my WAL" in thirty seconds, live, in production, with negligible overhead. Those three methods take you from "the graphs look fine" to knowing what is really going on.
The Order of Operations
Now to address the question of cost directly. When the diagnosis is ambiguous, or the budget is limited (when is it not?), spend in this order:
Free: recordsize match, compression level, atime=off, database/ARC cache pairing
Hundreds: RAM, until the demand hit ratio stops climbing
Hundreds: one PLP NVMe as SLOG, if and only if commits wait on sync
Thousands: layout change to mirrors / more vdevs, for IOPS floors
Thousands: faster or additional disks, last, once the above are clean
This list is ordered by impact per dollar, what is going to make the most difference with the least effort. The discipline comes in not skipping steps: item 5 bought before item 1 is how a pool of NVMe ends up slower than a tuned pool of SATA disks.
When It's None of the Three
Sometimes every histogram is clean, and the database still crawls. When you face this, it is time to check the next two usual suspects: pool capacity and fragmentation. A pool over 85% full allocates slowly no matter the hardware, per Part 1. High fragmentation means gang-blocks that are brutal for latency. At the application layer, these issues mask themselves and make it appear that there’s another underlying storage problem.
None of these commands matter much in the crisis if you have no baseline. Run the histograms and arcstat for one quiet hour this week and keep the output. If you can also capture an hour of peak time, that can also be useful later on. Next time there is an incident or report, you're comparing against known-good instead of guessing what normal looked like. That habit, plus the order-of-operations list above, are the most important parts of what a storage performance engagement delivers, and they are the parts you can start doing today.
If you want a second set of eyes on your pools, our Quarterly ZFS Health Check covers recordsize, compression, ARC sizing, capacity, and fragmentation every quarter, and keeps the baseline for you. Get in touch at klarasystems.com.
