PostgreSQL on Linux: Counting Committed Memory

By default Linux uses a controversial (for databases) memory extension feature called overcommit. How that interacts with PostgreSQL is covered in the Managing Kernel Resources section of the PG manual.

Overcommit allows clients to pre-allocate virtual memory beyond even server RAM. They are only nailed down to a real allocation, committed to use its terminology, when it's actually used. This lets applications have a flatter memory model without having to grapple with virtual memory coding. This model improves how effectively swap can work as well.

If you upgraded PostgreSQL or increased your server's shared_buffers setting recently, you may find a larger chunk of memory is now listed in Linux's "Committed" section that wasn't noticeable before. Let's walk through enough of this area to interpret the associated system memory metrics.

Shared memory history

In PostgreSQL versions up to 9.2, the shared memory block needed to run the server was allocated directly as UNIX System V shared memory. Documentation from that era gave an estimate of memory needed in that block. The 9.2 Kernel Resources has it in Table 17-2 "PostgreSQL Shared Memory Usage".

Starting in PostgreSQL 9.3, "PostgreSQL normally allocates a very small amount of System V shared memory, as well as a much larger amount of POSIX (mmap) shared memory", quoting the 10.0 Kernel Resources. The system then commits the shared_buffers memory to pin them down and initialize. That's why the shared/committed balance of newer Postgres servers will look very different from older versions. The memory use formula numbers were made largely obsolete by this change, and that table was impossible to maintain well in the documentation anyway. That's why the level of detail was reduced when switching to the new mmap allocation style.

PG10 example

This example uses the PostgreSQL 10 included with Ubuntu 18.04; you can use any Linux distribution albeit with different service control scripts. Start with the server down (more on the right syntax below) and look at the memory use:

$ service postgresql stop
$ cat /proc/meminfo | grep Commit
CommitLimit:    10252072 kB
Committed_AS:     806928 kB

On this 16GB RAM server, that gives CommitLimit=10252072kB 10GB. Currently locked down, committed RAM Committed_AS=806928kB 800MB. This is memory dedicated to the core Linux operating system and its utilities. You might conclude that this OS as configured requires at least 1GB to run at all, which is accurate.

On this server, starting the database correctly means I have to drop back to my user account to use sudo. You can easily give those powers to the postgres Linux account instead, it's just not necessary on my test system. The proper systemd call to stop and start the database on this server uses systemctl. Here are some alternate forms of startup lines you might need to use instead:

gsmith@hydra:~$ sudo systemctl start postgresql@10-main
postgres@hydra:~$ service postgresql start
postgres@hydra:~$ pg_ctlcluster 10 main start # Debian/Ubuntu, PG10

Confirm the database just restarted:

postgres@hydra:~$ ps -eaf | grep postgres
postgres  8022     1  0 06:40 ?        00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
postgres  8024  8022  0 06:40 ?        00:00:00 postgres: 10/main: checkpointer process
postgres  8025  8022  0 06:40 ?        00:00:00 postgres: 10/main: writer process
postgres  8026  8022  0 06:40 ?        00:00:00 postgres: 10/main: wal writer process
postgres  8027  8022  0 06:40 ?        00:00:00 postgres: 10/main: autovacuum launcher process
postgres  8028  8022  0 06:40 ?        00:00:00 postgres: 10/main: stats collector process
postgres  8029  8022  0 06:40 ?        00:00:00 postgres: 10/main: bgworker: logical replication launcher
postgres@hydra:~$ date
Sat May  1 06:42:45 EDT 2021

And check the biggest user of committed memory, shared_buffers:

postgres@hydra:~$ psql -c "show shared_buffers"
 shared_buffers
----------------
 4GB

Now let's look at memory again:

postgres@hydra:~$  cat /proc/meminfo | grep Commit
CommitLimit:    10252072 kB
Committed_AS:    5115160 kB

Committed_AS jumped to 5115160 kB4.9GB. Since it was 800MB before, that means the database server committed a new 4308232kb4.1GB on startup. That's the shared memory block, which includes shared_buffers plus some overhead for clients and other shared state.

Digging into the memory

You can see more about where the memory is going when using the pmap utility. While most of the bytes are shared_buffers, the bulk of the text output is linking to various shared libraries. Here's a grep command that screens most of the trivia out:

postgres@hydra:~$ pmap -x 8022 | egrep -v "anon|lib|ld|locale"
Address           Kbytes     RSS   Dirty Mode  Mapping
00005637cb721000    7012    3492       0 r-x-- postgres
00005637cb721000       0       0       0 r-x-- postgres
00005637cbffa000     136     136     136 r---- postgres
00005637cbffa000       0       0       0 r---- postgres
00005637cc01c000      52      52      52 rw--- postgres
00005637cc01c000       0       0       0 rw--- postgres
00007f919cb75000 4317408  108240  108240 rw-s- zero (deleted)
00007f919cb75000       0       0       0 rw-s- zero (deleted)
00007f92ae841000       8       4       4 rw-s- PostgreSQL.158420325
00007f92ae841000       0       0       0 rw-s- PostgreSQL.158420325
00007f92ae843000       4       4       4 rw-s-   [ shmid=0x48000 ]
00007f92ae843000       0       0       0 rw-s-   [ shmid=0x48000 ]
00007ffec9539000     132      32      32 rw---   [ stack ]
00007ffec9539000       0       0       0 rw---   [ stack ]
---------------- ------- ------- -------
total kB         4492416  124396  110252

The key block is obviously this one:

00007f919cb75000 4317408  108240  108240 rw-s- zero (deleted)

That shows 4317408k is the zeroed out buffer space holding shared_buffers, while 108240k is nailed down using an old SysV resident memory allocation (RSS). That RSS chunk is the overhead Postgres needs to run, things similar to what the old documentation put into the "Shared Memory Usage" table.

Most people find this information easier to track on a hot server using the top command. For Postgres top -c is recommended because it will decode what all the database processes are doing. top output from this server shows the big virtual memory block in the VIRT column:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 8025 postgres  20   0 4492412  36020  33992 S   0.0  0.2   0:00.15 postgres: 10/main: writer process
 8026 postgres  20   0 4492412  21412  19388 S   0.0  0.1   0:00.42 postgres: 10/main: wal writer process
 8028 postgres  20   0  175124   4396   2260 S   0.0  0.0   0:00.26 postgres: 10/main: stats collector process
 8024 postgres  20   0 4493760  62308  59052 S   0.0  0.4   0:00.65 postgres: 10/main: checkpointer process
 8029 postgres  20   0 4492724   4984   2840 S   0.0  0.0   0:00.00 postgres: 10/main: bgworker: logical replication launcher
 8027 postgres  20   0 4492816   6800   4552 S   0.0  0.0   0:00.14 postgres: 10/main: autovacuum launcher process

Dealing with shared memory on modern PostgreSQL and Linux versions is far improved from the old days when you had to endlessly tweak kernel parameters just to make a database run. There is still another level of work to support Huge Pages, which I'll demonstrate next time.