PostgreSQL Tutorial: Calculate memory usage

January 16, 2024

Summary: in this tutorial, you will learn how to calculate PostgreSQL memory usage.

Table of Contents

PostgreSQL Architecture

PostgreSQL‘s architecture is based on three fundamental parts: Processes, Memory, and Disk.

The memory can be classified into two categories:

  • Local Memory: It is loaded by each backend process for its own use for queries processing. It is divided into sub-areas:
    • Work mem: The work mem is used for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables.
    • Temp buffers: It is used for store temporary tables.
    • Local cache: It is used for caching execution plans, relation and catalog information.
  • Shared Memory: It is allocated by the PostgreSQL server when it is started, and it is used by all the processes. It is divided into sub-areas:
    • Shared buffer pool: Where PostgreSQL loads pages with tables and indexes from disk, to work directly from memory, reducing the disk access.
    • WAL buffer: The WAL data is the transaction log in PostgreSQL and contains the changes in the database. WAL buffer is the area where the WAL data is stored temporarily before writing it to disk into the WAL files. When a transaction is committed, PostgreSQL will flush the WAL data to disk. This is very important to avoid the loss of information in the event of a server failure.

Linux process smaps file

The /proc/PID/smaps is an extension based on maps, showing the memory consumption for each of the process’s mappings. For each mapping (aka Virtual Memory Area, or VMA) there is a series of lines such as the following:

00400000-00914000 r-xp 00000000 09:00 3545633                            /usr/pgsql/bin/postgres
Size:               5200 kB
Rss:                 964 kB
Pss:                 214 kB
Shared_Clean:        964 kB
Shared_Dirty:          0 kB
Private_Clean:         0 kB
Private_Dirty:         0 kB
Referenced:          964 kB
Anonymous:             0 kB
AnonHugePages:         0 kB
Swap:                  0 kB
KernelPageSize:        4 kB
MMUPageSize:           4 kB
Locked:                0 kB
00b13000-00b14000 r--p 00513000 09:00 3545633                            /usr/pgsql/bin/postgres
Size:                  4 kB
Rss:                   4 kB
Pss:                   0 kB
Shared_Clean:          0 kB
...

The first of these lines shows the same information as is displayed for the mapping in /proc/PID/maps. Following lines show the size of the mapping (size); the size of each page allocated when backing a VMA (KernelPageSize), which is usually the same as the size in the page table entries; the page size used by the MMU when backing a VMA (in most cases, the same as KernelPageSize); the amount of the mapping that is currently resident in RAM (RSS); the process’ proportional share of this mapping (PSS); and the number of clean and dirty shared and private pages in the mapping.

The “proportional set size” (PSS) of a process is the count of pages it has in memory, where each page is divided by the number of processes sharing it. So if a process has 1000 pages all to itself, and 1000 shared with one other process, its PSS will be 1500. “Pss_Dirty” is the portion of PSS which consists of dirty pages.

Memory usage calculation

To calculate memory usage of PostgreSQL processes on Linux:

ps -u postgres o pid= | \
sed 's# *\(.*\)#/proc/\1/smaps#' | \
xargs sudo grep ^Pss: | \
awk '{A+=$2} END{print A}'
  • first, get the process PIDs of all processes running under user postgres
  • then get the name of the corresponding smaps file (/proc/PID/smaps) for each process
  • then get the Pss line from that file, which contains the “proportional stack size” which divides shared memory by the number of processes attached to it
  • finally, add up the numbers

See more

PostgreSQL Monitoring

Search

Categories

Tags