System tuning recommendations for write-heavy operations on Postgres systems
File access times
Applying the noatime attribute can significantly improve the file I/O performance of your server. Edit the /etc/fstab file and add the noatime attribute as follows:
/dev/sdb1 on / PostgreSQL ext3 rw,noatime
/dev/sdc1 on / Pg_xlog ext3 rw,noatime
/dev/sdd1 on / Tablespaces for tables ext3 rw,noatime
/dev/sde1 on / Tablespaces for indexes ext3 rw,noatime
# repeat for each additional disk
Read ahead
Modifying the Read-Ahead parameter can improve the read performance of a disk.
Type # blockdev --getra /dev/sda to check the Read-Ahead value of an individual disk. The default value is generally 256.
To permanently increase this value to 4096, edit the /etc/rc.local file by adding the following block to the end of the file:
blockdev --setra 4096 /dev/sda
blockdev --setra 4096 /dev/sdb
blockdev --setra 4096 /dev/sdc
# repeat for each additional disks
Read caching and swapping
Modifying the following parameters increases database performance:
Parameter | Recommendation |
|---|---|
vm.dirty_background_ratio | Decrease this value to 5 to make flushes more frequent but result in fewer I/O spikes. |
vm.dirty_ratio | Decrease this value to 10 to make flushes more frequent but result in fewer I/O spikes. |
vm.swappiness | Deactivate swapping to eliminate the tendency of the kernel to move processes out of physical memory and onto the swap disk. |
To permanently change these parameter values, edit the /etc/sysctl.conf file as follows:
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.swappiness = 0
To set the values without rebooting, run the sysctl -p command after saving and exiting the sysctl.conf file.
Shared memory and semaphores
A large PostgreSQL installation can quickly exhaust various operating system resource limits. The following example shows how to increase the shared memory to 16 GB of RAM:
# sysctl -w kernel.shmmax=8420048896
# sysctl -w kernel.shmall=2055676
# sysctl -w kernel.sem = 250 32000 32 128