Tuning Red Hat Enterprise Linux Family for PostgreSQL

March 12, 2021

When it comes to PostgreSQL performance, tuning the operating system gives you extra opportunities for better performance. In this blog post, I will explain how you can tune PostgreSQL for Red Hat Enterprise Linux (RHEL) family. 

Tuned daemon

Most of the tuning on RHEL is done with the tuned daemon. This daemon adapts the operating system to perform better for the workload.

Note that the commands shown below are for RHEL 8. If you're using RHEL 7, you should use the yum command wherever dnf is shown in the examples.

The tuned daemon comes installed by default. If it does not (based on kickstart files), install it with:

dnf -y install tuned

And enable it with:

systemctl enable --now tuned

Tuned helps sysadmins to change kernel settings easily and dynamically, and they no longer need to make changes in /etc/sysctl -- this is done via tuned.

Tuned  comes with a few predefined profiles. You can get the list using the

tuned-adm list

command. The RHEL installer will pick up a good default based on the environment. The bare metal default is “throughput-performance”, which aims at increasing throughput. You can run the following command to see what tuned daemon will recommend after assessing your system:

tuned-adm recommend

Use  the command below to see the preconfigured value:

tuned-adm active
Current active profile: virtual-guest

However, the defaults may end up slowing down PostgreSQL -- they may prefer power saving, which will slow down CPUs. Similar arguments are valid for network and I/O tuning as well. To solve this problem, we will create our own profile for PostgreSQL performance.

Creating a new profile is quite easy. Let’s call this profile as “edbpostgres”. Run these commands as root:

#  This directory name will also be the
#  name of the profile:
mkdir /etc/tuned/edbpostgres
# Create the profile file:
echo "
[main]
summary=Tuned profile for EDB PostgreSQL Instances
[bootloader]
cmdline=transparent_hugepage=never
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[sysctl]
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.overcommit_memory=0
net.ipv4.tcp_timestamps=0

[vm]
transparent_hugepages=never
" > /etc/tuned/edbpostgres/tuned.conf

The lines that include [ and ] are called “tuned plugins”, and are used to interact with the given part of the system.

Let’s examine these parameters and values:

  • [main] plugin includes summary information, and can also be used to include values from other tuned profiles with include statements.
  • [cpu] plugin includes settings around CPU governor, and cpu power settings.
  • [sysctl] plugin includes the values that interact with procfs.     
  • [vm] and [bootloader] plugins enable/disable transparent  huge pages (bootloader plugin will help us to interact with GRUB command line parameters).


With these changes, we aim to achieve the following:

  • CPUs will not enter power saving modes (PostgreSQL won’t suffer from random performance slowdowns)
  • Linux will be much less likely to swap.
  • The kernel will help Postgres to flush dirty pages, reducing load of bgwriter and checkpointer. 
  • The pdflush daemon will run more frequently  
  • It is a good practice to turn TCP timestamps off, to avoid (or, at least, reduce) spikes caused by timestamp generation.
  • Disabling transparent huge pages is a great benefit to PostgreSQL performance.


To enable these changes, run this command:

tuned-adm profile edbpostgres

This command may take some time to complete.

To disable transparent huge pages completely, run this command:

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

and reboot your system.

systemctl start reboot.target

Optimizing the filesystem

Another tuning point is disks. PostgreSQL does not rely on atime for  the data files, so disabling them will shave cpu cycles.

Open /etc/fstab, and add atime,nodiratime just near the defaults value for the drive that PostgreSQL data and WAL files are kept.

/dev/mapper/pgdata-01-data /pgdata xfs 	defaults,noatime 1 1

To activate it immediately, run:

mount -o remount,noatime,nodiratime /pgdata

Please note that these suggestions are good for a start, and you need to monitor both the operating system and PostgreSQL to gather more data for finer tuning.
 

Huge pages

By default, the page size on Linux is 4kb. A typical PostgreSQL instance may allocate many GBs of memory, which will end up with potential performance problems with such a small page size. Also, given that these pages will be defragged, using them for large data sets will end up with extra time for mapping them.

Enabling huge pages on Linux will give a performance boost to PostgreSQL. 

By default, huge pages are not enabled on Linux, which is also suitable for PostgreSQL’s default huge_pages setting, which is “try”, basically means “use huge pages if available on the OS, otherwise no.”

There are two aspects to setting up huge pages for PostgreSQL: Configuring OS, and configuring PostgreSQL.

Let’s start with finding how many huge pages are needed on your system for PostgreSQL. When a PostgreSQL instance is started, postmaster creates a file called postmaster.pid file in $PGDATA. You can find the pid of the main process there:

$ head -n 1 $PGDATA/postmaster.pid
1991

Now, find VmPeak for this instance:

$ grep -i vmpeak /proc/1991/status
VmPeak:     8823028 kB

Tip: If you are running more than one PostgreSQL instance on the same server, please calculate the sum of all VmPeak values in the next step.
Let’s confirm the huge page size:

$ grep -i hugepagesize /proc/meminfo
Hugepagesize:     2048 kB

Finally let’s calculate the number of huge pages that the instance(s) will need:

8823028 / 2048 = 4308.12

The ideal number of huge pages is just a bit higher than this -- just a bit. If you increase this value too much, processes that need small pages that also need space in the OS will fail to start. This may even end up with the operating system failing to boot or other PostgreSQL instances on the same server failing to start.

Now edit the tuned.conf file created above, and add the following line to the [vm] section:

vm.nr_hugepages=4500

and run this command to enable new settings:

tuned-adm profile edbpostgres

Now you can set:

huge_pages=on

in postgresql.conf , and (re)start PostgreSQL.

We also need to make sure that tuned service will start before PostgreSQL service after reboot. Edit unit file:

systemctl edit postgresql-13.service

and add these 2 lines:

[Unit]
After=tuned.service

Run:

systemctl daemon-reload

for the changes to take effect.
 

Conclusion

Tuning the RHEL family for PostgreSQL involves both hardware and software. The tuned daemon is a central point to tune many of the knobs involved; a few tweaks will help PostgreSQL to utilize the system better.

We will publish blogs about tuning PostgreSQL on Debian/Ubuntu as well, so please stay tuned!

Share this

Relevant Blogs

What is pgvector and How Can It Help You?

There are a thousand ways (likely more) you can accelerate Postgres workloads. It all comes down to the way you store data, query data, how big your data is and...
November 03, 2023

More Blogs

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023