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

Solution Verified - Updated

Environment

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

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, noop, or none scheduler can be implemented.

  • IMPORTANT: The noop scheduler applies to versions of Red Hat Enterprise Linux 5-7; for Red Hat Enterprise Linux
    8 and above, noop has been replaced by none. Furthermore, the IO scheduler deadline applies to Red Hat Enterprise Linux 5-7 while the IO scheduler mq-deadline applies to Red Hat Enterprise Linux 8 and above. For more information on these points, please refer to the the following knowledgebase articles;

For physical systems

The IO scheduler configurations differ between physical systems and virtual guests; virtual machine hosts often employ IO schedulers on behalf of their guests. If a guest does IO reordering, this guest-level reordering may be mitigated by the hosts reordering. As such, it is best to disable guest-level IO reordering with noop/none. Physical systems may still benefit from OS-level IO reordering.

Dynamically setting configurations

IO scheduler changes can be made dynamically while the system is running and/or statically to take effect after a reboot and persist across reboots.

Red Hat Enterprise Linux 7 and below

The deadline scheduler is recommended and can be set in the following manner:

for i in `ls /sys/block/sd*/queue/scheduler`; do echo "deadline" > $i; done
Red Hat Enterprise Linux 8 and above

The deadline scheduler is recommended and can be set in the following manner:

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

Statically setting configurations

Statically setting the configurations to persist across reboots requires a reboot for those change to apply if they are not set dynamically as described above.

Red Hat Enterprise Linux 7 and below

To statically set the configurations, add the elevator=deadline option to the linux kernel boot entry (Red Hat Enterprise Linux 6 and below) or the linux16 kernel boot entry (Red Hat Enterprise Linux 7) 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)
	linux /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
Red Hat Enterprise Linux 8 and above

To statically set the configurations, add the elevator=mq-deadline to the kernel parameters via grubby;

grubby --args='elevator=mq-deadline' --update-kernel=ALL

For virtual systems

  • The noop scheduler (Red Hat Enterprise Linux 5-7) or the none scheduler (Red Hat Enterprise Linux 8+) 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

(For Red Hat Enterprise Linux 8+, replace noop with none.)

  • Persistently, though requiring a reboot in order to take effect. Add the elevator=noop option (Red Hat Enterprise Linux 5-7) or the elevator=none option (Red Hat Enterprise Linux 8+) 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

(For Red Hat Enterprise Linux 8+, replace elevator=noop with elevator=none.)

  • Similarly for Red Hat Enterprise Linux virtual systems, add the elevator=noop option (Red Hat Enterprise Linux 5-7) or the elevator=none option (Red Hat Enterprise Linux 8+) 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"

(For Red Hat Enterprise Linux 8+, replace elevator=noop with elevator=none.)

  • 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
SBR
Product(s)
Components
Category
Tags

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.