System tuning recommendations for write-heavy operations


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

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*