Why baseline disk tests lie
Running fio and seeing high sequential throughput tells you almost nothing about real database performance. Your application doesn't write 1MB sequential blocks.
Databases issue small random writes and reads, often 4KB to 16KB, with mixed sync and async operations. A disk that saturates sequential bandwidth can still choke on random IOPS, and that's where query latency lives. So the eight tests below focus on the patterns your database actually uses: random small-block I/O, fsync behavior, concurrency under load, and filesystem choices that matter when milliseconds add up.
I've watched support tickets where users migrated to NVMe and saw no improvement. The disk wasn't the bottleneck—query planning was, or the filesystem mounted with defaults that serialized writes. We'll fix that.
Test 1: Random 4K IOPS under realistic queue depth
Most synthetic benchmarks run single-threaded or at queue depth 1. Real database engines queue dozens of operations.
Run fio with random 4K reads and writes at queue depth 16 to simulate moderate concurrency:
fio --name=rand_rw_4k \
--ioengine=libaio \
--direct=1 \
--bs=4k \
--rw=randrw \
--rwmixread=70 \
--iodepth=16 \
--numjobs=4 \
--size=2G \
--runtime=60 \
--time_based \
--group_reporting \
--filename=/mnt/nvme/testfile
Watch the IOPS numbers and latency percentiles (p95, p99). If p99 latency spikes above 10ms on NVMe, you have contention or the scheduler is wrong. Check cat /sys/block/nvme0n1/queue/scheduler—it should show none or mq-deadline for NVMe. If it says cfq you're on an old kernel and that's your first problem.
Test 2: Fsync latency (the write-ahead log killer)
Databases call fsync() constantly to commit transactions. A slow fsync stalls every write.
Measure fsync latency directly:
fio --name=fsync_test \
--ioengine=sync \
--direct=1 \
--bs=4k \
--rw=write \
--fsync=1 \
--size=1G \
--runtime=30 \
--time_based \
--filename=/mnt/nvme/fsync_testfile
Look at the average fsync latency. On good NVMe it should be under 1ms. If it's above 5ms, check your mount options—barriers must be enabled, not disabled. Also verify the disk doesn't have a volatile write cache pretending to be durable; enterprise NVMe has power-loss protection, consumer drives often don't.
Test 3: Filesystem choice and mount options
Ext4, XFS, and Btrfs behave differently under database workloads. XFS generally wins for large files and parallel writes, but your mileage varies.
Mount XFS with these options for MySQL or PostgreSQL data directories:
mkfs.xfs -f -L nvme_data /dev/nvme0n1p1
mount -o noatime,nodiratime,nobarrier /dev/nvme0n1p1 /var/lib/mysql
Wait—nobarrier? Yes, if your NVMe has battery-backed or supercap-protected write cache. Otherwise leave barriers on. The noatime saves a write on every read; nodiratime does the same for directories. These add up when a database opens thousands of files per second.
For ext4, try data=writeback if you have durability guarantees elsewhere (replication, backups you trust):
mount -o noatime,nodiratime,data=writeback,commit=30 /dev/nvme0n1p1 /var/lib/pgsql
The commit=30 delays journal commits to every 30 seconds instead of the default 5. Risky if you lose power, but it batches writes and can double throughput on write-heavy workloads.
Test 4: InnoDB buffer pool and redo log tuning
MySQL's InnoDB engine defaults to conservative settings that waste NVMe speed.
Set the buffer pool to 70-80% of your RAM so hot data stays in memory:
[mysqld]
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_log_at_trx_commit = 2 flushes logs every second instead of every transaction; you risk one second of data on a crash, but writes don't block. O_DIRECT bypasses the OS page cache (you already cached in the buffer pool). innodb_io_capacity tells InnoDB it can issue 2000 IOPS for background flushing—way higher than spinning disk defaults.
Restart MySQL and run your workload. Check SHOW ENGINE INNODB STATUS\G and look for "log sequence number" advancing smoothly. If checkpoint age is constantly maxed out, increase log file size further.
So what if queries still drag?
Disk might not be the bottleneck anymore. Profile your queries.
Enable the slow query log with a 1-second threshold:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
After a few hours, parse the log with pt-query-digest (from Percona Toolkit):
pt-query-digest /var/log/mysql/slow.log
It will rank queries by total time spent. Often the culprit is a missing index or a full table scan on a growing table. Add the index, and suddenly the 5x speed gain appears—not because NVMe got faster, but because you stopped thrashing it.
Test 5: PostgreSQL shared buffers and WAL tuning
PostgreSQL's defaults assume you're on shared hosting with 128MB of RAM. You're not.
Edit postgresql.conf:
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
wal_compression = on
random_page_cost = 1.1
shared_buffers is the internal cache; set it to 25% of RAM. effective_cache_size tells the planner how much OS cache is available—set it to 50-75% of RAM. random_page_cost = 1.1 tells Postgres that random reads are nearly as cheap as sequential on NVMe (default is 4.0, which is spinning-disk thinking). That changes query plans dramatically; index scans become cheaper.
Restart Postgres and re-run EXPLAIN ANALYZE on your slowest queries. You'll see the planner pick different indexes or join orders.
Test 6: Kernel I/O scheduler and queue tuning
Linux defaults to mq-deadline or bfq on many distros. For NVMe under database load, none often wins.
Switch the scheduler:
echo none > /sys/block/nvme0n1/queue/scheduler
Make it permanent in /etc/udev/rules.d/60-scheduler.rules:
ACTION=="add|change", KERNEL=="nvme[0-9]n[0-9]", ATTR{queue/scheduler}="none"
Also increase the queue depth if your workload is highly concurrent:
echo 1024 > /sys/block/nvme0n1/queue/nr_requests
This lets the kernel queue more I/O before blocking. Default is 128, which can bottleneck under parallel writes from multiple database threads.
Test 7: Measure actual query latency with real traffic
Synthetic benchmarks are useful, but production traffic has patterns you didn't anticipate. Capture real query times.
For MySQL, enable performance schema:
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'statement/%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_statements%';
Then query the top statements by total latency:
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
For PostgreSQL, install pg_stat_statements:
CREATE EXTENSION pg_stat_statements;
Query it:
SELECT
query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
This shows which queries actually burn time in production. Optimize those first.
Test 8: Stress test with sysbench or pgbench
Run a standardized OLTP benchmark to compare before and after.
For MySQL with sysbench:
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=bench \
--mysql-password=pass \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
prepare
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=bench \
--mysql-password=pass \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=16 \
--time=300 \
--report-interval=10 \
run
For PostgreSQL with pgbench:
pgbench -i -s 100 benchdb
pgbench -c 16 -j 4 -T 300 -P 10 benchdb
Record transactions per second and average latency. Apply your tuning changes (filesystem, mount options, config), reboot to ensure settings persist, and re-run. A 5x improvement is realistic if you started from defaults and your workload is write-heavy or uses many small transactions.
When NVMe doesn't help
Sometimes the bottleneck is CPU, network, or bad queries that read entire tables. Adding faster storage won't fix a query that processes a million rows when it should process ten. Profile CPU usage during load—if mysqld or postgres is pegged at 100% on multiple cores, your queries are compute-bound, not I/O-bound.
Also check network latency if your application and database are on separate VMs. A 2ms round-trip between app and DB means even a zero-latency disk still waits for the network on every query. Colocate them or use connection pooling to amortize round trips.
What to test first
Start with the fio random IOPS test at queue depth 16—if you don't see at least 10,000 read IOPS and sub-10ms p99 latency, your disk or scheduler is misconfigured. Fix that before tuning the database.
Then measure fsync latency. If fsync takes more than a few milliseconds, check your filesystem mount options and verify write barriers are on (or that you have durable write cache).
Once disk is proven fast, profile your actual queries with the slow query log or pg_stat_statements. Optimize the top time-wasters by adding indexes or rewriting queries.
Finally, tune your database config—buffer pool size, WAL settings, and random_page_cost for Postgres. Restart, benchmark with sysbench or pgbench, and compare the numbers.
You'll hit 5x faster queries if you started from defaults and your workload actually stresses I/O. If not, you've narrowed down the real bottleneck, which is just as valuable.