Database tuning for NVMe-backed servers

Start with the RAM budget

Every setting below is a slice of memory. Decide the budget first. Run your app under realistic load and read the real numbers:

free -m
ps -eo rss,comm --sort=-rss | head -15

Whatever the app, web server and OS cache need is off the table. The rest is the database's. On a dedicated database server, that is most of the box.

These are starting points for a dedicated database unless noted. Postgres relies on the OS page cache for the rest, which is why its buffer number looks small next to InnoDB's.

Postgres settings that matter

Drop overrides in a file rather than editing the shipped config, so upgrades stay clean. On Debian and Ubuntu that lives under /etc/postgresql/16/main/conf.d/; on AlmaLinux and Rocky the data directory holds postgresql.conf and you can use include_dir.

# /etc/postgresql/16/main/conf.d/tuning.conf  (Warp, dedicated DB)
shared_buffers = 4GB
effective_cache_size = 11GB      # what the planner may assume the OS caches
work_mem = 32MB                  # per sort/hash node, per query; keep it modest
maintenance_work_mem = 512MB
max_connections = 60

# NVMe: random reads cost about the same as sequential
random_page_cost = 1.1
effective_io_concurrency = 200

# WAL
wal_compression = on
max_wal_size = 4GB
checkpoint_completion_target = 0.9

Apply with sudo systemctl restart postgresql for shared_buffers; most others take a reload. Confirm with SHOW shared_buffers;.

The single most impactful line for NVMe is random_page_cost. The default of 4 tells the planner that index lookups are expensive, so it picks sequential scans that would be right on a spinning disk and wrong here.

Watch work_mem. It is allocated per operation, so one complex query with several hashes across 40 connections can claim many times the value. Raise it per session for reports: SET work_mem = '256MB';.

MySQL and MariaDB settings that matter

# /etc/mysql/mysql.conf.d/zz-tuning.cnf  (Nitro, dedicated DB)
[mysqld]
innodb_buffer_pool_size = 7G
innodb_log_file_size = 1G          # MySQL 8.0.30+: use innodb_redo_log_capacity = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
max_connections = 100
table_open_cache = 4000

O_DIRECT stops InnoDB double-caching pages in the OS cache alongside its own buffer pool. innodb_io_capacity tells background flushing how hard it may push the disk; the default of 200 is sized for old SATA drives.

fsync on NVMe: what changes and what does not

Commit latency is dominated by the flush to stable storage. NVMe-backed storage makes that flush quick, so the usual hacks to avoid it are mostly unnecessary. Keep durability on:

• Postgres: leave fsync = on and full_page_writes = on. If you can tolerate losing the last few hundred milliseconds of commits after a crash, synchronous_commit = off is the safe knob: it never corrupts data, it only risks recent transactions.
• MySQL: innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 for full durability. Setting 2 trades the same small loss window for throughput.

Test, do not guess. pg_test_fsync ships with Postgres and reports how many syncs per second your volume sustains with each method:

/usr/lib/postgresql/16/bin/pg_test_fsync -f /var/lib/postgresql/fsync.test

Run it on your own server and keep the output; it is the baseline for any later "the disk feels slow" conversation.

Turn on slow query logging

Tuning memory buys a constant factor. Fixing the three worst queries usually buys more.

# Postgres
log_min_duration_statement = 250ms
shared_preload_libraries = 'pg_stat_statements'

# then, once:
CREATE EXTENSION pg_stat_statements;
SELECT calls, round(mean_exec_time) AS ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

# MySQL
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.25
log_queries_not_using_indexes = 0

# summarise
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Sort by total time, not mean. A 40 ms query run 20,000 times an hour costs more than one 5-second report. Then EXPLAIN (ANALYZE, BUFFERS) in Postgres or EXPLAIN ANALYZE in MySQL 8 to see whether it is missing an index or reading far more rows than it returns.

Lock it down

Databases should not listen on the public IPv4 unless they must. Bind to 127.0.0.1 when the app is local. When it is on another server, allow only that server's address with a portal firewall rule and in pg_hba.conf, and require TLS. The nftables guide covers the host side.

Finally, schedule dumps before you tune anything else. The backups guide has the commands.