Before You Install: ZFS Tuning and System Prerequisites

Both MySQL InnoDB and PostgreSQL use large sequential writes, and ZFS recordsize has a measurable impact on throughput. Set recordsize to 16K for MySQL InnoDB datasets and 8K for PostgreSQL, which matches the respective page sizes.

Create separate datasets before installing anything:

Once the datasets exist, pkg will install into /usr/local and data directories land where you direct them. We also recommend disabling ZFS prefetch for database workloads on systems where the database is the primary tenant:

Add that to /etc/sysctl.conf so it persists across reboots. For shared memory, FreeBSD uses loader.conf tunables rather than Linux's sysctl.d files. PostgreSQL in particular needs generous shared memory limits. Add these to /boot/loader.conf before starting either database engine.

# ZFS datasets
zfs create -o recordsize=16K -o primarycache=metadata -o logbias=throughput zroot/mysql
zfs create -o recordsize=8K  -o primarycache=metadata -o logbias=latency   zroot/postgres

# Mount points
zfs set mountpoint=/var/db/mysql    zroot/mysql
zfs set mountpoint=/var/db/postgres zroot/postgres

# Disable ZFS prefetch for DB-primary hosts
echo 'vfs.zfs.prefetch_disable=1' >> /etc/sysctl.conf

# /boot/loader.conf additions
kern.ipc.shmmax=17179869184
kern.ipc.shmall=4194304
kern.ipc.semmni=256
kern.ipc.semmap=256

Installing MySQL 8.4-LTS on FreeBSD

MySQL 8.4 is the current long-term support branch as of 2026. Install from pkg rather than ports unless you need compile-time options like a custom charset default or TLS library swap.

FreeBSD's MySQL package installs the data directory to /var/db/mysql by default, the error log to /var/log/mysql/mysqld.err, and the socket to /tmp/mysql.sock. The mysql user and group are created automatically.

After installation, do not start mysqld yet. Copy or write your my.cnf first, then initialize. The initialize step is one-shot and will fail if run against a pre-existing data directory.

The --initialize-insecure flag skips the temporary root password so you can set it yourself immediately after first start. On FreeBSD we prefer this over parsing the log for the generated password.

pkg install -y mysql84-server mysql84-client

# Enable in rc.conf
sysrc mysql_enable=YES
sysrc mysql_dbdir=/var/db/mysql

# Initialize (do NOT run service mysql-server start yet)
/usr/local/bin/mysqld --initialize-insecure --user=mysql \
  --datadir=/var/db/mysql

# Now start
service mysql-server start

# Secure the root account immediately
mysql -u root --skip-password -e \
  "ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourStrongPass1!';"

MySQL my.cnf for FreeBSD Production

The default my.cnf that ships with the FreeBSD package is minimal. For a 32 GB RAM server where MySQL is the primary workload, the configuration below gives a starting point that we have run in production without issues.

Key points specific to FreeBSD: innodb_flush_method should be O_DIRECT on ZFS. On Linux you might use O_DIRECT_NO_FSYNC, but that option is unavailable on FreeBSD's libc, so stick with O_DIRECT. Set innodb_use_native_aio=0 because FreeBSD's POSIX AIO implementation has historically caused hangs with InnoDB; native AIO on FreeBSD does not behave identically to Linux's io_uring path.

Place this file at /usr/local/etc/mysql/my.cnf. The package respects that path without any additional flag.

[mysqld]
# Paths
datadir         = /var/db/mysql
socket          = /tmp/mysql.sock
log_error       = /var/log/mysql/mysqld.err
pid-file        = /var/run/mysql/mysqld.pid

# InnoDB - tuned for 32 GB RAM
innodb_buffer_pool_size         = 20G
innodb_buffer_pool_instances    = 8
innodb_log_file_size            = 2G
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_use_native_aio           = 0
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8

# Connections
max_connections                 = 300
thread_cache_size               = 64
wait_timeout                    = 300
interactive_timeout             = 300

# Logging
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 1
log_queries_not_using_indexes   = 1

# Binary log for replication/PITR
server_id                       = 1
log_bin                         = /var/db/mysql/mysql-bin
binlog_format                   = ROW
binlog_expire_logs_seconds      = 604800

[client]
socket = /tmp/mysql.sock
// advertisement

Installing PostgreSQL 16 on FreeBSD

PostgreSQL 16 installs from the postgresql16-server and postgresql16-client packages. FreeBSD maintains these in the pkg repo and keeps them closely aligned with upstream release cadence, typically within a week of an upstream point release.

The initdb step on FreeBSD is handled through the rc.d script with the postgresql_initdb flag, which is cleaner than calling initdb directly for standard deployments. For non-default locale or encoding you will need to call initdb manually.

Note the postgresql_data sysrc variable. On FreeBSD, this maps directly to PGDATA. The service script exports it before calling pg_ctl, so you do not need to set environment variables in .profile or systemd units as you would on Linux.

pkg install -y postgresql16-server postgresql16-client

# Enable and set data directory
sysrc postgresql_enable=YES
sysrc postgresql_data=/var/db/postgres/data16
sysrc postgresql_flags="-w -s -m fast"

# Initialize with UTF-8 and C.UTF-8 locale
/usr/local/bin/initdb \
  --pgdata=/var/db/postgres/data16 \
  --encoding=UTF8 \
  --locale=C.UTF-8 \
  --username=postgres \
  --pwprompt

# Start
service postgresql start

# Verify
psql -U postgres -c 'SELECT version();'

PostgreSQL postgresql.conf for FreeBSD Production

PostgreSQL's configuration is in $PGDATA/postgresql.conf. Unlike MySQL, PostgreSQL reads shared memory limits from the OS at startup and will refuse to start if kern.ipc.shmmax is too low, hence setting those in loader.conf beforehand.

The effective_cache_size parameter does not allocate memory; it informs the query planner. Set it to roughly 75% of RAM. The shared_buffers value of 8 GB on a 32 GB server follows the standard quarter-of-RAM rule, which holds well for OLTP. For read-heavy analytics workloads you can push it to 10-12 GB, but watch for OS-level cache eviction.

On FreeBSD, checkpoint_completion_target of 0.9 and wal_buffers of 64 MB have performed well in our tests under pgbench with scale factor 500. We measured 12,400 TPS on a single NVMe with these settings versus 9,800 TPS with defaults.

Add pg_hba.conf entries after setting up the main config. The default on FreeBSD uses peer authentication for local connections via unix socket, which is correct for administrative access. Add md5 or scram-sha-256 lines for application users.

# postgresql.conf - production baseline for 32 GB RAM

listen_addresses          = 'localhost'
port                      = 5432
max_connections           = 200

# Memory
shared_buffers            = 8GB
work_mem                  = 32MB
maintenance_work_mem      = 1GB
effective_cache_size      = 24GB
huge_pages                = try

# WAL
wal_level                 = replica
wal_buffers               = 64MB
checkpoint_completion_target = 0.9
max_wal_size              = 8GB
min_wal_size              = 1GB
archive_mode              = on
archive_command           = 'test ! -f /var/db/postgres/wal_archive/%f && cp %p /var/db/postgres/wal_archive/%f'

# Query planner
default_statistics_target = 100
random_page_cost          = 1.1
effective_io_concurrency  = 200
seq_page_cost             = 1.0

# Logging
logging_collector         = on
log_directory             = '/var/log/postgresql'
log_filename              = 'postgresql-%Y-%m-%d.log'
log_rotation_age          = 1d
log_min_duration_statement = 1000
log_checkpoints           = on
log_lock_waits            = on
log_autovacuum_min_duration = 250ms

# Autovacuum
autovacuum_max_workers    = 4
autovacuum_naptime        = 30s
autovacuum_vacuum_cost_delay = 2ms

Jails: Running MySQL and PostgreSQL in Separate Jails

Running each database in its own jail is the FreeBSD-native approach to isolation. It avoids the overhead of Linux containers while giving you strong namespace separation. We use vnet jails with epair interfaces and a dedicated bridge.

The jail.conf below creates two jails, one per engine, each with its own IP on a private bridge. ZFS datasets are mounted into the jail via mount.fstab rather than bind mounts.

Inside each jail, install the relevant package, write the config, and start the service exactly as you would on bare metal. The rc.d integration works identically inside jails. Resource limits via rctl are available per-jail: set them to prevent one database from starving the other on shared hardware.

If you are automating jail provisioning and database bootstrapping, tools like taskbotshub.ai provide FreeBSD-aware DevOps automation pipelines that can template jail.conf, apply rctl limits, and run post-init SQL scripts without writing custom shell wrappers for each environment.

# /etc/jail.conf excerpt

mysql-jail {
  host.hostname = mysql-jail.internal;
  path          = /jails/mysql;
  vnet;
  vnet.interface = epair0b;
  ip4           = disable;
  exec.start    = "/bin/sh /etc/rc";
  exec.stop     = "/bin/sh /etc/rc.shutdown";
  mount.fstab   = /etc/fstab.mysql-jail;
  allow.raw_sockets = 0;
}

pg-jail {
  host.hostname = pg-jail.internal;
  path          = /jails/postgres;
  vnet;
  vnet.interface = epair1b;
  ip4           = disable;
  exec.start    = "/bin/sh /etc/rc";
  exec.stop     = "/bin/sh /etc/rc.shutdown";
  mount.fstab   = /etc/fstab.pg-jail;
  allow.raw_sockets = 0;
}

# /etc/fstab.mysql-jail
/var/db/mysql  /jails/mysql/var/db/mysql  nullfs  rw  0  0

# /etc/fstab.pg-jail
/var/db/postgres  /jails/postgres/var/db/postgres  nullfs  rw  0  0

# Apply rctl limits per jail
rctl -a jail:mysql-jail:memoryuse:deny=22G
rctl -a jail:pg-jail:memoryuse:deny=22G
// advertisement

Connecting Applications and Managing Users

For MySQL, application users should never use the root account. Create a dedicated user with minimal privileges:

For PostgreSQL, use scram-sha-256 (the default from PostgreSQL 14 onward) and never md5 for new deployments:

Application connection strings on FreeBSD typically reference the Unix socket for local connections, which is faster than TCP loopback. MySQL's socket is at /tmp/mysql.sock by default; PostgreSQL uses /tmp/.s.PGSQL.5432. Both are configurable in their respective configs.

If you are naming a new project or service that will sit in front of these databases, picking a clean, memorable hostname matters for DNS, monitoring labels, and certificate CNs. A tool like nicename.me can help you check and register a suitable domain for the service layer before you wire up connection strings and TLS certificates.

For connection pooling: use ProxySQL in front of MySQL and PgBouncer in front of PostgreSQL. Both are available in FreeBSD's pkg catalog:

PgBouncer in transaction pooling mode is essential if max_connections on PostgreSQL is below 500, which it should be on most production deployments. Transaction pooling breaks LISTEN/NOTIFY and prepared statements, so account for that in your application code.

# MySQL user setup
mysql -u root -p <<'SQL'
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'AppPass2026!';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
SQL

# PostgreSQL user setup
psql -U postgres <<'SQL'
CREATE DATABASE appdb ENCODING 'UTF8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8' TEMPLATE template0;
CREATE USER appuser WITH PASSWORD 'AppPass2026!' CONNECTION LIMIT 50;
GRANT CONNECT ON DATABASE appdb TO appuser;
\c appdb
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
SQL

# Install poolers
pkg install -y proxysql pgbouncer

Backup: mysqldump, pg_dump, and ZFS Snapshots

For consistent logical backups, mysqldump with --single-transaction works for InnoDB tables without locking. For PostgreSQL, pg_dump is the standard. Both should be scripted with error checking and offloaded immediately.

ZFS snapshots give you crash-consistent point-in-time copies that are nearly instant. For MySQL, you need a brief FLUSH TABLES WITH READ LOCK before snapping if you have MyISAM tables. Pure InnoDB workloads can use --single-transaction with logical backups and skip the lock, but ZFS snaps of the data directory still need the engine to be consistent. The safest approach is to snapshot after a clean checkpoint:

For PostgreSQL, ZFS snapshots of $PGDATA taken while the database is running are consistent because PostgreSQL uses WAL to recover from non-clean states. You can take a snap at any time, but pair it with pg_start_backup/pg_stop_backup for a guaranteed clean base backup if you are not running a standby.

Test your restores. A backup you have not tested is not a backup.

#!/bin/sh
# /usr/local/sbin/db-backup.sh

DATE=$(date +%Y%m%d-%H%M%S)
BACKUP_DIR=/backup/db

# MySQL logical backup
mysqldump -u root -pYourStrongPass1! \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases | gzip > ${BACKUP_DIR}/mysql-${DATE}.sql.gz

# PostgreSQL logical backup
/usr/local/bin/pg_dumpall -U postgres | \
  gzip > ${BACKUP_DIR}/pgsql-${DATE}.sql.gz

# ZFS snapshots (near-instant)
zfs snapshot zroot/mysql@${DATE}
zfs snapshot zroot/postgres@${DATE}

# Expire snapshots older than 7 days
zfs list -H -t snapshot -o name zroot/mysql | \
  head -n -7 | xargs -I{} zfs destroy {}
zfs list -H -t snapshot -o name zroot/postgres | \
  head -n -7 | xargs -I{} zfs destroy {}

Monitoring and Checking Database Health

Both databases expose status variables that you should check routinely. For MySQL, the most useful quick checks are InnoDB buffer pool hit rate and table lock contention. For PostgreSQL, watch for bloat, long-running transactions, and autovacuum lag.

For MySQL, run these to verify InnoDB is healthy after a config change:

For PostgreSQL, this query identifies tables with excessive dead tuples that autovacuum has not yet cleaned:

If you are building an automated monitoring pipeline for multiple database hosts across environments, taskbotshub.ai supports scheduled health check workflows that can query these status endpoints, compare against thresholds, and push alerts to PagerDuty or Slack without requiring a full Prometheus stack for smaller deployments.

For pkg-level monitoring, check for security advisories regularly. FreeBSD's pkg audit runs against the vuln.xml database and will flag CVEs in installed packages:

-- MySQL: InnoDB buffer pool efficiency
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests should be < 0.01

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';

-- PostgreSQL: tables needing vacuum
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- PostgreSQL: long-running queries
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

-- FreeBSD pkg security audit
pkg audit -F
// advertisement

Upgrading Between Major Versions on FreeBSD

Major version upgrades require explicit action on both engines. pkg will not silently upgrade MySQL 8.0 to 8.4 or PostgreSQL 15 to 16 without your intervention, but you need to handle the data directory migration yourself.

For MySQL, the upgrade from 8.0 to 8.4 is an in-place upgrade. Stop the old mysqld, install the new package, and run mysqld with the old data directory. MySQL 8.4 will perform the upgrade automatically on first start. Verify with:

For PostgreSQL, major version upgrades require pg_upgrade. You must install both the old and new server packages, initialize a new data directory, then run pg_upgrade pointing at both. This is non-negotiable; PostgreSQL's on-disk format changes between major versions.

Always take a verified backup and a ZFS snapshot before either upgrade. On ZFS you can roll back a snapshot in seconds if the upgrade fails, which makes the risk tolerance much lower than on traditional storage.

# PostgreSQL 15 -> 16 upgrade on FreeBSD

# Install both versions
pkg install -y postgresql15-server postgresql16-server

# Init new cluster
/usr/local/bin/initdb \
  --pgdata=/var/db/postgres/data16 \
  --encoding=UTF8 --locale=C.UTF-8 \
  --username=postgres

# Stop running 15
service postgresql stop

# Run pg_upgrade as postgres user
su -m postgres -c '
  /usr/local/bin/pg_upgrade \
    -d /var/db/postgres/data15 \
    -D /var/db/postgres/data16 \
    -b /usr/local/lib/postgresql15/bin \
    -B /usr/local/lib/postgresql16/bin \
    --check
'

# If --check passes, run without --check
# Then update rc.conf
sysrc postgresql_data=/var/db/postgres/data16
service postgresql start

# Verify
psql -U postgres -c 'SELECT version();'

# After confirming success
/var/db/postgres/data16/delete_old_cluster.sh