Articles

Advanced ZFS Filesystem Optimization for Relational Databases: Architecture, Performance, and Integration for PostgreSQL and MySQL

Published: May 22, 2026 · Author: Marcin Szewczyk-Wilgan

A tuning guide for the ZFS + PostgreSQL and ZFS + MySQL/MariaDB stacks, covering both the OS kernel and the database engine. Topics include ARC and L2ARC cache architecture, SLOG device selection, elimination of redundant write-protection mechanisms (full_page_writes, doublewrite buffer), io_uring configuration, and deployment differences between Linux and FreeBSD.

Introduction: ZFS in the context of relational databases

Traditional relational databases – PostgreSQL, MySQL with InnoDB, and their kin – were designed for decades against the backdrop of conventional filesystems like ext4 and XFS. Those filesystems operate on an overwrite-in-place model: when data changes, the old bytes on disk are simply replaced. That model forced database developers to build their own crash-safety mechanisms: Write-Ahead Logs, doublewrite buffers, and full-page image writes, all designed to ensure a partially written block never leaves the database in a corrupted state.

ZFS (Zettabyte File System, now developed as OpenZFS) takes a different approach. It never modifies data in place. Instead, it uses Copy-on-Write (CoW): every modification is written to a freshly allocated block, and only after the write completes does ZFS atomically update the pointer tree – a Merkle structure verified by checksums at every level. A block is either fully written and valid, or the old version remains intact. Silent data corruption becomes structurally impossible, and the architecture delivers inline compression and instant snapshots as a side effect.

The problem is that layering the heavy random-I/O workload of a relational database onto a default ZFS configuration creates architectural collisions. The result is write amplification, double buffering between the database cache and the ARC, uncontrolled metadata growth in RAM, and degraded throughput. The sections that follow explain how to tune this stack for production use at both the kernel and application level.

The ARC cache – architecture and self-tuning

Where the Linux page cache uses a straightforward LRU (Least Recently Used) eviction algorithm, ZFS provides the ARC – Adaptive Replacement Cache. The difference matters for databases. A simple LRU is highly susceptible to cache thrashing: running a pg_dump backup sequentially reads the entire database into memory, evicting the hot working set that your OLTP queries depend on.

ARC avoids this by tracking not just recency of access but also frequency. It maintains two active lists and two ghost lists:

• MRU (Most Recently Used): Recently accessed data that has not been requested repeatedly.
• MFU (Most Frequently Used): The hot working set – pages the database requests constantly.
• Ghost MRU and Ghost MFU: These store only signatures and metadata for recently evicted blocks, consuming negligible memory.

The total ARC target size is represented internally as C, split between MRU and MFU by a moving boundary P. The self-tuning mechanism works like this: when a request arrives for a block whose signature exists in the Ghost MRU (a ghost hit), ZFS infers that the MRU segment was too small and shifts P to expand it at the expense of MFU. The system continuously recalibrates, which is precisely what makes it resistant to large sequential scans.

RAM management alongside a database engine

By default, ZFS tries to consume as much available memory as possible. When the database engine – which has its own large memory regions like PostgreSQL's shared_buffers or MySQL's innodb_buffer_pool_size – suddenly needs a large allocation, the Linux OOM Killer may terminate the database process.

The fix is straightforward: enforce strict ARC limits using zfs_arc_max and zfs_arc_min. The exact split depends on the workload profile. In PostgreSQL environments, where shared_buffers should get at least 25% of RAM, a reasonable starting point is 40–50% of physical RAM for zfs_arc_max. In MySQL setups with a large innodb_buffer_pool_size the proportions may be reversed. Regardless of the split, always set a hard zfs_arc_max limit and leave headroom for work_mem, connection pools, and the kernel.

L2ARC optimization and metadata overhead

When expanding RAM is impractical or prohibitively expensive, ZFS offers L2ARC – a second-level read cache that uses SSD or NVMe storage as an extension of the in-memory ARC. Because L2ARC is strictly a read cache, losing the device – say, a failed drive or a cloud instance reboot – carries no risk of data loss whatsoever.

The hidden cost: L2ARC pointer overhead in RAM

Attaching a large L2ARC device without accounting for its metadata cost is a common deployment mistake. ZFS organizes data in hierarchical block pointer trees, verified by checksums at every level (a Merkle tree). Every block offloaded from RAM to the L2ARC device must maintain a corresponding in-memory header. On 64-bit systems each header occupies roughly 70–200 bytes, depending on the OpenZFS version and block size. Attach a multi-terabyte SSD to a server with modest RAM and the headers alone can consume the entire ARC, making everything slower than before.

This effect is tightly coupled to the recordsize setting. Measurements on AWS (test database of 850 GB) produced the following approximate ARC metadata overhead as a fraction of database size:

• recordsize=128KB (default): metadata overhead is just 0.1% of database size.
• recordsize=64KB: overhead rises to 0.2%.
• recordsize=32KB: overhead is 0.4%.
• recordsize=16KB: overhead reaches 0.8% – nearly 7 GB of pointer data for an 800 GB database.

Using cloud ephemeral storage for L2ARC

Cloud providers in the storage-optimized instance families (AWS i3, Google Cloud, Azure Ls-series) include local NVMe drives that offer sub-millisecond latency and no IOPS throttling from a network storage controller. Since data on these drives is lost on instance stop, they are unsuitable for primary data – but they are ideal for L2ARC.

Because L2ARC compresses its buffers (LZ4 by default), a 75 GB NVMe drive can effectively cache a database more than three times that size. To accelerate the initial fill of the device beyond ZFS's conservative wear-protecting defaults, tune the following parameters in /sys/module/zfs/parameters/:

• l2arc_write_boost=134217728: enables aggressive seeding immediately after pool import.
• l2arc_write_max=67108864: raises the steady-state per-second write limit to the NVMe device.

Under very heavy OLTP write loads, the l2arc_feed thread may struggle to keep up. In those cases a simple warm-up script – something like cat /var/lib/mysql/data/* > /dev/null – run during off-peak hours will preload the cache effectively.

Synchronous writes and ZIL/SLOG: latency vs throughput

Relational database engines depend on fsync to guarantee transactional durability (the D in ACID). In ZFS, synchronous write requests are handled by the ZFS Intent Log (ZIL). When the ZIL shares spindles or flash with the main data pool, the database competes for the same I/O bandwidth as everything else. Any serious production deployment must place the ZIL on a dedicated device – the SLOG (Separate Log Device).

What matters in a SLOG device is latency, not capacity. A consumer NVMe with impressive sequential throughput may show latency spikes of 460 microseconds during synchronous flushes. An enterprise PCIe device based on Intel Optane technology maintains consistent latencies around 16 microseconds, which translates directly to higher transactions per second.

Hardware requirement: The SLOG device must have Power-Loss Protection (PLP) capacitors. If the server loses power while transactions are buffered in the SLOG and the device has no PLP, those transactions are gone – and the database index structures will be damaged on the next startup. Consumer drives, however fast, must not be used as SLOG devices in production.

The logbias attribute

The per-dataset logbias property tells ZFS how to handle writes triggered by fsync:

• logbias=latency (default): routes synchronous writes immediately to the SLOG device, minimizing latency and unblocking the database connection as quickly as possible.
• logbias=throughput: bypasses the SLOG entirely for that dataset. ZFS accumulates changes into large batches and writes them directly to the main pool at maximum throughput. This is ideal for bulk-load operations and backup jobs, and it protects the SLOG from unnecessary wear.

Block geometry (recordsize) and write amplification

ZFS allocates storage in blocks of up to 128 KB by default. InnoDB pages are 16 KB; PostgreSQL pages are 8 KB. When a database modifies 8 KB of data stored in a 128 KB ZFS record, Copy-on-Write forces ZFS to read the full 128 KB, recalculate checksums through the Merkle tree, incorporate the 8 KB change, and write the entire 128 KB to a new location on disk. This read-modify-write cycle is write amplification – it hammers throughput and accelerates physical wear on flash storage.

The historical advice was to match recordsize exactly to the database page size – recordsize=8k for PostgreSQL, for instance. That does eliminate write amplification, but it also destroys compression effectiveness. Algorithms like LZ4 and ZSTD need enough data in a single block to find repeating patterns; an 8 KB block gives them almost nothing to work with.

Taking into account both the write-amplification and the ARC metadata overhead described earlier, the modern engineering consensus for transactional workloads is recordsize=32K. This preserves meaningful compression while keeping metadata overhead at a manageable level. Note that changing recordsize affects only newly written blocks – applying it to an existing database requires migrating the data through zfs send/receive.

Tuning PostgreSQL on ZFS (PoZoL) end-to-end

Running PostgreSQL on ZFS on Linux has accumulated enough best practices to earn its own shorthand – "PoZoL." The biggest performance gains come from carefully disabling database-level mechanisms that duplicate protection already provided by the filesystem.

Torn pages and the full_page_writes switch

To guard against torn pages – which occur when a power failure interrupts an 8 KB page write to a physical disk with 4 KB sectors – PostgreSQL's full_page_writes parameter causes the engine to copy the entire 8 KB raw page image into the WAL on the first modification after each checkpoint. This generates substantial redundant I/O and measurably constrains TPS.

ZFS Copy-on-Write makes torn pages structurally impossible. A new block is not considered committed until it has been written completely and its checksum verified in the Merkle tree. If a write is interrupted, ZFS simply keeps pointing to the previous valid version. There is no partially-written block to recover from.

Set full_page_writes = off in postgresql.conf. This reduces disk traffic and eliminates I/O stalls with no downside – provided two caveats are respected: do not disable it if recordsize is smaller than 8 KB, and do not disable it if the instance could ever be replicated to an ext4 filesystem via rsync.

One trade-off is a slightly longer crash recovery time. Starting with PostgreSQL 15 (experimentally available since 14), recovery_prefetch mitigates this. Controlled via maintenance_io_concurrency, it uses posix_fadvise hints to asynchronously prefetch blocks needed during recovery, reducing the penalty substantially.

Separating WAL from table data

Best practice is to place WAL and table data on separate ZFS datasets, each tuned for its access pattern:

Several other PostgreSQL settings also duplicate ZFS functionality and should be addressed:

1. Checksumming: Disable database-level checksums. ZFS Merkle trees verify cryptographic integrity on every block, faster and more comprehensively than anything at the application level.
2. WAL file recycling: Set wal_init_zero = off and wal_recycle = off. ZFS handles zero-filled blocks natively; PostgreSQL's artificial pre-zeroing of WAL files only wastes write cycles.
3. Sync method: Change wal_sync_method to fdatasync – fully safe and correct when a hardware SLOG with PLP is present. Also ensure shared_buffers is sized conservatively enough not to compete aggressively with the ARC.

Bug #16936 – data corruption in streaming replication on ZFS

In HA clusters running PostgreSQL 15+ on OpenZFS 2.1.x or 2.2.x, a defect has been identified and is tracked as Bug #16936 in the OpenZFS issue tracker. As of this writing the issue remains open with no official fix in OpenZFS.

The symptom on the replica is log messages such as invalid magic number 0000 in log segment or incorrect resource manager data checksum, which crash the walreceiver process and leave the standby permanently broken.

The root cause is a race condition between walsender and the ZFS layer. Although fsync/fdatasync on a WAL page returns success, the data is not yet fully consistent for reads by another process. walsender reads the WAL page before ZFS has physically committed the metadata blocks to disk, and the standby receives a structurally inconsistent stream. The defect is most pronounced on newer kernels (Rocky Linux 9, kernel 5.14+) and with smaller recordsize values.

Workaround: The only proven mitigation is to temporarily switch from continuous TCP streaming replication to file-based WAL shipping (e.g. via rsync or pg_receivewal to ext4). A delay of a few seconds is sufficient – by the time an archived WAL segment is closed and transferred, the race condition window has passed and the file is structurally complete.

Structural tuning of the InnoDB engine (MySQL / MariaDB)

Deploying MySQL – along with forks like MariaDB and Percona XtraDB – on ZFS requires solving the same class of duplicated-I/O problems, this time through InnoDB's own configuration surface.

The doublewrite buffer

The doublewrite buffer is InnoDB's answer to the torn page problem. Before writing a modified page to its final location in the .ibd tablespace file, InnoDB first writes a copy to a separate sequential area on disk. Only after confirming that write succeeded does the engine update the actual table file. In practice this mechanism consumes up to 100% of available write I/O bandwidth with no benefit to the database's actual workload.

Since ZFS Copy-on-Write makes a half-written, corrupted page physically impossible, the doublewrite buffer serves no purpose on ZFS. Disable it by setting innodb_doublewrite = 0 in my.cnf. This can nearly double TPS by eliminating redundant write cycles.

Native AIO and io_uring

Native asynchronous I/O is routinely recommended for InnoDB on ext4. On ZFS for Linux, however, the AIO implementation relies on a compatibility shim that merely simulates asynchrony. Under load, this shim becomes a bottleneck, serializing I/O requests and stalling the database instance.

There are several ways to handle this depending on the engine version:

• On older deployments, the standard workaround was to disable native AIO (innodb_use_native_aio = 0, plus innodb_use_atomic_writes = 0 on MariaDB/Percona) and compensate by increasing the InnoDB I/O thread counts (innodb_read_io_threads and innodb_write_io_threads).
• io_uring on MariaDB 10.11+ / 12.0+: Newer versions of MariaDB introduced the innodb_linux_aio flag. When set to auto, the engine uses the Linux io_uring interface directly, bypassing the shim and markedly improving async I/O performance.
• Container caveats: Running io_uring inside Docker may produce mariadbd: io_uring_queue_init() failed with ENOMEM. The fix is to raise the locked-memory limit at the host level – either the memlock: "262144" key in Docker Compose or LimitMEMLOCK in a systemd unit. Without sufficient locked memory, MariaDB will silently fall back to the legacy thread-based mode.

ZFS dataset layout for MySQL

Linux kernel dependencies: xattr, atime, and ashift

One of the most consistently overlooked aspects of deploying ZFS on Linux is how it handles POSIX extended attributes. For historical compatibility reasons, ZFS defaults to storing extended attributes – including POSIX ACL entries – as hidden files in auxiliary directories. This means that when an application opens any file with extended attributes, the kernel must perform separate directory lookups to find them, generating extra I/O seeks before even touching the data. This overhead nullifies much of the benefit of a fast ARC cache.

The solution is to set xattr=sa (System Attributes) on your datasets. In this mode, extended attributes are stored directly inside the ZFS inode structure rather than in separate files. The OpenZFS 2.2 documentation explicitly recommends xattr=sa for best performance, particularly for database environments and SMB shares where ACL checks are frequent. Pair it with acltype=posixacl for full POSIX compatibility. Keep in mind that the change applies only to newly written files; existing records are updated only when migrated via zfs send/recv.

A second quick win is disabling access time updates: zfs set atime=off. Without this, every file read by the database – including sequential table scans – triggers a write to update the atime metadata, generating unnecessary wear on flash storage.

At the hardware level, the single most important parameter before creating a new pool on modern storage is ashift. SSDs and NVMe drives using the Advanced Format standard operate on 4 KB physical sectors, which requires ashift=12. Creating a pool with the legacy 512-byte default (ashift=9) on a 4 KB device produces hardware-level write amplification – every logical 512-byte operation expands to a 4 KB physical read-modify-write. No amount of recordsize tuning or buffer expansion can compensate for this mistake, and the pool must be recreated to fix it.

FreeBSD specifics and performance comparison with Linux

ZFS originated in the Solaris ecosystem, and FreeBSD was the first open-source OS to adopt it. Many of the Linux-specific issues discussed above do not manifest on FreeBSD in the same way, owing to differences in kernel architecture and deeper ZFS integration.

Kernel parameters: sysctl vs sysfs

On Linux, ARC and L2ARC tunables such as l2arc_write_max and l2arc_write_boost are modified through the virtual filesystem at /sys/module/zfs/parameters/. On FreeBSD the equivalent mechanism is sysctl, with persistent settings in /boot/loader.conf or /etc/sysctl.conf. For example, L2ARC write limits are exposed as vfs.zfs.l2arc_write_max and vfs.zfs.l2arc_write_boost, while the maximum block size is controlled via vfs.zfs.max_recordsize.

ACLs and extended attributes on FreeBSD

The earlier discussion about POSIX ACL overhead and the need to set xattr=sa and acltype=posixacl is Linux-specific. FreeBSD does not support POSIX ACLs on ZFS natively. It uses NFSv4 ACLs (acltype=nfsv4) by default, and in current OpenZFS versions on FreeBSD these attributes are stored in System Attributes automatically. The optimization that requires manual intervention on Linux resolves itself on FreeBSD without any configuration change.

Async I/O in MySQL/MariaDB on FreeBSD

The warning about the native AIO compatibility shim – and the recommendation to set innodb_use_native_aio=0 – applies specifically to Linux, where the ZFS kernel module uses an emulation layer that serializes what should be asynchronous operations. The same architectural problem does not exist in FreeBSD's ZFS implementation. For this reason, many production MySQL configuration files annotate the AIO disablement with a "Linux only" comment. Additionally, the io_uring interface is exclusively a Linux kernel technology and has no FreeBSD equivalent.

Aggressive prefetching

FreeBSD ZFS database deployments often include a recommendation to disable ZFS read-ahead prefetching entirely via vfs.zfs.prefetch.disable=1. With database workloads – which tend to be highly random – ZFS prefetch predictions are frequently wrong, and the speculative reads waste I/O bandwidth and pollute the ARC with data that will never be requested.

FreeBSD or Linux – which performs better?

Despite the historical relationship between ZFS and FreeBSD, there is no clear-cut winner. The answer depends on workload type and the expertise of the team doing the deployment.

FreeBSD often delivers better raw I/O performance out of the box. The system ships with a server-oriented kernel configuration that requires little tuning to achieve low baseline latency, whereas Linux frequently needs manual adjustments – I/O scheduler selection, CPU power-state configuration – to reach comparable latency.

Benchmark results are mixed across workload types, though:

• FreeBSD tends to win on raw I/O, but can trail Linux on higher-level application write operations such as FWrite.
• In some cloud benchmarks (AWS instances), PostgreSQL on FreeBSD has shown lower throughput in read-heavy scenarios compared to equivalent Ubuntu configurations.
• Linux has io_uring, which gives it an edge in async-heavy workloads. For engines that can exploit it – particularly with newer MariaDB releases – the advantage is concrete and measurable.

Ultimately, the choice comes down to team expertise and the specific workload. FreeBSD offers native ZFS support and solid I/O performance out of the box. Linux, after appropriate tuning, can match or exceed FreeBSD in specific metrics, and its larger community along with a faster pace of kernel-level innovation (io_uring, eBPF) keep it the dominant choice in commercial deployments.

Conclusion

A default ZFS configuration is not prepared for the random I/O patterns of relational databases. The changes described above – ARC limits, a dedicated SLOG, correct recordsize, elimination of redundant write protection, and proper xattr/ashift settings – produce a stack that combines the data integrity guarantees of Copy-on-Write with the throughput a production database requires.

Frequently asked questions

FAQ – ZFS and databases