June 14, 2024
Summary: in this tutorial, you will learn how to troubleshooting the backend memory usage in PostgreSQL.
Table of Contents
Introduction
A postgres backend functions as a standalone operating system process that is forked from the postmaster. This is actually a very memory efficient operation on linux, because a lot of pages can be shared between the postmaster and the newly forked PostgreSQL process. However, as soon as the backend process starts executing code itself to bootstrap the process initialization in general but for PostgreSQL in our case, it allocates and writes its own memory pages, which are unique to the process and remain paged in, and thus add to the RSS and are not shared.
The PostgreSQL processing memory areas are administered by PostgreSQL, which uses its own palloc() allocator, which calls malloc(), but provides some extra services, such as an administration of memory area’s. This is very helpful, because this allows diagnosis of these memory areas.
GNU debugger
PostgreSQL’s palloc is a hierarchical memory allocator that wraps the platform allocator. Memory allocated with palloc is assigned to a memory context that’s part of a hierarchy rooted at TopMemoryContext. Each context has a name.
You can dump stats about a memory context and its children via gdb, the GNU debugger by calling MemoryContextStats(MemoryContext*) function. In the most common usage, that’s:
$ gdb -p $backend_pid
(gdb) print MemoryContextStats(TopMemoryContext)
The output is written to stderr. This may appear in the main server log file, a secondary log used by the init system for before PostgreSQL’s logging collector starts, journald, or on your screen if you are running a backend directly without a postmaster.
TopMemoryContext: 67424 total in 5 blocks; 12256 free (7 chunks); 55168 used
...
TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
PortalContext: 1024 total in 1 blocks; 448 free (0 chunks); 576 used:
SPI Proc: 32768 total in 3 blocks; 11528 free (2 chunks); 21240 used
SPI TupTable: 8192 total in 1 blocks; 6544 free (0 chunks); 1648 used
PLpgSQL per-statement data: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
expanded array: 82299680 total in 5006 blocks; 32040 free (29 chunks); 82267640 used
...
Grand total: 83565536 bytes in 5233 blocks; 401536 free (148 chunks); 83164000 used
This shows the start of the MemoryContextStats() dump, where each memory context lists its own stats.
Furthermore, although PostgreSQL uses it’s own memory allocation logic via palloc(), but that calls malloc() eventually. malloc has some diagnostics that can be called to see what is going on. There are some diagnostics, such as the function malloc_stats() and malloc_info(). This can be called when attaching with gdb, and call that function inside the process using gdb.
pg_log_backend_memory_contexts()
pg_log_backend_memory_contexts() is a system function for instructing the server to log the memory contexts of the backend with the specified PID. pg_log_backend_memory_contexts() was added in PostgreSQL 14.
Example of pg_log_backend_memory_contexts() execution:
postgres=# SELECT pg_log_backend_memory_contexts(11727);
pg_log_backend_memory_contexts
--------------------------------
t
(1 row)
The relevant output in the PostgreSQL log file:
[2023-06-15 08:12:51 UTC] psql postgres postgres LOG: 00000: statement: SELECT pg_log_backend_memory_contexts(11727);
...
[2023-06-15 08:13:30 UTC] LOG: 00000: logging memory contexts of PID 11727
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 0; TopMemoryContext: 60528 total in 5 blocks; 16224 free (6 chunks); 44304 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; TopTransactionContext: 8192 total in 1 blocks; 6728 free (0 chunks); 1464 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; smgr relation table: 16384 total in 2 blocks; 4544 free (3 chunks); 11840 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; Portal hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; TopPortalContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; Relcache by OID: 16384 total in 2 blocks; 7616 free (3 chunks); 8768 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; CacheMemoryContext: 262144 total in 6 blocks; 128408 free (3 chunks); 133736 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; WAL record construction: 49776 total in 2 blocks; 6344 free (0 chunks); 43432 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; PrivateRefCount: 8192 total in 1 blocks; 2584 free (0 chunks); 5608 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; MdSmgr: 8192 total in 1 blocks; 7896 free (0 chunks); 296 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; LOCALLOCK hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
[2023-06-15 08:13:30 UTC] LOG: 00000: level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
[2023-06-15 08:13:30 UTC] LOG: 00000: Grand total: 658848 bytes in 38 blocks; 270616 free (32 chunks); 388232 used
Summary
This tutorial introduced us how to dump out the backend memory usage in PostgreSQL. It is really handy to help us quickly locate the cause of some “Out of memory” issues.