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!