Databases on ZFS Masterclass

This guide goes deep on running production databases on ZFS — PostgreSQL, MySQL, Redis, MongoDB, and SQLite, each tuned to eliminate redundant I/O protection layers, exploit ZFS copy-on-write semantics, and integrate with the snapshot and replication workflows that make ZFS the best filesystem for databases in existence. If you have read the ZFS Masterclass and the Snapshots Guide, this is the natural next step: you understand ZFS; now make your databases run faster and safer on top of it.

1. ZFS Changes How Databases Work

2. The recordsize Story — The Single Biggest Performance Win

ZFS stores data in fixed-size blocks called records. The recordsize property controls the maximum size of these records. The default is 128K. For general file storage — media files, logs, backups — 128K is optimal: large sequential I/O, high throughput. For databases, it is catastrophic.

Every database has its own internal page size: the unit at which it reads and writes data. PostgreSQL uses 8K pages. MySQL InnoDB uses 16K pages. When a database writes an 8K page to a dataset with recordsize=128K, ZFS must read the existing 128K block into memory, modify the 8K portion, and write the full 128K block back out. That is 16x write amplification for every database page write. Your database thinks it is doing 8K writes. ZFS is doing 128K writes.

The fix is exact: set recordsize to match the database's page size. An 8K PostgreSQL write to a dataset with recordsize=8K is a clean 8K ZFS write. No amplification. No unnecessary reads. No wasted I/O.

Setting recordsize on an existing dataset does not rewrite existing data — it applies to new writes only. To fully benefit, you must dump, recreate the dataset, and reload. For new installations, set it before first use:

# PostgreSQL data directory
zfs create -o recordsize=8k   rpool/data/postgres

# PostgreSQL WAL directory
zfs create -o recordsize=64k  rpool/data/postgres-wal

# MySQL data directory
zfs create -o recordsize=16k  rpool/data/mysql

# MongoDB data directory
zfs create -o recordsize=64k  rpool/data/mongodb

3. PostgreSQL on ZFS

Dataset Layout

Separate datasets for data and WAL. Different recordsize, different compression settings, different caching behavior — they should not share a dataset.

# Create pool datasets
zfs create rpool/data
zfs create -o recordsize=8k   \
           -o compression=lz4  \
           -o atime=off        \
           -o xattr=sa         \
           rpool/data/postgres

zfs create -o recordsize=64k  \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/postgres-wal

# Point PostgreSQL at them
mkdir -p /data/postgres /data/postgres-wal
chown postgres:postgres /data/postgres /data/postgres-wal
chmod 700 /data/postgres /data/postgres-wal

Initialize the cluster with WAL on its own dataset:

initdb -D /data/postgres --waldir=/data/postgres-wal

postgresql.conf — Tuned for ZFS

The key changes versus a default PostgreSQL install on ext4:

# ── Data integrity ────────────────────────────────────────────
# ZFS CoW means no torn pages. Full-page writes write the entire
# 8K page to WAL after a checkpoint to allow recovery from a
# partial write. On ZFS there are no partial writes. Turn it off.
full_page_writes = off

# ── WAL tuning ────────────────────────────────────────────────
# ZFS handles sparse files efficiently. Pre-allocating WAL
# segments buys nothing and creates unnecessary write I/O.
# (PostgreSQL 15+ uses wal_init_zero instead of wal_init_flags)
wal_init_zero = off

# WAL compression: ZFS already compresses WAL on the dataset.
# Enabling wal_compression as well is double work. Leave it off.
# wal_compression = off  (already the default)

# WAL segment size: default 16MB is fine. Larger reduces WAL
# directory churn on busy systems.
# wal_segment_size = 16MB  (set at initdb time, not here)

# ── Checksums ─────────────────────────────────────────────────
# data_checksums is set at initdb time (--data-checksums).
# ZFS already checksums every block end-to-end. Database
# checksums are redundant CPU and I/O overhead. However, keep
# them on if you want pg_verify_checksums / pg_basebackup
# integrity verification — the cost is low and the tooling
# compatibility is worth it.
# Recommended: keep data_checksums ON (set at initdb).

# ── Memory ────────────────────────────────────────────────────
# On a dedicated database server, set shared_buffers to 25% of
# RAM. The ZFS ARC gets the rest. Both cache — the question is
# who caches what. See section 11 for the full ARC discussion.
shared_buffers = 4GB           # 25% of 16GB server
effective_cache_size = 10GB    # hint to the query planner
work_mem = 64MB                # per sort / hash, can multiply

# ── I/O ───────────────────────────────────────────────────────
# ZFS manages its own I/O scheduler. Tell PostgreSQL the disk is
# fast (it is — ZFS ARC makes random reads near-instant).
random_page_cost = 1.1         # treat disk like SSD
effective_io_concurrency = 200 # for parallel index scans

# ── Autovacuum ────────────────────────────────────────────────
# More aggressive autovacuum prevents table bloat.
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01

# ── Logging ───────────────────────────────────────────────────
log_min_duration_statement = 1000   # log queries over 1 second
log_checkpoints = on
log_temp_files = 0

Snapshot Before Schema Migrations

The workflow every DBA wishes they had on ext4: an instant, consistent snapshot before running a risky migration, with a one-command rollback if it goes wrong.

# 1. Take a consistent snapshot (checkpoint first for clean state)
psql -c "CHECKPOINT;"
zfs snapshot rpool/data/postgres@pre-migration-2026-04-02
zfs snapshot rpool/data/postgres-wal@pre-migration-2026-04-02

# 2. Run the migration
psql -f migrate_schema_v42.sql

# 3a. Migration succeeded — destroy the snapshot (optional, saves space)
zfs destroy rpool/data/postgres@pre-migration-2026-04-02
zfs destroy rpool/data/postgres-wal@pre-migration-2026-04-02

# 3b. Migration failed — roll back instantly
systemctl stop postgresql
zfs rollback rpool/data/postgres@pre-migration-2026-04-02
zfs rollback rpool/data/postgres-wal@pre-migration-2026-04-02
systemctl start postgresql

Zero-Cost Clone for Testing

# Clone production data for a test instance
zfs clone rpool/data/postgres@pre-migration-2026-04-02 \
          rpool/data/postgres-test
zfs clone rpool/data/postgres-wal@pre-migration-2026-04-02 \
          rpool/data/postgres-wal-test

# Start a test PostgreSQL instance on port 5433
pg_ctl -D /data/postgres-test \
       -o "-p 5433" \
       -l /var/log/postgres-test.log start

# Test your migration against real production data
psql -p 5433 -f migrate_schema_v42.sql

# Destroy the clone when done — reclaims space immediately
pg_ctl -D /data/postgres-test stop
zfs destroy rpool/data/postgres-wal-test
zfs destroy rpool/data/postgres-test

Replication with syncoid

# Send the PostgreSQL datasets to a DR host
syncoid --recursive rpool/data/postgres dr-host:rpool/replica/postgres
syncoid --recursive rpool/data/postgres-wal dr-host:rpool/replica/postgres-wal

4. MySQL / MariaDB on ZFS

Dataset Layout

# InnoDB data directory — 16K matches InnoDB's default page size
zfs create -o recordsize=16k  \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/mysql

# InnoDB redo logs / binary logs — sequential, larger blocks better
zfs create -o recordsize=64k  \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/mysql-logs

mkdir -p /data/mysql /data/mysql-logs
chown mysql:mysql /data/mysql /data/mysql-logs

my.cnf — Tuned for ZFS

[mysqld]
# ── Data directories ─────────────────────────────────────────
datadir         = /data/mysql
log_bin         = /data/mysql-logs/mysql-bin
innodb_log_group_home_dir = /data/mysql-logs

# ── The critical ZFS setting ──────────────────────────────────
# InnoDB's doublewrite buffer exists to protect against torn
# pages on ext4 — it writes every page twice (once to a special
# area, once to its final location) so a power failure mid-write
# leaves the original intact. ZFS writes are atomic. There are no
# torn pages. The doublewrite buffer is pure overhead: disable it.
innodb_doublewrite = OFF

# ── Checksums ─────────────────────────────────────────────────
# Keep InnoDB's CRC32 checksums. They are cheap (hardware CRC32
# instruction), and they catch in-memory corruption that ZFS
# end-to-end checksums cannot see (data corrupt before it leaves
# the CPU). ZFS catches disk/controller corruption. InnoDB CRC32
# catches RAM/software corruption. Use both.
innodb_checksum_algorithm = crc32

# ── I/O method ────────────────────────────────────────────────
# O_DIRECT bypasses the Linux page cache so InnoDB talks directly
# to the block layer. The ZFS ARC handles caching. Without this,
# data passes through both page cache and ARC — double buffering,
# wasted RAM.
innodb_flush_method = O_DIRECT

# ── Buffer pool ───────────────────────────────────────────────
# 50-70% of RAM for a dedicated MySQL server. The rest goes to
# ZFS ARC. On a 16GB server:
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8   # one per GB, up to 64

# ── Log size ─────────────────────────────────────────────────
# Larger redo logs = fewer checkpoints = less write I/O.
innodb_redo_log_capacity = 4G      # MySQL 8.0.30+

# ── Flush behavior ────────────────────────────────────────────
# 2 = fsync log, O_DSYNC data. Safe on ZFS. If you want maximum
# throughput at the cost of up to 1 second of data on crash, use 0.
innodb_flush_log_at_trx_commit = 1

# ── Concurrency ───────────────────────────────────────────────
innodb_io_capacity     = 2000
innodb_io_capacity_max = 4000

5. Redis on ZFS

Redis is memory-first. Every key lives in RAM. Disk is persistence only — RDB snapshots and the append-only file (AOF). ZFS's value here is not throughput (Redis is in-memory), it is persistence reliability and operational simplicity.

Dataset Layout

# RDB dump files — large sequential writes, no need for small records
zfs create -o recordsize=64k  \
           -o compression=off  \
           -o atime=off        \
           rpool/data/redis-rdb

# AOF — append-only, sequential writes
zfs create -o recordsize=64k  \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/redis-aof

Why compression=off on RDB? Redis compresses its RDB dump with LZF by default. The data arriving at ZFS is already compressed. ZFS compression on already-compressed data wastes CPU for near-zero benefit. The AOF is plain text commands — lz4 compresses it well.

redis.conf — Tuned for ZFS

# RDB snapshots
dir /data/redis-rdb
dbfilename dump.rdb

# Save policy: snapshot every 60 seconds if 1000 keys changed,
# every 300 seconds if 10 keys changed
save 60 1000
save 300 10

# AOF
appendonly yes
appendfilename appendonly.aof
appenddirname /data/redis-aof

# Fsync policy:
#   always  — fsync after every write. Safest, slowest.
#   everysec — fsync every second. Good balance.
#   no      — let the OS/ZFS decide. Fastest, relies on ZFS CoW.
# On ZFS, 'everysec' is the right default. ZFS CoW means an
# unsynced write that crashes will leave the previous consistent
# state intact — you lose at most 1 second of data, not a corrupt
# file.
appendfsync everysec
no-appendfsync-on-rewrite yes

# RDB compression (LZF)
rdbcompression yes

# ARC considerations: Redis data is already in RAM. ZFS ARC for
# Redis datasets is mostly metadata. Prevent ARC from evicting
# other datasets' hot pages to cache Redis data:
# (set per-dataset in ZFS, not in redis.conf)
# zfs set primarycache=metadata rpool/data/redis-rdb
# zfs set primarycache=metadata rpool/data/redis-aof

# Tell ZFS not to cache Redis data (it's in Redis RAM already)
zfs set primarycache=metadata rpool/data/redis-rdb
zfs set primarycache=metadata rpool/data/redis-aof

Snapshot-Based Redis Backup

# Trigger a background save to get a fresh RDB
redis-cli BGSAVE
# Wait for it to complete
redis-cli LASTSAVE

# Snapshot the RDB dataset — atomic, instantaneous
zfs snapshot rpool/data/redis-rdb@$(date +%Y%m%d-%H%M)

# Replicate to DR
syncoid rpool/data/redis-rdb dr-host:rpool/replica/redis-rdb

6. MongoDB on ZFS

MongoDB's WiredTiger storage engine uses 64K internal pages by default (configurable). The dataset recordsize should match.

Dataset Layout

# WiredTiger data — 64K pages
zfs create -o recordsize=64k  \
           -o compression=off  \
           -o atime=off        \
           rpool/data/mongodb

# Journal (write-ahead log equivalent)
zfs create -o recordsize=64k  \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/mongodb-journal

Why compression=off on MongoDB data? WiredTiger uses snappy compression by default. Running ZFS lz4 compression on top of snappy-compressed data wastes CPU for essentially no space savings — compressed data has near-random byte distribution. Either let WiredTiger compress (and set ZFS compression=off), or disable WiredTiger compression and let ZFS lz4 handle it. Do not run both.

mongod.conf — Tuned for ZFS

storage:
  dbPath: /data/mongodb
  journal:
    enabled: true
    commitIntervalMs: 100
  directoryPerDB: true      # separate subdirectory per database
                             # enables per-database ZFS datasets
  wiredTiger:
    engineConfig:
      cacheSizeGB: 8         # 50% of RAM on a dedicated server
      journalCompressor: snappy
    collectionConfig:
      blockCompressor: none  # Let ZFS handle compression instead
                             # OR: keep snappy and set zfs compression=off
    indexConfig:
      prefixCompression: true

With directoryPerDB: true, each MongoDB database gets its own subdirectory. You can create per-database ZFS datasets for finer-grained snapshot control:

# After creating databases
zfs create -o recordsize=64k -o compression=off rpool/data/mongodb/appdb
zfs create -o recordsize=64k -o compression=off rpool/data/mongodb/analyticsdb

# Snapshot just the application database before a risky operation
zfs snapshot rpool/data/mongodb/appdb@pre-index-rebuild

7. SQLite on ZFS

SQLite is the most widely deployed database in the world, running inside every application that needs embedded state storage: Plex, Grafana, Home Assistant, Nextcloud, Gitea, and hundreds more. Most administrators never think about tuning the filesystem for SQLite — and they are leaving reliability and performance on the table.

Dataset Setup

# SQLite uses 4K pages by default. 8K is the recommended page size
# for SQLite databases larger than a few MB. Use 8K ZFS recordsize.
zfs create -o recordsize=8k   \
           -o compression=lz4  \
           -o atime=off        \
           rpool/data/plex-db   # or whatever application

Application-Level Tuning

Configure SQLite to use WAL mode (where you can control the application's PRAGMA settings). WAL mode is safer on any filesystem and plays better with ZFS CoW:

-- Enable WAL mode for concurrent reads and crash safety
PRAGMA journal_mode=WAL;

-- Increase page size to match ZFS recordsize (set before any tables exist)
PRAGMA page_size=8192;

-- Tell SQLite the filesystem is reliable (ZFS CoW)
-- PRAGMA synchronous=NORMAL reduces fsync calls; ZFS handles atomicity
PRAGMA synchronous=NORMAL;

-- Keep SQLite's own page cache small — let ZFS ARC handle caching
PRAGMA cache_size=-8192;    -- 8MB SQLite cache

Snapshot-Based SQLite Backup

SQLite databases can be corrupted by a naive file copy while the database is in use. ZFS snapshots solve this: a snapshot is crash-consistent (equivalent to the state after a clean shutdown), which SQLite's WAL recovery handles correctly on next open.

# Snapshot the application database — crash-consistent
zfs snapshot rpool/data/plex-db@before-upgrade

# Roll back if the upgrade corrupts the database
zfs rollback rpool/data/plex-db@before-upgrade

8. Snapshot Workflows for Databases

ZFS snapshots are the most powerful operational tool you have for database management. They are instantaneous (milliseconds regardless of database size), space-efficient (only store changed blocks), and consistent (atomic across multiple datasets).

Atomic Multi-Dataset Snapshot

# Atomic snapshot of data + WAL — both at the same transaction boundary
# PostgreSQL: checkpoint first for cleanest state
psql -c "CHECKPOINT;"
zfs snapshot rpool/data/postgres@$(date +%Y%m%d-%H%M)
zfs snapshot rpool/data/postgres-wal@$(date +%Y%m%d-%H%M)

# Or use -r for recursive snapshot of a parent dataset
# (requires data and wal to be children of the same parent)
zfs create rpool/data/pgcluster
zfs create -o recordsize=8k  rpool/data/pgcluster/data
zfs create -o recordsize=64k rpool/data/pgcluster/wal

# Now snapshot both atomically:
zfs snapshot -r rpool/data/pgcluster@$(date +%Y%m%d-%H%M)

Automated Hourly Snapshots with sanoid

# /etc/sanoid/sanoid.conf
[rpool/data/pgcluster]
  use_template = databases
  recursive = yes

[template_databases]
  hourly = 24
  daily = 30
  monthly = 6
  autosnap = yes
  autoprune = yes

9. Clone Workflows for Development

On ext4, copying a 500GB production database for a test environment takes 30 minutes and consumes 500GB of additional disk. Developers avoid it. Tests run against stale data. Production issues surface in production because nobody ran the migration against real data first. ZFS clones eliminate this constraint entirely.

The Clone Lifecycle

# Step 1: Snapshot production (after a CHECKPOINT for consistency)
psql -p 5432 -c "CHECKPOINT;"
zfs snapshot rpool/data/pgcluster@for-testing-$(date +%Y%m%d)

# Step 2: Clone for testing — instantaneous, zero disk until divergence
zfs clone rpool/data/pgcluster/data@for-testing-$(date +%Y%m%d) \
          rpool/data/pgcluster-test/data
zfs clone rpool/data/pgcluster/wal@for-testing-$(date +%Y%m%d)  \
          rpool/data/pgcluster-test/wal

# Step 3: Start a test PostgreSQL instance on port 5433
pg_ctl -D /data/pgcluster-test/data \
       -o "-p 5433 -c hba_file=/etc/postgresql/pg_hba_test.conf" \
       -l /var/log/postgres-test.log start

# Step 4: Run the migration against real production data
psql -p 5433 -f migrate_schema_v43.sql

# Step 5: Verify
psql -p 5433 -c "\d application_users"

# Step 6a: Migration looks good — apply to production, destroy test clone
pg_ctl -D /data/pgcluster-test/data stop
zfs destroy -r rpool/data/pgcluster-test
zfs destroy rpool/data/pgcluster@for-testing-$(date +%Y%m%d)

# Step 6b: Migration failed — destroy clone, fix the migration, try again
pg_ctl -D /data/pgcluster-test/data stop
zfs destroy -r rpool/data/pgcluster-test
# Edit migrate_schema_v43.sql, repeat from Step 2

Parallel Test Environments

# Create 5 parallel test environments from the same snapshot
# for concurrent integration tests
SNAP=rpool/data/pgcluster@for-testing-$(date +%Y%m%d)

for i in 1 2 3 4 5; do
  zfs clone ${SNAP}/data rpool/data/pgtest-${i}/data
  zfs clone ${SNAP}/wal  rpool/data/pgtest-${i}/wal
  pg_ctl -D /data/pgtest-${i}/data \
         -o "-p $((5433 + i))" \
         -l /var/log/postgres-test-${i}.log start
done

# Run test suites in parallel, each against its own clone
# Each clone uses zero disk until its test writes diverge

CI Pipeline Clone Pattern

#!/bin/bash
# ci-db-setup.sh — called at start of each CI run
# Requires: production snapshot taken daily by sanoid

SNAP=$(zfs list -t snapshot -o name -s creation rpool/data/pgcluster/data \
       | grep "@daily-" | tail -1)
JOB_ID=${CI_JOB_ID:-$$}

zfs clone ${SNAP} rpool/data/ci-${JOB_ID}/data
# CI test suite runs against /data/ci-${JOB_ID}/data

# ci-db-teardown.sh — called at end of each CI run
zfs destroy -r rpool/data/ci-${JOB_ID}

10. Replication for DR

syncoid replicates ZFS datasets incrementally, block by block. It does not know or care what is inside the dataset — PostgreSQL pages, MySQL tablespaces, Redis RDB dumps. The mechanism is identical for all of them. No database-specific replication configuration needed.

syncoid-Based Database Replication

# Initial full replication (slow — sends everything)
syncoid --recursive rpool/data/pgcluster dr-host:rpool/replica/pgcluster

# Subsequent runs are incremental — only changed blocks since last sync
syncoid --recursive rpool/data/pgcluster dr-host:rpool/replica/pgcluster

Replication Over WireGuard

syncoid uses SSH. If your DR site is connected via a WireGuard tunnel (which kldload sets up automatically), syncoid traffic flows through the encrypted tunnel with no additional configuration:

# WireGuard provides the encrypted channel
# syncoid sees a normal SSH target on the WireGuard IP
syncoid --recursive \
        --sshoption="-i /etc/wireguard/syncoid_ed25519" \
        rpool/data/pgcluster \
        10.100.0.2:rpool/replica/pgcluster

systemd Timer for Hourly Replication

# /etc/systemd/system/db-replicate.service
[Unit]
Description=Replicate database datasets to DR site
After=network-online.target
Wants=network-online.target

[Service]
Type=oneshot
User=root
ExecStart=/usr/sbin/syncoid --recursive --no-privilege-elevation \
  rpool/data/pgcluster dr-host:rpool/replica/pgcluster
ExecStart=/usr/sbin/syncoid --recursive --no-privilege-elevation \
  rpool/data/mysql dr-host:rpool/replica/mysql
ExecStart=/usr/sbin/syncoid --recursive --no-privilege-elevation \
  rpool/data/redis-rdb dr-host:rpool/replica/redis-rdb
TimeoutStartSec=3600

[Install]
WantedBy=multi-user.target

# /etc/systemd/system/db-replicate.timer
[Unit]
Description=Hourly database replication to DR site

[Timer]
OnCalendar=hourly
RandomizedDelaySec=120
Persistent=true

[Install]
WantedBy=timers.target

systemctl enable --now db-replicate.timer

# Check status
systemctl status db-replicate.timer
journalctl -u db-replicate.service -n 50

RPO (Recovery Point Objective) is your replication interval — one hour in this configuration. RTO (Recovery Time Objective) is the time to receive the dataset on the DR host and start the database — typically under 5 minutes for PostgreSQL or MySQL, seconds for Redis.

11. ARC Tuning for Database Workloads

ZFS maintains an Adaptive Replacement Cache (ARC) in RAM. Every database also maintains its own in-process buffer pool (PostgreSQL shared_buffers, MySQL innodb_buffer_pool_size). When both are active on the same server, data can be cached in two places simultaneously — wasting RAM. The question is who should cache, and what.

Setting ARC Size

# Check current ARC size
arc_summary | grep -E "^(ARC size|Maximum)"
# or
cat /proc/spl/kstat/zfs/arcstats | grep -E "^(c |size)"

# Set ARC maximum (persistent across reboots)
# Example: cap ARC at 10GB on a 16GB server running PostgreSQL
echo "options zfs zfs_arc_max=10737418240" \
  > /etc/modprobe.d/zfs.conf

# Apply without reboot
echo 10737418240 > /sys/module/zfs/parameters/zfs_arc_max

Per-Dataset Cache Control

# Strategy A: database owns the cache
zfs set primarycache=metadata rpool/data/postgres
zfs set primarycache=metadata rpool/data/postgres-wal

# Strategy B: ARC owns the cache (default — nothing to set)
# zfs set primarycache=all rpool/data/postgres  # already the default

# Redis: data is in Redis RAM, not on disk — ARC caching disk
# blocks is wasteful. Always use metadata-only for Redis.
zfs set primarycache=metadata rpool/data/redis-rdb
zfs set primarycache=metadata rpool/data/redis-aof

12. Connection Pooling and ZFS

Connection pooling (PgBouncer for PostgreSQL, ProxySQL for MySQL) sits between applications and the database, multiplexing many application connections onto few database connections. This is a database concern, not a ZFS concern — but it has ZFS implications.

13. Monitoring Database Performance on ZFS

ZFS I/O Monitoring

# Overall pool I/O — watch for high write amplification
# (writes >> what the database thinks it is writing)
zpool iostat -v 1

# Per-dataset I/O (requires ZFS >= 2.1)
zfs iostat -v 1

# ARC statistics — cache hit rate should be > 95% for database workloads
arc_summary

# Watch ARC hit rate live
watch -n1 'cat /proc/spl/kstat/zfs/arcstats | \
  awk "/^hits /{h=\$3} /^misses /{m=\$3} END{printf \"ARC hit rate: %.1f%%\n\", h*100/(h+m)}"'

Database-Specific Metrics

# PostgreSQL: queries causing most I/O
SELECT query, calls, total_exec_time, blk_read_time, blk_write_time
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 10;

# PostgreSQL: buffer hit rate (should be > 99% with good shared_buffers)
SELECT
  sum(heap_blks_hit) /
  (sum(heap_blks_hit) + sum(heap_blks_read) + 0.001) AS hit_rate
FROM pg_statio_user_tables;

# MySQL: InnoDB buffer pool hit rate
SHOW ENGINE INNODB STATUS\G
# Look for: Buffer pool hit rate N/1000

# MySQL: check doublewrite is actually off
SHOW VARIABLES LIKE 'innodb_doublewrite';

eBPF: I/O Latency Distribution

# biolatency: histogram of block I/O latency
# Is the database slow because of disk, or is it the query plan?
biolatency -D 10 1

# bpftrace: trace PostgreSQL write syscalls and their latency
bpftrace -e '
  tracepoint:syscalls:sys_enter_pwrite64
  /comm == "postgres"/
  { @start[tid] = nsecs; }

  tracepoint:syscalls:sys_exit_pwrite64
  /comm == "postgres" && @start[tid]/
  {
    @latency_us = hist((nsecs - @start[tid]) / 1000);
    delete(@start[tid]);
  }
' --duration 10

Grafana Dashboard Combination

Combine ZFS metrics (via node_exporter's ZFS collector) with database metrics (postgres_exporter, mysqld_exporter) in Grafana. Key panels:

• ARC hit rate over time — drops indicate working set exceeds RAM
• Pool write amplification — iostat writes / database writes should be near 1.0
• Database buffer hit rate — should be > 99%
• pg_stat_statements top I/O queries — find the queries that actually touch disk
• I/O latency percentiles (p50, p95, p99) from biolatency

14. Troubleshooting

Diagnosis Runbook

# Step 1: Is it the disk?
biolatency -D 5 1
# If p99 < 2ms: disk is fine, look at the query plan
# If p99 > 10ms: ARC miss or disk contention

# Step 2: Is the ARC working?
arc_summary | grep -E "(hit rate|size)"
# Hit rate < 95%: ARC too small or primarycache=metadata set
# Check: zfs get primarycache rpool/data/postgres

# Step 3: Is there write amplification?
# Compare: zpool iostat (actual writes) vs database write stats
zpool iostat -v 5
# vs
psql -c "SELECT sum(buffers_checkpoint) * 8 / 1024 || 'MB' FROM pg_stat_bgwriter;"

# Step 4: Any checksum errors?
zpool status -v rpool
# CKSUM errors: hardware problem — scrub immediately
zpool scrub rpool

# Step 5: Snapshot health check
zfs list -t snapshot -o name,used,creation -s creation rpool/data/postgres
# No recent snapshots: sanoid/cron not running

Related Pages

• ZFS Masterclass — pool design, ARC deep dive, recordsize mechanics
• Snapshots Guide — sanoid configuration, snapshot lifecycle
• ZFS Zero to Hero — foundational ZFS concepts
• ZFS Tuning for Workloads — ARC, I/O scheduler, recordsize reference
• Memory & ARC — ARC internals, sizing, eviction policy
• Build: Databases on ZFS — step-by-step setup guide
• Observability Intermediate — Grafana dashboards for ZFS + database metrics
• Observability Masterclass — eBPF tracing, biolatency, bpftrace for I/O analysis