Tuning a Red Hat Enterprise Linux server for a resident PostgreSQL database

Environment

• Red Hat Enterprise Linux 5
• Red Hat Enterprise Linux 6
• Red Hat Enterprise Linux 7
• Red Hat Enterprise Linux 8

Issue

• Our Production PostgresSQL Database is running slowly, how can we tune our Red Hat Enterprise Linux server for a PostgreSQL database workload?

Resolution

• Tuning for a Database workload, and specifically PostgreSQL, workload involves a number of different tuning be implemented. They fall into the categories below:

Virtual Memory

• The default tune profile for a Red Hat Enterprise Linux server is quite beneficial for I/O bandwidth intensive workloads. Database workloads on the other hand are quite latency sensitive and generally require the pagecache tunings below in order to function as efficient as possible:

/etc/sysctl.conf
<snip>
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 15
vm.dirty_background_ratio = 3

• These values indicate that:
  • The system is much less likely to swap.
  • Dirty pages, pages that have been marked as not matching the contents on disk via a write operation, are marked as old and eligible for pdflush operation much earlier after the initial write.
  • The pdflush daemons will wakeup for their periodic times twice as often.
  • The overall level of dirty pages in which the application load will be throttled to the speed of the backing storage during a write occurs at 15% of overall RAM.
  • The level related to dirty pages in which the pdflush daemons will wakeup in order to stay below the overall dirty_ratio value.

Note: These values are subject to variation in particular workload and will need to be evaluated after being implemented

I/O Scheduler tuning

• The default CFS I/O scheduler favors long sequential reads/writes over the smaller average database request size. In order to help favor these smaller requests, either the deadline or noop scheduler can be implemented.

For physical systems

• The deadline scheduler is recommended and can be implemented in the following manner:
  • Non-persistently only for this single boot iteration. Using only sdN devices in the example below:

for i in `ls /sys/block/sd*/queue/scheduler`; do echo "deadline" > $i; done

• Persistently, though requiring a reboot in order to take effect. Add the elevator=deadline option to the linux kernel boot entry within the /boot/grub/grub.conf configuration file as shown below:

/boot/grub/grub.conf
# cat /boot/grub/grub.conf 
<snip>
title Red Hat Enterprise Linux (2.6.32-358.el6.x86_64)
	root (hd0,0)
	kernel /vmlinuz-2.6.32-358.el6.x86_64 ro root=/dev/mapper/vg_00-lv_root elevator=deadline
	initrd /initramfs-2.6.32-358.el6.x86_64.img

• Similarly for RHEL7 physical systems, add the elevator=deadline option to the GRUB_CMDLINE_LINUX line in /etc/default/grub configuration file as shown below:

GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
#GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="vconsole.keymap=us crashkernel=128M rd.lvm.lv=RHEL7CSB/Root rd.luks.uuid=luks-64db41f8-4cc4-4b1b-80da-543423b948c5 rhgb quiet elevator=deadline"
GRUB_DISABLE_RECOVERY="true"
GRUB_THEME="/boot/grub2/themes/system/theme.txt"

• After modifying /etc/default/grub, regenerate the GRUB2 configuration. If your system uses BIOS firmware, execute the following command:

# grub2-mkconfig -o /boot/grub2/grub.cfg

• On a system with UEFI firmware, execute the following instead:

# grub2-mkconfig -o /boot/efi/EFI/redhat/grub.cfg

For virtual systems

• The noop scheduler is recommended and can be implemented in the following manner:
  • Non-persistently only for this single boot iteration. Using only sdN devices in the example below:

for i in `ls /sys/block/sd*/queue/scheduler`; do echo "noop" > $i; done

• Persistently, though requiring a reboot in order to take effect. Add the elevator=noop option to the linux kernel boot entry within the /boot/grub/grub.conf configuration file as shown below:

/boot/grub/grub.conf
# cat /boot/grub/grub.conf 
<snip>
title Red Hat Enterprise Linux (2.6.32-358.el6.x86_64)
	root (hd0,0)
	kernel /vmlinuz-2.6.32-358.el6.x86_64 ro root=/dev/mapper/vg_00-lv_root elevator=noop
	initrd /initramfs-2.6.32-358.el6.x86_64.img

• Similarly for RHEL7 virtual systems, add the elevator=noop option to the GRUB_CMDLINE_LINUX line in /etc/default/grub configuration file as shown below:

GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
#GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="vconsole.keymap=us crashkernel=128M rd.lvm.lv=RHEL7CSB/Root rd.luks.uuid=luks-64db41f8-4cc4-4b1b-80da-543423b948c5 rhgb quiet elevator=noop"
GRUB_DISABLE_RECOVERY="true"
GRUB_THEME="/boot/grub2/themes/system/theme.txt"

• After modifying /etc/default/grub, regenerate the GRUB2 configuration. If your system uses BIOS firmware, execute the following command:

# grub2-mkconfig -o /boot/grub2/grub.cfg

• On a system with UEFI firmware, execute the following instead:

# grub2-mkconfig -o /boot/efi/EFI/redhat/grub.cfg

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.