Use XFS or ext4 file system and configure noatime, nodiratime, logbufs=8, logbsize=256k and other mount options to improve I/O efficiency; 2. Select mq-deadline or none scheduler for SSD/NVMe, and use deadline to reduce the additional overhead of the database by using deadline for mechanical disks; 3. Set vm.swappiness=1 to reduce switching, and set vm.overcommit_memory=1 (PostgreSQL) or 0 (MySQL InnoDB) according to the database type; 4. Use numactl to bind CPU and memory nodes to the multi-channel NUMA system to isolate the core and reduce interference; 5. Tune the TCP buffer and connection parameters such as rmem_max, wmem_max, tcp_fastopen=3 to improve network performance—the optimization needs to be gradual verification based on monitoring indicators, and do not directly apply the default configuration to end.

When running database workloads on Linux—whether it's MySQL, PostgreSQL, MongoDB, or another—you're not just managing queries; you're managing how efficiently the OS handles I/O, memory, CPU scheduling, and network. Linux is highly tuneable, but default settings often favor general-purpose use, not database performance. Here's how to optimize it effectively.

1. Filesystem and Mount Options

Databases are I/O-heavy. The choice of filesystem and how it's mounted matters.

• Use XFS or ext4
  XFS generally performs better for large files and concurrent I/O (great for PostgreSQL WAL or MySQL InnoDB). ext4 is stable and works well too—especially with proper tuning.

  

• Mount with performance-friendly options
  Example for XFS:

   /dev/sdX /var/lib/mysql xfs noatime,nodiratime,logbufs=8,logbsize=256k 0 0

  • noatime,nodiratime : Skip updating file access times—reduces unnecessary writes.
  • logbufs=8,logbsize=256k : Increase XFS journal buffer size for better write throughput.

Pro tip: Avoid barrier=0 unless you're on battery-backed RAID—data integrity matters more than raw speed.

2. I/O Scheduler Tuning

The I/O scheduler manages how disk requests are queued.

• For SSDs or fast NVMe: Use none (aka "noop" for some kernels) or mq-deadline :

   echo mq-deadline > /sys/block/nvme0n1/queue/scheduler

• For traditional spinning disks: deadline is usually better than CFQ for databases.

Why? Databases manage their own I/O ordering—letting the kernel do extra scheduled just adds overhead.

3. Memory Management (Swappiness & Overcommit)

Databases love RAM—but Linux might not know that.

• Reduce swappiness to avoid unnecessary swapping:

   vm.swappiness = 1

  (Default is 60; 1 means swap only when absolutely necessary.)

• Enable memory overcommit for PostgreSQL-style forking:

   vm.overcommit_memory = 1

  This allows processes to allocate more virtual memory than physically available—safe for databases that pre-allocate shared buffers.

Note: For MySQL with InnoDB, keep overcommit_memory=0 if you're tight on RAM—InnoDB doesn't fork like PostgreSQL.

4. NUMA and CPU Affinity (for Multi-Socket Systems)

If you're on a multi-socket server:

• Check NUMA topology :

   numactl --hardware

• Bind database processes to local NUMA nodes :

   numactl --cpunodebind=0 --membind=0 mysqld

  This avoids cross-node memory access, which is slow due to interconnect latency.

• Also consider isolating CPU cores for database threads using isolcpus in GRUB (prevents kernel noise).

5. Network and TCP Tuning (for Remote Clients)

Even local databases benefit from TCP tuning if clients are remote.

• Increase socket buffer sizes:

   net.core.rmem_max = 134217728
  net.core.wmem_max = 134217728

• Enable TCP Fast Open and reuse:

   net.ipv4.tcp_fastopen = 3
  net.ipv4.tcp_tw_reuse = 1

These help reduce connection overhead and improve throughput under high concurrency.

Bonus: Monitor Before and After

Use tools like:

• iostat -x 1 → watch for high %util or await
• vmstat 1 → check for paging ( si/so )
• htop or top → CPU steel, load average
• Database-specific metrics (eg, InnoDB buffer pool hit rate, WAL write stalls)

Tuning without metrics is guesswork.

Bottom line:
Linux gives you the knobs—use them wisely. Start with I/O and memory (most impactful), then move to CPU/network if needed. Every database workload is different, so test changes in staging first.
Basically, just stop using defaults.

The above is the detailed content of Optimizing Linux Performance for Database Servers. For more information, please follow other related articles on the PHP Chinese website!