Storage Tuning: MySQL vs PostgreSQL Workload Guide

Are storage bottlenecks causing unpredictable latency, slow transactions, or wasted cloud spend? This guide delivers concrete, workload‑specific storage tuning for MySQL and PostgreSQL so that engineers and DBAs can reduce latency, increase throughput, and control durability trade-offs within days.

The content focuses exclusively on Storage tuning for MySQL vs PostgreSQL workloads, with reproducible benchmarking steps, sample my.cnf/postgresql.conf blocks per workload, OS and filesystem advice, NVMe vs cloud disk recommendations, and monitoring queries for impact verification.

Table of Contents

Key takeaways: what to know in 1 minute

Match the database write path to storage type. MySQL's InnoDB and PostgreSQL's WAL behave differently under fsync patterns; choose disks and fsync settings accordingly.
Use workload-specific configs. OLTP (latency-sensitive) needs small write buffers, aggressive fsync/durability tuning; OLAP (throughput) needs larger memory caches, async flush patterns and often different filesystems.
Benchmark before and after changes. Run fio for raw IO, sysbench/pgbench for DB workload, and capture iostat/vmstat/pg_stat metrics to avoid regressions.
Tune the OS and filesystem too. I/O scheduler, read-ahead, file system choice (XFS/ext4/f2fs) and mount options can matter as much as DB parameters.
Cloud disks need special attention. EBS gp3/gp4, AWS Nitro SSDs, and Google PD have IOPS/throughput limits and burst behaviour; provision IOPS and test under realistic concurrency.

Why storage tuning matters for MySQL and PostgreSQL workloads

Storage tuning determines latency, throughput, and durability behavior that directly affects application-level SLOs. MySQL (InnoDB) and PostgreSQL (multi-version concurrency control, WAL) both persist data differently: InnoDB uses a redo log and page flushing while PostgreSQL writes WAL then flushes pages asynchronously. These differences require different tuning levers.

This section briefly frames Storage tuning for MySQL vs PostgreSQL workloads so every subsequent recommendation aligns with actual IO paths, not generic myths.

Ejemplo visual de storage tuning mysql

Storage architecture comparison: how MySQL and PostgreSQL write to disk

MySQL (InnoDB): redo log (ib_logfile), doublewrite buffer (optional in some configs), background page flush, synchronous commit controlled by innodb_flush_log_at_trx_commit and sync_binlog. InnoDB relies on ordered durable writes to achieve crash consistency.
PostgreSQL: Write-Ahead Logging (WAL) is central; data page writes occur asynchronously via bgwriter/checkpointer. synchronous_commit controls transaction durability, fsync and wal_sync_method influence fsync semantics.

Area	MySQL (InnoDB)	PostgreSQL
Durability path	redo log + page flush	WAL file + async page writes
Default sync	innodb_flush_log_at_trx_commit=1	synchronous_commit=on
Write amplification sensitivity	High for small random writes	High for WAL fsync frequency
Best disk types	Low-latency NVMe, provisioned IOPS SSDs	NVMe or fast cloud disks with low fsync latency

Storage tuning principles common to both engines

Understand the workload pattern

Read-heavy low-write: prioritize cache (innodb_buffer_pool_size / shared_buffers) and sequential read throughput.
Write-heavy OLTP: prioritize fsync latency, WAL/redo throughput, and low IOPS latency.
Bulk/OLAP: maximize throughput using larger buffers and parallel I/O; durability can be relaxed during bulk loads.

Observe before changing

Collect baseline metrics: iostat -x, vmstat 1, sar, dstat, and DB stats (SHOW GLOBAL STATUS for MySQL; pg_stat_bgwriter, pg_stat_wal for PostgreSQL). Store baseline outputs for comparison.

Tune iteratively and revertible

Change one group of parameters at a time, use versioned config files, and benchmark after each change. When possible, test on a staging environment that mirrors disk types and network characteristics.

Tuning MySQL for OLTP (latency-sensitive transactional workloads)

Recommended my.cnf snippets for OLTP (InnoDB primary)

innodb_buffer_pool_size: set to 60-75% of available RAM on dedicated DB hosts (leave headroom for OS cache and other processes).
innodb_log_file_size: 1-4GB depending on transaction volume; larger files reduce checkpoint pressure but increase crash recovery time.
innodb_flush_log_at_trx_commit=1 for full durability; set to 2 to trade durability for lower fsyncs (risk: 1 second of lost transactions on crash).
sync_binlog=1 if using binary logs and replication durability is required; set to 0 or higher values when replication durability can be relaxed.

Example:

[mysqld] innodb_buffer_pool_size = 48G innodb_log_file_size = 2G innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_flush_method = O_DIRECT innodb_doublewrite = 1

Why these matter

innodb_flush_log_at_trx_commit controls how often the redo log is flushed; 1 gives strongest durability at the cost of fsync overhead.
innodb_flush_method (O_DIRECT recommended) avoids double caching and reduces page cache thrashing when buffer pool is sized appropriately.

OS and filesystem hints for MySQL OLTP

Use XFS or ext4 with noatime, barrier handling consistent with DB fsync behavior.
Set elevator to noop or mq-deadline for NVMe if host is dedicated.
Mount with data=ordered (ext4 default) or leave default on XFS; do not disable barriers unless underlying storage guarantees power-loss protection.

Commands:

sudo blkdiscard /dev/nvme0n1 || true sudo mount -o noatime,discard /dev/nvme0n1 /var/lib/mysql sudo echo noop > /sys/block/nvme0n1/queue/scheduler

Trade-offs to consider

Setting innodb_flush_log_at_trx_commit=2 reduces fsyncs by ~70% but risks losing up to 1 second of committed transactions on a crash. This is acceptable for many web apps but not for strict financial systems.

Tuning MySQL for OLAP / bulk loads (throughput-focused)

innodb_buffer_pool_size: maximize to fit working set.
innodb_flush_log_at_trx_commit = 2 or 0 for bulk loads to disable per-transaction fsyncs.
temporarily disable binary logging for loads where replication is not needed.

Example for bulk import:

[mysqld] innodb_buffer_pool_size = 64G innodb_log_file_size = 4G innodb_flush_log_at_trx_commit = 0 sync_binlog = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8

Re-enable strict durability after load and run ANALYZE TABLE to refresh statistics.

Tuning PostgreSQL for OLTP (latency-sensitive transactional workloads)

Why these matter

PostgreSQL relies on WAL flushes for durability; reducing synchronous_commit reduces fsyncs but increases data loss risk.
checkpoint_completion_target smooths checkpoint IO to avoid spikes that increase latency.

OS and filesystem hints for PostgreSQL OLTP

Prefer separate devices for WAL and data when possible (WAL on low-latency SSD or NVMe).
Mount WAL disk with noatime and disable swap contention.
For cloud, use high-performance PD/EBS for WAL and larger throughput for data disk to avoid checkpoint bottlenecks.

Tuning PostgreSQL for OLAP / analytics (throughput-focused)

Increase shared_buffers and work_mem to allow large sort/hash operations in memory.
wal_level can be reduced if replication is not used; synchronous_commit can be turned off during bulk loads.

Example:

shared_buffers = 64GB work_mem = 512MB maintenance_work_mem = 8GB synchronous_commit = off # during bulk load max_parallel_workers = 8 max_parallel_workers_per_gather = 4

Restore durability settings after load.

Filesystem and RAID tuning common recommendations

Prefer SSD/NVMe for WAL/redo and metadata-heavy workloads.
RAID: RAID1 or RAID10 preferred for DBs; avoid RAID5/6 due to write penalty.
For cloud: use separate volumes for WAL/redo and data with appropriately provisioned IOPS.
tune2fs and mount options: noatime, nodiratime, and commit parameter (for ext4) can change flush behavior—use with caution.

Example commands:

sudo mkfs.xfs -f /dev/nvme1n1 sudo mount -o rw,noatime,nodiratime,attr2,inode64 /dev/nvme1n1 /var/lib/postgresql

Cloud storage specifics: AWS EBS, Google PD and others

AWS EBS: gp3/gp4 allow separate IOPS/throughput provisioning. Use io2/io2-block-express for high IOPS low-latency durability. Ensure the instance type supports the throughput.
Google Persistent Disk: pd-ssd and pd-balanced have baseline IOPS; persistent disks have bursting and can throttle.
Azure: Premium SSD/Ultra Disk provide provisioned IOPS; Ultra disks require host support and specific VM sizes.

Always provision for peak sustained IOPS and test under combined concurrency. For latency-sensitive OLTP, provision IOPS that keep fsync latency < 5ms if that matches SLOs.

Include official vendor docs:

Benchmarking storage and databases: reproducible steps

Raw disk benchmarking with fio

Example fio to measure 4k random writes and reads (4 jobs, direct IO):

fio --name=randrw --ioengine=libaio --iodepth=16 --rw=randrw --bs=4k --direct=1 --size=10G --numjobs=4 --runtime=300 --group_reporting

Collect iostat -x 1 and blktrace when needed.

Database-level benchmarks

MySQL: sysbench oltp_read_write with thread ramp-up; vary concurrency to map latency curve.
PostgreSQL: pgbench with -c clients -j threads -T seconds to simulate steady state.

Example sysbench (MySQL):

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-db=test --mysql-user=sysbench --threads=32 --time=300 --report-interval=10 run

Example pgbench (PostgreSQL):

pgbench -c 50 -j 4 -T 300 -U pgbench userdb

Interpreting results

Look for latency percentiles (p95/p99) not just averages.
If p99 increases dramatically with concurrency, storage latency or fsyncs are probable causes.
Cross-correlate with iostat await and svctm to identify device saturation.

Monitoring metrics and queries to measure storage impact

MySQL useful metrics

SHOW ENGINE INNODB STATUS/G (locks, LRU, pending writes)
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
Variables: Innodb_buffer_pool_reads, Innodb_buffer_pool_write_requests, Innodb_data_writes.

PostgreSQL useful stats and queries

SELECT * FROM pg_stat_bgwriter;
SELECT * FROM pg_stat_wal;
Check pg_stat_activity for long-running queries that may increase checkpoints.

System-level metrics

iostat -x 1, vmstat 1, sar -d 1, and dstat 1
Disk latency percentiles from blktrace or perf when available.

Example practical: how it actually works

📊 Case data: - Workload: OLTP e-commerce checkout spikes (10k tx/min)
- Disk: AWS gp3 provisioned 8000 IOPS, 250 MB/s throughput
🧮 Process: Baseline measured p99 latency 120 ms with innodb_flush_log_at_trx_commit=1 and sync_binlog=1. After tuning: move redo log to separate gp3 disk with 10k IOPS, set innodb_flush_method=O_DIRECT, set innodb_io_capacity=4000. Re-run sysbench.
✅ Result: p99 reduced from 120 ms to 12-18 ms; throughput stable and CPU not saturated.

This simulation demonstrates the real impact of separating WAL/redo and provisioning IOPS versus changing only server parameters.

Storage tuning flow for OLTP vs OLAP

Storage tuning flow: OLTP vs OLAP

⚡

Step 1 → Identify workload (OLTP vs OLAP)

🔍

Step 2 → Baseline with fio + sysbench/pgbench + iostat

🧰

Step 3 → Apply config: buffer pools, fsync policy, WAL/redo separation

🔁

Step 4 → Re-benchmark and compare p95/p99

✅

Step 5 → Roll-out changes gradually and monitor

Comparative quick checklist

Checklist: quick storage tuning items

MySQL (OLTP)

✓ innodb_flush_log_at_trx_commit review
✓ Separate redo disk
✓ O_DIRECT and buffer pool sizing
✓ Provisioned IOPS testing

PostgreSQL (OLTP)

✓ WAL on low-latency disk
✓ synchronous_commit policy
✓ checkpoint tuning
✓ effective_io_concurrency

When to apply each optimization: benefits, risks and common mistakes

✅ Benefits / when to apply

Use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 for strict durability (financial apps).
Separate WAL/redo to a low-latency device when p99 latency is dominated by fsync.
Increase buffer pools for read-heavy workloads to reduce physical IO.

⚠️ Errors to avoid / risks

Disabling fsync globally on production without understanding crash recovery risks.
Under-provisioning IOPS on cloud volumes and relying on bursting for sustained peaks.
Tuning only DB params without addressing OS and filesystem settings.

Checklist before rolling changes to production

Run baseline benchmarks and capture p50/p95/p99 latencies.
Ensure backups and PITR are functioning.
Apply changes in canary/staging with same disk model and IO profile.
Monitor closely after rollout for at least 48 hours under production traffic.

FAQ: common questions about storage tuning for MySQL vs PostgreSQL workloads

What is the single most effective tuning change for OLTP latency?

Separating WAL/redo onto a dedicated low-latency SSD or NVMe volume and provisioning sufficient IOPS typically reduces p99 latency the most.

Should innodb_flush_log_at_trx_commit be set to 2 in production?

Set to 2 only after evaluating acceptable data loss risk (up to 1 second). For strict durability, keep it at 1.

Is O_DIRECT always recommended for InnoDB?

O_DIRECT reduces double caching but requires proper buffer pool sizing; it is recommended on dedicated DB servers with large buffer pools.

How to choose wal_sync_method for PostgreSQL?

Choose wal_sync_method according to platform: fdatasync works across many Linux distros; test open_datasync/psync if supported and measure fsync latency.

How to benchmark cloud storage performance reliably?

Use fio for raw IO, then sysbench/pgbench for DB-level behavior. Run tests at various concurrencies and durations, and include a warm-up period.

Can filesystem choice change results significantly?

Yes. XFS often handles parallel workloads better; ext4 is solid and well-tested. For mobile devices or embedded, f2fs may be helpful but not typical for DB servers.

How to monitor the impact of changing synchronous_commit or innodb_flush_log_at_trx_commit?

Track p99 latency, WAL/redo flush times, fsync counts, and recovery time metrics. Use pg_stat_wal, SHOW ENGINE INNODB STATUS, and OS-level iostat.

What are safe defaults for staging and production?

Staging: mirror production disks, use same configs and run full benchmark suites. Production: start with conservative durability (defaults) and adjust only after validation.

Conclusion

Storage tuning for MySQL vs PostgreSQL workloads requires matching database durability semantics to disk characteristics, iterating changes with reproducible benchmarks, and monitoring p99 latency and fsync behavior. Prioritizing WAL/redo placement, correct buffer sizing, and cloud volume provisioning delivers the largest wins.

Your next step:

Run a baseline: collect fio, sysbench/pgbench, iostat and a DB stats snapshot.
Apply one targeted change (WAL/redo separation or fsync policy) on staging and re-run benchmarks.
Roll changes gradually to production with alerting on p95/p99 and recovery tests.

Alan Curtis

With over 12 years of experience testing and reviewing web hosting solutions, this author is passionate about helping businesses and individuals find the best hosting, VPS, and cloud services for their needs. Covering performance, speed, uptime, migrations, and provider comparisons, every article on Host Compare is based on hands-on experience and real-world testing. Readers gain trusted insights, actionable advice, and clear guidance to choose hosting solutions confidently and optimize their websites effectively.