Infrastructure at your Service

Daniel Westermann

How much shared memory is PostgreSQL using? (2) – details

In the last post in this two post series we’ve briefly looked at configuring huge pages on Linux for PostgreSQL. It became clear, that calculating the number of huge pages purely based on the setting of shared_buffers will not work. PostgreSQL requires more shared memory, but what exactly is it? Well, this is the goal of this post and I’ll introduce a new feature of PostgreSQL 15 which helps in identifying those bits without digging into the operating systems details about memory management.

Let’s quickly review the current situation. We have shared_buffers set to 128MB:

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

Using the official way of calculating the huge pages we see that PostgreSQL currently requires this amount of shared memory (huge pages in PostgreSQL need to be disabled for this):

[email protected]:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
826
[email protected]:/home/postgres/ [pgdev] pmap 826 | awk '/rw-s/ && /zero/ {print $2}'
145360K

As we already have seen in the last post this is more than the 128MB configured for shared_buffers. What else could change the requirement for shared memory. What about wal_buffers? In the default configuration (-1) this consumes 128/32 MB:

postgres=# show wal_buffers;
 wal_buffers 
-------------
 4MB
(1 row)

What happens if we change it?

[email protected]:/home/postgres/ [pgdev] psql -c "alter system set wal_buffers='16MB'"
ALTER SYSTEM
[email protected]:/home/postgres/ [pgdev] pg_ctl restart
[email protected]:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
858
[email protected]:/home/postgres/ [pgdev] pmap 858 | awk '/rw-s/ && /zero/ {print $2}'
157664K

This increases shared memory usage from 145360K to 157664K. So, yes, wal_buffers definitely impact the usage of shared memory. What about extensions, could they have an impact as well?

[email protected]:/home/postgres/ [pgdev] psql -c "alter system set shared_preload_libraries=pg_stat_statements"
ALTER SYSTEM
[email protected]:/home/postgres/ [pgdev] pg_ctl restart
[email protected]:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
882
[email protected]:/home/postgres/ [pgdev] pmap 882 | awk '/rw-s/ && /zero/ {print $2}'
159176K

We’ve again increased shared memory usage from 157664K to 159176K. Extensions also might consume shared memory. What about max_connections?

[email protected]:/home/postgres/ [pgdev] psql -c "show max_connections"
 max_connections 
-----------------
 100
(1 row)

[email protected]:/home/postgres/ [pgdev] psql -c "alter system set max_connections=1000"
ALTER SYSTEM
[email protected]:/home/postgres/ [pgdev] pg_ctl restart
[email protected]:/home/postgres/ [pgdev] head -1 $PGDATA/postmaster.pid
900
[email protected]:/home/postgres/ [pgdev] pmap 900 | awk '/rw-s/ && /zero/ {print $2}'
201288K

Next increase, this time from 159176K to 201288K. Without going any further: There was no way to see the real shared memory consumption from inside PostgreSQL and this is why this discussion started. The result is this:

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

postgres=# show shared_memory_size;
 shared_memory_size 
--------------------
 197MB
(1 row)

… which is pretty close to 201288K. Really nice.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure