Optimizing I/O for Rented Servers Running MySQL/PostgreSQL

Posted at 2025-08-26

When deploying MySQL or PostgreSQL on a rented server (dedicated server, VPS, or bare-metal), the I/O subsystem often becomes the real bottleneck. CPU and RAM are important, but without efficient I/O, queries pile up, transactions stall, and latency spikes.

In this article, we’ll go beyond surface-level tuning and look into I/O optimization from hardware → OS → database, with practical techniques and benchmark insights.

1. Why I/O Matters for Databases

Both MySQL (InnoDB) and PostgreSQL rely heavily on disk I/O:

WAL (Write-Ahead Logging) → Every transaction must be fsync’ed.
Checkpointing → Dirty pages in memory must eventually flush to disk.
Random Reads/Writes → Index lookups and joins hit storage if not cached.

On rented servers, you don’t always control the storage backend, so choosing the right server configuration and tuning I/O stack is critical.

2. Choosing the Right Storage Hardware

Different storage types impact I/O drastically:

Storage	IOPS (random read/write)	Latency	Suitable workload
HDD	~100	~10 ms	Archival, logs
SATA SSD	~5K – 50K	0.5–1 ms	Moderate OLTP
NVMe SSD	~500K+	~100 µs	High-concurrency OLTP/OLAP

👉 Recommendation: For transactional databases (banking, e-commerce), always choose NVMe-based rented servers.

3. OS-Level Tuning (Linux)

The Linux kernel’s I/O subsystem can be tuned for databases:

3.1 I/O Scheduler

noop or none for SSD/NVMe → minimal overhead.
deadline → better latency control for mixed workloads.

Check & change scheduler:

cat /sys/block/nvme0n1/queue/scheduler echo deadline > /sys/block/nvme0n1/queue/scheduler

3.2 Dirty Page Flushing

Control how Linux flushes dirty pages:

sysctl -w vm.dirty_background_ratio=5 sysctl -w vm.dirty_ratio=15

This prevents sudden large flushes that stall queries.

3.3 NUMA Awareness

On dual-CPU rented servers, enable NUMA interleaving or pin database processes to a specific node for consistent memory-to-I/O performance.

4. File System Considerations

ext4: Safe, widely tested.
xfs: Recommended for PostgreSQL (better parallel write scaling).

Mount options:

UUID=xxx /data xfs defaults,noatime,nodiratime 0 0

noatime: Avoid extra writes on reads.
nodiratime: Skip directory access time updates.

5. MySQL I/O Tuning

Key InnoDB parameters:

# my.cnf innodb_flush_log_at_trx_commit = 2 innodb_io_capacity = 2000 # Adjust to storage device (2000 for NVMe) innodb_io_capacity_max = 4000 innodb_flush_method = O_DIRECT innodb_buffer_pool_size = 70% of RAM

O_DIRECT avoids double caching.
buffer_pool_size reduces disk reads by caching hot data.

6. PostgreSQL I/O Tuning

Core parameters for PostgreSQL:

# postgresql.conf synchronous_commit = off # If durability trade-off is acceptable shared_buffers = 25%-40% of RAM effective_io_concurrency = 200 # Higher for SSD/NVMe wal_compression = on checkpoint_completion_target = 0.9

effective_io_concurrency → Leverages SSD parallelism.
checkpoint_completion_target spreads I/O load smoothly.

7. Benchmarking and Monitoring

Always measure, don’t guess.

fio → synthetic I/O benchmarking:

fio --name=randrw --rw=randrw --size=2G --bs=4k --numjobs=4 --runtime=60 --iodepth=32 --time_based

sysbench (MySQL):

sysbench oltp_read_write --db-driver=mysql --tables=10 --table-size=100000 --threads=16 run

pgbench (PostgreSQL):

pgbench -i -s 50 pgbench -c 16 -j 4 -T 60

Monitoring: iostat, iotop, pg_stat_io (PG 16+), performance_schema (MySQL).

8. Case Study: NVMe vs SATA SSD on Rented Server

SATA SSD VPS: 20K TPS (transactions per second) under sysbench OLTP.
NVMe Dedicated Server: 180K TPS under the same workload.
Takeaway: I/O choice can yield a 10x performance difference.

9. Best Practices by Workload

OLTP (high write concurrency) → Prioritize WAL tuning, fast NVMe, RAID10.
OLAP (analytics, reporting) → Use parallel query, optimize sequential reads, increase work_mem.
Mixed workloads → Separate OLTP and OLAP servers, or at least WAL on dedicated disks.

Conclusion

Optimizing I/O for MySQL/PostgreSQL on rented servers is not just about picking the biggest server — it’s about tuning every layer:

Choose the right hardware (NVMe > SATA > HDD).
Tune OS and file system for database workloads.
Adjust MySQL/PostgreSQL parameters to match I/O capacity.
Benchmark regularly to catch bottlenecks early.

By applying these techniques, you can extract maximum performance from rented infrastructure while keeping costs under control.

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up