Configure Linux filesystem tuning and optimization for database workloads

Check current filesystem and I/O scheduler

First, identify your current filesystem type and I/O scheduler configuration to establish a baseline.

df -T /var/lib/postgresql
lsblk -f
cat /sys/block/sda/queue/scheduler

Configure filesystem mount options for database performance

Optimize mount options for your database storage partition. These settings reduce metadata updates and improve write performance.

sudo cp /etc/fstab /etc/fstab.backup
sudo nano /etc/fstab

Add or modify the mount options for your database partition:

# For ext4 filesystem (recommended for databases)
/dev/sda2 /var/lib/postgresql ext4 defaults,noatime,nodiratime,barrier=0,data=writeback 0 2

For XFS filesystem (alternative for large databases)
/dev/sda2 /var/lib/postgresql xfs defaults,noatime,nodiratime,nobarrier,logbufs=8,logbsize=256k 0 2

Apply the new mount options:

sudo systemctl stop postgresql
sudo umount /var/lib/postgresql
sudo mount -a
sudo systemctl start postgresql

Configure I/O scheduler optimization

Set the optimal I/O scheduler for database workloads. The mq-deadline scheduler works best for most database scenarios.

# Check available schedulers
cat /sys/block/sda/queue/scheduler

Set mq-deadline scheduler temporarily
echo mq-deadline | sudo tee /sys/block/sda/queue/scheduler

Make the I/O scheduler change permanent:

# Set I/O scheduler for database storage devices
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/scheduler}="mq-deadline"
ACTION=="add|change", KERNEL=="nvme[0-9]n[0-9]", ATTR{queue/scheduler}="mq-deadline"

Configure I/O scheduler queue depth and read-ahead:

# Optimize queue depth for databases
echo 32 | sudo tee /sys/block/sda/queue/nr_requests

Set read-ahead for sequential workloads
sudo blockdev --setra 4096 /dev/sda

Configure kernel parameters for database I/O

Optimize kernel parameters to improve database I/O performance and reduce latency.

# Filesystem and I/O optimizations for databases
Reduce dirty page writeback for consistent performance
vm.dirty_ratio = 5
vm.dirty_background_ratio = 2
vm.dirty_expire_centisecs = 1000
vm.dirty_writeback_centisecs = 200

Optimize virtual memory for database workloads
vm.swappiness = 1
vm.vfs_cache_pressure = 50
vm.zone_reclaim_mode = 0

Increase file descriptor limits
fs.file-max = 2097152

Optimize network and I/O buffers
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

Apply the kernel parameter changes:

sudo sysctl -p
sudo sysctl --system

Configure transparent huge pages for databases

Most databases perform better with transparent huge pages disabled. Configure this system-wide setting.

# Check current transparent huge pages setting
cat /sys/kernel/mm/transparent_hugepage/enabled

Disable transparent huge pages temporarily
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defrag

Make the transparent huge pages setting permanent:

# Add to GRUB_CMDLINE_LINUX parameter
GRUB_CMDLINE_LINUX="transparent_hugepage=never"

sudo update-grub
sudo reboot

For more details on memory optimization, see our Linux transparent huge pages guide.

Configure filesystem-specific optimizations

Apply additional optimizations based on your chosen filesystem type.

# Optimize ext4 for database workloads
sudo tune2fs -o journal_data_writeback /dev/sda2
sudo tune2fs -O ^has_journal /dev/sda2

Set optimal ext4 parameters
echo 0 | sudo tee /sys/fs/ext4/sda2/mb_group_prealloc
echo 512 | sudo tee /sys/fs/ext4/sda2/mb_stream_req

# Optimize XFS for database workloads
Set optimal allocation group size during filesystem creation
sudo mkfs.xfs -f -d agcount=32 -l logdev=/dev/sdb1,size=1g /dev/sda2

Configure XFS runtime parameters
echo 65536 | sudo tee /proc/fs/xfs/xfssyncd/sync_interval
echo 0 | sudo tee /proc/sys/fs/xfs/xfsaild_workqueue

Configure storage device optimizations

Apply low-level storage optimizations for maximum database performance.

# Disable NCQ (Native Command Queuing) for databases if using traditional HDDs
echo 1 | sudo tee /sys/block/sda/queue/nomerges

For SSDs, enable NCQ and optimize for random I/O
echo 0 | sudo tee /sys/block/nvme0n1/queue/nomerges
echo 0 | sudo tee /sys/block/nvme0n1/queue/rotational

Set optimal I/O timeout values
echo 30000 | sudo tee /sys/block/sda/queue/io_timeout

Create systemd service to apply storage optimizations at boot:

[Unit]
Description=Apply storage optimizations for database workloads
After=multi-user.target

[Service]
Type=oneshot
ExecStart=/bin/bash -c 'echo mq-deadline > /sys/block/sda/queue/scheduler'
ExecStart=/bin/bash -c 'echo 32 > /sys/block/sda/queue/nr_requests'
ExecStart=/bin/bash -c 'blockdev --setra 4096 /dev/sda'
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target

sudo systemctl enable storage-tuning.service
sudo systemctl start storage-tuning.service

Install I/O monitoring tools

Install essential tools to monitor and validate your filesystem performance improvements.

sudo apt update
sudo apt install -y sysstat iotop htop fio

sudo dnf install -y sysstat iotop htop fio

Benchmark filesystem performance

Run I/O benchmarks to validate your filesystem optimizations using fio (Flexible I/O Tester).

# Test random read performance (database read workload)
sudo fio --name=db-random-read --ioengine=libaio --rw=randread --bs=8k --numjobs=4 --iodepth=32 --runtime=60 --time_based --filename=/var/lib/postgresql/test --size=1G

Test random write performance (database write workload)
sudo fio --name=db-random-write --ioengine=libaio --rw=randwrite --bs=8k --numjobs=4 --iodepth=32 --runtime=60 --time_based --filename=/var/lib/postgresql/test --size=1G --fsync=1

Clean up test files:

sudo rm -f /var/lib/postgresql/test