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 Committed_AS
=806928kB
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 kBshared_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.
Related Articles
- Postgres Partitioning with a Default Partition
16 min read
- Iceberg ahead! Analyzing Shipping Data in Postgres
8 min read
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read
- Loading the World! OpenStreetMap Import In Under 4 Hours
6 min read