{"id":16673,"date":"2021-09-08T17:42:03","date_gmt":"2021-09-08T15:42:03","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-much-shared-memory-is-postgresql-using-1-basics\/"},"modified":"2021-09-08T17:42:03","modified_gmt":"2021-09-08T15:42:03","slug":"how-much-shared-memory-is-postgresql-using-1-basics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-much-shared-memory-is-postgresql-using-1-basics\/","title":{"rendered":"How much shared memory is PostgreSQL using? (1) – basics"},"content":{"rendered":"

Have you ever wondered if the value of shared_buffers<\/a> 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<\/a> 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<\/a>. 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.<\/p>\n

<\/p>\n

Let’s start with the default configuration on Linux: shared_buffers=128MB:<\/p>\n

\npostgres=# show shared_buffers;\n shared_buffers \n----------------\n 128MB\n(1 row)\n<\/pre>\n
The default page size on Linux is 4kB:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] getconf PAGESIZE\n4096\n<\/pre>\n
This means all memory is allocated in chunks of 4kB. The default huge page size on Linux is 2MB:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] grep Hugepagesize \/proc\/meminfo \nHugepagesize:       2048 kB\n<\/pre>\n
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.<\/p>\n
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<\/a> for easier configuration of huge pages:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo apt install tuned -y\n<\/pre>\n
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:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] tuned-adm active\nCurrent active profile: virtual-guest\n<\/pre>\n
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):<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] ls \/usr\/lib\/tuned\/\naccelerator-performance  defirqaffinity.py   intel-sst                 network-throughput       realtime                server-powersave\natomic-guest             desktop             laptop-ac-powersave       optimize-serial-console  realtime-virtual-guest  spectrumscale-ece\natomic-host              desktop-powersave   laptop-battery-powersave  oracle                   realtime-virtual-host   spindown-disk\nbalanced                 enterprise-storage  latency-performance       postgresql               recommend.d             throughput-performance\ncpu-partitioning         functions           mssql                     powersave                sap-hana                virtual-guest\ndefault                  hpc-compute         network-latency           __pycache__              sap-netweaver           virtual-host\n<\/pre>\n
Another way to get the available profiles is this:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] tuned-adm list\nAvailable profiles:\n- accelerator-performance     - Throughput performance based tuning with disabled higher latency STOP states\n- atomic-guest                - Optimize virtual guests based on the Atomic variant\n- atomic-host                 - Optimize bare metal systems running the Atomic variant\n- balanced                    - General non-specialized tuned profile\n- cpu-partitioning            - Optimize for CPU partitioning\n- default                     - Legacy default tuned profile\n- desktop                     - Optimize for the desktop use-case\n- desktop-powersave           - Optmize for the desktop use-case with power saving\n- enterprise-storage          - Legacy profile for RHEL6, for RHEL7, please use throughput-performance profile\n- hpc-compute                 - Optimize for HPC compute workloads\n- intel-sst                   - Configure for Intel Speed Select Base Frequency\n- laptop-ac-powersave         - Optimize for laptop with power savings\n- laptop-battery-powersave    - Optimize laptop profile with more aggressive power saving\n- latency-performance         - Optimize for deterministic performance at the cost of increased power consumption\n- mssql                       - Optimize for MS SQL Server\n- network-latency             - Optimize for deterministic performance at the cost of increased power consumption, focused on low latency network performance\n- network-throughput          - Optimize for streaming network throughput, generally only necessary on older CPUs or 40G+ networks\n- optimize-serial-console     - Optimize for serial console use.\n- oracle                      - Optimize for Oracle RDBMS\n- postgresql                  - Optimize for PostgreSQL server\n- powersave                   - Optimize for low power consumption\n- realtime                    - Optimize for realtime workloads\n- realtime-virtual-guest      - Optimize for realtime workloads running within a KVM guest\n- realtime-virtual-host       - Optimize for KVM guests running realtime workloads\n- sap-hana                    - Optimize for SAP HANA\n- sap-netweaver               - Optimize for SAP NetWeaver\n- server-powersave            - Optimize for server power savings\n- spectrumscale-ece           - Optimized for Spectrum Scale Erasure Code Edition Servers\n- spindown-disk               - Optimize for power saving by spinning-down rotational disks\n- throughput-performance      - Broadly applicable tuning that provides excellent performance across a variety of common server workloads\n- virtual-guest               - Optimize for running inside a virtual guest\n- virtual-host                - Optimize for running KVM guests\nCurrent active profile: virtual-guest\n<\/pre>\n
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:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"vm.nr_hugepages=64\" | sudo tee -a \/usr\/lib\/tuned\/virtual-guest\/tuned.conf\nvm.nr_hugepages=64\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo tuned-adm profile virtual-guest\npostgres@debian11pg:\/home\/postgres\/ [pgdev] egrep \"HugePages_Total|HugePages_Free\" \/proc\/meminfo \nHugePages_Total:      64\nHugePages_Free:       64\n<\/pre>\n
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?<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl stop\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl start\npostgres@debian11pg:\/home\/postgres\/ [pgdev] egrep \"HugePages_Total|HugePages_Free\" \/proc\/meminfo \nHugePages_Total:      64\nHugePages_Free:       64\n<\/pre>\n
No, not at all. Lets try to force it by setting huge_pages<\/a> to “on”. By doing this PostgreSQL will not start if there are not enough huge pages available (the default is “try”):<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"alter system set huge_pages=on\" \nALTER SYSTEM\n19:12:28 postgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl stop\n19:12:34 postgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl start\nwaiting for server to start....2021-09-08 19:12:36.508 CEST [2542] FATAL:  could not map anonymous shared memory: Cannot allocate memory\n2021-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.\n2021-09-08 19:12:36.508 CEST [2542] LOG:  database system is shut down\n stopped waiting\npg_ctl: could not start server\nExamine the log output.\n<\/pre>\n
Why is that? If you follow the official documentation<\/a> for calculating huge pages on Linux, you’ll notice that the procedure is slightly more complicated:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] cat \/dev\/null > $PGDATA\/postgresql.auto.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl start\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pmap 2573 | awk '\/rw-s\/ && \/zero\/ {print $2}'\n145360K\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"145360\/2048\" | bc\n70\n<\/pre>\n
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:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"alter system set huge_pages=on\" \nALTER SYSTEM\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo sed -i 's\/64\/70\/g' \/usr\/lib\/tuned\/virtual-guest\/tuned.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo tuned-adm profile virtual-guest\npostgres@debian11pg:\/home\/postgres\/ [pgdev] egrep \"HugePages_Total|HugePages_Free\" \/proc\/meminfo \nHugePages_Total:      70\nHugePages_Free:       70\n<\/pre>\n
Will this work?<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl stop\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl start\nwaiting for server to start....2021-09-08 19:23:57.097 CEST [2618] FATAL:  could not map anonymous shared memory: Cannot allocate memory\n2021-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.\n2021-09-08 19:23:57.097 CEST [2618] LOG:  database system is shut down\n stopped waiting\npg_ctl: could not start server\nExamine the log output.\n<\/pre>\n
No. Add one more and you’ll be fine:<\/p>\n
\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo sed -i 's\/70\/71\/g' \/usr\/lib\/tuned\/virtual-guest\/tuned.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] sudo tuned-adm profile virtual-guest\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl start\nwaiting 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\n2021-09-08 19:25:11.395 CEST [2633] LOG:  listening on IPv6 address \"::1\", port 5432\n2021-09-08 19:25:11.395 CEST [2633] LOG:  listening on IPv4 address \"127.0.0.1\", port 5432\n2021-09-08 19:25:11.413 CEST [2633] LOG:  listening on Unix socket \"\/tmp\/.s.PGSQL.5432\"\n2021-09-08 19:25:11.443 CEST [2636] LOG:  database system was shut down at 2021-09-08 19:23:55 CEST\n2021-09-08 19:25:11.462 CEST [2633] LOG:  database system is ready to accept connections\n done\nserver started\n<\/pre>\n
Why? Because 145360\/2048 gives 70.9765625 exactly, so you’ll need 71 instead of 70. <\/p>\n
That’s it for the basics, things to remember:<\/p>\n