Unlock Lightning‑Fast Queries: Tuning PostgreSQL & MySQL for Peak NVMe Performance on Bare‑Metal Servers

When you pair modern NVMe SSDs with a bare‑metal server, the storage performance you can achieve far exceeds what SATA or SAS drives can deliver. However, the raw speed of NVMe is only useful if the operating system and the database are tuned to make the most of it. This article walks you through practical, proven tweaks for PostgreSQL and MySQL (or MariaDB) that squeeze every last IOPS and bandwidth out of NVMe on a dedicated platform. By the end, you’ll know why each setting matters, how to apply it safely, and what trade‑offs to watch for in production.

1. Why NVMe Excels on Bare Metal

NVMe (Non‑Volatile Memory Express) was designed from the ground up for flash storage, offering:

• Low latency (often < 100 µs) thanks to a streamlined PCIe command set.
• High parallelism—dozens of queues, each with up to 64,000 commands.
• Massive throughput (several GB/s per drive), outpacing SATA/SAS by 5–10×.

On a bare‑metal server you control the kernel, firmware, and hardware layout, so you can eliminate virtualization overhead and give the OS full PCIe bandwidth. The default database settings, however, are often tuned for spinning disks or generic SSDs, turning many of NVMe’s strengths into bottlenecks.

2. Core System‑Level Settings

2.1. Kernel I/O Scheduler

Linux ships with cfq (Completely Fair Queuing) on many distributions, which is great for HDDs but can throttle NVMe. Switch to none (the “noop” scheduler) or mq-deadline for multi‑queue devices:

# echo none > /sys/block/nvme0n1/queue/scheduler
# or make it persistent via /etc/udev/rules.d/60-nvme-scheduler.rules

NVMe already implements sophisticated queue management, so a simple FIFO scheduler lets the hardware shine.

2.2. Filesystem Choice and Mount Options

While ext4 is ubiquitous, XFS and btrfs (with ssd and nobarrier) often deliver better parallel‑write performance on NVMe. A typical mount line for XFS:

/dev/nvme0n1p1 /var/lib/postgresql xfs rw,noatime,allocsize=64k 0 0

Key options:

• noatime – eliminates unnecessary metadata updates.
• allocsize (XFS) – aligns allocation to the SSD’s erase block, reducing write amplification.
• ssd_spread (ext4) – spreads allocations across the device to avoid hot spots; on NVMe the benefit is modest.

2.3. Transparent Huge Pages (THP)

THP can cause latency spikes during memory compaction. Disable it for database workloads:

# echo never > /sys/kernel/mm/transparent_hugepage/enabled

2.4. CPU Affinity and NUMA Awareness

High‑core servers often have multiple NUMA nodes. Pin database processes to the CPUs that share the same PCIe root complex as the NVMe drive. For PostgreSQL, add to postgresql.conf:

cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

Launch the service with numactl --cpunodebind=0 --membind=0 (adjust node numbers to match your hardware).

3. PostgreSQL‑Specific Tuning

3.1. WAL (Write‑Ahead Log) Placement

Put the WAL on a dedicated NVMe partition or a separate NVMe drive. The WAL is the most write‑intensive component, so isolating it prevents contention with data files.

# postgresql.conf
wal_level = replica
wal_sync_method = fdatasync
wal_compression = on            # reduces bandwidth for bulk writes
wal_buffers = 64MB              # default 16MB; larger helps bursty workloads
wal_writer_delay = 10ms         # lower for ultra‑low latency

3.2. Checkpoint Configuration

Frequent checkpoints generate large sequential writes that waste NVMe bandwidth. Tune for longer intervals while keeping crash recovery reasonable:

checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

These settings let PostgreSQL spread checkpoint I/O over a longer window, turning many random writes into efficient sequential patterns.

3.3. Shared Buffers and Work Memory

NVMe reduces the penalty of cache misses, so you can afford slightly smaller shared_buffers and allocate more to per‑query work_mem:

shared_buffers = 4GB               # 25% of RAM on a 16GB system
effective_cache_size = 12GB
work_mem = 64MB                    # increase for complex joins
maintenance_work_mem = 512MB

Higher work_mem reduces temporary‑file spills that would otherwise hit the NVMe device.

3.4. Parallelism

NVMe’s low latency makes parallel query execution more attractive. Enable and tune:

max_parallel_workers_per_gather = 4
max_parallel_workers = 16

Monitor with pg_stat_activity and adjust if CPU becomes the bottleneck.

4. MySQL / MariaDB Tuning for NVMe

4.1. InnoDB File‑Per‑Table and Redo Log Placement

Set innodb_file_per_table=ON (default in recent versions) to keep each table’s data and indexes in separate files, allowing the OS to better schedule I/O across NVMe queues.

# my.cnf
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2   # 2 = async flush, safe for most workloads
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_log_group_home_dir = /mnt/nvme0n1/innodb_log

4.2. Adaptive Flushing and I/O Capacity

InnoDB’s adaptive flushing keeps the dirty‑page ratio around 10‑20 %. With NVMe you can safely raise the threshold, reducing background I/O bursts:

innodb_max_dirty_pages_pct = 30
innodb_io_capacity = 4000          # matches NVMe IOPS (adjust per drive)
innodb_io_capacity_max = 8000

4.3. Buffer Pool Size and Partitioning

Allocate a large portion of RAM to the buffer pool, but leave headroom for the OS page cache and other services:

innodb_buffer_pool_size = 12G       # 75% of a 16GB system
innodb_buffer_pool_instances = 8   # improves concurrency

4.4. Temporary Tables and Sort Buffers

Set generous per‑thread buffers so that large sorts stay in memory instead of spilling to disk:

tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 8M
read_rnd_buffer_size = 4M

Because NVMe handles random reads and writes far better than HDDs, you can be more aggressive with these values without fearing catastrophic I/O spikes.

5. Monitoring, Validation, and Ongoing Maintenance

After applying the above tweaks, verify that the system truly benefits from NVMe’s capabilities.

1. Benchmark with realistic workloads. Use pgbench for PostgreSQL and sysbench for MySQL to simulate OLTP patterns. Compare latency and throughput before and after changes.
2. Track I/O statistics. iostat -x 1 /dev/nvme0n1 shows queue depth, await, and %util. Aim for await < 0.2 ms and %util under 70 % during peak load.
3. Watch for write amplification. NVMe drives expose SMART counters (nvme smart-log /dev/nvme0n1) that include Data Units Written. Excessive writes may indicate sub‑optimal checkpoint or flush settings.
4. Automate health checks. Include pg_stat_bgwriter (PostgreSQL) or SHOW ENGINE INNODB STATUS (MySQL) in your monitoring dashboards to spot growing checkpoint lag or buffer‑pool pressure.

Revisit these metrics after OS upgrades, firmware updates, or workload changes. NVMe performance can evolve dramatically with new driver versions, so staying current is part of the tuning lifecycle.

Conclusion

NVMe SSDs on bare metal provide a performance envelope that can transform database workloads—from sub‑millisecond transaction latencies to multi‑gigabyte data loads per second. Realizing that promise, however, requires aligning the operating system, filesystem, and database configurations with the hardware’s strengths. By switching to a lightweight I/O scheduler, choosing an NVMe‑friendly filesystem, isolating WAL/redo logs, and carefully tuning checkpoints, buffers, and flushing parameters, you let PostgreSQL and MySQL run at the speed they were designed for.

Tuning is an iterative process: start with the baseline settings outlined here, benchmark, observe, and fine‑tune. With disciplined monitoring and a willingness to adapt as firmware and kernel improvements arrive, your bare‑metal NVMe servers will consistently deliver the maximum throughput and lowest latency that modern applications demand.