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 Debian / Ubuntu. Please refer to this post, Tuning Red Hat Enterprise Linux family for PostgreSQL, if you want to learn how to tune PostgreSQL on that platform.
sysctl
Most of the tuning on Debian and Ubuntu is done with the sysctl. The postgresql-common and edb-as-common packages already provide a minimal set of settings, but we need to add more on top of that.
sysctl first reads files under the /etc/sysctl.d directory, and then reads sysctl.conf. Please make sure that there are no overlapping parameters in sysctl.conf which will override the ones with the file that we will create now. The files under /etc/sysctl.d are read by numeric order, so files with larger numbers will override files with smaller numbers.
Run these commands as root:
# Create sysctl config file:
echo "
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
" > /etc/sysctl.d/40-edbpostgres.conf
With these changes, we aim to achieve the following:
- 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.
To enable these changes, run this command:
sysctl --system
The changes will take effect immediately.
Disabling transparent hugepages
Unlike hugepages, transparent hugepages are allocated dynamically during runtime, and they are swappable. This is not good for most database systems, as it decreases performance. Disabling transparent huge pages is a great benefit to PostgreSQL performance. On Debian / Ubuntu, first edit /etc/default/grub and add transparent_hugepage=never to GRUB_CMDLINE_LINUX_DEFAULT line:
GRUB_CMDLINE_LINUX_DEFAULT="biosdevname=0 net.ifnames=0 console=tty0
console=ttyS0,115200 earlyprintk=ttyS0,115200 consoleblank=0
systemd.show_status=true transparent_hugepage=never "
Now, make that setting permanent:
update-grub
Every kernel update will pick up this value from now on.
Finally reboot your system with the new kernel:
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 next to 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 (for example /var/lib/postgresql/13/main/ for PostgreSQL 13) . 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 /etc/sysctl.d/40-edbpostgres.conf file we created above, and add the following line to
vm.nr_hugepages=4500
and run the following command for this new setting to be effective. Please note that this may take some time while Linux is busy with setting up huge pages:
sysctl --system
Now you can set
huge_pages=on
in postgresql.conf, and (re)start PostgreSQL.
Conclusion
Just like tuning RHEL for Postgres, tuning the Debian and Ubuntu family for PostgreSQL involves both hardware and software. Unlike RHEL which uses tuned, Debian and Ubuntu utilize sysctl for tuning the operating system. By following the steps in this blog post you should be able to optimize the tuning of your operating system for running PostgreSQL.
Want to dive deeper on how to get the most out of PostgreSQL? Visit EDB Docs for more information on a variety of topics.