PostgreSQL Shared Memory (Huge Pages)

Daniel Westermann

April 26, 2023 - 7 minutes read - 1410 words

Have you ever wondered if the value of shared_buffers is the only setting which controls how much shared memory PostgreSQL is actually using? There was a nice addition committed to PostgreSQL 15 recently, which helps in answering this question (more on that later). shared_buffers for sure is the biggest chunk of shared memory that PostgreSQL is using, but there might be other factors which come into the game.

You might ask yourself why that is important, and one bit you need to keep in mind are huge pages. Those need to be calculated by you and provided by the operating system for PostgreSQL to be able to use them. In the default configuration of PostgreSQL you might not even notice that the number of huge pages provided by the OS are not sufficient. PostgreSQL will happily start and you will probably never notice that huge pages are not used at all.

Default Configuration on Linux

Let’s start with the default configuration on Linux: shared_buffers=128MB.

postgres=# show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

The default page size on Linux is 4kB:

postgres@debian11pg:/home/postgres/ [pgdev] getconf PAGESIZE
4096

This means all memory is allocated in chunks of 4kB. The default huge page size on Linux is 2MB:

postgres@debian11pg:/home/postgres/ [pgdev] grep Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

The benefit of switching to huge pages should already be clear now: instead of allocating 32768 ((1281024)/4) pages of memory it will be only 64 ((1281024)/2048) with huge pages, so much less effort to manage those lists.

Configuring Huge Pages with tuned

If shared buffers are the only bit to consider, we should be able to start PostgreSQL with 64 huge pages provided by the operating system. Let’s try that and install tuned for easier configuration of huge pages:

postgres@debian11pg:/home/postgres/ [pgdev] sudo apt install tuned -y

I am not going into the details of tuned here (check the link above if you want to know more). The currently active profile in my case is this one:

postgres@debian11pg:/home/postgres/ [pgdev] tuned-adm active
Current active profile: virtual-guest

The default profiles are located here. You should create your own profiles for your specific needs under /etc/tuned; do not modify the default ones as they might be overwritten with the next system update:

postgres@debian11pg:/home/postgres/ [pgdev] ls /usr/lib/tuned/
accelerator-performance  defirqaffinity.py       intel-sst
networkthroughput        realtime                server-powersave
atomic-guest             desktop                 laptop-ac-powersave
optimize-serial-console  realtime-virtual-guest  spectrumscale-ece
atomic-host              desktop-powersave       laptop-batterypowersave
oracle                   realtime-virtual-host   spindown-disk
balanced                 enterprise-storage      latencyperformance
postgresql               recommend.d             throughput-performance
Cpu-partitioning         functions               mssql
powersave                sap-hana                virtual-guest
default                  hpc-compute             network-latency
__pycache__              sap-netweaver           virtual-host

Another way to get the available profiles is this:

postgres@debian11pg:/home/postgres/ [pgdev] tuned-adm list
Available profiles:
- accelerator-performance     - Throughput performance based tuning with disabled higher latency STOP states
- atomic-guest                - Optimize virtual guests based on the Atomic variant
- atomic-host                 - Optimize bare metal systems running the Atomic variant
- balanced                    - General non-specialized tuned profile
- cpu-partitioning            - Optimize for CPU partitioning
- default                     - Legacy default tuned profile
- desktop                     - Optimize for the desktop use-case
- desktop-powersave           - Optimize for the desktop use-case with power saving
- enterprise-storage          - Legacy profile for RHEL6, for RHEL7, please use throughput-performance profile
- hpc-compute                 - Optimize for HPC compute workloads
- intel-sst                   - Configure for Intel Speed Select Base Frequency
- laptop-ac-powersave         - Optimize for laptop with power savings
- laptop-battery-powersave    - Optimize laptop profile with more aggressive power saving
- latency-performance         - Optimize for deterministic performance at the cost of increased power consumption
- mssql                       - Optimize for MS SQL Server
- network-latency             - Optimize for deterministic performance at the cost of increased power consumption, focused on low latency network performance
- network-throughput          - Optimize for streaming network throughput, generally only necessary on older CPUs or 40G+ networks
- optimize-serial-console     - Optimize for serial console use.
- oracle                      - Optimize for Oracle RDBMS
- postgresql                  - Optimize for PostgreSQL server
- powersave                   - Optimize for low power consumption
- realtime                    - Optimize for realtime workloads
- realtime-virtual-guest      - Optimize for realtime workloads running within a KVM guest
- realtime-virtual-host       - Optimize for KVM guests running realtime workloads
- sap-hana                    - Optimize for SAP HANA
- sap-netweaver               - Optimize for SAP NetWeaver
- server-powersave            - Optimize for server power savings
- spectrumscale-ece           - Optimized for Spectrum Scale Erasure Code Edition Servers
- spindown-disk               - Optimize for power saving by spinning-down rotational disks
- throughput-performance      - Broadly applicable tuning that provides excellent performance across a variety of common server workloads
- virtual-guest               - Optimize for running inside a virtual guest
- virtual-host                - Optimize for running KVM guests
Current active profile: virtual-guest

As I am currently using the “virtual-guest” profile, let’s add the calculated number of huge pages to that profile and then make the system re-apply the settings:

postgres@debian11pg:/home/postgres/ [pgdev] echo "vm.nr_hugepages=64" | sudo tee -a /usr/lib/tuned/virtual-guest/tuned.conf
vm.nr_hugepages=64
postgres@debian11pg:/home/postgres/ [pgdev] sudo tuned-adm profile virtual-guest
postgres@debian11pg:/home/postgres/ [pgdev] egrep "HugePages_Total|HugePages_Free" /proc/meminfo
HugePages_Total:      64
HugePages_Free:       64

This confirms that we now have 64 huge pages available, of which all are free currently. Will PostgreSQL use any of those if we restart?

postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl stop
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
postgres@debian11pg:/home/postgres/ [pgdev] egrep "HugePages_Total|HugePages_Free" /proc/meminfo
HugePages_Total:      64
HugePages_Free:       64

No, not at all. Let’s try to force it by setting huge_pages to “on”. By doing this, PostgreSQL will not start if there are not enough huge pages available (the default is “try”):

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "alter system set huge_pages=on"
ALTER SYSTEM
19:12:28 postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl stop
19:12:34 postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
waiting for server to start....2021-09-08 19:12:36.508 CEST [2542] FATAL:  could not map anonymous shared memory: Cannot allocate memory
2021-09-08 19:12:36.508 CEST [2542] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 148897792 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
2021-09-08 19:12:36.508 CEST [2542] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Calculating the Correct Number of Huge Pages

Why is that? If you follow the official documentation for calculating huge pages on Linux, you will notice that the procedure is slightly more complicated:

postgres@debian11pg:/home/postgres/ [pgdev] cat /dev/null > $PGDATA/postgresql.auto.conf
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
postgres@debian11pg:/home/postgres/ [pgdev] pmap 2573 | awk '/rw-s/ && /zero/ {print $2}'
145360K
postgres@debian11pg:/home/postgres/ [pgdev] echo "145360/2048" | bc
70

This is 6 more than what we calculated above and confirms that shared_buffers is not the only setting to consider. Anyway, let’s try with this value:

postgres@debian11pg:/home/postgres/ [pgdev] psql -c "alter system set huge_pages=on"
ALTER SYSTEM
postgres@debian11pg:/home/postgres/ [pgdev] sudo sed -i 's/64/70/g' /usr/lib/tuned/virtual-guest/tuned.conf
postgres@debian11pg:/home/postgres/ [pgdev] sudo tuned-adm profile virtual-guest
postgres@debian11pg:/home/postgres/ [pgdev] egrep "HugePages_Total|HugePages_Free" /proc/meminfo
HugePages_Total:      70
HugePages_Free:       70

Will this work?

postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl stop
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
waiting for server to start....2021-09-08 19:23:57.097 CEST [2618] FATAL:  could not map anonymous shared memory: Cannot allocate memory
2021-09-08 19:23:57.097 CEST [2618] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 148897792 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
2021-09-08 19:23:57.097 CEST [2618] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

No. Add one more and you will be fine:

postgres@debian11pg:/home/postgres/ [pgdev] sudo sed -i 's/70/71/g' /usr/lib/tuned/virtual-guest/tuned.conf
postgres@debian11pg:/home/postgres/ [pgdev] sudo tuned-adm profile virtual-guest
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
waiting for server to start....2021-09-08 19:25:11.393 CEST [2633] LOG:  starting PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2021-09-08 19:25:11.395 CEST [2633] LOG:  listening on IPv6 address "::1", port 5432
2021-09-08 19:25:11.395 CEST [2633] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-09-08 19:25:11.413 CEST [2633] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-09-08 19:25:11.443 CEST [2636] LOG:  database system was shut down at 2021-09-08 19:23:55 CEST
2021-09-08 19:25:11.462 CEST [2633] LOG:  database system is ready to accept connections
 done
server started

Why? Because 145360/2048 gives 70.9765625 exactly, so you will need 71 instead of 70.

Key Takeaways

That’s it for the basics. Things to remember:

shared_buffers is not the only configuration you need to take into account for configuring huge pages
tuned is a nice and easy way to deal with system/kernel configurations, and it is available on Debian as well
the official way of configuring huge pages for PostgreSQL works, but there is more to know for doing it correctly

References

How Much Shared Memory Is PostgreSQL Using? (1) Basics