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
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.
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.
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
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
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
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,
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 kB
shared_buffers plus some overhead for
clients and other shared state.
You can see more about where the memory is going when using the
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
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.
June 11, 2021 •More by this author