How much shared memory is PostgreSQL using? (1) – basics

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 being 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’ll probably never notice that huge pages are not used at all.

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 ((128*1024)/4) pages of memory it will be only 64 ((128*1024)/2048) with huge pages, so much less effort to manage those lists.

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                 network-throughput       realtime                server-powersave
atomic-guest             desktop             laptop-ac-powersave       optimize-serial-console  realtime-virtual-guest  spectrumscale-ece
atomic-host              desktop-powersave   laptop-battery-powersave  oracle                   realtime-virtual-host   spindown-disk
balanced                 enterprise-storage  latency-performance       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           - Optmize 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. Lets 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.

Why is that? If you follow the official documentation for calculating huge pages on Linux, you’ll 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, lets 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’ll 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’ll need 71 instead of 70.

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

In the next post will dig into more details about which configurations change the shared memory requirements for PostgreSQL.