This is a technical investigation into memory allocation at the PostgreSQL and linux (memory allocation/malloc()) levels. Any comments are welcomed!
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 initialisation 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 area's.
This investigation is executed with a PostgreSQL server compiled from the source of version 11. I written a small PLpgSQL anonymous code block to do nothing else than filling an array, for the sake of allocating memory. This is the source code of that:
do $$ declare array text; counter int:=1; max_counter int:=5000; begin loop array[counter]:=repeat('x',16384); counter:=counter+1; if counter = max_counter then exit; end if; end loop; perform pg_sleep(60); end $$;
It really is quite simply looping over an array that gets filled with
repeat('x',16384), and once it finished
max_counter loops, it calls
pg_sleep(60) to have the array being allocated for some time for analysis. (and pg_sleep() can easily be interrupted with ctrl-c)
When I create a postgres backend by logging in with psql, it occupies 18M, but proportionally (because of the shared pages) 7.9M:
# smem -k | grep -e [P]ID -e 6197 PID User Command Swap USS PSS RSS 6197 postgres postgres: postgres postgres 0 5.5M 7.9M 18.0M
When I run the anonymous PLpgSQL block, The size increases because of the allocations for the array:
# smem -k | grep -e [P]ID -e 6197 PID User Command Swap USS PSS RSS 6236 postgres postgres: postgres postgres 0 85.1M 87.6M 97.9M
The memory allocations for RSS and PSS increased by roughly 80M, which is not shocking: the array data needs to be stored.
We can see the the PostgreSQL memory allocations via gdb, the GNU debugger by calling
print MemoryContextStats(TopMemoryContext). For version 11 this is the only way that I am aware that is reasonably convenient to get the PostgreSQL level memory details and sizes:
# gdb -p 6197 ... (gdb) c Continuing. ^C Program received signal SIGINT, Interrupt. 0x00007fe154a1d9eb in epoll_wait () from /lib64/libc.so.6 (gdb) print MemoryContextStats(TopMemoryContext) $1 = void (gdb) c Continuing.
The command prints the memory details in the PostgreSQL log file:
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, so the TopMemoryContext does not include the array allocation.
A little further in the TopPortalContext the allocation for the array can be found in 'expanded array', which shows 82267640 bytes (78.5M)in use. This is all logical and reasonable.
When the call to sleep expires, the anonymous block ends. This releases any memory allocated by the PLpgSQL procedure and thus the array. This is completely logical, and to be sure we can perform the same call to MemoryContextStats() to validate PostgreSQL level allocations:
TopMemoryContext: 67424 total in 5 blocks; 13888 free (13 chunks); 53536 used ... TopPortalContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used (no further sub allocations in TopPortalContext) ... Grand total: 1067136 bytes in 161 blocks; 289296 free (113 chunks); 777840 used
The grand total shows 1067136 bytes, which is approximately 1M which is allocated to the backend.
However: if we look at the linux level allocation with smem again:
# smem -k | grep -e [P]ID -e 6236 PID User Command Swap USS PSS RSS 6236 postgres postgres: postgres postgres 0 66.4M 68.8M 79.2M
There still is much memory memory allocated to the process, which is approximately 60M, for which we know from the PostgreSQL memory dump is not allocated by PostgreSQL anymore. Where did it go?
Luckily, there is some help here too. PosgreSQL 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. One of these diagnostics is the function
malloc_stats(). This can be called when attaching with gdb, and call that function inside the process using gdb, which is the same principle as the PostgreSQL function call to MemoryContextStats():
(gdb) call (void) malloc_stats()
This will print an overview from malloc, which sits in between the operating system and PostgreSQL as user land executable:
Arena 0: system bytes = 63832064 in use bytes = 917696 Total (incl. mmap): system bytes = 71811072 in use bytes = 8896704 max mmap regions = 3 max mmap bytes = 7979008
This explains the hiatus between the operating system reporting 70M and the PostgreSQL level memory dump saying it released memory down to approximately 1M!
What we see is 'Arena 0', which is roughly put the administration of memory allocations of malloc() for this process, which has allocated from 'system' 63832064 bytes (60.9M), whilst actually in use (by PostgreSQL) is 917696 bytes (1M). What malloc() tries to do, is keep memory allocated to prevent having to deallocate and allocate over and over.
The question is how this works further down the line; I tried running some simple SQLs to see if this would trigger malloc() to release more memory, which it didn't do. My idea was that that the simple SQLs showed the execution didn't need all of that memory anymore.
There are descriptions that can be found on forums that say that the memory not in use is marked with
madvise() calls. I don't know if that can be seen on the linux level; and if it can, if that would mean the
LazyFree should be set indicating there is memory still allocated that can be freed by the OS? (I also did not find madvise() calls being executed by the PostgreSQL backend that performed the PLpgSQL code, so it does not seem likely to me this happens; I validated this by breaking on madvise())
Based on what linux tells me in the smaps file, it seems the pages being private paged in pages cannot be reused. But I hope someone can tell me if this indeed is the case, the backend needs to stop to truly release the memory for reuse, or if there is something that I have not seen yet? I am happy to add it to this investigation!
smem utility can be found in EPEL.
There is another function that can be used inside a program that uses malloc (ptmalloc) to obtain the status of memory, which is the function malloc_info.
If you want to use this for PostgreSQL and trigger it to write to the PostgreSQL log file, perform calling the function in the following way:
(gdb) call (int) malloc_info(0,stderr)
This results in malloc level allocation administration results:
<malloc version="1"> <heap nr="0"> <sizes> <size from="65" to="65" total="65" count="1"/> <size from="209" to="209" total="209" count="1"/> <size from="369" to="369" total="369" count="1"/> <size from="785" to="785" total="785" count="1"/> <unsorted from="1041" to="23595025" total="62780003" count="19"/> </sizes> <total type="fast" count="0" size="0"/> <total type="rest" count="24" size="62914999"/> <system type="current" size="63832064"/> <system type="max" size="83521536"/> <aspace type="total" size="63832064"/> <aspace type="mprotect" size="63832064"/> </heap> <total type="fast" count="0" size="0"/> <total type="rest" count="24" size="62914999"/> <total type="mmap" count="3" size="7979008"/> <system type="current" size="63832064"/> <system type="max" size="83521536"/> <aspace type="total" size="63832064"/> <aspace type="mprotect" size="63832064"/> </malloc>
Which does not seem to show memory area's actually in use, but rather the malloc administration of the chunks.